The richest set of cloud computing services comes from a little e-commerce company known as Amazon.com. Developers can access the Amazon Web Services (AWS) platform using numerous tools including the .NET platform.

Amazon.com is a major player in the cloud computing space and has numerous services available to developers. In late 2009, Amazon released the AWS SDK for .NET. This article will demonstrate using the AWS SDK to create a custom backup service using the Amazon Simple Storage Service (S3).

My company has a web server where I house a number of different web applications. All of these web applications use SQL Server as their database platform. One of the responsibilities of a server host is to back up the databases on a regular basis. Over the years I have employed a number of different backup solutions including: back up data from one server to another, use portable hard drives and recently I’ve used solutions like Carbonite and Mozy to perform online backups. Each of these solutions has its ups and downs and I have never been satisfied with any of them. Being a clever hacker, I decided to take the job into my own hands; I would backup my database applications into the “cloud" using the Amazon cloud.

I chose the Amazon cloud for a number of reasons. Amazon charges approximately $.15 per gigabyte per month for storage of my database files. I will be responsible for paying data upload charges later in 2010 and these data transfer charges will be priced around $.0.15 per gigabyte as well. One item of note: the prices for data storage and transfer go down as volume goes up. Amazon offers the ability to control how and when data is uploaded and removed from my own personal cloud. This flexibility is paramount to my decision to use Amazon.

This article will discuss how you can create a custom SQL Server backup solution.

Types of Amazon Web Services

Table 1 describes the Amazon Web Services available in the Amazon cloud:

For full descriptions and cost descriptions of the above listed services, consult the Amazon Web Services website http://aws.amazon.com.

Registering with Amazon

The first step to using the Amazon cloud service is to establish an account with Amazon. If you have already ordered books from Amazon.com you are already covered. Once you have established a basic Amazon account, go to http://aws.amazon.com and select from the list of services on the left-hand side of the screen (Figure 1) Click the signup button and you will be asked to enter a phone number (Figure 2). Amazon will show you a PIN number (Figure 3) that you will verify via a phone call (that happens almost as quickly as you click the signup button). Once you have signed up for a service you may need to generate a new access code. To generate an access code and key, select Your Account - Security Credentials from the menu. To generate a new key, select the “Create a new Access Key” from the Access Keys tab. This will generate two items: an access key and a secret key. Your access key will be shown on screen (Figure 4) your secret key is hidden behind a link. When you make calls to Amazon Web Services you will be required to use your access key and secret key to authenticate your requests.





Once you have signed up for a service and created an access key you are ready to create your backup solution.

The Backup Solution

The first step to generating your backup solution is to actually back up your SQL Server databases into a folder. To back up a list of databases you need to perform the following tasks:

• Create a list of databases to backup.
• Create a unique file name based on date and database name.
• Create a T-SQL command to send to SQL Server to perform the backup.
• Connect to SQL Server.
• Perform a backup command for each database.

Listing 1 and Listing 2 show you the Visual Basic and C# code to back up databases into a folder using the set of steps described above.

Downloading the SDK

Now that you have created your backup code, you should retrieve the AWS SDK for .NET at http://aws.amazon.com/sdkfornet/.

Once you have downloaded and installed the AWS SDK, you will find the following items installed. Three C# templates (console, empty project and web project), help file topics integrated into Visual Studio and a number of sample applications. You can find the sample files and DLLs for the SDK in your Program Files\AWS SDK for .NET folder.

One item I found refreshing is the availability of the AWS SDK source code. You can modify and redistribute the AWS SDK under the Apache 2.0 license with the proper attribution. Amazon also accepts code contributions via their online forums. You can find the source code for the AWS SDK at http://d1q6xku6craafi.cloudfront.net/AWSSDKForNet.src_1.0.3.zip.

Once you have downloaded and installed the SDK you need to add a reference to the AWSSDK.DLL found in the BIN subfolder of the SDK installation folder.

Amazon Simple Storage Service (Amazon S3)

The next step is to take the backup files generated earlier and store them in Amazon Simple Storage Service (S3). Amazon S3 is basically a big file system in the cloud. Amazon S3 gives you the ability to store text and image files into a pseudo directory structure. You can access Amazon S3 via REST or SOAP services. The AWS SDK for .NET wraps the communication with Amazon in a managed code interface.

A few notable features of Amazon S3:

