Voice of the DBA

Tag: mcm

  • T Minus Three Days to the MCM Test

    I take the MCM test on Friday. I’ve been watching videos and reading white papers and blogs for the last few weeks and I know that I don’t necessarily know enough. To some extent I have an advantage since I test well on multiple choice tests and I have a broad set of knowledge for SQL Server.

    rockholesBut I don’t know enough. The more I study, the more it becomes painfully clear how much more there is to SQL Server that I don’t understand. Each day I learn something new, but I also learn that there are things I wasn’t aware of

    Often, I find that things I just learned are things that friends already know. The thing that gives me some hope is that they will learn something and tell me, not realizing I already know about it.

    We all have these uneven holes of knowledge, and just because we do, or don’t, know about feature X, doesn’t mean that we don’t know a lot about feature Y. We have un-evenly learned SQL Server since there isn’t a set path on which to learn.

    So I’m nervous, just because it’s a measure of my skill, not because it matters to my career. It will give me a benchmark, and a place from which I can make a plan for how much more I want to learn about SQL Server, and in which areas to work.

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

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