Ways to replicate databases in Azure for DevOps

Azure Relational Databases often consolidate data access across services and become important for transactional processing. The data stored in these databases are mission critical and consequently some steps to ensure business continuity and disaster recovery are needed. Daily backups and continuous replication are some of the frequently sought after methods to do that and often organizations build their own GitOps initiatives to take backup and restore across databases and database servers. This article compares these two methods.

Backup and restore is a feature that allows you to create a copy of your server and its databases at a specific point in time, and restore it to a new server if needed. This is useful for recovering from user or application errors, or for migrating data to a different region.

Continuous replication is a feature that allows you to create one or more read-only replicas of your server in the same or different region, and synchronize them with the primary server asynchronously. This is useful for scaling out read workloads, improving availability, and reducing latency.

If a database server is introduced into every azure subscription that an organization owns, with the sole purpose of receiving replications from other databases and database servers, then it can even eliminate the need for a backup and restore or provide different levels of service for source databases. There cannot be multiple database servers in Azure that replicate to the same database server instance. This is because each replica server must have a unique server ID in a replication topology. But we can have multiple source servers that replicate to different replica servers using the Data-in Replication feature or the read replica feature. These features allow us to synchronize data from a source MySQL server to one or more read-only replica servers in the same or different region. Also, we cannot set up multiple replication channels from a single replica to a single source. This is again because the server IDs of replicas must be unique in a replication topology. But we can set up multiple replication channels from a single source to multiple replicas using the read replica feature in Azure Database for MySQL. This feature allows us to replicate data from an Azure Database for MySQL server or flexible server instance to up to five or 10 read-only servers, respectively. This can help us scale out read workloads, improve availability, and reduce latency

The main differences between backup and restore and continuous replication are:

    - Backup and restore requires you to manually initiate the restore process, while continuous replication automatically streams the data from the primary to the replicas.

    - Backup and restore has a longer recovery time objective (RTO) and a higher potential data loss (RPO) than continuous replication, depending on the backup frequency and retention period.

    - Backup and restore is at the server level, not at the database level, while continuous replication allows you to select which databases to replicate.

    - Backup and restore can be configured to use either locally redundant or geographically redundant storage, while continuous replication always uses geographically redundant storage.

    - Backup and restore is included in the service cost, while continuous replication incurs additional charges for the replicas.

Besides these options, the database migration service and mysqldump also provide resiliency.

The azure data migration service instance comes with the following.   

Pros:  

1.       One instance works across all subscriptions.  

2.       Can transfer between on-premises and cloud and cloud to cloud.  

3.       Pay-per-use billing.  

4.       Provides a wizard to create data transfer activity.  

Cons:  

1.       Limited features via IaC as compared to the portal but enough to get by.  

2.       Not recommended for developer instances or tiny databases that can be exported and imported via mysqldump.  

3.       binlog_expire_logs_seconds must be set to non-zero value on source server.  

4.       Supports only sql login 

The steps to perform the data transfer activity between the source and destination MySQL servers involves:  

1.       Create a source mysql instance mysql-src-1 in rg-mysql-1  

2.       Create a database and add a table to mysql-src-1  

3.       Create a destination mysql instance mysql-dest-1 in rg-mysql-1  

4.       Deploy the DMS service instance and project.  

5.       Create and run an activity to transfer data.  

6.       Verify the data.  

The mysqldump utilities, on the other hand, prepare the SQL for replay against the destination server. All the tables of the source database can be exported. 

Pros: 

1.       The SQL statements fully describe the source database. 

2.       They can be edited before replaying 

3.       There are many options natively supported by the database server. 

Cons: 

1.       It acquires a global read lock on all the tables at the beginning of the dump. 

2.       If long updating statements are running when the Flush statement is issued, the MySQL server may get stalled until those statements finish 

 

These are some of the ways to replicate databases across servers.