Wednesday, April 9, 2014

Synchronize local MS SQL database to SQL Azure

In this article I will be taking you through the steps to synchronize your local MS SQL Database to SQL Azure. This is one of the regular requirement you may find in your application development when you are using Windows Azure. Synchronizing the local database will allow you to export all the data from your local database to SQL Azure in few seconds or in few minutes.
SQL Azure allows you to configure this synchronization automatically and will allow you to set the frequency of data synchronization as well. 
Ok, as usual we will go through the complete procedure step by step.
First we will create a database in our local database system and insert few data in the table.
image

I have only one table in the database and we are going to synchronize only this table for demo purpose.
Now we will login to windows azure and will start the Database synchronization configurations. Here for testing purpose we are going to use the sample web application and the database I have created earlier. If you don’t have any sample application with SQL Azure database connectivity you can read my earlier article .
The web application we are going to use in this article is “sqlazuredemodotnetgalaxy”
image
And the database we are going to use here is “sqlazuredemodotnetgalaxy”. This database is connected with the application so we will be able to see the effect of synchronization of data between the database.
image
First we will query the database and see if there is any data in the database or not. You can see that the table is empty now.
image
In order to do the synchronization first we have to create the Sync Agent in SQL Azure. For that Login to your Windows Azure—>Click on the SQL Database—> and then Click on the ADD SYNC button at the bottom of the screen and then Select “New Sync Agent” option.
image
Once you click on the New Sync Agent option it will open a pop up. If you haven’t installed the local client Sync Agent then you have to link here to download the latest software. This must be installed on your database server to proceed further. When you complete the installation you can see that Microsoft SQL Data Sync software in your start menu. leave the client agent as it is we will do the client configuration later.
image 
Enter the Name for your Sync Agent and click Tick mark at the bottom right corner. After clicking the Tick mark it may take couple of minutes to create the Sync agent. Once it is created you will find that new tab will be created in your SQL Azure page as SYNC and also you can find the new Sync Agent which we have created. In below screen print you can notice that I have highlighted “MANAGE KEY” option, we will be generating the agent key and will be using this key in client agent next.
image
Now it’s time to configure the client agent. Let’s open the Microsoft SQL Data Sync Agent Preview tool. You can find that all the options except “Submit Agent Key Configuration” will be disabled. This agent we have to create it by clicking the MANAGE KEY button from the SQL AZURE which I have highlighted in above screen print.
image
let’s generate the Agent key from SQL Sync agent first, Click on the MANAGE KEY button and then Click on the “GENERATE” button. You can see that a unique key will be generate, you can copy the same by clicking the copy button next to the key.
image
Now open the Client Agent by opening Microsoft SQL Data Sync Agent Preview tool and then click on the button “Submit Agent Key Configuration”. Paste the key copied from SQL Azure Sync Agent here and then Click OK. It may take a sec to do the configuration, once it is completed you can see that all the options gets enabled.
image
We will configure SQL database in our local database which we want to sync with the SQL Azure, for that click on the Register button, it will open a pop up, from the pop up click on SQL button and the enter the server name, database name, login ID and password. Once enter all the data make sure to test the connection. If everything is fine Click ok to finish the Client agent configuration.
image
Now our client agent is ready and you can see the database we have configured just now in the client agent preview window.
image
Let’s have a look at the cloud and see how this local database is configured. To see this local database details just click on the Sync Agent. You will find your local database in SQL Azure Sync agent. Now it’s the time to configure the Sync group.
image 
To add the new Sync Group we will again click on the ADD SYNC button and the select the “New Sync Group
image
in New Sync Group settings enter the Name and select the region you wanted to do the Sync and then Click on the right Arrow to move further.
image
In next screen we will select the database in SQL Azure to sync and also provide the user ID and password to connect to the hub. IN HUB DATABASE dropdown you can find all the database in your SQL AZURE account. You may select the database which you wanted to sync with the local database. Here I have selected “sqlazuredemodotnetgalaxy795” database. Click right arrow to proceed further.
image
Now we have to select the reference database to Sync, in our case it will be the local database and it has been part of the Sync Agent. You can see all the database in cloud and also the databases you have added in your sync agent. If you have multiple databases in your Sync agent that also will be displayed under the sync agent. This will make it clear that you can do the sync between any databases which means Sync between the Azure databases as well as with the local database.
Select the local database name, in our case it is DemoAzure local database also the direction. You have 3 options for Sync Direction. First one is “Bi-Directional”, then “Sync To The Hub” and then the “Sync From the Hub” In this case I am selecting Bi-Directional so that you can sync in both the ways.
If you select Sync To The Hub then sync will happen only one direction and that will be from your local database to the Azure database.
If you select Sync From The Hub then again sync will happen only one direction and that will be from your Azure database to the local database.
Let’s Click on the Tick mark to finish this configuration.
image
Once you select the database name and the direction final screen will be like below. Since we have selected the local database which is part of the sync agent field to enter User name and Password will be disabled by default.
image
After clicking on the tick mark it may take couple of minutes to create the group and it will show the status as GOOD.
image
Now we will sync the database. Click on the new Sync group we have created to open the group and then go to the Configure section if you wanted to do the sync automatically.  
image
Another method is to do the Sync Manually. To do the sync manually go to Sync Rules. If you can’t find the table details you can click on refresh schema which will automatically refresh the complete schema. Another option is by clicking the Sync button at the bottom.
Else click on the DEFINE button and select the database you wanted to refresh. Here I select the Azure Database to refresh the data. Click on the Tick button to close the window and then Click on the Sync button to refresh the data.
image
This will take a min depends on the data you are refreshing. Once it shows the Synchronization Successful all your data form the local database will be refreshed in your Azure database.
Let’s query the Azure table and see the refreshed data from the local database.
image 
That’s it. Hope you have enjoyed reading this article. I hope I haven’t missed any task in above steps. If you find any difficulty in any of the steps or doubt in any of the cloud activity you can always reach me via email.

No comments: