SQL Azure Sync CTP2 – How to Synchronize SQL Azure with On Premise SQL Server and vice versa–Part 1
SQL Azure Data Sync CTP2 is a SQL Azure Labs beta project which is a cloud-based data synchronization service built on Microsoft Sync Framework.
Using SQL Azure Data Sync you can synchronize data between SQL Azure to SQL Azure or SQL Azure to On Premise SQL Server on specific schedules for the specific tables.
You can sign up for SQL Azure Data Sync CTP2 invite in this website http://connect.microsoft.com/sqlazurectps
In this post, we will see how to Setup a Sync job to synchronize data between SQL Azure and SQL Server bi directionally
Once you received the Invitation Code from Microsoft Connect, you can activate the Sync CTP2 account in SQL Azure Labs (http://connect.microsoft.com/sqlazurectps )
After activating the Invite Code, Please login to https://datasync.sqlazurelabs.com using your Azure account
Sync Dashboard – has options to Manage the Sync Group, Databases (SQL Azure and On Premise SQL Server Databases), Agents
Let us first setup a Agent in Local SQL Server machine, click on “Agents” tab to download the Agent Setup
Click on “AgentServiceSetup.msi” link to save the MSI to your computer where you want to run the setup
Before installing the setup, we need to Generate Agent Key, Agent Key is unique to each Agents (Computers), used for its identification, To generate agent key, click on “Generate Agent Key”
Specify the Name of the Agent, and then click “Generate”
The Agent Key will be generated and saved automatically, to view the Agent’ Key click on the Agent Name,
The Agent Key will be displayed on the right side, Copy that save it for your reference, we will use this during Agent Installation, if you have multiple agents, you need to generate multiple keys. You cannot use the same key for multiple agent installations.
If you notice the Status column, it will be shown as Warning, the reason is Agent is not communicating to the SQL Azure Sync Server.
Let us now install the agent and configure it to communicate to SQL Azure Sync Server
To install the agent, open the folder where you have saved the AgentSerivceSetup.msi and run the setup
Click “Next” to proceed
Select “I Agree” and then Click “Next” to proceed
Specify the Service Account User Name and Password, If you are planning to use Windows Authentication for accessing the databases for Sync Process, please make sure this Service User has permissions to connect to SQL Server and access the databases.
After specify the Service username and password, click “Next” to proceed
Select the installation folder, specify whether to install just for you or for every one and then click “Next” to proceed
Click “Next” to confirm the installation and proceed
Installation will continue
Once the installation is completed, click “Close” to complete the setup.
Before stating the Sync Agent Configuration program, please go to Control Panel –> Administrative Tools –> Service to start the “SQL Azure Data Sync Service”
Right click on the service and then click “Start”
Go to Start Menu –> All Programs –> Microsoft SQL Azure Data Sync CTP, the following two shortcuts will be available
Click on “SQL Azure Data Sync Agent CTP2” to start the Sync Configuration program
Click on “Edit Agent Key” to setup the Agent Key
Copy the Agent Key we saved earlier and paste it here and click “Ok” to proceed
Once saved click on “Ping Sync Service” to check whether the Agent is able to successfully connect to SQL Azure Sync Server
On success connection, you should get this below message
Click “Ok” to proceed and then click “Add Member” to add a new database for doing synchronization
Select the tab based on the authentication you are planning to do, In this demo we are going to “Windows Authentication”, after select the tab, specify the name of the SQL Server Instance and then specify the database and then click “Test Connection”
Once the connection is successful, click on “Save” to save the Sync Member
If you want to change the database or Authentication, click on “Properties” to edit the saved Sync Member.
To delete the Sync Member, use “Delete Member” option
Click on “Check Member Schema” option to verify the selected database schema is compatible for SQL Azure sync
Now you have successfully download the agent, generated the agent key, installed the agent and configured the Agent and Sync Member ready for Sync.
In the next post we will see how to configure the Sync Group and schedule the sync job and synchronize the data between SQL Azure to SQL Server and vice versa.
I hope you all find this information helpful, Please post your comments and feedback !!!