Hi,

I am currently studying several possibilities to synchronize SQL Server databases on premises to an SQL Azure database.

The scenario is more or less like this:

- Each of our customer has its own database.

- Each of our customer would like to publish some of their data to a centralized database in the cloud.

- We want to host that data in SQL Azure.

- Customers must be able to update their data on their local database and have it reflected on the SQL Azure database.

- Customers can't change data from other customers.

 

We plan to export some data of the SQL Server on premises database to new tables (containing less columns, etc) of a different schema in the same database because I've seen that SQL Azure Data Sync creates some tables and columns for data versioning on the synchronized databases.

Is SQL Azure Data Sync a good candidate to implement this? Has anyone done anything similar with SQL Azure Data Sync??

 

Best regards,

Manuel Felício.

you can use Sql Azure Data Sync (currently just a CTP) or you can roll your own similar to this one if you cant wait for Sql Azure Data Sync v1: http://social.technet.microsoft.com/wiki/contents/articles/walkthrough-of-windows-azure-sync-service-sample.aspx.

you don't necessarily have to export the data to a new schema since Data Sync doesnt alter the tables being synchronized. The change tracking metadata is stored on a separate table and not on the table being synched.

key thing you should consider is the PK of the customer tables making sure that theyre unique across all customer databases and in the central Azure database.

 

• Marked as answer by KJian_Moderator Tuesday, June 21, 2011 2:08 AM

you can use Sql Azure Data Sync (currently just a CTP) or you can roll your own similar to this one if you cant wait for Sql Azure Data Sync v1: http://social.technet.microsoft.com/wiki/contents/articles/walkthrough-of-windows-azure-sync-service-sample.aspx.

you don't necessarily have to export the data to a new schema since Data Sync doesnt alter the tables being synchronized. The change tracking metadata is stored on a separate table and not on the table being synched.

key thing you should consider is the PK of the customer tables making sure that theyre unique across all customer databases and in the central Azure database.

 

• Marked as answer by KJian_Moderator Tuesday, June 21, 2011 2:08 AM

you can use Sql Azure Data Sync (currently just a CTP) or you can roll your own similar to this one if you cant wait for Sql Azure Data Sync v1: http://social.technet.microsoft.com/wiki/contents/articles/walkthrough-of-windows-azure-sync-service-sample.aspx.

you don't necessarily have to export the data to a new schema since Data Sync doesnt alter the tables being synchronized. The change tracking metadata is stored on a separate table and not on the table being synched.

key thing you should consider is the PK of the customer tables making sure that theyre unique across all customer databases and in the central Azure database.

 

• Marked as answer by KJian_Moderator Tuesday, June 21, 2011 2:08 AM

Best solution is to use SQL Azure Data Sync Service, currently its in CTP, but you can easily apply for it on 

http://connect.microsoft.com/sqlazurectps

For initial migration to SQL Azure use SQL Azure Migration wizard (http://sqlazuremw.codeplex.com/) to analyse the database and fix any issues and then migrate it, subsequently you can use SQL Azure Sync Service

For detailed instructions on the SQL Azure to SQL Server sync and vice versa, read the following articles

http://sqlxpertise.com/2011/06/06/sql-azure-sync-ctp2-how-to-synchronize-sql-azure-with-on-premise-sql-server-and-vice-versa/

http://sqlxpertise.com/2011/06/07/sql-azure-sync-ctp2-how-to-synchronize-sql-azure-with-on-premise-sql-server-and-vice-versacreate-sync-groups-and-schedules-part-2/

http://sqlxpertise.com/2011/06/08/sql-azure-sync-ctp2-synchronize-sql-azure-with-on-premise-sql-server-and-vice-versa-how-sync-framework-tracks-changes-in-databases-part-3/

You can also use DACPACCLI, but it backup whole schema and data, you cannot select tables

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

To copy databases within SQL Azure, you can use 

http://sqlxpertise.com/2011/05/03/how-to-backup-azure-database-within-sql-azure/

Thanks for the answers.

I was thinking in creating a new schema or even a new database because I don't want to synchronize all the columns in every table that I need data from.

For example:

 

ProductTable (ProductId, ProductCategoryId, ...)

ProductCategoryTable(ProductCategoryId, ProductCategoryName)

Could be synchronized to ProductTableInAzure (ProductId, ProductCategoryName, ..) . Can I do that with the SQL Azure Data Sync wizards? What about more complex scenarios?

 

Does the CTP has a go live license?

Ability to select sub set of colulmns for synchronization seems to be planned to cinclude in SQL Azure Data Sync CTP 3 as per this blog.

CTP is not intended to use for Production and thus there would not be any go live license for products in CTP.

HTH.

I'm using Sync Framework 2.1 now. It is working fine so far.. the only problem I have now is that I would like to store files in Azure Storage instead of the SQL Azure and I don't know how to do this with Sync Framework. Those files are in the SQL Server DB that is going to be synchronized to Azure. I would like the data to be sent to SQL Azure and the files to be sent to Azure Storage. Is this possible?

SQL Azure Data Sync will prvide you capability to sync the databases. It will not provide you the capability to sync files between you on-prem file system to Windows Azure Storage. That is not in the scope of SQL Azure Data Sync currently. You will have to either move the files as part of columns in your database or do it independently of the SQL Azure Data Sync.

What's the status of SQL Azure Sync Service? I see it is still in Preview. It's been three years in Preview. Will they ever be releasing it and supporting in to the public?

as of today, yes, it is still in Preview.