In this post I go through the steps to implement synchronization of a local SQL database to a database on SQL on Azure, which is the PaaS offering of Azure. Let’s Dive in!
Creating a Resource Group
This step is optional. You may use an existing resource group. But I’d advice creating a new RG for this to keep things clean and separate. 🙂
Log into the ARM portal (portal.azure.com) and go to Resource Groups. Click add. Give the a Name, select the subscription and select a location. “Click create” when done.
Now that we have our own resource group, lets create our database in the cloud. This will be the DB we will be syncing our onprem db with.,
Select SQL Database from the menu, and click Add as shown the in the below screen cap.
In the SQL Database blade, enter a database name (I used “PTsyncDB” here),
Select the appropriate subscription,
Selected your newly created resource group, (you have the option to create a new one here as well.)
then click on the configure required settings to create a server instance.
New Server blade opens up. Type a Server Name, an Admin login and password, and select the appropriate location. Click Select.
Once the server is created, You’ll come back to the SQL Database blade. You ll see that the server is populated with the server you just created.
Scroll down in the SQL Database blade. Click pricing tier. Select your requirements as desired. Each tier will give you a description so that makes it easier for you to use.
Once you apply pricing settings, you will be back to the SQL Database blade.
Click Create. You’ll see deployment in progress in notifications.
Once completed, you’ll see the database appearing under SQL Databases blade and “Deployment Succeeded” in notifications panel.
Click the newly created database. Click Sync to other database from the database blade, and the New sync group. A sync group is used to define the sync frequency etc.
Give a Name for the group.
Select “User existing database” and select the database we created earlier.
Select Automatic Sync and set is a desired sync frequency. Minimum you can go is 5 minutes. I have set the local DB to win if there is a conflict. Click OK when done.
Once the creation of sync group is completed, we’ll be setting up our local DB for syncing.
Enter the credentials for the database in azure under hub database,
We can select either an Azure database or a local database for sync. as planned we will configure an on-premise database.
Select “Add an On-premise Database”
Under Configure On-premises, Select Choose the Sync Agent Gateway.
Under Select Sync Agent, Select Create a new Agent. Click Download to download the on-prem agent.
While it goes provide a name under Agent Name and click create and generate key. Copy the generated key to notepad to use when you set up the on-prem sync agent. Hit OK.
Go to the server properties on azure. Select Firewall and Add client IP. This enables communication between local server and server in cloud.
Configuration at Local SQL Server
Before running the sync agent, make sure to install the following components.
Goto this link and expand details. find sqlsysclrtypes.msi and sharedmanagmentobjects.msi files. Both files are available in x86 and x64 flavors. download the appropriate version for the sync agent. Install the CLRtypes first.
Now install the sync agent. Give a local or a domain user account which has network access.
Open Microsoft SQL Data Sync. Click submit agent key. Once prompted, enter the Agent key generated when you created the sync agent in azure and the credentials for the database created in azure.
Once the test connection is successful, Click register.
I have configured Windows authentication. so selected windows, enter the server name and the local database name. click save.
Okay..almost there! 🙂 Back to the Azure portal now..(we left off at adding the on prem database..) Now that we are done choosing the sync gateway, we will select the database to sync.
Give a name, and select the local database form the dropdown list (notice that it has been synced now). We want to sync from on prem to the cloud so we select to the hub under sync directions.
Hit OK when done,. keep OK ing till you come to the new sync group blade.
No we have reached the 3rd step on new sync group. to select tables to sync up.
Select the local database and select the table you need to sync. you are given the option of selecting the columns. You should note that having a primary key in your tables is mandatory for the them to sync.
Hit Save when you are done.
Under sync groups, click the sync group you just created. enjoy the success!!