Manage Learn to apply best practices and optimize your operations.

Aiming for zero: Techniques for lossless SQL Server data protection

No doubt you have SQL Server data protection plans in place. But how much data do you stand to lose should disaster strike? Expert Don Jones offers pointers on how to make that number zero.

SQL Server stores some of your organization’s most important data, so recovering in the event of a devastating failure -- like that of the hardware itself -- is crucial. Clustering, log shipping and database mirroring offer SQL Server data protection against a server failure. But there is still the opportunity for lost data -- the equivalent of a few minutes’ work or, depending on what you’ve built, much, much more. Today many organizations are looking for faster recovery options that provide less, or even zero, loss of data.

One increasingly popular SQL Server recovery technology is the lossless, disk-based backup system. Offered by a variety of vendors, it often works below SQL Server, at the Windows operating system level. Essentially, a locally installed agent monitors for changes to individual disk blocks, collects the changed blocks and sends them to a backup server. The backup server typically compresses and deduplicates the data, and then writes it to its own disk storage. By tracking which blocks change at any given point in time, the backup server can reconstruct the entire SQL Server disk image from any precise moment.

This technique provides two SQL Server recovery models: First, by bringing back the disk image you can access older data and use it to roll back changes without having to resort to slower backup tapes. Second, in the event of a total server failure, the backup server’s most recent disk image can be used to quickly construct a new server -- even inside a virtual machine (VM) -- a useful technique for whole-site disaster recovery. Some vendors in this particular space even offer “streaming recovery,” which gets you back up and running in just a few minutes; data is restored from the backup server to your recovery server in a stream of data over a longer period of time.

Another lossless SQL Server recovery technique takes advantage of our growing uses of virtualization. With virtualization-based recovery, SQL Server runs in two identical VMs, each running on separate virtualization hosts. Each VM is synchronized in real time, meaning that the memory, processor and possibly even the virtual disk are all in lockstep on both hosts. If a single VM or host fails, the other simply keeps right on processing with no switchover time.

Because SQL Server is such a great user of multiple processors, this recovery technique works best when it supports multiprocessor synchronization. A cleverly designed system can even support really complex failure scenarios. For example, suppose that one virtualization host loses its network connection and the other loses its disk file. Combined, the two can still function as a single server, utilizing the surviving network connection and synchronizing disk state from the machine with the healthy disk. Note that this virtualization approach doesn’t help with damaged or improperly changed data, but it does work well as a high-availability adjunct to a real-time, block-based backup system.

SQL Server’s own availability and recovery options continue to improve. In the upcoming 2012 release, SQL Server’s database mirroring grows up, becoming a true clustering system built upon Windows Cluster Services. This technology provides increasingly sophisticated availability options right in SQL Server, and can be deployed across hosts in organizations moving toward virtualization.

SQL Server’s native weak point is its inability to quickly recover a database to a specific point in time. While transaction log backups can be used to do that, it typically requires a separate copy of the database and a bit more work -- and the expertise of a database administrator -- than a third-party disk block-based recovery system.

Whatever your data recovery goals, the technologies are out there to meet them. Don’t be afraid to look outside of SQL Server’s native capabilities; Microsoft knows that it can’t meet every organization’s needs right out of the box and has built a solid and extensible platform for third parties to help meet your specific SQL Server data protection requirements and goals.

Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 books on IT and a speaker at technical conferences worldwide. He can be reached through his website at

Dig Deeper on SQL Server Backup and Recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.