Introduction

This post walks through a real world use case for backing up a SQL Server database to Amazon S3.  We use our CloudBerry S3 Backup to backup our SQL server database from our StarTeam source code control repository. To protect our database backups from physical disk failures, we decided to conduct periodic backups on Amazon S3 cloud storage.

Further, this use case illustrates how our tools eliminate the hassles of doing manual SQL Server database backups to Amazon S3. Being a specialist cloud company, we understand such business requirements and challenges as well as anyone and we have all tools readily available to facilitate such backups.

What is Amazon S3?

To use CloudBerry S3 Backup, you need an Amazon S3 account. Amazon S3 offers unlimited secure internet storage that leverages Amazon infrastructure.  Amazon S3 is primarily designed for software developers who want to take advantage of scalable internet storage in their applications. The service aims to maximize benefits of scale and to pass those benefits on to developers.

Amazon S3 Pricing Model

There are no sign-up or minimum fees. You only pay for what you use. In many cases, your monthly fee will be less than a dollar! The costs start at $0.15 per GB of data storage a month and $0.1 per GB of data transfer. At the time of this writing, inbound data transfer for Amazon S3 is free till June, 2010.

Check here for more info on Amazon S3 pricing. If you want to learn more about Amazon S3 and how to get an account, refer our step-wise tutorial for the same.

Why Amazon S3 is the Perfect Backup Solution?

Irrespective of whatever disk backup mechanism you use, there’s always a certain degree of risk involved in using disks. Further, most disk-based and RAID solutions are not ideal to handle large volumes of data touching up to Petabytes. Amazon uses a high-speed network which accelerates moving large amounts of data to S3.

Amazon S3 is the perfect solution for backing up an SQL Server database because

• It provides a highly durable storage infrastructure. Amazon S3 helps maintain the durability of your objects by quickly detecting and repairing any lost redundancy.
• It provides periodic integrity checks. If corruption is detected, it is repaired using redundant data.
• It provides further protection via Versioning. With Versioning, you can easily recover from both unintended user actions and application failures.

What about SQL Server?

One of the major challenges encountered by was to backup the SQL Server database in a secure and easy to use manner to Amazon S3. CloudBerry Explorer, our freeware Amazon S3 client helps backup your Amazon S3 files with a few clicks using an intuitive GUI. See Image 1. It comes with a PowerShell command line interface with capabilities to automate Amazon S3 related tasks.

Image 1: CloudBerry Explorer freeware main screen.

Using CloudBerry Backup to streamline the process

The complexity involving Amazon S3 backups is one of the most challenging aspects of using S3. To simplify and streamline the backup process, our CloudBerry S3 Backup product offers an easy way to automate backup and restore processes to Amazon S3 cloud storage.   Although primarily designed for data backup, CloudBerry S3 Backup works just as well for SQL Server backups.  Image 2 demonstrates CloudBerry Backup overview screen.

Image 2: CloudBerry Backup overview screen

We set up weekly FULL backup of SQL Server database and daily Differential backups using SQL Server Management Studio. The backups are scheduled to run at 4 AM every night. Next, we created a backup plan for source files using CloudBerry Backup Wizard.   See image 3

Image 3: CloudBerry Backup Wizard Welcome Screen

Then, we scheduled the tool to run at 6 AM every morning. This ensured that SQL Server backup is completed till this time and there is no pending user activity. See Image 4.

Image 4: CloudBerry Backup Wizard Schedule screen

What about Transaction Log Backups?

Many organizations require transaction logs to be backed up as part of SQL Server backup and this is handled well in our CloudBerry Backup tool.  Since SQL Server Transactions Logs are typically large in size, their backup should ideally be done in pre-scheduled automated manner without requiring manual intervention.

Though, CloudBerry Backup currently doesn’t provide granular scheduling options, but it planned as part of the product roadmap.  We also compensate the lack of granular scheduling options by offering a powerful command line interface which facilitates automation of more granular backups.  You can use Windows Task Scheduler to configure more granular backups.

A CloudBerry Backup Plan, the one that you initially configure using the Backup Wizard can be started using:

 “CloudBerry Online Backup Service.exe” <plan id>

where the <plan id> can be determined using:

• CBBackupPlan.exe –b –<plan name> “Existing CloudBerry backup plan name”
  or
• CBBackupPlan.exe -l (command will list all CloudBerry backup plans)

You can learn more about CloudBerry Backup command line interface here.

Data retention

CloudBerry Backup allows you to control the data retention period. You can specify the duration for which data needs to be retained on Amazon S3. Further, we offer the flexibility of using the same retention policy globally or specifying a retention policy for each backup plan base. See image 5. The end user options for data retention include the following parameters

• how many versions of the file to keep
• don’t keep versions of the file older than XX days

For SQL Server backups, these options can be used to remove old backup files which are no longer required.

Image 5: CloudBerry Backup Wizard purge options screen

Compression

It is well known that SQL Server backup files compress well and can save up to 95% storage. Since Amazon S3 account is charged based on two parameters - storage and bandwidth, compression helps to significantly cut down on storage and transfer costs!  CloudBerry Backup comes with built-in zip compression algorithm which you can optionally turn on for a backup plan. See Image 6.

Image 6: CloudBerry Backup Wizard Compression and Encryption options

Security

CloudBerry S3 Backup provides a fully secure environment to backup your Amazon S3 data. The tool provides a flag which can be used to turn encryption on/off.  CloudBerry Backup supports a number of industry standard encryption methods to encrypt your data under the hood.

CloudBerry Backup supports a number of industry standard encryption methods to encrypt your data under the hood.  See Image 6.

You can avoid the hassle of manual encryption, by using a tool like Redgate SQL Backup which offers advanced compression and encryption. Just schedule the task for copying backup files to Amazon S3 and you should be all set with your automated S3 backup. But if you use native SQL Server backup you are better of leveraging compression and encryption options that come with CloudBerry Backup.  

Notification

There’s nothing worse than losing your data and not being notified about it. We realize that internet connections cannot always be relied upon and therefore we provide a safeguard notification to indicate if the backup was successful or not. CloudBerry Backup comes with an option to notify you on backup completion by email. You can configure if you want to be notified for successful backups, failed backups or both. If a backup fails, you receive an instant email about it. You can then take the necessary mitigation actions such as troubleshooting your network connection and running the backup manually.

Don’t loose sleep with Manual S3 Backups

There’s no doubt that Amazon S3 is a great could storage and backup service. However, its manual backup procedure leaves a lot to be desired. Manually copying of data is time consuming and is a tedious process to say the least.

Further, as the volume of data increases, so does the complexity of manual data backups. CloudBerry Backup works equally well with all data sizes and types, be it a few thousand lines of source code,  a few hundred megabytes of SQL Server dump or a terabyte of SQL Server backup.

What’s more, with manual backups, users live in the ‘fear of uncertainty’. There is no guarantee that all your data is backed up and to make things worse, there is no easy way to check it either.

Conclusion

We combine the power and flexibility of Amazon S3 with the ease-of-use of Cloudberry S3 Backup to offer a perfect solution for SQL Server backups. We plan to further streamline SQL Server cloud backup configuration as part of the product roadmap for CloudBerry S3 Backup.  Besides, we support a wide variety of cloud storage services including Microsoft Azure, Nirvanix, Diomede Storage and Amazon S3. No matter, which cloud service you use, we’ve the perfect backup solution to go with it.

CloudBerry Backup is a Windows program that leverages Amazon S3 infrastructure. You can download the trial version at http://cloudberrydrive.com/ . CloudBerry Backup is licensed per computer and it costs $29.99(US) per copy.