Problem solve Get help with specific problems with your technologies, process and projects.

Fault tolerance and backups and snapshots, oh, yes

They are vital tools all DBAs depend on. But what are the best products on the market these days? Expert Don Jones lets us know some of his favorites.

Administrators have certain tools they work with frequently and rely on in their day-to-day work. As a consultant, I often whine a lot when I’m working with a company that doesn’t have the tools I typically use to get the job done faster. Especially when I find myself working with “reluctant” DBAs, tools can make all the difference in the world.

For example, Marathon Technologies’ everRun MX tool is one I frequently recommend, especially to smaller organizations. It offers an easy way to build high-performance fault tolerance for SQL Server, permitting you to use dissimilar server hardware and providing what I call unbalanced protection: One server’s disks can fail, while another’s network connection could fail, and the two work as a unit to continue providing no-interruption services. There are no special hardware requirements, the price is low compared with other fault tolerance technologies, and it supports SQL Server instances that use up to eight processors. It’s also very easy to install, meaning a skilled power user can generally get it up and running in a day or so.

For more on SQL Server and fault tolerance

Maintaining highly available SQL Server virtual machines with fault tolerance

Storage on the edge gives new meaning to fault tolerance

Backups are obviously part of the fault tolerance game, and it’s important to have a good backup system in place. I’ve developed an intense dislike for backup technologies that mandate server downtime. Far too many of my clients are intolerant of maintenance windows for their line-of-business applications. I also dislike point-in-time backups -- it seems that failures only occur after the maximum possible time between backups, ensuring the maximum amount of data loss. Call it Murphy’s Law of Recovery.

Instead, continual backup technologies are all the rage these days. High-end storage systems can often provide continual backup through clever use of mirroring, but I find that smaller organizations -- which are more likely to have a reluctant DBA rather than a giant SQL Server budget -- can get by with software-based systems. One I’m familiar with is from AppAssure; it essentially captures changes in real-time at the disk block level. You can restore a server to any specific point in time to recover from a failure or accidental data change, and the product even lets you mount the backup store separately, meaning you can access data without having to restore it to the production server it came from.

Another form of backup is what I call a snapshot -- no relation to SQL Server’s replication feature of the same name. There are a few vendors that offer this feature, but the one I’m familiar with is from Idera, included as part of its SQL Server administrator tools package. The tool lets you snapshot a database in a given state, and then roll back to that state any time you need to. It's very fast and doesn’t take the database offline. I have customers use it before making any schema or major data changes to a database, such as when they push out a new version of a line-of-business application. If something goes awry, the click of a button puts the database back the way it used to be. There are also options for comparing different versions of a snapshot to see what’s changed between them, which can be useful for documenting changes made by a vendor’s installer or upgrade tool.

Tools like these -- and the many others used by DBAs every day -- not only help make your job easier, but provide functionality that you simply can’t build on your own, no matter how skilled a scripter you are.


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. Check out the archive page for Jones’ series, "SQL Server for the Reluctant DBA.”

Dig Deeper on SQL Server Backup and Recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.