• Files sizes can be from one byte to 5 gigabytes.
• You can control files via access control lists (ACLS).
• You can choose to store files in Europe, Virginia, Northern California or in the generic cloud. Different charges apply based on region.
• You can provide BitTorrent access to your files.

Amazon S3 organizes its data files into buckets and objects. Buckets are like folders. Buckets in Amazon S3 are single depth, meaning they are not hierarchical. Amazon S3 doesn’t have the concept of a “sub-bucket”. Buckets contain instances of objects. Objects can contain text, images or whatever binary structure you wish to load in them. The maximum size of an object is 5GB.

Authentication

Whenever you communicate with AWS via the SDK you will need to provide your access key and secret key. As you well know in .NET applications there are numerous options where you can store data items like access keys and secret keys. The design decision of this application is to simply store them in an app.config file. Your app.config file should look like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="AWSAccessKey" value="KEY"/>
    <add key="AWSSecretKey" value=" SECRET"/>
  </appSettings>
</configuration>

Once you have your authentication settings in the app.config file, you can access them using facilities provided via the System.Configuration namespace. Add a reference to this file and then add the following code to your backup application:

// Authenticate on Amazon
Dim appConfig = _
  ConfigurationManager.AppSettings

Dim s3Client = _
  AWSClientFactory.CreateAmazonS3Client( _
            appConfig("AWSAccessKey"), _
            appConfig("AWSSecretKey"))

// Authenticate on Amazon
NameValueCollection appConfig = 
_ConfigurationManager.AppSettings;

AmazonS3 s3Client =   
  AWSClientFactory.CreateAmazonS3Client(
                appConfig["AWSAccessKey"],
                appConfig["AWSSecretKey"]
                );

The code snippets above retrieve the access key and secret key from your application’s app.config file. Your application passes these parameters to the AWSClientFactory class, which returns an AmazonS3Client object. The AmazonS3Client object will be used to send and retrieve data from Amazon S3.

Creating Buckets

Once you have created your Amazon S3 client you can create a bucket to store your backup files. To create a bucket in Amazon S3 you need to generate a new PutBucketRequest object and pass it to the Amazon S3 client. The following snippet shows how to create a new bucket.

// Create the bucket
Dim pbr = New PutBucketRequest() With _
   {.BucketName = "backupbucket" + suffix}

s3Client.PutBucket(pbr)
// Create the bucket
 var pbr = new PutBucketRequest 
    {BucketName = "backupbucket" + suffix};

s3Client.PutBucket(pbr);

The PutBucketRequest object has two major properties you need to concern yourself with. The BucketName property represents the name of your bucket. The second property is the region where you want to store the bucket. You can specify SFO (Northern California), EU (Europe) or US (Default, wherever S3 wants to store the data)

Once you have created a bucket you can begin sending objects to that bucket.

Uploading Objects

After creating a bucket you can send files to that bucket using the PutObjectRequest class. The PutObjectRequest class has a number of properties you need to set:

•     BucketName - The name of the bucket where you wish to store your file.
  

•     Key - A unique name to attach to your file.
  

•     TimeOut - How long to attempt an upload in milliseconds before timing out.
  

•     InputStream - A Stream object that will be used to send a file to Amazon S3.
  

Listing 3 and Listing 4 show you the Visual Basic and C# code used to upload the binary files created by the backup process shown above. The Key property is the name of the database, the TimeOut is set to 4 hours (some of these files are quite large and will take some time to upload) and finally, the InputStream property is set to a FileStream object pointing at the backup file to upload.

Putting It All Together

The simplicity of using the Amazon Web Service SDK for .NET is impressive. With approximately 50 lines of code you can develop a solution capable of backing up a list of databases and uploading them into a reliable and robust cloud storage system. Listing 5 and Listing 6 show the final fully implemented Amazon S3 backup solutions in Visual Basic and C#, respectively. Figure 5 shows the console output of these backup applications.


Other Amazon S3 Operations

Amazon S3 offers a number of other operations you may be interested in.

•     ListBuckets() - Lists all buckets for a given access and secret key.
  

•     ListObjects() - List all objects for a given bucket name as specified on a ListObjectsRequest object.
  

•     GetObject() - Returns an object for a given bucket and key name as specified on a GetObjectRequest object. The GetObject() function returns a response object that contains a stream that can be used to export the object to file stream (or whatever stream you choose).
  

•     DeleteObject() - Deletes an object for a given bucket and key name as specified on a DeleteObjectRequest object.
  

