Voice of the DBA

Tag: sql server

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

  • A Full Backup can Impact a Log Backup–MCM Prep

    In the early days of SQL Server you could not run a log backup while a full backup was running. In fact,you tried to schedule them apart from each other early in my career to prevent collisions. Nothing bad happens, but it does cause failures in your monitoring and those are annoying.

    In modern versions of SQL Server, you can run log backups and simultaneous full backups. They don’t collide or block each other, but there is a way that the full backup impacts a log backup.

    If you start a full backup, once all data pages are written to the backup device, the log records that were created since the start of the full backup are added to the full backup file. This allows the redo/undo process to complete and this gives you a full backup set that is intact as of the point in time in which the data pages finish writing.

    If you run a log backup, typically the log records are written out, and then the VLFs in the log file that were written to disk are marked as inactive.

    However since those log records are needed for the full backup to be complete, the log backup cannot clear those VLFs when the log backup finishes. That process still occurs, and technically this is part of the log backup, but it is deferred until the full backup completes.

  • SQL Server Recovery–MCM Prep

    I always have to think about what happens during the recovery process in SQL Server after a restore. I’ve always known the process as roll forward and roll back, which is how the items are noted in the SQL Serve error log. Transactions are rolled forward or rolled back.

    But which comes first?

    If you think about it, you want to roll forward the committed transactions, which were not written to the data files. Then you roll back the changes made to the data files which were not committed. How you get transactions in these states is for another day.

    However I found an easier way to remember. While studying for the MCM, I heard these processes referred to as redo and undo, which apply to roll forward and roll back, respectively. If you use these terms and place them in alphabetical order, you get redo, undo.

    Or redo before undo.

  • Backing up the log in simple mode

    Someone posted a note saying that their backups were really large and slow. So they were trying to manage their backup scheme and ensure some level of recovery.  I was going back and forth, along with others, talking about the way your recovery would go and the merits of that particular backup scheme. While posting, the person mentioned at one point that his databases were in simple mode.

    Huh?

    AFAIK, or rather, As-Far-As-I-Remember, you can’t do a log backup in simple mode. I think that’s been a part of the product, but before posting, I decided to test.

    Short answer, if you don’t want to read, is that you can’t backup a transaction log if the database is in simple mode.

    First I grabbed a sample database on my local 2008 instance and set it to simple mode. Actually, first I had to set a database to simple mode, and I didn’t want to use the GUI. This isn’t something I do often, so I had to look up the syntax:

    ALTER DATABASE SET OPTIONS – The first example shows setting to full recovery mode.

    ALTER DATABASE db2 SET RECOVERY SIMPLE

    Once this was set, I created a few transactions and ran a backup.

    CREATE TABLE MyTable (id INT)

INSERT MyTable DEFAULT VALUES
    BACKUP LOG db2 TO DISK = 'db2_log_test.trn'

    This gives me the following error:

    Msg 4208, Level 16, State 1, Line 2

    The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

    Msg 3013, Level 16, State 1, Line 2

    BACKUP LOG is terminating abnormally.

    This is what I expect, as there is no backup chain that can be used to build a backup. What about running a full and then a log right away?

    BACKUP DATABASE db2 TO DISK = 'db2_db_test.bak'
BACKUP LOG db2 TO DISK = 'db2_log_test.trn'

    Once again, the error occurs, and there should be no checkpoint after the full backup starts.

    db_simple_log

    So I think that my posted is mis-informed of his environment, which is a dangerous place for a DBA to be, especially with regard to backup and recovery.