Copy Data Files from AWS EC2 to S3

Goal: To copy files from a EC2 instance running Windows to S3.

For example, you may want to copy database backup files from a Windows Server with SQL Server running in a AWS server instance.

Approaches:

1. Write your own method using AWS SDK for .NET

http://docs.aws.amazon.com/AmazonS3/latest/dev/UploadObjSingleOpNET.html

2.  Use an open source Windows command utility

http://s3.codeplex.com/wikipage?title=put%20command&referringTitle=Documentation

3. Use powershell to call upon a free 3rd party snap-in

http://friism.com/ec2-sql-server-backup-strategies-and-tactics

For example, cloudberry has this free snap-in:

http://www.cloudberrylab.com/default.aspx?page=amazon-s3-powershell

Note that CloudBerry Explorer for Amazon S3 is Freeware.

My Approach:

On Amazon side:

  1. Using Amazon Management Console, I go to IAM Management
  2. Create a group with S3 full access permission. 
  3. Create a user and add to that group.
  4. Go to S3.  Create a new bucket (e.g. mysqlbackup)
  5. Note that there is no need to add permission to the bucket.  You cannot add a IAM account as a grantee.

On the SQL Server:

  1. Download s3.exe from here and copy it to a folder (e.g. C:\Program Files\WinWin\)
  2. Create a new job “CopyDatabaseBackupToS3”
  3. Create a new step.
  4. For Type, choose “Operating system (CmdExec)”
  5. Under the command textbox, enter

    “C:\Program Files\WinWin\s3.exe” put mysqlbackup C:\Backup\ /sub:withdelete /yes /sync /nogui”

  6. Create a daily maintenance plan with the following steps 
    1. Backup Database Task. 
    2. Maintenance Cleanup Task (optional: to delete old database files)
    3. Step 3: Execute SQL Server Agent Task (to execute  CopyDatabaseBackupToS3)

Explanation on the s3.exe command syntax:

In the above example, s3.exe is located at “C:\Program Files\WinWin\”

put – put (e.g. store files) to S3

mysqlbackup – the name of the S3 bucket to put files into

C:\Backup\ – this is the source directory where all my sql backup files are stored.

/sub:withdelete – this copy the entire directory tree and also delete keys on S3 that correspond to a local file

/yes – when used with /sub:withdelete, it suppresses prompting on each delete.

/sync – only uploads new or modified files since last upload.

/nogui – suppress windows popup.

But what about authentication?

You need to either use s3.exe auth or you can save it to Windows user profile.  If you run the s3.exe in a command line under the service account, you will be prompted for the access key Id and Secret Access key.

image

It is recommended that you encrypt your Secret Access Key with a password.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s