Voice of the DBA

Category: Blog

  • T-SQL Tuesday #15 – Automation

    TSQL2sDay150x150It’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.

  • ConFab Conference Scheduling – SQL Saturday Advice

    One of the things that’s hard to manage for all events is scheduling. Larger events print a brochure, and smaller ones tend to post schedules outside of rooms. Most SQL Saturdays also seem to give a printed schedule out in the bags in the morning when people check in. However a few events have found a better, way, at least for some users.

    Confab is an app for iPhone/iTouch/iPad that allows you to have a conference schedule online. I got a note that it was available for SQL Saturday #45 in Louisville, but couldn’t attend, so I didn’t try it. However I did use it for SQL Saturday #60 in Cleveland, allowing me to quickly and easily see what sessions were available at each time slot. I haven’t found an app for Android or Blackberry, or WP7, but if anyone knows of one, please let me know.

    I know the printed schedules are nice, but those can be out of date, especially for last minute cancellations. Also, they’re not as convenient to me, someone that always has my phone, but hates digging around in my laptop bag. The SQL Saturday site is slow for the schedule, and hard to read on a small device.

    I’d consider using an app for scheduling, and hopefully we’ll get some for other platforms as well.

  • Rolling to SQL Saturday #60

    I’m still stunned as I see numbers above 50 in the hash tags for SQL Saturdays. Yet here we are, #60 in Cleveland this weekend and I’m on the road this morning. Or in the air, catching a flight to Cleveland from Denver.

    I’ve never really been to Ohio, only having driven through the state on my way to someplace else. Stopping for gas and coffee doesn’t count as a visit, so I’m looking forward to this. Allen White, a friend I’ve known for years, will be my host, along with a number of other SQL people I’m looking forward to seeing again, or meeting for the first time.

    The event is sold out, which is a good thing. It shows interest in the free training and I hope this means that a few of you will help out and get another SQL Saturday in Cleveland sometime in the fall.

    I don’t have any great plans of things to see, other than run with Allen on Sunday morning. If any SQL runners are out there, feel free to join us, and follow the #sqlsat60 tag on Twitter. I’ll post some info about where/when as I get it.

  • SQL Saturday Advice–Upload Decks Early

    One of the neat features for the SQL Saturday site is that it allows the speakers to upload a deck that is attached to their session. If you go to the schedule after the event, and pick a session you attended, you can get the Powerpoint deck, the code, anything the speaker has uploaded. That’s handy, and too often I’ve been at events where speakers point you to some location they use and it’s easy to forget where this is, or where the information is stored.

    However the organizers have to send this note to speakers, and in most events, this has come after the event. I’ll admit that sometimes I’ve been busy and have forgotten to upload the deck, or I’ve been slow to do so. However Saturday night, Sunday, or early the next week is when the attendees are thinking about the event, and if the deck isn’t there, they might not go back.

    This week I got the link early for SQL Saturday #60 in Cleveland. I was changing a couple slides in the presentation, based on feedback from previous events, and was a day late in uploading, but I have finished, and my deck is uploaded. Now I don’t have to worry about it, and the link is live, people can get the deck for themselves.

    I know some speakers are working on their decks at the last minute. I don’t think that’s a great idea, but I understand people have busy lives and they might be trying to add something on Friday that they learned recently. Or they remember to correct something from the last session as they’re reviewing the night before. That’s OK, and the organizers can always replace the code with a new download.

    I do think, however, that most attendees would appreciate having the downloads available right away, and by sending the upload link to speakers before the event, you can improve the chances you’ll have all the downloads uploaded and ready.