ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

MS SQL Server Data Archive / Archiving

Updated on April 28, 2011

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.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Steve Johnson 

      10 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.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)