In my previous article, we created a proposal based on the company’s overview, goals, and technology, then highlighting the benefits of an architecture base on Azure resources and giving an overview of its flow. In today’s article, I am going to start guiding you by implementing the proposed architecture by creating an Azure SQL database resource in our main company’s database in Montana, replicating it in Europe, and then synchronizing it with the On-Premises database.

Create an Azure SQL Database

• Sign in to the Azure portal.
• Select + Create a resource, filter the result for Databases, and click on SQL Database

• On the creation tab, select a resource group, insert a database name, create a new server in a Central US (primary location of the company), and select a database’s configuration.

• Click Review + create to create the Azure SQL database resource.

Replicate the database

• Once the creation process is completed, select your SQL server, and click your SQL database from the list of available databases.

• Select the Geo-Replication tab and click North Europe (closest region to the company’s target audience).

• In the creation form, create a new server in North Europe, select the same configuration as the primary location, and click OK.

• Azure will then initialize the new instance and then seed the data from the primary database to your replica.

Synchronize your On-Premise database to Azure SQL database

• Select your primary SQL database from the list of available databases.
• Click the Sync to other databases tab and click New Sync Group.

• On the creation tab, insert a group name, select the Existing database and enable the automatic synchronization. By doing so, you will set the frequency with which the primary instance of your Azure SQL database will synchronize with the group and resolve possible conflicts. The possible options are:
• Hub win: When conflicts occur, data in the hub database overwrite conflicting data in the member database.
• Member win: When conflicts occur, data in the member database overwrite conflicting data in the hub database.

• Once the synchronization for the group is created, you will see in the status column, Not Ready.

• Select the synchronization group, click databases, and then click the Add an On-Premises Database button.

To complete the configuration of an On-Premises database, we must set up an SQL Azure Data Sync agent on the machine where the SQL server is located and then Select which Database to sync.

Set up the SQL Azure Data Sync Agent

• Click Choose the sync Agent Gateway.
• If you don’t have an agent already installed on your machine, select the Create a new agent radio button.

• Download the data sync agent from the following link https://www.microsoft.com/en-us/download/details.aspx?id=27693.
• Open the installer and start the installation wizard.

• Read and accept the License Agreement and Privacy Information, select Accept and click Next.
• Enter the credential of the account with Network Access to run Windows Service (for example, contoso\gtrekter) and click Next.

• Enter the location where you want to install the agent and click Next to start the installation.
• Once the installation is complete, launch the Microsoft SQL Data Sync Agent.
• Before continuing, go back to Azure, fill the agent name textbox, and click Create and Generate Key button. Once the key is generated, copy it to the clipboard.

• In the Microsoft SQL Data Sync, click the Submit Agent Key button.

• In the configuration window, add the Agent Key generated by Azure and the Login and Password fields. Enter the Azure SQL Database server’s credentials where the Hub database is located.

• Test that everything is working properly by clicking on the Test Connection button and then OK.
• Click the Register button, and in the SQL Server Configuration window, select the Authentication type, the Server, and the Database to synchronize.

• Once again, check that everything is fine by clicking on the Test Connection button and Save.

Select the database

To complete the configuration of your On-Premises database, follow the following steps:

• Click select the database.
• The tab that will appear, add a Sync Member Name, select the On-premises database that you have previously registered in the Microsoft SQL Data Sync and select the Sync Directions. The available options are:
• Bi-directional Sync: Data changes on either the on-premises SQL Server database or the hub database will be written to the other database.
• To the Hub: Changes in the on-premises SQL Server database are written to the hub database but not vice versa.
• From the Hub: Changes in the hub are written to the on-premises SQL Server, but not vice versa.

• Click OK

Congratulations! You have successfully created an Azure SQL database resource, geo-replicated it in a secondary location, and synchronized it with your On-Premises database!