MS SQL Server Data Archive / Archiving

SQL Server Data Archiving

Reviews on two SQL Server Archiving products for MS SQL Server DBAs in large database environments.

Background Info

I've recently become interested in other SQL Server backup utilities because our tape backups were going to three tapes on our full backups, and our SQL Server native backups was an area that I wanted to tackle. Also, we're delving into disaster recovery, and we were trying to find some cheap solutions to help us reduce our RTO and RPO. Some ways to do that is to reduce the size of the disk space your SQL Server backups consume.

You're probably familiar with the native database backup utilities included with MS SQL Server, but did you know there's companies that specialize in this area?

Companies That Specialize In SQL Server Archiving

I've evaluated two utilities so far. The first one I tried was SQLsafe by Idera at Idera.com. I thought I had found the Holy Grail in utilites that would solve all my issues! This tool was easy to use, cut backup space to a fraction of the native SQL Server backup, and took only a few minutes for a large SQL Server database, and restored it just as fast!

My evaluation period had expired before I had time to really dig into the software, and I was concerned about the high-cost per server, so I went searching around trying to find other compression technologies. I looked a pkWare and 7-Zip products because there were other files I wanted to compress on the network.

The problem with trying to save disk space is that there's performance degradation via disk usage, memory, and CPU utilization issues. Also, for tape media usage, the tape drive usually does its own hardware compression algorithms, which are usually more efficient than software compression. In other words, if your tape drive's harware compression option is turned on, and you try to save tape media by compressing a file, you're probably not going to save much space on your tape media, defeating the purpose and wasting CPU cycles.

However, there's PLENTY of room for improvement on the speed in which you can backup/restore your databases, as well as the time it takes to transfer said database backup files over the WAN. My SQL Server database is around 80 GB including log files. SQL Server native backup reduces this to 20 GB. If I were to try to transfer my 20 GB database backup file over the WAN, it might take a day, or at least 12 hours. If I can reduce that to maybe 3.5 GB, then transferring that data only takes 4-5 hours (over a bonded T1 line, or a consistent speed of around 1.94 Mbps or 243 KBps). Even if you only have one T1 line, it can still be done overnight when no one's using your Internet bandwidth, and if it leaks over to the next day when people are working, it still shouldn't work its way into your peak Internet usage times.

So What Product Do You Recommend?

I've since downloaded a product from Red-Gate.com called SQL Backup. This software has similar functionality of SQLsafe, but it differs in some key areas.

Running at minimal compression, SQLsafe and SQL Backup (by Red-Gate.com) are about the same. I would say that Red-Gate's SQL Backup is slightly more compressed. However, the big difference is when doing maximal compression. Red-Gate was much faster and produced a smaller file than SQLsafe. It took a little longer to backup the database on local hard drives (on a RAID 5) than a native backup, but the size savings were worth it! An 80 GB database, using native SQL Server backup utilities took about 2:30 minutes, and it took 20 GB of disk space. Red-Gate's SQL Backup, however, ran about 5 minutes, BUT ONLY TOOK 3.5 GB of disk space! This is almost the perfect amount of data to try to send over a WAN line.

You may be thinking that your data center has multiple terabytes of information, and that a your SQL Server is a fraction of the rest of your data. Okay, but how much is it worth to you, after a disaster, to have at least one system functioning and ready to go within minutes with possibly only a maximum of 4-5 hours of RPO (Recovery Point Objective)? one complete system that you don't have to worry about, that your users can sign onto almost right away? During a hurricane warning, you can backup your database remotely, and send it over the WAN to your warm site. Users can be using this server immediately after the damage to your data center has been done, with very little lost data (if any if your data center was closed during all this)!

I'm actually embarrassed, as a self-proclaimed DBA, to not know about these tools until just recently. I guess I didn't have any need for them until now, so didn't really look for them.

Conclusion

Either tool will do the job and comes highly recommended, but my top recommendation, at this point, is the SQL Backup by red-gate.com. It compresses the data quickly and to a significant degree better, and is even more cost effective than SQLsafe.

Download MS SQL Server 2008 R2 Express Tools

You can download MS SQL Server 2008 R2 Express Tools from the following Microsoft website. 32-bit here, 64-bit here.

You can download MS SQL Server 2008 R2 Express here.

More by this Author


Comments 1 comment

Steve Johnson 8 years ago

I love SQL Backup. We use it where I work, and it saves disk space and the time it takes to back up our MS SQL databases.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working