1

I have a use case where I need to keep an on-premise SQL Server database in-sync with my Azure SQL database.

Is there a way I can set up replication / sync from the Azure SQL db to the on-prem db?

Improve this question
2

2 Answers 2

Reset to default
2

The following list of article might provide adequate information for you to put together an Azure SQL Data Sync:

SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.

(Emphasis mine)

In this tutorial, you learn how to set up Azure SQL Data Sync by creating a hybrid sync group that contains both Azure SQL Database and SQL Server instances. The new sync group is fully configured and synchronizes on the schedule you set.
[...]

Add an Azure SQL Database

In the Member Database section, optionally add an Azure SQL Database to the sync group by selecting Add an Azure Database. The Configure Azure Database page opens.

On the Configure Azure Database page, do the following things:

  1. In the Sync Member Name field, provide a name for the new sync member. This name is distinct from the name of the database itself.

  2. In the Subscription field, select the associated Azure subscription for billing purposes.

  3. In the Azure SQL Server field, select the existing SQL database server.

  4. In the Azure SQL Database field, select the existing SQL database.

  5. In the Sync Directions field, select Bi-directional Sync, To the Hub, or From the Hub.

  6. In the Username and Password fields, enter the existing credentials for the SQL Database server on which the member database is located. Don't enter new credentials in this section.

  7. Select OK and wait for the new sync member to be created and deployed.

Add an on-premises SQL Server database

In the Member Database section, optionally add an on-premises SQL Server to the sync group by selecting Add an On-Premises Database. The Configure On-Premises page opens.

On the Configure On-Premises page, do the following things:

  1. Select Choose the Sync Agent Gateway. The Select Sync Agent page opens.

  2. On the Choose the Sync Agent Gateway page, choose whether to use an existing agent or create a new agent.

    If you chose Existing agents, select the existing agent from the list.

    If you chose Create a new agent, do the following things:

    a. Download the client sync agent software from the link provided and install it on the computer where the SQL Server is located.

    Important You have to open outbound TCP port 1433 in the firewall to let the client agent communicate with the server.

    b. Enter a name for the agent.

    c. Select Create and Generate Key.

    d. Copy the agent key to the clipboard.

    e. Select OK to close the Select Sync Agent page.

    f. On the SQL Server computer, locate and run the Client Sync Agent app.

    g. In the sync agent app, select Submit Agent Key. The Sync Metadata Database Configuration dialog box opens.

    h. In the Sync Metadata Database Configuration dialog box, paste in the agent key copied from the Azure portal. Also provide the existing credentials for the Azure SQL Database server on which the metadata database is located. (If you created a new metadata database, this database is on the same server as the hub database.) Select OK and wait for the configuration to finish.

    Note If you get a firewall error at this point, you have to create a firewall rule on Azure to allow incoming traffic from the SQL Server computer. You can create the rule manually in the portal, but you may find it easier to create it in SQL Server Management Studio (SSMS). In SSMS, try to connect to the hub database on Azure. Enter its name as <hub_database_name>.database.windows.net. To configure the Azure firewall rule, follow the steps in the dialog box. Then return to the Client Sync Agent app.

    i. In the Client Sync Agent app, click Register to register a SQL Server database with the agent. The SQL Server Configuration dialog box opens. j. In the SQL Server Configuration dialog box, choose whether to connect by using SQL Server authentication or Windows authentication. If you chose SQL Server authentication, enter the existing credentials. Provide the SQL Server name and the name of the database that you want to sync. Select Test connection to test your settings. Then select Save. The registered database appears in the list.

    k. You can now close the Client Sync Agent app.

    l. In the portal, on the Configure On-Premises page, select Select the Database. The Select Database page opens.

    m. On the Select Database page, in the Sync Member Name field, provide a name for the new sync member. This name is distinct from the name of the database itself. Select the database from the list. In the Sync Directions field, select Bi-directional Sync, To the Hub, or From the Hub.

    n. Select OK to close the Select Database page. Then select OK to close the Configure On-Premises page and wait for the new sync member to be created and deployed. Finally, click OK to close the Select sync members page.

  3. To connect to SQL Data Sync and the local agent, add your user name to the role DataSync_Executor. Data Sync creates this role on the SQL Server instance.

Step 3 - Configure sync group

After the new sync group members are created and deployed, Step 3, Configure sync group, is highlighted in the New sync group page.

  1. On the Tables page, select a database from the list of sync group members, and then select Refresh schema.

  2. From the list of available tables, select the tables that you want to sync.

  3. By default, all columns in the table are selected. If you don't want to sync all the columns, disable the checkbox for the columns that you don't want to sync. Be sure to leave the primary key column selected.

  4. Finally, select Save.

SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.

Data Sync is based around the concept of a Sync Group. A Sync Group is a group of databases that you want to synchronize.

Improve this answer
1
  • 2
    Just anecdotally, while this information is correct, I have experienced a significant number of issues with Azure Data Sync to the point where we had to abandon it as a solution on our project. That isn't to say that it wouldn't work for the OP, just that there are cases where adding a bunch of triggers to the database to maintain state between 2 independent servers isn't the best solution.
    – akousmata
    Commented Apr 8, 2019 at 16:44
0

I found this solution:

https://www.youtube.com/watch?v=7QI1VjbS0YI&t=460s&ab_channel=RajasekharReddyBolla

In case you need to migrate with zero downtime from Azure VM to Azure SQL Database

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.