Tag Archives | SQL

POLL: Which SAN vendor have you had the best “SQL” experience with?

First and foremost, I want to see a totally anonymous poll that shows which vendor is the most loved by the SQL community. At the same time, if you would be kind enough to carry on a dialogue through the comments then I believe everyone would benefit. Choosing or replacing a SAN vendor requires getting past all the marketing fluff and down to the stuff that really matters. I hope that I can start a little momentum in that direction with this poll. Benchmarks and reviews are great if you have links to share. I want to know your bad experiences as well as the days when you were glad you chose a certain vendor.

 

Here are a few links to add to the discussion. If you have additional links feel free to add them.

http://www.brentozar.com/sql/sql-server-san-best-practices/
http://www.windowsitpro.com/article/hard-disk/hybrid-ssd-solutions-142189
http://www.storageperformance.org/home/
http://www.ideasinternational.com/Home

 

Comments { 0 } Posted on June 7, 2012 in SAN, SQLServer, SQLServerPedia Syndication

Beginning SQL Joes 2 Pros Giveaway

My Books

Laptop, stickers, and artsy fartsy photo filter from camera+ not included :-(

If you haven’t heard about the excellent giveaway that Pinal Dave (Blog|Twitter) is putting on then you are missing out. I had a little more time in the beginning to participate so I was very fortunate to receive my prize yesterday. I remember reading Pinal’s review last year and wanting to check it out for myself. I’ve got a few of the series on the Kindle and I highly recommend it to those starting out with SQL Server. Pinal also did an interview with Rick A. Morelan (Blog|Twitter), who authored the books and I would encourage you to check that out as well.

Don’t forget to head on over to Pinal’s blog and get in on the contest as there is plenty of time left to learn more about SQL and possibly win something in the process.

I’m starting to be more vocal about getting my readership up on the blog, so I’m going to start asking more questions about how I can improve things. Please feel free to add comments in the meantime if you see something that I’m doing that isn’t working or a way that I can reach more people. Ideas for posts are always welcome as well.

Comments { 1 } Posted on August 18, 2011 in SQL, SQLServer, SQLServerPedia Syndication

Deleting Data in Small Chunks on SQL Server

Chunky Shirt

I’ve had this post on the backburner for a bit of time, but Glenn Berry (Blog|Twitter) recently posted on gradually deleting data in SQL Server and that made me think of it again. I would advise looking at his post for guidance on how to effectively clean up your data in chunks, but I want to give you a little more background on what made me want to post this.

For some time, I’ve been using the following code to perform the task:


SET ROWCOUNT 10000
--DECLARE @Text varchar(255)

--SET @Text = 'Deleted 10000 rows'
DELETE  FROM [database].[dbo].[table]
WHERE   DateChecked < '2011-01-01'
WHILE @@rowcount > 0
BEGIN
DELETE  FROM [database].[dbo].[table]
WHERE   DateChecked < '2011-01-01'
--PRINT (@Text)
END
SET ROWCOUNT 0
GO

So when I answered a post on ask.sqlservercentral.com that seemed to be fitting for my answer, I got the opportunity to learn a few things that I wasn’t aware of. Pavel Pawlowski (Blog|Twitter) pointed out that it’s better not to use SET ROWCOUNT.

On SQL Server 2005+ it’s better to use DELETE TOP 10000 instead of SET ROWCOUNT 10000. Also you can read on MSDN SET ROWCOUNT (Transact-SQL).

He mentions SQL Server 2005 and later, but what about SQL 2000? Does anyone have a preferred method when dealing with SQL Server 2000 that’s different? Is there a one size fits all?

Comments { 0 } Posted on August 16, 2011 in SQL, SQL Server 2000, SQL Server 2005, SQLServer, SQLServerPedia Syndication

I cheated on SQL Server

It’s hard to believe that it has been roughly 2.5 years and 45 posts since I started this blog. I don’t need to do the math to see that I should be blogging more, but in reality…I’m not sure how much I can do at this point in my life. I have two young children that are going to know who their daddy is. I have to support my family so my full-time gig is what keeps things going. That’s where this story began a few years back, with a passion for learning and helping others along the way.

So I guess you may be wondering what I mean when I say that I “cheated on SQL Server”. Maybe the image of a teenager with “SELECT *” on the palm of his hand during an exam comes to mind (right), or being unfaithful to our better half. Well, I haven’t cheated on anyone or anything but I am ashamed of what I have done. I almost let a great deal of hard work and effort go by the way side as I lost track of my vision. A few years back I took a job where I was handed some DBA material and asked to see if I could do some performance tuning and get things to stop crashing. What I saw in the days after that could only be described as an enlightened view into a community different from most others. I was fortunate enough to find a few really bright and passionate individuals who helped me discover the path I was about to take. This list isn’t comprehensive but a few of those individuals:
Brent Ozar (Blog|Twitter)
Thomas LaRock (Blog | Twitter)
Brad Mcgehee (Blog|Twitter)

I wish I could include everyone but there were so many that had a passion for what they did and wanted to help others follow the same path. I started the journey to go from systems administration to “Exceptional DBA”. That meant reading everything about SQL Server that I could get my hands on, getting certified to put a time sensitive deadline as a goal, and starting a blog (“Becoming A SQL Master”) to start giving back to the community. On top of starting the blog, I also began going to forums and try to answer questions by researching it or seeing if I could reproduce the problem. So thus began the long nights of studying SQL Server 2005 when the only servers in my environment were SQL Server 2000. With an infant son, it was quite the struggle to stay dedicated to the goals I had set but I still had my dedicated “home office” at the time so I could close the door and hammer it out. I was tired of being a “Jack of all trades, master of none” I saw that the true experts are the ones who were in demand and could write their own ticket. It sure wasn’t the guy who knew a little bit about everything, and that’s the bucket I fell in.

In Little Rock, there aren’t a ton of junior level SQL Server DBA positions, so the waiting game was long and taxing. I had opportunities in other locations, but I couldn’t pick up my family again and move. I had a couple of contract-to-hire positions come up, but at the time I didn’t feel comfortable enough in my abilities to take that risk.

So if you have made it this far you are either really bored or you’re a family member so I’ll get to the cheating.

Sometime around January of this year (2011), I decided that it just wasn’t going to happen for me. Maybe I was just destined to be a Systems Administrator…not that there’s anything wrong with that and the good Lord may put me back in that role someday. I even went so far as to change the title of the blog to “A hodgepodge of Information Technology and Life” so that I could write about other topics and broaden my reach. At the time of the change I had 19 subscribers on my feed (don’t laugh, as I thought that was a start), but after feedburner got everything sorted out it was down to 4 subscribers. I began to get more involved in Active Directory, PowerShell, Group Policy, and all things IT. I tried to get involved in different communities, but to me there just wasn’t something like the SQL Server community. I began to miss the thing that had encompassed so much of my time and I enjoyed it.

Then it happened…conversation started with a consulting firm that had contacted me earlier but I didn’t feel confident enough to take the risk. This time was different. I had the knowledge, I had the passion, and I had 4 subscribers…I could do this. I’m not afraid of working hard and proving myself and that’s what I have to do. I left a very secure job (nevermind that I was out of room for advancement), to take a contract-to-hire job. The goal is to get hired on full-time. The goal is to start a PASS Chapter in Little Rock. The goal is to become an MVP. The goal is to never lose a passion for being the best that I can be while being unselfish in helping others who want to do the same.

I’m back with SQL Server and it’s pretty exclusive. I’m throwing away my little black IT book and going back to full-fledged SQL Server. Hopefully I don’t get off track again for some time.

Comments { 2 } Posted on July 22, 2011 in SQL, SQLServerPedia Syndication

Where’s the page file when you need one?

I’m not sure how I missed this one given that I do quite a bit of reading online about best practices. My intentions were noble. I wanted to give my SQL Server the perfect environment so that it could flourish and thrive.  I wanted to allow the latest and greatest Microsoft OS (Windows Server 2008 R2) to shine on its pretty new C: drive. What I learned in my attempts at greatness is that I dropped my guard. I didn’t have the iron-clad check list that I thought I had. So now, it’s back to the drawing board to come up with my ideal checklist and installation guide for the Operating System and SQL Server 2008 (post date TBD). And if you are really curious about my fatal mistake that cost me a day and a half before I gave up trying to bring the server back up read on.

For the 3rd time, I’ve had a Dell R900 with SQL Server 2008 and Windows Server 2008 R2 start dropping iSCSI drives. When you believe you’ve properly laid out your separation of duties for SQL (E:\SQLData, F:\SQLLogs, T:\TempDB, and X:\SQLBackup) one would hope there wouldn’t be more problems. But for some reason, these ingredients mixed together have caused me quite the headache. I’m not giving up on Windows Server 2008 R2 just yet, as I’m trying to learn from my mistakes and kick this problem’s tail. There are two crucial mistakes I made this time which caused me more downtime and gave me more work to do in order to get back to an operational state. I listed four drive specifications above that I have used for everything but the OS, SQL Install files and Shared Features, and OS Paging File(s). I have had a practice of doing the following:

  • The OS on the C: drive.
  • The SQL Binaries on the D:\ drive (minus the shared resources that have to go on the C: drive).
  • Remove the paging file from the C: Drive, and place a paging file 1.5 times the RAM on the D: drive.

Maybe some seasoned veteran is reading this and laughing at me, and I’m sure I deserve that after the blunder I made. Because I didn’t have a page file of any size on the C: drive, I was unable to analyze the .dmp file to find out what exactly was causing Windows to go into a reboot cycle. I had lost one of the iSCSI connections so SQL was dead because it kind of needs that Tempdb thing. So I panicked and rebooted, but this time the Logs drive didn’t come up. So I started having deja vu from my previous experience with Windows Server 2008 R2, iSCSI, and SQL Server 2008 only to remember something in my mind thinking that a patch might have caused this since I had been running fine for a few days. So I uninstall the patch that was put on earlier that day (slight possibility that it was SP1 but I don’t believe so), and it goes to reboot. Now we are in an endless cycle of reboots, and there’s no turning back. Oh, but let’s count the positives that came out of this. I now have a few future posts in the works (when I can find the time with two young children) ranging from the Microsoft Disaster Recovery Toolset, Operating System Installation Guide and Checklist, a SQL Server Installation Guide and Checklist to recovery options when you have hit a wall.

Let’s get back to the biggest lesson learned here. You really do need a paging file on your boot volume. Coming from the Windows side of things the best document I could find was a technet post by CC Hameed it suggests RAM + 1MB for a complete dump. For something specific to Windows Server 2008 R2 you can look at at these Memory Dump Options.  You can also learn how to generate a dump file in Windows Server 2008.

From a SQL Server side, that’s probably not realistic. And you’ve got to look at what your physical resources are as well. I would point you to a great article by Buck Woody (blog|twitter) entitled “The Windows Page File and SQL Server”. I hope to write a follow-up post with an example of this process from one of my servers.

So the takeaway from today is not to end up with the inability to diagnose your problem by not having a paging file on your boot drive. How you determine to size that paging file is going to be up to you and your system. Use the resources I’ve listed and please include new ones that you find.  Feel free to add any experiences that you have had that you can’t believe you made or gotchas that others can learn from.

Comments { 2 } Posted on March 1, 2011 in SAN, SQL, SQLServerPedia Syndication, Windows Server