SQL Server and SQL Azure sync

Question

Hi All,

I have created a web application using windows azure and SQL Azure for database and deployed on cloud using windows azure.

Now this application is in production. I need the production database on my system (on premise) for the development and testing.

But SQL AZure not allowing to take a back and restore at our end. 

 

Could any one help me please. How to get the production database on my premise.

 

Thanks 

Answer 1

I have not used it myself but you might want to look at the recently released Enzo Backup for SQL Azure. It is advertised:

-- The Enzo Backup for SQL Azure solution offers unprecedented flexibility by allowing you to backup a SQL Azure database and restore the backup to other SQL Azure databases or on premise SQL Server databases. You can also schedule backup jobs!

 

 

Answer 2

Sayyed, the Enzo Backup for SQL Azure will solve this problem for you (as Neil mentioned below). The other options is to use SQL Azure Data Sync services, but in all honesty, the Enzo Backup solution is your best bet simply because you can back up an Azure database and restore it on-premise.

---

Scott Klein Blue Syntax http://www.bluesyntax.net

Answer 3

I've been struggling with the same issue and am currently trialling SQL Compare and SQL Data Compare from red-gate.com. Both products have updates for SQL Azure. So far both products seem to work quite well.

---

Seonaidh

Answer 4

Hi Sayyed,

You can use Red Gate SQL Azure Backup or use SQLAzure DACPACImportCLI, For more information read the below article

http://sqlxpertise.com/2011/05/11/dacimportexportcli-import-export-data-from-sql-azure-to-sql-server-vice-versa/

http://sqlxpertise.com/2011/05/04/backup-sql-azure-database-using-red-gate-sql-azure-backup-tool/

FYI, Red Gate also has Database Comparison tool for SQL Azure, using which you can easily script the changes and update your production SQL Azure server from Development

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"

Answer 5

Hello Sayyed,

The best option is to use “SQL Azure data Sync Services”. You can create a Sync group to sync the data from your SQL Azure Database onto both On-Premise SQL Servers (I,e Development and Testing).

http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-data-sync-overview.aspx

Or you can use Database Import and Export for SQL Azure

http://www.sqlazurelabs.com/ImportExport.aspx

Answer 6

Hi,

Please find below are the options -

·         Use of BCP/SSIS for massive migration is very subjective and app owner would be the best person to assess the situation and decide the best approach. However if there are straight forward use cases to execute during such migrations then I recommend using Sql Azure Migration Wizard (bcp based tool) from codeplex. Not sure if your use cases allow use of SQL Azure Sync CTP releases but would be worth trying this managed service as well.

There is another option to take SQL Azure backup into onpremise.    

I have heard good things about the RedGate tools

Given that the current onprem schema is reasonably compatible with Azure to let this run: http://www.red-gate.com/products/sql-development/sql-data-compare/ (or at least let it handle what it can and do remaining manually)

IMO SSIS would be my next choice since you could resolve any schema differences via transformations during the migration

Painful lessons learned from past data migration projects (when it is possible)

·         (nearly) Always go for fully automated approach so you can repeat multiple times if required if tests shows errors

·         If possible develop/practice with a smaller subset of the data (e.g. delete 99% of rows from all tables) in a copy of the database during development (to avoid waiting time while building the jobs)

And for Azure

·         Really consider the red-gate tool also for comparing schemas

When migrating databases also remember

·         Potential use of triggers

·         Potential use of stored procedures (that have nasty non-cloud compatible stuff inside)

·         Advanced views

·         Etc. etc.

One of the first thing you should do (from SQL Mgmt Studio 2008R2) is to export the database in Azure format and try to deploy it on a LOCAL instance

·         Then use VS2010 schema compare to compare the two (only works on-prem)

·         Then  deploy it to Azure and validate that part works

/J

 

---

cpsingh