Katmai's backup compression

I remember a conversation I had at the Redmond campus with the program manager (at that time it was Paul Randall... now I think he goes by Paul Tripp, but all the same he's not a MS anymore.). Anyway though, he was asking me about my favorite Katmai features and I listed 2 or 3, and he was astounded that my favorite feature wasn't backup compression. They were particularly proud of that one. It turns out the SQL

I remember a conversation I had at the Redmond campus with the program manager (at that time it was Paul Randall... now I think he goes by Paul Tripp, but all the same he's not a MS anymore.). Anyway though, he was asking me about my favorite Katmai features and I listed 2 or 3, and he was astounded that my favorite feature wasn't backup compression. They were particularly proud of that one. It turns out the SQL team at MS had never even conceived that someone wouldn't just jump out of their skin for the chance to have backup compression in the product.

My reasoning is sound. And it's not that the feature itself isn't cool, or even long overdue. It's that the implementation itself isn't practical to real DBAs. Let's look at what we have today and how the native Katmai backup compression will stack up.

Today, if you want backup compression you go with one of the big vendors... Quest LiteSpeed, Red-Gate SQL Backup, Hyperbac, etc. And these products bring you features like object-level restore, resource management, and central reporting. And those are just the top 3, there are more features that come with products like these that make them attractive. One of the most important to any DBA is centralized reporting because it's far easier than writing routines to cycle through all your servers to pull all the backup stats into a single report. Those routines will usually consist of an SSIS package that does the cursoring or putting a job on each box to ship the job info up to a central server repo. Either way it's manual work with moving parts and queries that can be difficult to work out properly.

So what does Katmai's backup compression offer? Well, it offers you the ability to compress your backups. Period. That's it. There's no centralized reporting, there's no object-level recovery, there's no resource management. Resource management is another one that's important. Sometimes, I want to open up my compression to all CPUs, or maybe 1/4 of the CPUs, etc. And you just don't have that kind of ability with native SQL backups. Another aspect of resource management is the ability to choose your compression algorithm. Native SQL compression offers you only one. You either compress the backup or you don't. The other tools offer you several algorithms. The advantage of this is clear. On a prod system when you need good compression, but you also need the speed, you can set a lower compression rate. But on dev systems where the backups can take longer, but need to be much smaller, you can use a higher compression rate. Typically dev systems will hold several backups of the same DB so you can restore back to several points in time. This is why you typically want higher compression on these systems.

So now that I've talked about the actual features, what about the implementation? How has MS's specific implementation affected it's usefulness in the field?

Well, simply, it's too limited. Even if you could put up with the missing features, backup compression in Katmai is limited to the enterprise edition. Personally, that's unacceptable as a backup solution because I don't want to have several backup processes to manage.

Let's look at an example. You've got 30 SQL2K boxes, 50 SQL2K5 boxes, and let's say you're really keeping up with the times and you've got 100 Katmai boxes. And of those Katmai boxes you've got say 3-5 with enterprise installed. Just to be generous I'll say 5. So you've got 5 boxes with compressed backups. What about the rest? You still need something for those too, right? Just because they're not enterprise doesn't mean they're not big or important. OK, so let's say you go with LiteSpeed... or it could Red-Gate, it doesn't matter in this discussion. You're getting centralized reporting for all of your boxes except your 5 native SQL backup boxes. So now you have to find a way to merge those boxes' reporting info into the LiteSpeed repo so you can report from one place with one method. And the time and effort it may take to do that and support it may be more trouble than it's worth. And you can't just leave it out because now you're supporting 2 methods for backups and that's just not good practice. So you're better off installing LiteSpeed (or Red-Gate) on your 5 Katmai enterprise boxes and not only getting the extra functionality, but also having a single method to support. It's really a no-brainer. Why would you limit yourself like that? Even if you had 20 servers and they were all Katmai enterprise, the other features still make the 3rd party tools more attractive.

So while it's nice that MS has given us compression, I think that limiting it to enterprise makes it an extremely incomplete solution. And I think that because you can't use it with anything lower than Katmai also limits you tremendously.

I have nothing bad to say about the compression itself though. In fact, just the other day someone told me that he knew someone who tested Katmai's backup compression and he was getting better compression than LiteSpeed. Now, that's very subjective because what LiteSpeed compression level did he use, and what did the data set look like, etc? I've talked a lot on this topic in the past and the algorithm being used matters depending on what the data looks like. But it sounds like the Katmai team got it right on some level anyway. I'll be putting backup compression to a whole battery of tests here pretty soon so I'll know the full story in pretty short order.

So that's why backup compression isn't my favorite feature in Katmai. I've heard good rumors about its abilities, but it's a definite case of coding in a vacuum. It has practically no real-world application compared to what we've already got, and implementing it in any large shop will only complicate the backup solution as a whole. And it's pretty clear to me that MS isn't going to be putting any of these other guys out of business anytime soon. Maybe eventually it'll become worth it to drop the other solutions in favor of native SQL compression though. Afterall, the other vendors have been doing it for years and MS just started. I remember when LiteSpeed started. They didn't have all the features they have now either. Neither did Red-Gate. So MS is allowed to get into the game the same way the others did. But that doesn't mean they're currently ready to compete. Who knows, maybe we get a couple releases into the future and they'll have a richer feature set. But for now, it's just not feasible.

Sorry guys, that's just the way it is.

Watch my free SQL Server Tutorials at:

http://MidnightDBA.ITBookworm.com Read my book reviews at:

www.ITBookworm.com

Blog Author of:

DBA Rant – http://dbarant.blogspot.com

Related: