Azure SQL Data Sync - How to synchronize on-premises and cloud SQL databases

Learn how to synchronize your on-prem SQL database with an Azure SQL Database.
Azure SQL Data Sync
docs.microsoft.com/en-us/azur...
CLR Types for SQL Server
www.microsoft.com/en-us/downl...
Click “Install instructions” and scroll down and pick “Microsoft System CLR Types for Microsoft SQL Server 2012”
Pick X86 for 32-bit installation.
SQL Server Management Objects
www.microsoft.com/en-us/downl...
Click “Install instructions” and scroll down to “Microsoft SQL Server 2012 Shared Management Objects”
Pick X86 for 32-bit installation.

[@stephenkemp4809]

You've just earned a subscriber. Thank you so much for taking the time to explain this.

[@SQLTalk]

Glad the video was helpful. Thank you very much for your comment.

[@ibrahimozgon]

Video helped me a lot. Thank you for sharing!

[@rameshdevanaboina2010]

This video has good information and it helped with sync process , thank you for sharing!

[@vap72a25]

Thanks.. very instructive and direct.

[@mau_lopez]

I appreciate the effort of making this tutorial. It's better than the confusing manuals at Microsoft but, it's not well explained. In my case I've found very confusing to understand what credentials are being used when because we are taking here about different machines and instances, so it would be more useful to go a little more slowly but fully referencing each machine or instance in every step. Hope this helps as a constructive comment.

[@SQLTalk]

Thank you for the constructive feedback Mau. I appreciate it.

[@dbtechnos7148]

When i am trying to add a new column in existing sync group table in bi-directional replication, the sync is not working. What is the exact procedure to add new column in existing table in bi-directional replication?

[@zulia74]

Amazing!!!

[@ferestu]

Very good.

[@anaciduplessis2671]

Is there a better view on permissions and accounts needed to set this up? We are trying to sync an Azure db to an on prem server one way. Thanks!

[@usamasaleem3426]

Is there any feature of azzure to sync the data of our own choice. e.g We want to sync data of all tables from all databases to HUB but can only get data of selected tables to those dbs.

[@ash3rr]

I'm assuming the on-premise machine has to be internet facing? What happens if it is behind a DMZ?

[@prasannaanandh9649]

Can you please send details regarding how to monitor set of datasyncs created

[@Balamurugan-df4qw]

How to raise the mail alert if error occurs in the source table structure is changed

[@danielleco7802]

My Database name does not show in configure, it only shows master only. Why is that so?

[@ronaldscholten7]

All the errors in the log of the sync group, are they supposed to happen? 12:11

[@aamirghanchi9487]

Thank you for the great video! How is Syncing different from Replication?

[@SQLTalk]

This sync is based on SQL Server triggers and not ideal for large workloads. Transactional Replication is for larger databases and tables and is based on an initial Bulk Copy (BCP) for the snapshot load and then on stored procedures to keep the tables up to date.

[@94089463]

Thank you so much for this practical explanation. Question: How do your open Azure windows side by side? That's very useful.

[@hafsazulfiqar8104]

The system will also be able to synchronize local data with the central online database periodically ?? can u explain how can i do this??

[@17airwings]

When you create a "Sync group" and turn automatic sync "On" you will get the options (second, minutes, hours, days) to sync it. The range must be >=5 minutes and

[@pradeeprao822]

Thanks... How reliable is this? Does it have any performance issues

[@SQLTalk]

It's reliable and now has an high availability component to make it more reliable than ever. Regarding performance, you would want to make sure your on-prem server has proper indexing and capacity to satisfy the query load.

[@nicholaskipkoechbett8955]

the sync agent is intall on on-premise server or we install on azure server?

[@SQLTalk]

Yes sir! The agent should be installed on the on-prem or cloud VM or bare metal SQL instance.

[@marcusshamase2050]

how do I synchronize multiple databases in one sync agent?

[@SQLTalk]

I think you simply register another database in the sync agent. It's what we did at minute 9 in the video.

[@ramisohail]

i am syncing a table with 1 billion records. its been more than 36 hours since i triggered the sync, no failure or anything - on premise table is still empty but on the cloud it has sync button disabled and stop button enabled so it must be syncing but there is no progress how much it reached when it would finish and so on, do you have a way to monitor this more properly. as the process might take 5 - 6 days of sync and we have no idea whats going on.

[@SQLTalk]

Rami, how is the sync going? The speed to copy the 1 billion records to your on-prem server will depend on how "wide" your table is and also the connection between Azure on on-prem. Do you see any indication of progress?

[@MoisesHernandez-pj4ss]

What is the difference between Database Migration Services and Data Sync in Azure? Thank you!

[@krepko7]

DMS is for when you want to fully migrate to the cloud, cutover and shut down old server. Data Synch keeps both in sync.

[@funkengruven3535]

What if there are new tables, does that get sync to Azure?

[@17airwings]

Yes.

[@mickexland2993]

So how to define Username/password if On Premises database is in different AD domain than Azure Database?

[@17airwings]

On premise Database sync is done with help of sync agent (windows utility). After submitting the agent key, when you register the local DB, it ask you whether you want to connect with SQL authentication or windows authentication. Refer this video from 9:07

[@fatihabennani5891]

Can you just make this point clear for me please.? we should first migrate our db onprems to azure and after that we will Sync or just the sync tool with take care of that it means will migrate as well as the sync. in my case i'v just create a new blank db on azure and sync it with the other on premis and it works fine but i feel like something missing ?

[@SQLTalk]

You have a few options. If you are going from on-prem SQL to Azure SQL DB (Single DB) use the wizard in SQL Server Management Studio (SSMS) to export the DB to Azure. You can also use what is called the Database Migration Service. Here is a link that explains all the different migration options for SQL and other databases to Azure. datamigration.microsoft.com/

