A Step by step guide to set up Azure SQL sync with a local SQL database

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.

RG1

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.

DB1

New Server blade opens up. Type a Server Name, an Admin login and password, and select the appropriate location. Click Select.

DB2

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.

DB5

Once you apply pricing settings, you will be back to the SQL Database blade.

RG3

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.DB6

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.

DB8

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.

syncgrp1

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

syncgrp2

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.

syncgrpagent

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.

serverfw

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.

agent

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.

setup1

Once the test connection is successful, Click register.

reg

I have configured Windows authentication. so selected windows, enter the server name and the local database name. click save.

succ

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.

onprem

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.

tablesJPG

Under sync groups, click the sync group you just created. enjoy the success!!

done

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s