SQL Database Continuous Availability

Failover Clustering and Mirroring

Server clustering is out with the scope of a storage site but is a critical part of providing full database failover. In brief, a cluster consists of between two and eight servers, all of which can access all the storage devices and the customer network. If a database server fails, then all the applications that were running on that server fail over to one of the other database servers in the cluster.

When server clustering is combined with database mirroring you achieve complete and automatic failover from both server loss and disk subsystem loss. If you combine these together in a stretched fabric SAN that spans buildings, you are also protected from building loss, at least in theory. Database mirroring works by writing the transaction log to both a source server and a destination server. If the primary server is lost then applications can reconnect to the secondary server database with only a few seconds delay. Database mirroring does not require any special or certified storage devices or servers though it does need a 'witness instance' to spot the failure and instigate a failover.

Database Snapshots

The ability to take database snapshots came with SQL Server 2005. Database snapshot is the same in principle to disk based snapshots, except that with disks the unit of data is a block, whereas the unit of a database snapshot is a page. A snapshot is a point-in-time copy of a database that can be used for rapid backups, or for testing purposes. The initial copy is made by creating a set of pointers that refer to the pages in the original or source database. As the source database is updated, the original pages are copied to the snapshot, thus preserving the point in time condition of the snap without having to copy all the data initially. A user initiated database snapshot is implemented by using sparse files, an NTFS feature. Initially a sparse file contains no user data and has no disk space allocated. This process is often called copy-on-write.

SQL Server creates its own database snapshots when it issues some DBCC commands like DBCC CHECKTABLE. These snapshots are implemented using sparse alternate data streams.

• Clustering must run on certified hardware while mirroring can run on any hardware
• Mirroring provides a redundant set of data but clustering does not
• Clustering failover is totally transparent as the IP address is preserved on failover. Mirroring auto directs services and so can have a slight impact

A database snapshot is consistent. There is no need to quiesce or stop the database before taking a snapshot as SQL Server ensures consistency of the snap copy by rolling back any uncommitted transactions at the point the snap was taken. Transactions are applies as normal to the source database. Because a snapshot database gets pages that have not been updated from the source database, the source database must be available and both databases must be in the same server for the snap to work.

A database snapshot is read only. From the user's point of view the snapshot database always looks unchanged because reads always access the original data at the point of snap. If a page has not yet been updated on the source database, then a read operation on the snapshot reads the original page from the source database. If a page from the source has been updated, then the original page will be copied to the source file before the update is written to disk. So after a page has been updated, a read operation on the snapshot still accesses the original page, which is now stored in a snapshot file.

A database snapshot does not use much space. Well, that statement is not exactly true. Initially a snapshot will use hardly any space, but if your source database has a lot of update activity then potentially the snapshot can grow to be the same size as the source. As more and more updates happen in the source database, pages are added to the snapshot database. Sparse files grow in 64KB increments, and if there is no room on disk to add an extra 64KB the snapshot will be marked as suspect and has to be dropped. None of this affects the source database, unless it is competing for disk space with the snapshot. It is a good idea, especially for a busy database, to have a process for renewing snapshots at regular intervals to prevent them from using up too much disk space. It is also useful to monitor a database before a snapshot is taken, so you know how much update activity to expect and can get the snap sizing correct.

A database snapshot is dependent on the original database. If that database becomes unavailable then the snapshot is unusable. However is a source database is damaged due to user error, it should be possible to put the source database back to the point where the snap was taken, by using the updates from the snapshot. This does not mean that you do not need to take backups, backups are needed to protect against physical failure.

back to top

By entering and using this site, you accept the conditions and limitations of use