StackOverflow’s database servers are hosted in a colo facility. They pay by the byte for transfer in and out of their servers, so it’s really crucial to compress the database backups as much as possible. The smaller the backups, the less they pay. They’re already using backup compression, but they wanted to explore more ways to cut their backup sizes.
There’s two things DBAs can do to deflate their database backups, but both of these have serious implications. The only way this compression process will work is if you’ve got a second database server that can:
- Restore the production backups
- Do some work on them to decrease their size
- Back up the smaller databases
Notice I didn’t use the word “shrink” – nowhere in today’s discussion am I going to shrink the databases. Shrinking databases is evil. Instead, we’re going to do some things to lose the fat and keep the muscle.
Do not try what you’re about to read on your own production server. The only reason we can do these at StackOverflow is because we’ve got a second database server available. These techniques should never be used if you’ve only got one database server, because dropping your indexes and changing to 100% fill factor will both give you horrendous performance.
Consider taking this approach with a dedicated fire drill server. In a perfect world, you’ve got a server lying around that you’re using for fire drills: periodic restores of your databases. The whole reason we do backups is so that we can do restores, and you should be testing your backups as often as possible. In this scenario, we’re actually testing every single one of our backups.
Now, on to the shrin…I mean, compressing!
Drop All Non-Clustered Indexes
There’s two kinds of indexes – clustered, and non-clustered. Clustered indexes aren’t really indexes at all; they define how the table itself is laid out on disk. Dropping a clustered index doesn’t save space. Non-clustered indexes, on the other hand, take up space because they’re additional copies of parts of the table. Non-clustered indexes can help performance, but they can be rebuilt from scratch as long as you save the index definition first.
Which of these takes less space to back up:
- The string “CREATE INDEX IX_EmployeeName ON dbo.Employees (LastName, FirstName)”, or
- An index with thousands or millions of rows in it
The answer is the string. To minimize what we’re backing up, we need to:
- Script out all of the non-clustered indexes in the database
- Save those definitions to a table (or a stored procedure)
- Create a stored proc that will loop through those index definitions and recreate them later
- Drop the indexes
If our database’s content is, say, 30% indexes, we just dropped the size of our backup by 30%. The downside is that after the database is restored somewhere else, it’ll take longer for the database to be fully available, because we have to run the stored proc to recreate all our indexes. If we’re running SQL Server Enterprise Edition, our database will be online while the indexes are recreated.
Here’s a couple of scripts to generate T-SQL scripts for all of the indexes in a database:
To make these work, you’ll want to adapt them so that they insert the scripts into a table for later execution, and then drop the indexes. I know, I know, you’re going to want the script I’m using at StackOverflow, but I’m not quite comfortable publicizing that code just yet. It’s still a little duct-tape-y. I’ll revisit that in the future though.
This index removal task is extremely fast, only a matter of seconds, because SQL Server is just dumping metadata. The next step, however, is much more intensive.
Rebuild Tables with 100% Fill Factor
SQL Server stores data in pages, and you can think of these much like the pages in the phone book. If we tried to fill the pages completely full, then whenever we needed to add a single record, we’d have all kinds of shuffling to do. When we know we’re going to be adding a lot of data, we’re better off leaving some working area on each page so that we can add records without doing a lot of shuffling every time. The default amount of space we use on each page is known as the Fill Factor.
Normally, StackOverflow’s tables wouldn’t have a 100% fill factor because insert performance would be horrible. However, in our backup scenario, we want to cram all of the pages completely full. The solution is to rebuild all of the clustered indexes with a 100% fill factor. The lower your default fill factor, the more space you’ll save by rebuilding to 100% fill factor. This step takes a lot of IO crunching, and again, it only makes sense when you’ve got a second database server available to do all this work.
Our Results: 50-55% Smaller Backups
Cutting backup size in half means:
- Half the bandwidth expense
- Half the time to move backups across the wire for disaster recovery
- Half the long-term storage requirements
Your results will vary depending on your fill factor and your amount of indexes.
Brent,
Great post! These are some clever ways to reduce the size of a DB backup file.
You may have mentioned this in your post and I missed it – sorry if this is a repeat. The describe tasks do save DB backup storage space at the cost of both processing time to create the smaller DB backups (this you mentioned in the post) AND processing time (i.e., longer recovery windows required – RTO / RPO) to undo the compression (rebuilding the desired fill factor on a table and rebuilding the non-clustered indexes – unfortunately, not as quick as dropping them) and get the restored DB back to where it was and performing well.
Not a criticism, but a point of observation – most things in life are trade-offs (no free lunch).
Scott R.
Scott – yes, but remember that we already have the full-size backups going to a file share inside the same datacenter. These smaller files are only for offsite disaster recovery. Due to the length of time it takes to transmit these files offsite, this method actually cuts our RTO/RPO because we can get the files copied, restored, and recreate the indexes faster than we can copy the full-size files.
Great couple of tips Brent. Thanks…
i never understood why MS couldn’t write a SQL backup API where the index is backed up as a pointer or whatever and on restore it runs create index. no need to backup the index data except the restore is faster. we run our backups straight to LTO-4 tape and in QA we can restore a 150GB database in an hour or so.
Wow those are some really good tips! It’s amazing that rebuilding your clustered indexes with 100% fill factor reduced the size of your backups by 50%.
So let me get the whole process straight: before each backup, you drop all non-clustered indexes, rebuild all clustered with 100% fill factor, backup, then run a proc which rebuilds all non-clustered indexes and sets the fill factor on the clustered indexes back to what it was previously?
Nick – yes, but keep in mind that this happens on a second system. We don’t do this on the production server. We do a backup in production, restore it on the second system, and then create this newly compressed copy for offsite DR use.
Great post! We currently use an SSIS task to 7z the backups (smaller databases so this works for now) and send them to a vendor SFTP. But modifying the SSIS task to do something like this might be in our future to minimize the transfer time.
Well, yes, this is a great idea. Only if you have an extra server, non-clustered indexes and fill factors less than the default 100 (or 0). (Some 80% of the clients I work with have none of the above!) And, you also need to have some mighty fast and reliable drives.
Feodor – yes, that’s exactly what I covered in the post. It’s not for everyone. There are very few tips that make everyone’s backup & recovery solution faster universally. If there was an easy button, it’d already be pushed.
Of course you should have some mighty fast and reliable drives – what OTHER kinds of drives would you have on a database server?
Pingback http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!860.trak
Brent, reality is oftenly surprising…
I saw something similar in the MVP Deep Dives book – instead of dropping them, though, the author put the non-clustered indexes in a separate filegroup. Then do a filegroup backup, skipping that filegroup (the index metadata is in the system tables). His restore process is more cumbersome because he doesn’t have the indexes scripted out – which is something that Brent’s approach has.
So a hybrid approach sounds awesome; use the various scripts to script out the indexes, put the nonclustereds on a different filegroup, and skip the backup of that particular FG.
Yeah, I liked that approach in MVP Deep Dives, but it requires schema changes – something most shops can’t do, or they have designs for things like partitions that usually put data and indexes on the same filegroups.
That’ll teach me to post without fully grokking the subject; the Deep Dives problem is that you have to drop the already-existing indexes (via the system tables) in order to create them, and that requires single-user mode.
But I swear I remember reading about restoring FG backups and not including one, and recreating it later.
Yeah, I’ve recorded a video about it:
http://sqlserverpedia.com/wiki/Restoring_File/Filegroup_Backups
But you can’t just recreate it later. The only thing you can do is bring the database online without the affected filegroups, copy the data out into a new database, and build things again from scratch. That’s painful and slow.