Been developing a SQL Azure based solution and so starting to think about Operational aspects like backup / offsite copies etc.
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.
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
/AzureDatabase:<AzureDBName> /AzureUserName<AzureSQLogin> /AzurePassword:<AzureSQLPassword>
/LocalServer:<AmazonRDSInstanceName> /LocalDatabase:<AmazonDB> /DropLocal /Verbose
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.
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.
1. Perform more detailed comparison of the 2 offerings
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