Backing up databases from SQL Azure to Amazon RDS

Been developing a SQL Azure based solution and so starting to think about Operational aspects like backup / offsite copies etc.

So far have been using the Redgate SQL Azure Backup tool to backup the database to a .bacpac and restore to a local SQL Express database on my laptop as a temp measure.

Saw that Amazon ECS had now has a SQL Server offering and so decided to change to try as a target for the restore. Also wanted a contingency plan for getting off Azure platform (in case of outage, bill shock,etc).

Obviously this involves pulling data out of the Azure datacenter that will potentially increase data charges.

Setup
1. Get a Amazon ECS a account and create a SQL Server instance

See here . Be sure to set up database access group to allow your IP Address / client access to the Instance* , which is like the azure firewall. Test connectivity from Management Studio.

(* to find your public facing IP Address , goto www.google.com and type in “What is my Ip Address” – tells u at the top of the page)

2. Download and Install Red-Gate SQL Azure Backup Tool

3. Run below script , changing params <> where required

RedGate.SQLAzureBackupCommandLine.exe /AzureServer:<AzureSQLInstanceName>.database.windows.net

/AzureDatabase:<AzureDBName> /AzureUserName<AzureSQLogin> /AzurePassword:<AzureSQLPassword>

/LocalServer:<AmazonRDSInstanceName> /LocalDatabase:<AmazonDB> /DropLocal /Verbose

/LocalUserName:<AmazonUserID> /LocalPassword:AmazonPassword

Findings
1. User is not permitted to perform requested operation

When I ran , it took a while and eventually got output below

Error: User does not have permission to perform this action.
System.AggregateException: One or more errors occurred. —> System.Data.SqlClient.SqlException: User does not h
ission to perform this action.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

But it had created database and objects. So I ran SQL Profiler from my laptop against the Amazon instance (!) with the “User Error” Event class added and found 2 statements below caused the error

A) sp_updatestats – quite easy to replace with Own code

B) DBCC TRACEON(3604) – WTF ?

reproduced the error in Query window. I don’t know at this stage whether these commands are specific to the .bacpac process or Redgates tool . I’m guessing the former.

These are not show stoppers , apart from the process produces an error.

Key takeaways

1. Using .bacpac allows you to backup between SQL 2012 (azure) and SQL 2008 R2 (Amazon)

This is quite cool. Obviously DTS / SSIS has had this for years but the .dacpac method seems far less fiddly / more reliable so far when transferring a whole db

2. Profiler works against Amazon

I was quite happy when this worked. As some who’s been using SQL Trace since Oasis were just roadies, been feeling a bit exposed / nervous of supporting an instance without having it available.

3. Amazon has database backups built in

Noticed this when setting up my instance , up to 5 days, scheduled etc.

Next Steps.

1. Perform more detailed comparison of the 2 offerings
Nuff said

2. Attempt to migrate web site to Amazon

I.e so we have a fully working alternative on another provider. We are using ACS 2.0 for auth so might need a bit of work 😉

3. Use Powershell to create / restore .bacpac

As the Redgate tool is discontinued

4. Azure data sync to Amazon RDS ?

See here – . Don’t think this will be possible as an agent has to be installed on the target SQL sever – ie amazon

Advertisements

One thought on “Backing up databases from SQL Azure to Amazon RDS

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