Give Me A Coconut and Six Months
So You’re On A Deserted Island With WiFi and you’re still on the clock at work. Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?
In my case I have an advantage over many of my peers when it comes to the environment they administer. I created the environment. When I started my role as a SQL Server (and occasional Oracle) Database Administrator there was a single SQL Server with 13 databases hosted. We never had a DBA (the Server Engineers acted in that role as best they could). Therefore I was able to set the standards by which I live today. That is a blessing, but at the same time it is also a curse. Just because I’ve had full control over the SQL Server environment in my organization does not mean that I’ve structured it correctly. Many of the decisions I’ve had to live by were made by someone who was a Junior DBA in the role of a Senior DBA. This was on-the-job training folks and that is quite obvious to the DBA I am today looking back on the work of the DBA I used to be. In some cases it is as though I did inherit the environment from another DBA. However, my advantage is that the former DBA still works for the organization and I can “read” his work. His schedule also is compatible with mine so he’s always free for questions.
It is because of this that my “top hit list” may be different than those of you out there. The top three in order are…
Backups
I vow that if I had the time to get them configured I would finally start backing up my databases. “Durnit” (as Andy Leonard would say) it’s about time I start backing these things up!
Yeah, right.
No, what I mean by backups is that I would standardize the backup process across the environment. Currently I still rely on Database Maintenance Plans for my SQL Server 2000 instances, whereas I’ve Chong’d (rolled my own) backup jobs in SQL Server 2005. I did this mainly out of the shortfall with the RTM for SQL Server 2005 that did not clean up expired backup files from their associated folders when using Maintenance Plans. I found the processes I created for SQL 2005 are more dynamic, customizable, and compatible with the environment I support. I would take the time to retrofit these processes for SQL Server 2000 as well. The processes not only backup the databases, but also generate the cummulative restore scripts for each database since the last full backup and also script the backup process just in case a manual backup needs to be reissued due to a failure.
Security
Now that I have all this fictional free time I will stop using the sa login (with the requisite password of sa as well).
Again, I joke.
No, what I mean is that times have changed. The easy route is still being navigated by the poor choices and architechures of the past. Internally-developed solutions using SQL Server security instead of a tighter, but more fluid Active Directory (Trusted) security model need to be refitted; vendors who “require” SQL logins or elevated rights need to be ushered towards AD group rights and restricted rights.
Security does not end with users, logins, roles and the like. There is also the need to keep Little Bobby Tables out of your environment and that means protection against SQL injection. This requires so much more than that month of freedom though, particularly when you have over 1000 databases in your environment. I may need to ask for another five months at least!
Monitoring
This really should be my number one. I’ve given up on getting my Server Engineering team to set up SCOM properly for support of SQL Server monitoring. I’ve since moved on to using third party software for this critical task. The process for getting any new product is time intensive and frustrating as well; not because of the product, but because of the process. I constantly find myself in discussions with my young sons about their tendency to get frustrated because they are not instantly good at everything they touch. However, a little known secret that I can share with you because they are not all that into reading blogs at the ages of 8 and 10 is that it reminds me of that anti-pot commercial from the 80’s – they learned it from watching me. I need to power through the pain and dig into the product to configure monitoring for such items as CPU, memory pressure, file growth, low free space in the data and log files, and disk space concerns. I already have custom reports from SQL Server Reporting Services for such items as backup failures, SQL Agent Job failures, and security changes from the previous 24 hours, but I need to switch these over to on-demand monitoring reports.
Looking back on this list I need six months, better ask the boss for a year instead just to be sure. After all, I did not even get consolidation and patching on the list. Perhaps a trip to Hawaii and a three-hour tour on the Minnow with my laptop and air card. If I am resourceful I could build a generator out of a coconut and waste away the time taking care of these items. That or I could just work on my tan and get caught up on some sleep. Let’s see, to get this to work I’ll need to recreate the circumstances by which the Minnow originally got Lost… get me JJ Abrams and Sherwood Schwartz on the phone!
That being said, I am going to tag Tom LaRock, Brent Ozar, and Jason Massie to give up their list to the masses to digest. Granted, not all these fine men are active DBAs, but we’re really asking about what you would spend time on professionally if you could devote all your time to that/those tasks(s). Please link back to this post and send on the challenge to three other SQL Server professionals.