SQL Server Backups: I'll Take The Whole Pie, Thanks

2015-09-21 0 Comments

Way back in the SQL Server 2000 era, I was a junior developer. My company at the time had a customer with a need to schedule some executables at regular intervals. The solution was for me to write a "scheduler" application for the customer. Fortunately, before I got too far into designing and coding, someone in a developers meeting asked "Why don't you just tell them to use the Windows Task Scheduler?" You could hear crickets chirping. Eventually the silence broke as the group collectively admitted "I can't believe no one thought of that until now." Then the jokes started. "Hey, isn't there an API for the task scheduler? We could write the code for their custom scheduler in less than a day and bill them for 40 hours!" That story came to mind as I reviewed a couple of SQL Server backup solutions.

The first solution from "Primatech" installs an agent on the SQL host that backs up databases to a storage appliance (also from Primatech) 1 The storage appliance is pretty cool. It compresses and deduplicates data with great efficiency. Hmmmm...An agent that may or may not play well with others that must be installed on every SQL host. I'm skeptical and defensive already... The agent includes a GUI executable, a command line executable, and some other binaries. The GUI can be used to back up one or more databases on demand. It can also be used to generate scripts that are required by the command line exe. The scripts are not complex. They're little more than property name/value pairs, one per line in the script file. However, the script "language" is proprietary in the sense that it's not t-sql. To schedule database backups, you'd generate the proprietary backup scripts on the SQL host and create SQL Agent jobs that run the command line exe (along with the path\name of the script to use).

Generating scripts on hundreds of SQL hosts is not something I'd want to do. I don't want to maintain them either. I hypothesized that instead, I could shell out to the command line exe dynamically from my existing SQL Agent jobs and pass the name/value pairs to it. I guessed wrong. I then wondered if I could leverage some of the functionality in the other binaries--specifically, the main dll used by the executables. They invoke dll functions to perform the backups. Why can't I do that too with Visual Studio? I'd soon realize the dll is neither a .Net assembly nor a COM object. I'd guess it's written in C or C++. There's no header file or type library to tell me what the functions are. A senior developer also pointed out something obvious I had overlooked: even if I could access the dll functions, Primatech didn't create it as an API and they're under no obligation to make it backward compatible in the future. I'd be one update away from a bunch of broken code and backup jobs that no longer work. That was all I needed to know about Primatech's software.

The second solution is from "Hadden Industries" and has many similarities with Primatech's offering. It installs an agent on the SQL host that backs up databases to a storage appliance from Hadden 2 Their storage appliance also compresses and dedupes data. It's just as impressive as Primatech's appliance. There's a GUI executable, a command line executable, and some other binaries. As expected, the GUI can backup databases on demand. This is where most of the similarities end. The command line executable reads the necessary parameters from (wait for it) the command line! How refreshing. Though still gratuitously complex and inelegant, the command line exe could be invoked dynamically (without the messy proprietary script files) via SQL Agent jobs. Hadden takes it a step further and provides an even more attractive option. The agent additionally "installs" backup and restore CLR functions that can be invoked directly via t-sql. I started to warm up a little. The CLR functions take a single parameter which is a giant string of name/value pairs. A little clumsy, sure, but I was appreciative nonetheless.

It's clear that one product is much more desirable than the other. Which one did I pick? Neither! Forget about Primatech and Hadden Industries for a moment. Let's consider native SQL Server backups. How many types of database backups are there? What are all the options you can specify for a backup? If I represented all of that functionality and the options that are available natively, it would look something like this:

Now, back to Primatech and Hadden. How much of the "SQL pie" do they give you? A big slice? A sliver? Does it really matter? No matter what, you won't get the whole thing. Pick a number between 1 and 100. You picked 30, right? Here's what you get:

You sure give up a lot using someone else's tool, just to integrate with a nifty storage device. The trade-off isn't worth it. You can keep your slice. I'll take the whole pie, thanks.