Synchronize SQL Server Databases
Database Synchronization Options
There are many database synchronization methods available, however it's up to you which method will work best for your company. The methods described below vary in complexity and overall cost. The tutorials for Azure SQL Data Sync and SQL Server Replication assume you have at least some prior experience with SQL Server and SQL Databases.
Refer to this article: Distributed Disconnected Database (D3) Solution, added October 2023
Microsoft Azure SQL Data Sync
You'll need a Microsoft Azure account and Azure SQL Database
SQL Data Sync allows you to sync your data across multiple databases. The Hub Database must be an Azure SQL Database. The Member Database can be an Azure database or an instance of SQL Server (on-premises). The Sync Metadata Database contains the metadata and log for Data Sync and must be an Azure SQL Database. If you'd like to sync an Azure database with an on-premises database, you'll need to install a Client Sync Agent. Follow along with this tutorial to learn how to set up SQL Data Sync on Azure: https://docs.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-sql-server-configure
SQL Server Replication
You'll first need to follow this tutorial to prepare SQL Server for replication. Replication components must be installed on the server. You can run SQL Server Setup again and select the option to install replication. SQL Server Express or SQL Server Compact cannot be replication publishers. Also, replication is not supported on SQL Server instances that are more than two versions apart.
There are two types of replication: Transactional Replication and Merge Replication. Transactional Replication simply replicates the Publisher (main) database and gives the data to the Subscriber (copy) database. Merge Replication syncs with the Publisher when connected to the network and updates all changes made between the Publisher and Subscriber since the last sync. Merge Replication allows multiple subscribers to update data and give those changes to the Publisher and other subscribers.
Note: Conflicts with data changes may occur.
Off-the-Shelf Methods
There are several off-the-shelf database synchronization options available. The following options have a free 30-day trial.
SQL Database Studio
Connect to your server and launch the Data Synchronization wizard. Then, set the Source and Target databases. You can add a new server at this time if you'd like to sync databases on different servers. After you select which tables you'd like to synchronize, you can save the synchronization script and run it on the source, target (recommended) or the current database.
Data Compare for SQL Server
This program integrates with SQL Server Management Studio (SSMS). After installing, simply right click the databases you wish to synchronize directly in SSMS, click Data Compare and set one as the Source and the other as the Target. Then, the Data Compare program will open so you can compare the changes table by table. Use the Data Synchronization Wizard to configure the synchronization options.
