August 16, 2012 by Mike Hillwig
Welcome to the latest installment of How Not to be a Cranky DBA. I’m really having fun writing these.
There is something about the visual on this slide that has grown on me over the past several months. Each of the characters may look different, yet they’re all incredibly similar. That’s how I approach my SQL Server installations. They all serve a different purpose, yet they’re all built incredibly similarly.
I’m running two versions of SQL Server in my environment, one version of SQL 2005 SP4 and one version of SQL 2008 R2 SP1. They’re all the same build.
I have one backup script that I run on every instance. And I mean every instance. The parameters for the backup script are stored in a table, but the process itself is absolutely consistent.
I don’t play “Where’s Waldo” with anything in my environment. Backups live on the same drive letter for every instance. The same holds true with TempDB and the Windows pagefile. I’m an absolute nut for consistency and it has served me well in this environment. There are a few exceptions, but not many. And those are documented. Live the rule and manage the exception. Don’t do it the other way around. I’ve worked in environments where you lived the exceptions and managed the rule. It’s hell. Don’t go there.
The best part of my environment, where I’m pushing close to 50 instances, is that when I change a script, I only have to change it in one place. That’s because we’re leveraging the SQL Agent Multi-Server Administration process. It’s arguably one of the most powerful, albeit most unsexy pieces of technology in SQL Server. Most people don’t use it. Yes, there are some drawbacks, but when you learn how to work around them, it works wonders.
SQL Server gives us a lot of tools for managing consistency. I use the Central Management Server (Registered Servers) and Multi-Server administration. But don’t overlook Policy Based Management in your toolbox, either. If you find these insufficient, Powershell is always at your disposal as well. And while you’re at it, banish maintenance plans. I’m not saying they’re bad. I will, however, say that it’s hard to keep them updated across multiple servers. If you can script it, you can deploy it.