Keeping SQL Server databases and SQL Database / SQL Managed Instance (SQL MI) databases in-sync asynchronously can be achieved through various methods. In this blog post, we will explore some of these options and discuss their use cases.
One common use case for syncing databases is to distribute reads, such as having a second copy of the database for reporting purposes. Another use case is for hybrid applications, where some data is stored on-premises and some in Azure. Additionally, syncing databases can be beneficial for globally distributed applications, as it helps reduce network latency by having data in regions closer to the users. Lastly, syncing databases can also be useful for keeping a test or development database in sync with a production database.
Let’s take a look at some of the options available for syncing SQL databases:
Azure Data Factory (ADF) or SQL Server Integration Services (SSIS)
Using an ETL tool like Azure Data Factory or SQL Server Integration Services allows for building a solution to sync databases. However, this approach can be complex as it requires determining which records have changed in the source database.
Azure SQL Data Sync
Azure SQL Data Sync is a Microsoft Windows Azure web service that provides bi-directional data synchronization capabilities for SQL databases. It enables syncing between on-premises SQL Server databases and Azure SQL databases, as well as keeping multiple Azure SQL databases in sync. Note that Azure SQL Managed Instance is not supported at this time.
SQL Server Always On availability group, SQL Database Active Geo-Replication, or SQL Managed Instance Auto-failover group
These options involve one-way replication that automatically copies data to one or more secondary replicas. They can be used for disaster recovery purposes.
SQL Server Replication
SQL Server supports six types of replication to copy data from one database to another. Azure SQL Database can only be used as the push subscriber in a one-way transactional or snapshot replication topology. The SQL Server instance acting as the publisher and/or distributor can be an on-premises SQL Server, an Azure SQL Managed Instance, or an instance of SQL Server running on an Azure virtual machine.
SQL Server backup/restore, export/import, or database snapshot
These options involve making a full copy of the database instead of just an incremental copy. They can be used for disaster recovery purposes, but they come with the tradeoff of possible data loss and longer downtime.
3rd party products
There are several 3rd party products available that can help with syncing SQL databases, such as Attunity Replicate for SQL Server, ScaleArc for SQL Server, StarQuest Data Replication, and RedGate SQL Data Compare.
Azure Database Migration Service (DMS)
Azure Database Migration Service offers an online (continuous sync) option for copying data from SQL Server to Azure SQL Database or SQL Managed Instance with minimal downtime.
Azure Arc
If you need to copy data for latency and/or data sovereignty reasons, Azure Arc might be a suitable solution. Azure Arc allows for transparent replication of data to specified regions with nearly instantaneous syncing and no downtime.
SQL Server Service Broker or Azure Event Hub
Creating a messaging and queuing application using SQL Server Service Broker or an asynchronous Azure Event Hub can also be used to sync databases. All writes are placed on the queue and sent to different servers.
All of the options mentioned above do not require any changes to the source database. However, if you have the flexibility to change your database solution, Cosmos DB can greatly simplify the process of copying data. It can transparently replicate data to all specified regions nearly instantaneously and with no downtime.
Syncing SQL databases can be a complex task, but with the right approach and tools, it can be achieved efficiently and effectively. Consider your specific use case and requirements to determine the best method for syncing your SQL databases.
Thank you for reading!