•     DeleteBucket() - Delete a bucket based on a given DeleteBucketRequest object.
  

Conclusion

Hopefully by now you have come to the conclusion that integrating your applications with the Amazon Web Services stack is simple. While this example focused on Amazon S3, accessing the other services follows a similar pattern.

I would backup my database applications into the “cloud” using the Amazon cloud.

Dim backuplist As New List(Of String)
backuplist.Add("master")
backuplist.Add("model")
backuplist.Add("msdb")

Dim conn As New SqlConnection("")
conn.Open()

Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.Text
cmd.Connection = conn
Dim currentDate = DateTime.Now

Dim suffix = currentDate.Year.ToString() + _
 currentDate.Month.ToString().PadLeft(2, "0") + _
 currentDate.Day.ToString().PadLeft(2, "0")

 For Each dbname As String In backuplist
    Dim fileName = "'d:\sqlbackup\" + dbname + _
    suffix + ".bak'"
    Dim backupString = "backup database " + _
      dbname + " to disk=" + fileName
    cmd.CommandText = backupString
    cmd.ExecuteNonQuery()
    Dim result = cmd.ExecuteNonQuery()
     Console.WriteLine("Executed command:" + _
          backupString +" " + Now.ToString)
Next

var backuplist = new List<string>();
backuplist.Add("master");
backuplist.Add("model");
backuplist.Add("msdb");

var conn = new SqlConnection("");
conn.Open();

var cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
DateTime currentDate = DateTime.Now;

string suffix = currentDate.Year + currentDate.Month.ToString().PadLeft(2, "0".ToCharArray()[0]) +
currentDate.Day.ToString().PadLeft(2, "0".ToCharArray()[0]);

