Welcome to this week’s installment of SQL University. I am writing this while in transit to the 2011 MVP Summit. Thankfully (?) Delta could not get an airplane out of the hangar on time at MSP this morning, which led to my 3 hour layover in the Sky Club and several glasses of wine and ultimately this post. The topic is on ‘Administration’, let’s get started!
tempdb
Administration, it’s the ‘A’ in ‘DBA’. It is a lot like being a mechanic, especially if you are the type to call your mechanic in the middle of the night to tell him that the engine is making a rapping noise. And it is also like being a mechanic if your mechanic is the type who works a pit crew for a racing team in his spare time (well, mine does at least, because I know how to recognize a quality craftsman when I see one).
To me, ‘administration’ has a simple definition: I am responsible for keeping the database instances available, for ensuring proper recovery in the event of disaster, and for helping people move data in and out as quickly as possible in order to meet business needs. Note how my definition does not mean that I believe it is the role of a DBA to write stored procedures, or to design a database, or to build SSIS packages. And while some DBAs certainly do perform those functions in their respective shops, it is not my belief that such duties should fall to a DBA by default.
I do believe, however, that a good DBA will be able to assist developers in writing better queries and stored procedures, to recommend better database designs when appropriate, and to help people get data in and out in general. I guess I try to draw the line between ‘administration’ and ‘development’. If you want me to be a database developer, fine, but you cannot expect one person to stay on top of the code and ensure you have proper backups in place. At some point that DBA will be stretched too thin, and mistakes will happen or deadlines missed.
msdb
At this level you need to be getting your job done efficiently. And to me that means one thing: standards. You should have a standard build outlined for a database server, for the installation of MSSQL, and for basic performance metrics (the DBA Trifecta!) This was important to have as it greatly reduce the number of headaches for me when trying to troubleshoot issues. For example, if a person could not connect to a server, I never had to examine the authentication mode for the instance because I already knew what it was set to.
I used Operations Manager to ensure quality of our boxes for the most part, and I started using PAL to ensure a performance baseline as well. This was especially important for a virtualization effort, I didn’t want a VM handed over to me that would not meet some performance standards before I turned it over to the end users.
Do yourself a favor and start putting together your standards as well, they really will save you time in the long run.
model
Automation is key at this level, you need to have a way to get common tasks done against multiple servers. I mentioned the use of Operations manager already, as that helped reduce my need to even run scripts to check on my boxes for certain basic information. But you need to do more than just monitor, you need to have a way to either get information from every server in an ad-hoc fashion, or you need to have a way to make a change to every server (or a subset of servers) in an efficient manner.
I won’t advocate any one way over another here (but you should use Powershell for this). If you want to use SQLCMD, go ahead (and use Powershell instead). If you want to configure a Central Management Server and use SSMS, go ahead (and use Powershell). If you wanted to use VBScript and Operations Manager you could do that as well (which I preferred at one time over Powershell because that’s how I roll).
The bottom line is this: at some point you may need to be the admin for more than six servers. When the time comes, be ready to be efficient (and to use Powershell).
master
At this level, you control everything. You are truly the master of your domain. You have standards in place. You can push out changes to all servers easily. You have a way to verify that your backups are complete and able to be restored if necessary.
And now is the time when you can start being more proactive, and address minor issues before they become big problems. Making the shift from being a reactive DBA to being proactive is the mark of a true master, in my opinion.
resourcedb
- Buck Woody (blog | @buckwoody)
- Aaron Nelson (blog | @SQLVariant)
- Cindy Gross (blog | @sqlcindy)
- Denny Cherry (blog | @mrdenny)
2 thoughts on “SQL University – Administration Week”