If you want to know more about SQL Data sync, you can refer to my previous article - Sync Data Across Multiple SQL Servers With SQL Data Sync - Azure.
Step 1 - Creating a sync group
- Login to your Azure portal.
- In the dashboard go to SQL Databases.
- On SQL Page, select the existing database you want to use as hub database for sync.
- After selecting the database, check for the option “sync to the other database”.
- We now need to create a new sync group, so on Sync page select New Sync Group, give a name to sync group in “Sync Group Name”. In the Sync Metadata Database section, choose to create a new database, or you can choose an existing database as well. Set “Automatic Sync” ON or OFF as you need -- if you check it as ON you can choose the frequency.
In “Conflict Resolution”, there are two options, “Hub Wins” or “Member Wins”, in [hub wins] if a conflict occurs it overwrites the conflicting data in the member database.
While if [Member Wins] is selected then if the conflict occurs, the data member overwrites the conflicting data in the hub database.
Step 2 - Adding sync members
After creating the sync group, now it’s the time to add sync members. In the Hub, database enters the existing credentials for SQL Database on which the hub database is located. Now check below; there is an option to add a new database. Add all the details which you need to create a SQL database.
In “Sync Directions” select [Bi- directional Sync], you can also select to the hub or choose from the hub, as you want.
Now, we need to add the on-premises database,
In “Configure On- Premises” choose sync agent gateway.
If you have an existing agent you can choose from the list, if not you can create a new agent, it will download the client agent software from the link provided and install it on the system where SQL Server is installed.
NOTE
If you are configuring a server system in your premises, you need to allow TCP Port 1433.
In the Azure portal, you can enter the agent name to generate the key. Once the key has been generated, you can copy it to a notepad for further use.
Now, install and open the downloaded SQL agent app, and once it opens, click on “Submit agent key”. In sync metadata configuration box, paste the agent key we copied to the notepad.
In login and password field, provide the credentials for Azure SQL Database on which the metadata database is located, you can test the connection or select ok to complete.
Now, in the Sync Agent Application, click register to register SQL server database with an agent. Using the connection in configuration, you can now check the populated list and whether the status is reachable or not.
Go back to the Azure portal in “configure on premises” select the database. A page opens, in that you'll see “Sync member Name”. Provide the new sync member. Select the database from the list in “On-Premises databases connected to this agent”. In Sync direction, again select the options as you want.
Step 3 - Sync Group
The third and last step is to configure the sync group. On “Tables Section”, select the database from the list of sync groups and refresh the schema, from the list select the tables you want to sync and select save.
That’s all -- you have successfully configured SQL Data Sync.