Voice of the DBA

Tag: sql server

  • A Full Backup Includes Everything (with a caveat)

    Full database backups in SQL Server include all of the data, objects, tables, rows, functions, stored procedures, etc. If something is in the database when the data reading portion of the backup concludes, it’s in there.

    Note that “in there” means committed in a transaction.

    If someone tells you the backup missed a row, or a procedure, or something else, they’re almost always wrong. 99.9999% of the time they are wrong, and you should stand by that.

    The issue is that things must be committed in the backup. If they aren’t committed, they aren’t included. And that means that there’s a small chance that something added to the database while the backup is running isn’t in the backup.

    If you remember how backups work, there’s a data reading portion of the backup and a log writing portion of the backup. The log writing portion of the backup takes a physical amount of time. If someone were to finish a transaction during this time, the data or objects would not be in a restored database. This is because the transaction didn’t exist or wasn’t committed when the data reading portion of the backup completed.

    If the data writing portion of the backup takes a few minutes, and a change was in the last minute or so of the process, someone might think something completed before the timestamp on the backup file is included. It wouldn’t be.

    It’s a small chance, and it’s not likely to come down to this point, but it could happen. Can you figure out the transactionally consistent time of the backup? Perhaps, but I don’t know how. You’d need to get the time for the last LSN written in the backup and map that to a time. If you know how to do that, let me know.

    Make sure that your backups are transactionally consistent. Don’t export, don’t use open file managers, don’t use anything that doesn’t respect transactions. The native SQL Server backup process does this. If you want a few other features, my employer makes SQL Backup Pro, which also respects transactions.

  • The Future of Auditing

    I was reading Captain’s Share the other day and enjoying a quiet afternoon at home. It’s a science fiction book about one man’s journey in the future as a captain of a space freighter. It’s an interesting series from Nathan Lowell that I’ve enjoyed and recommended to other science fiction fans. In the book, there’s a scene where the main character is leaving his old ship as first mate and moving to a new ship as the captain. However he notes that the formal process is to deactivate his records on the old ship and ensuring they will be read only forever. The book notes they can’t be deleted because they are a part of the ship’s records, log entries, etc.

    That seems to be a far cry from the way auditing takes place in current computer systems. Auditing of systems is under the control of the sysadmins (who are sometimes hackers) and can be altered, changed, etc. We, as software designers, haven’t done a good job of ensuring the integrity and longevity of log records. In some sense, it seems to be a fundamental flaw in OS and software design to not have separated out the auditing and recording of actions from the administration and rights of the rest of the system.

    I’d hope that we would recognize that auditing actions and preserving this data is something that ought to be tightly linked to, but separate from, the rest of system operation. I’d like to think that fundamental changes and actions taken on the system should be written separately to an area that is easily marked as readable by non-sysadmins that are designated to review the information. I know we have the challenges of managing the space and the problems of spurious actions being generated to fill (or rollover) logs, but I’d think after 50+ years of computing we would have considered some sort of event log that isn’t under the control of the people whose actions it is recording.

    SQL Server has improved its auditing features and capabilities, but far too much is still linked invariably to the sysadmin, often the same person the auditing should be watching. This is certainly one area that I hope matures in future versions as the need grows to track and review actions taken by privileged accounts.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 19.5MB) feed

    MP4 iPod Video ( 23.7MB) feed

    MP3 Audio ( 4.6MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Accidently Kicking a Database into the Restoring State

    I learn new things all the time. This was one that actually stunned me. Huge props to Gail Shaw for posting a note about this in a thread.

    Run this code: 

    CREATE DATABASE MyRestoreTest
;
GO
USE MyRestoreTest
go
BACKUP DATABASE MyRestoreTest TO DISK ='myrestoretest.bak';
GO
CREATE TABLE mytable( id INT)
;
GO
USE master
go
BACKUP LOG myrestoretest TO DISK = 'myrestoretest_log.trn' WITH norecovery

    You’ll see this in your Object Explorer

    backuplog

    Ugh.

    I haven’t started a restore. I’ve run a backup. Apparently this causes problems, as noted by Gail in the thread. Sure enough, it’s documented in the BACKUP command, in the Log-specific Options.

    I had never scheduled backups with this type of option, but you might have a job that does this if you were preparing for a failover. Having a script ready it a good idea, but if it executes unexpectedly, this could happen.

    The lesson: make sure you know the options when you run a command. Always test, and if something strange happens, search or ask what might have happened.

  • Is Your Data Relational?

    When should you use MongoDB? I’ve asked that question a few times, and I haven’t gotten a good answer I understand. I’ve read about a few of the NoSQL, document databases, and I think they do have a place in the world. There are domains of problems that they solve better than relational databases. I’m sure that streaming databases, graph databases, columnar databases, and more have places where we they shine, but I still believe that many, perhaps most, problems are best solved by relational databases.

    I saw this piece about MongoDB being a poor choice by the Diaspora developers because they hadn’t modeled their data well. It’s a decent analysis of a real situation, and I think it’s one that has probably been repeated many times by many developers that were intimidated, frustrated, or otherwise turned off by relational platforms.

    There is a cost to using a relational platform, and it can require expensive developer time to map objects to relational structures. However the solution isn’t to abandon the relational platform. The better solution is likely to train developers and hire a few DBAs that can help with the mapping and queries to solve problems.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( MB) feed

    MP4 iPod Video ( 17.0MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center