Voice of the DBA

Tag: syndicated

  • Backups after a DR Restore – MCM

    If you have a disaster, and you need to restore to some point in time prior to the last log backup you have, what’s the first thing you should do at this point in time?

    Make a full backup, of course.

    Actually anytime you complete a restore sequence, you want to do a full backup immediately before you turn users loose to do work. Why is that?

    Think about what happens if you complete the restore and then have another issue? You would have to go through the entire restore sequence again from before the first disaster. If that were a full backup, a differential, and a dozen log backups, would you want to explain that delay to your boss again?

    Would you even want to go through it?

    I wouldn’t, and it’s easy to prevent. Once you get done with your restore, start a full backup right away. You might never need it, but then again, you probably said that about the full backup you just restored.

  • T-SQL Tuesday Topics – Feb 2011

    I was looking for a static list of all the T-SQL Tuesday topics and couldn’t find one. The domain moves around to the current hosting blog, so when it was my turn to host it, I made a list just so I could reference what was out there.

    After a few more months, I decided to update my list with the latest topics. That will give me the chance to plan for my own future hosting of the party if Adam will have me again.

  • I make myself smile

    Sometimes, usually when I’m editing video, I see myself do something silly. Today was one of those times, and these two bloopers, kind of a spur of the moment thing while I was recording, came out funny.

    OldSQL

    My son thought this was pretty funny as well.

  • 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.