foreach (string dbName in backuplist)
{
string fileName = "'d:\\sqlbackup\\" + dbName + suffix + ".bak'";

string backupString = "backup database " + 
   dbName + " to disk=" + fileName;

cmd.CommandText = backupString;
cmd.ExecuteNonQuery();
int result = cmd.ExecuteNonQuery();

Console.WriteLine("Executed command:" + backupString + " " + 
DateTime.Now);

// Upload each backup file to Amazon S3
// Set time out to 4 hours for large backup files.
For Each dbname As String In backuplist
  Dim fileName = "d:\sqlbackup\" + _
     dbname + suffix + ".bak"
   Try

      Dim fs = New FileStream(fileName, _
      FileMode.Open, FileAccess.Read)
      Dim por = New PutObjectRequest() With _
        {.BucketName = pbr.BucketName, _
         .InputStream = fs, _
         .Key = dbname + suffix, _
         .Timeout = 1000 * 60 * 240}

      s3Client.PutObject(por)
      System.Threading.Thread.Sleep(1000)
      fs.Close()
      Console.WriteLine("Uploaded file:" + _
        fileName + " " + Now.ToString)
    Catch ex As Exception
      Console.WriteLine("Upload Error:" + _
           fileName + " " + _             ex.ToString + " " + Now.ToString)
    End Try
            
Next

// Upload each backup file to Amazon S3
// Set time out to 4 hours for large backup files.
foreach (string dbName in backuplist)
  {
  string fileName = "d:\\sqlbackup\\" + 
     dbName + suffix + ".bak";
  try
  {
  var fs = new FileStream(fileName, 
      FileMode.Open, FileAccess.Read);
   
  var por = new PutObjectRequest
      {
      BucketName = pbr.BucketName,
      InputStream = fs,
      Key = dbName + suffix,
      Timeout = 1000*60*240
     };
     s3Client.PutObject(por);
     Thread.Sleep(1000);
     fs.Close();
     Console.WriteLine("Uploaded file:" + 
        fileName + " " + DateTime.Now);
  }
  catch (Exception ex)
  {
  Console.WriteLine("Upload Error:" + 
      fileName + " " + ex + " " + DateTime.Now);
   }
}

Imports System.Data.SqlClient
Imports System.Collections.Specialized
Imports Amazon
Imports Amazon.S3
Imports System.Configuration
Imports Amazon.S3.Model
Imports System.IO

Module Main
    Public Sub Main()

        Dim backuplist As New List(Of String)
        backuplist.Add("master")
        backuplist.Add("model")
        backuplist.Add("msdb")

     Dim conn As New SqlConnection("")
        conn.Open()


        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.Text
        cmd.Connection = conn
        Dim currentDate = DateTime.Now

        Dim suffix = currentDate.Year.ToString() + _
           currentDate.Month.ToString().PadLeft(2, "0") + _
           currentDate.Day.ToString().PadLeft(2, "0")

        For Each dbname As String In backuplist
            Dim fileName = "'d:\sqlbackup\" + dbname + _
            suffix + ".bak'"
            
      Dim backupString = "backup database " + dbname _
            + " to disk=" + fileName
            cmd.CommandText = backupString
            cmd.ExecuteNonQuery()
            Dim result = cmd.ExecuteNonQuery()
            Console.WriteLine("Executed command:" + _
              backupString + " " + Now.ToString)
        Next

        // Authenticate on Amazon
        Dim appConfig = ConfigurationManager.AppSettings

        Dim s3Client = AWSClientFactory.CreateAmazonS3Client( _
            appConfig("AWSAccessKey"), _
            appConfig("AWSSecretKey"))

    // Create the bucket
       Dim pbr = New PutBucketRequest() _
           With {.BucketName = "backupbucket" + suffix}
        s3Client.PutBucket(pbr)

        // Upload each backup file to Amazon S3
        // Set time out to 4 hours for large backup files.
        For Each dbname As String In backuplist
            Dim fileName = "d:\sqlbackup\" + dbname + _
                 suffix + ".bak"
            Try

                Dim fs = New FileStream(fileName, _
                FileMode.Open, FileAccess.Read)
                
                Dim por = New PutObjectRequest() With _
                     {.BucketName = pbr.BucketName, _
                       .InputStream = fs, _
                       .Key = dbname + suffix, _
                       .Timeout = 1000 * 60 * 240}

                s3Client.PutObject(por)
                System.Threading.Thread.Sleep(1000)
                fs.Close()
                Console.WriteLine("Uploaded file:" + _
                 fileName + " " + Now.ToString)
            Catch ex As Exception
                Console.WriteLine("Upload Error:" + _
                fileName + " " + ex.ToString + " " + Now.ToString)
            End Try
            
        Next

    End Sub
End Module

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using Amazon;
using Amazon.S3;
using Amazon.S3.Model;

namespace S3backupCSharp
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var backuplist = new List<string>();
            backuplist.Add("master");
            backuplist.Add("model");
            backuplist.Add("msdb");

            var conn = new SqlConnection("");
            conn.Open();

            var cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
            DateTime currentDate = DateTime.Now;

            string suffix = currentDate.Year + 
              currentDate.Month.ToString().PadLeft(2, 
              "0".ToCharArray()[0]) +
              currentDate.Day.ToString().PadLeft(2, 
               "0".ToCharArray()[0]);

            foreach (string dbName in backuplist)
            {
                string fileName = "'d:\\sqlbackup\\" + 
                dbName + suffix + ".bak'";
                
                string backupString = "backup database " + 
                dbName + " to disk=" + fileName;
                
                cmd.CommandText = backupString;
                cmd.ExecuteNonQuery();
                int result = cmd.ExecuteNonQuery();
                Console.WriteLine("Executed command:" + 
                backupString + " " + DateTime.Now);
            }

            // Authenticate on Amazon
            NameValueCollection appConfig = 
               ConfigurationManager.AppSettings;

            AmazonS3 s3Client = 
              AWSClientFactory.CreateAmazonS3Client(
                appConfig["AWSAccessKey"],
                appConfig["AWSSecretKey"]
                );
            
            // Create the bucket
            var pbr = new PutBucketRequest {BucketName = 
                 "backupbucket" + suffix};

            s3Client.PutBucket(pbr);

            // Upload each backup file to Amazon S3
            // Set time out to 4 hours for large backup files.
            foreach (string dbName in backuplist)
            {
                string fileName = "d:\\sqlbackup\\" + dbName + 
                suffix + ".bak";
                try
                {
                    var fs = new FileStream(fileName, 
                        FileMode.Open, FileAccess.Read);
                    var por = new PutObjectRequest
                                  {
                                      BucketName = pbr.BucketName,
                                      InputStream = fs,
                                      Key = dbName + suffix,
                                      Timeout = 1000*60*240
                                  };
                    s3Client.PutObject(por);
                    Thread.Sleep(1000);
                    fs.Close();
                    Console.WriteLine("Uploaded file:" + 
                      fileName + " " + DateTime.Now);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Upload Error:" + 
                       fileName + " " + ex + " " + DateTime.Now);
                }
            }
        }
    }
}