It’s that time of the month again, and this month Pat Wright and his SQL Asylum are hosting the T-SQL Tuesday #15 party.
If you want to learn more about the party, read Adam Machanic’s intro post (Blog|Twitter). If you’re interested in hosting from your blog, contact Adam.
Automation is Crucial
I have worked in a number of different sized environments in my career. From single server, 3 person companies, to 1 many-hundred instance, 12,000 person organization. Surprisingly in most cases, the size of the DBA team hasn’t varied tremendously. While it’s often been just me at smaller organizations, at the largest company we only had 3 DBAs to manage hundreds of SQL Server instances.
How did we do it? You probably know: automation.
In every environment I’ve worked, my goal has been to be insurance for when things break. That means that all the routine tasks need to be handle by someone or something else. Preferably by SQL Server itself since it’s usually more reliable at the tedious routine tasks.
In the past, mostly SQL Server 2000 and before, I heavily used custom scripts, an administrative database on each instance, SQL Mail, and SQL Server Agent to perform regular maintenance activities, monitor jobs and processes, and summarize the results for the DBAs. My goal in all automation is to log the things that did happen, and only alert the DBA for items that need his or her attention.
More Complex and More Capable
When SQL Server 2008 was being developer, and new features were being introduced, I was pleased to see that a number of changes were made that I thought would help make automation easier. Policy Based Management, and the Powershell links to SMO objects, along with the new XEvents, Change Data Capture, the Data Collector, and more, there are many more hooks and ways to use automation to manage and monitor your SQL Server.
However there also is a tremendous amount of complexity in using these tools. PBM sounds easy, but once you start creating policies, you can easily end up with hundreds of policies that result in an environment that is harder to understand.
Watch the Watcher
The ultimate goal in automation is to handle tasks in a more efficient way, using code and scheduling applications. This should result in less work for the DBA, but it also means that the DBA might count on certain tasks being completed, which might not get done.
No matter what method you choose for implementing automation, you need to ensure you have some independent scripts and checks running to check on your automated tasks. An independent set of automation checks should be available to ensure your tasks run when scheduled, and they perform the tasks that are needed. While you might have a job to defragment indexes, you might have a second job that checks the fragmentation levels and independently notifies the DBA if the levels get too high.
You also need to distill information down to the essence of what is important for the DBA as well. Too much routine information dulls the DBA’s senses, and makes it harder for them to find out what is broken in their environment. While all actions need to be logged to analysis and auditing, only those actions truly requiring the DBA’s attention should be raised on a regular basis.
This sounds like very general advice, without a lot of detail, but the reality is that the balance to strike here will change with each environment. Usually I have had to spend months slowly tuning my automation and monitoring until I eliminate notification of the routine items, and only get notified of the critical “fires”.
Which means that I don’t need a notification every day. The lack of a “look at this” note is hard for many DBAs, myself included, to get used to.
The last piece of advice for implementing automation is this. Don’t try to account for every situation in your system. It results in too many code branches, and too complex an automated system. Handle the 80% of routine tasks and let the 20% anomalies fall through to the DBAs. It means a simpler system to build, and maintain.