[@fatihabennani5891]

@@SQLTalk thank you so much please i have on-prem sql db and i want to migrate it to Azure sql server and then i wanna perfom a synchronization can you just describe whait i must to do in a few steps Thanks in advance.

[@SQLTalk]

@@fatihabennani5891 Fatiha, the easiest way is in SQL Studio Management Studio (SSMS), right click on the database and pick Tasks. Then pick Deploy Database to Microsoft Azure Database. Make sure you have a pretty recent version of SSMS. I am using version 18.2. Let me know if that works for you. You need an Azure subscription of course.

[@fatihabennani5891]

​ SQLTalk yeees that's work fine and i've been migrate my onprems db to sql db azure server but now i need to sync the hub db on azure with the other onprems (i must use DATA SYNC tool) or the sync built in migration !!?? thank youuu

[@SQLTalk]

@@fatihabennani5891 If you have the DB on-prem and in Azure with different data (one being out of date) I don't know of a way other than a third party tool like SQL Redgate to figure out the data differences between two tables for example. You typically sync an empty Azure DB with on-prem using the Database Migration Service and at some point cut over to the Azure one. The Azure Data Sync tool is a go-forward, keep these # number of tables in sync. type of thing but it doesn't figure out the differences of the data in the two locations. There is also a built-in tabediff utility in SQL Server that you may want to look at. docs.microsoft.com/en-us/sql/tools/tablediff-utility?view=sql-server-ver15

[@halmitchell5531]

Thanks for the video. Initial setup has worked well, but I've run into an issue I can't seem to resolve. I am syncing an on prem (member) DB table (one way sync) to an Azure (hub) DB table. Initial setup works fine and sync works great. If I update a record on the member, the change syncs without issue. We have a need to remove all rows from the member table daily and use an SSIS package to insert the up to date data set. We've used both delete & truncate statements to remove the rows from the member table, then inserting the new data set with the SSIS package. In both circumstances, all rows are removed form the Azure DB table, but the newly inserted data to the member DB table never syncs to Azure. The logs show the "uploads" removal of rows, but from that point forward, the uploads will show 0 and the data will never sync to Azure. We've tried updating the schema of both with no effect. The only thing that works is to remove the sync group, re-add it, and sync which again works fine. Any ideas? Thanks so much and subscribing for future vids.

[@SQLTalk]

Can you double check to make sure you meet the four requirements listed here? docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-data Thanks!

[@halmitchell5531]

The hub and member tables have the same primary key column, none of the columns are ID columns, we don't use any of the characters listed, and snapshot isolation is ON for the member database. We also do not use any of the unsupported data types listed.

[@krepko7]

Thanks for checking. Let me check with the product team on this and let you know.

[@halmitchell5531]

It seems to be an issue only when deleting all of the rows in the table using truncate or delete then repopulating it.

[@SQLTalk]

This is what I found out. Data sync tracks changes using triggers. When you truncate the table it doesn't activate a trigger because the operation does not log individual rows that are deleted. Can you try just deleting the rows? I realize truncates are faster. If you are inserting data to the table using SSIS with bulk insert, BULK INSERT doesn’t activate triggers by default. You need to use the FIRE_TRIGGERS option to enable it.

[@rahulmanerikar3174]

Can we have both DBs as azure DBs, instead of 1 Azure and other on prem?

[@Kirbys911Heaven]

Yes. I don't see any reason why you couldn't have both DB's in Azure.

[@rahulmanerikar3174]

@@Kirbys911Heaven I see, thanks! And how about if 1 instance is VM and 1 instance is MI? Thanks a lot!

[@HWRLife]

Cool...it would be great if it can support Sync without PK or Any unique key...because some of legacy DB does not have key...A very bad design during old days...

[@akpersona]

How do i handle ID clashes in bi directonal synch ? I want to be able to insert data from both on premises and azure server, but due to same ids, data of both server are over riding each other. I NEED YOUR HELP URGENTLY........thanks in advance

[@SQLTalk]

If these clashes should end up as two distinct records then one option is to use an identity column as your PK and make one table odd and the other even. Seed one with the value 1 and increment by 2 and seed the other with the value of 0 and increment by 2. Not sure if this is your situation or not. Let me know.

[@akpersona]

Thank you for replying back. Although your idea sounds good, but setting up odd/even identities are just not possible for me. I need to setup mutiple sql servers locally on different location[ which will act as multiple subscribers] and i need to set Azure as a centeral/ hub server which will act as publisher.I want to setup a bi directional synch on each subscriber. I can afford to delay/ schedule this synch at different time intervals, but all servers will be up and running. This approach is possible using merge replication, but im not sure if i can use merge replication in Azure, merge replication can handle identites on its own with check identity constraint. While trying Azure synch, i am not able to manage identity conflicts ....... Consider this example... Hub server inserted a row with ID=1,Name='SqlTalk' & sub server inserted a row with ID=1,Name='Test' And if hub server has been given a priority over sub server, 'sqltalk' will be overwritten on subserver. Merge replication never allowed a user to even insert same id on server,instead it automatically increases the range of identity to prevent id clash.es..But Azure synch does allow to enter same ids....Do you know how can i use merge replication on AZURE ? I need to find solution quick, or the clients will kill me. :/

[@JonathanIsmaila]

How much does it cost?

[@jibcot8541]

It is free to use, but will cost money for data transfer , if you are moving data between servers in Azure.

[@JonathanIsmaila]

@@jibcot8541 how about if I want to sync data between my local pc and azure server?

[@shaileshpandey6214]

​can it also use free for any other cloud server data which is not in an azure server.?