How to Backup SQL Server Database to Amazon Glacier

Amazon Web Services (AWS) offers a large, sometimes bewildering array of server, storage, networking, and analytics cloud offerings, as seen here. One of their storage products is Amazon Glacier, in addition to the older storage solutions – S3 and Elastic Block Store (EBS). Glacier is a very affordable solution intended as a write-once, retrieve-seldom storage medium for long-term storage. Think of it as a storage locker that’s far away from your home, as opposed to your at-home garage or cabinet locker. It is also a good choice for your SQL Server backups …

About Amazon Glacier

From Amazon’s own Glacier page: “Amazon Glacier is a secure, durable, and extremely low-cost storage service for data archiving and online backup. Customers can reliably store large or small amounts of data for as little as $0.01 per gigabyte per month, significant savings compared to on-premises solutions. To keep costs low, Amazon Glacier is optimized for infrequently accessed data where a retrieval time of several hours is suitable.”

So Glacier is a long-term, low-cost solution. At this point, you are probably thinking it’s based on a tape-based backup method, especially as Amazon says retrieval time is about 5 hours. While we don’t know for sure, but the rumor mill has it that Glacier is actually based on low-cost, low-rpm disk drives – perhaps older technology that Amazon has decided to wring some more dollars out of before mothballing.

So why, you may wonder, would you use Amazon’s S3 (starting at $0.03/GB/month) instead of Glacier ($0.01/GB/month), which is a third of the cost. The answer is that Amazon charges you for data retrieval from Glacier; this is to deter you from frequent access. This data retrieval can get expensive – as noted on the Glacier pricing page, you can access 5% of your data for free per month, and after that, you’re charged at $0.01/GB, although it can escalate quickly from there.

SQL Server backups on Glacier

AWS Glacier is excellent for very long-term storage that you’re reasonably sure you will not need anytime soon. Some good examples of this are video and audio archives for media production companies, regulatory compliance archival for healthcare and financial-services companies, and as a replacement for tape archives for all types of organizations.

To back up your SQL Server data to Glacier, you have to first perform a backup as usual, and then physically move the backup file to your Glacier container. There is also a Glacier storage class within S3. This is slightly different from the standard Glacier, in that the S3 Glacier-class is accessed via the S3 API, rather than direct access to Glacier. The S3 Glacier-class is more useful as it allows you to automate the transfer of your SQL Server backup files. But you still require a level of skill, programming, and knowledge of AWS to set this up. The major limitation of Glacier is that because it takes up to 5 hours to retrieve your files from the storage medium, it is not possible to automate database restoration jobs. But as previously mentioned, if you find yourself needing to frequently retrieve files from Glacier, as opposed to a one-off, extraordinary event, then you’re using the wrong solution! You will probably be much better served switching to S3, which unlike Glacier, offers instantaneous data retrieval.

Backup to Glacier with SqlBak

As mentioned before, automating your SQL Server backups to Glacier isn’t a trivial matter. It is not super-complex, but it does call for a pretty good knowledge of AWS in general and S3 and Glacier in particular.

But with SqlBak’s online solution, you don’t really need to know Amazon services bells and whistles. SqlBak will guide you through a step-by-step procedure in which you can set up your SQL Server database backups to be automatically stored on S3 (backing up to Glacier is covered separately below). The only prerequisite is your Amazon account access key – you receive this when setting up your AWS account.

At Step 4 of your SqlBak setup procedure, as seen in the above screenshot, you provide your access key and specify what type of backups (Full / Differential, etc.) you are saving to your S3 account. You can also specify a particular path in your S3 account in which to save your backup files, as well as the storage class to use. For best results, choose the Standard class.

To back up your files to Glacier, you first save them to S3 as detailed above, then create a set of rules in your AWS account as detailed here with which to move your S3 files to Glacier. Because of Glacier’s unique setup and lack of instantaneous, real-time access to the underlying storage medium, it is unfortunately not possible to automate the saving of backups directly to Glacier in SqlBak. You need the two-step solution as described, in which you first use Sqlbak to save to S3, then use AWS’s own setup to move/ copy the files over to Glacier.

Leave a Reply

Your email address will not be published. Required fields are marked *