High Availability with Log Shipping
Log Shipping is a long standing technique for High Availability. It is not unique to SQL Server. SQL Server does have Log Shipping built into the Enterprise and higher editions of SQL Server as a failover technique.
Log shipping uses database backups as a technique to failover to another database server. A backup server has the database restored from a full backup, but the restore is not finalized, allowing additional restoration of transaction logs. As a transaction log backup occurs, it is shipped to the backup database server, and restored again without finalizing the database. Using this model, when failure occurs a final transaction log can be attempted. If un-available, the backup database may be finalized for use based on the last transaction log restored successfully.
When outage occurs, your failover time is limited to that of restoring a final backup (if available) and finalizing the database for use. Your risk of data loss is limited to the duration of time between transaction log backups.
One thing that is nice about transaction log backups is that it can be used on many database engines. Previously, my team wrote a tool using Microsoft Message Queues for log shipping. We had a custom tool to execute a backup or transaction log backup and send the output to a file, shrink it, split it up into message queue sized files, and submit it to a queue.
We then had a tool residing on the backup server which would get the data from the message queue, put it back together into a single file, decompress the data, and then restore it to the database, leaving the database in a restoration state.
The message queues helped assure the data was transported successfully. It handled conditions such as interrupted network connections.
We have not considered replication in any detail because it does so much more that High Availability. Because of its power and flexibility, replication doesn’t lend itself to a summary discussion in this forum.
SAN Replication is another technique we won’t cover here. SAN replication is hardware specific also making it too large a topic for our consideration; but we would like you to be aware that many SAN solutions have the ability to replication block data as it is written to disk.
So, that completes our survey of High Availability options in SQL Server.
Thanks for all your contributions. If you have anything you wish to add to the discussion feel free to write btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Database Design – Part 1
esigning a single database needs identifying the various entities and the relationships that binds up the entities. After the entities and the relationships have been identified, an Entity-Relationship (ER) diagram is drawn to logically model the data.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)