Extending the Publisher to the Cloud

SQL Azure Data Sync

SQL Azure Data SyncThis weekend I decided to setup a SQL Azure sync group to synchronize a SQL Azure database with an on-premise replication publisher.  I was curious if this would work, as I’ve been unable to find any documentation stating whether or not SQL Azure Data Sync can co-exist with SQL Server Replication.  Not only was I able to get this working, but I took it a step further and downloaded the Windows Azure and Windows Phone SDKs — then wrote a quick and dirty Windows Phone app which connects to my SQL Azure sync group hub database and pulls down Customer and related Order data to my mobile device, making it available via a pivot control within the application.

I was able to create a sync group in SQL Azure Data Sync to synchronize the Customer, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorksLTAZ2008R2 database, which also acted as an on-premise Merge publisher in my local network with 3 subscribers.  The solution provided a sync group hub database containing the Merge publication Customer and related Order data, synching one-way from on-premise to the cloud.  I settled for synchronizing the sync group every 30 minutes and kept existing subscribers synching with the on-premise publisher on their normal schedules, which was every 1 minute in my test topology.

Publisher Sync Group

Windows Phone Application

Windows Phone SDKFrom here, I created 2 projects — 1 Windows Azure WCF Service Web Role which allows for Windows Phone app interaction with a SQL Azure database using Entity Framework, and 1 Windows Phone application using WCF to connect to the SQL Azure sync group hub database through the service reference and consume the Customer and related Order data through a pivot control.

A couple of screenshots of the app:


View Customer OrdersCustomer Order
 

As you can see, the potential for SQL Azure Data Sync is interesting.  Not only can sync groups be useful in providing an offsite database storage solution, but when synchronized with an on-premise replication publisher, can also provide a mechanism for exposing enterprise data to the cloud to be consumed by mobile devices.  SQL Azure Data Sync is currently available for preview and can be found in the Windows Azure Management Portal.  More information about SQL Azure Data Sync can be found in SQL Azure Data Sync on MSDN.

Discuss
2 Comments
  1. Khaled Hussein says:

    Actually i am going through the exact scenario but i am facing an error in the publisher concerning the identity range conflict with the azure sync group
    if u can solve this problem please describe in details because my experience with merge replication was through the management studio wizard only,i am pasting the azure error bellow

    Sync completed with warnings in 23.82 seconds.
    Upload: 0 changes applied/0 failed
    Download: 0 changes applied/3 failed

    Data Sync will stop synchronizing changes for this sync group member in 44 days if the failures are not resolved.

    Download –
    errors for first 5 rows that failed to apply:
    Error #1: SqlException Error Code: -2146232060 – SqlError Number:548, Message: The insert failed. It conflicted with an identity range check constraint in database ‘Amaken_NasrCity’, replicated table ‘dbo.Inventory Transactions’, column ‘Transaction ID’. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. SqlError Number:3621, Message: The statement has been terminated.

    Error #2: SqlException Error Code: -2146232060 – SqlError Number:548, Message: The insert failed. It conflicted with an identity range check constraint in database ‘Amaken_NasrCity’, replicated table ‘dbo.Purchase Order Details’, column ‘ID’. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. SqlError Number:3621, Message: The statement has been terminated.

    Error #3: SqlException Error Code: -2146232060 – SqlError Number:548, Message: The insert failed. It conflicted with an identity range check constraint in database ‘Amaken_NasrCity’, replicated table ‘dbo.Purchase Orders’, column ‘Purchase Order ID’. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. SqlError Number:3621, Message: The statement has been terminated.

    For more information, provide tracing id ‘c7d6d41e-ec25-43d4-bcc7-9355c8a32d06’ to customer support.

  2. Hi Khaled,

    I had not considered making writes to the sync group hub when I wrote this post. I assumed for my application that the hub database would be read-only, serving simply as a way to pull down data to my device, with inserts/updates/deletes occurring only on the Publisher and Subscribers.

    Making writes to the hub database with replication and automatic identity range management in the mix becomes problematic as there is no built-in way for replication to manage identity inserts made at the sync group hub.

    It would be best to use manual identity range management if writes will be occurring at the SQL Azure hub database.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>