A SQL Azure tip a day (10) – Synchronize database

SQL Azure Data Sync (currently a CTP) is like replication in the cloud. It is easy to install and configure and you can use it to synchronize databases between a SQL server 2012 and SQL Azure, or between two SQL Azure databases. By using SQL Azure Data Sync, you can have an internal database application and publish some of your data to a cloud based application for external use or vice verse. Before you configure SQL Azure Data Sync, you need to create a user with permission to “logon as a service”. It will be used by the local Data Sync agent. In this blog post, I will try to guide you through the setup and configuration.

1. Provision the Data Sync First of all you need to provision the Data Sync, by clicking the Provision Data Sync button, select your subscription and click next.

S

Select a region and click finish. 

2. Create a Sync group Enter a name for your Sync group and press Enter to get to the next step. 3. Add a SQL server database to the sync group The second step is to add the SQL server database by click on the + sign in this dialog. 

Next you need to add the database by selecting an existing sync member database or add a new database, and decide if it should be Bi directional, sync from the hub or sync to the hub. The SQL azure database is used as a hub.

4. Add New SQL Server database If you choose to add a new database you need to select an existing Client Sync Agent or install a new one. In this blog post, I’ll assume you don’t have a Client Sync Agent installed and

5. Install the agent.If you need to install a new agent, you need to:
– Download and install the agent
– Specify a name for your agent
– Generate and copy an agent key that you will use to register your local agent.

Before you click next, you should open your local Client Sync agent and register it with the key you copied. In the Client Sync Agent you click the Submit Agent Key, paste your copied key in the text box and click OK. Click the Register button and enter your servername, databasename and your login credentials.

Close the Client Sync Agent and switch back to the management portal, click next in the “Add Database to Sync Group” dialog. As you already done with Step 1, you can move on to Step 2 and click the Get Database List, select your database in Step 3 and click finish. 

6. Add the SQL Azure database Now your SQL Server database is registered in the sync group and it’s time to add the SQL Azure database. Enter or select the server and databasename, enter a valid SQL Azure login and password and click Add.

7. Configure the sync group Next step is to set the Sync Schedule and the conflict resolution. Enter a Sync Schedule between 5 minutes and 1 Month and decide who the winner in a conflict should be.

8. Select the data to be synchronized In the last dialog you can choose the tables and the columns to use in your data sync, and for every table, you can add a row filter to get only the data that meets your criteria.

9. Deploy the Data Sync Now you are almost done, just click the Deploy button at the top of the SQL Azure Management Portal, and once the Data Sync is deployed it should look like something like this. Once you have configured this, you can add more databases to the sync group.