Replicate databases between ApsaraDB RDS for SQL Server instances - ApsaraDB RDS

ApsaraDB RDS for SQL Server supports the inter-instance database replication feature. The feature can meet the data migration and synchronization requirements in different scenarios. You can replicate one or more databases, database accounts, and account permissions on an ApsaraDB RDS for SQL Server instance to another ApsaraDB RDS for SQL Server instance in the ApsaraDB RDS console or by calling an API operation.

Feature description

During the replication process, ApsaraDB RDS first performs a full backup on the source RDS instance and then replicates the full data to the destination RDS instance. If data is written to the source RDS instance during the replication, incremental data of the source RDS instance is not replicated to the destination RDS instance.

You can choose to replicate a single database or all databases in the source RDS instance. If the replication task fails, no data is replicated to the destination RDS instance. This ensures data consistency. The following table lists the replication rules.

Item	Description
Database engine version	The database engine version of the existing RDS instance must be later than or equal to the database engine version of the original RDS instance.
RDS edition	The RDS edition of the existing RDS instance must be higher than or equal to the RDS edition of the original RDS instance. The following RDS editions are listed in descending order: RDS Cluster Edition, RDS High-availability Edition, and RDS Basic Edition.
Instance type	The RDS instances can belong to the same instance family or different instance families. The instance families include the general-purpose instance family and the dedicated instance family.

Comparison between the database replication feature of ApsaraDB RDS and the data migration feature of DTS

Item	Database replication	Data migration
Principle	ApsaraDB RDS replicates databases from the source RDS instance to the destination RDS instance by using data backup files or to a point in time. The feature does not delete data from the source RDS instance.	The data migration feature that is provided by Data Transmission Service (DTS) performs logical migration. You can use the data migration feature to read and parse the logs of the source instance to migrate the data. A complete data migration process consists of three phases: schema migration, full data migration, and incremental data migration. The feature does not delete data from the source instance.
Data source	ApsaraDB RDS for SQL Server instances.	Self-managed databases that are deployed on Elastic Compute Service (ECS) instances, self-managed databases that are deployed in data centers, self-managed databases that are deployed on third-party cloud servers, and ApsaraDB RDS for SQL Server instances. For more information, see Supported databases.
Implementation	Only one-time full replication is supported. The one-time full replication is free of charge.	Schema migration, full data migration, and incremental data migration are supported. For more information about the charges for incremental data migration, see Billing overview.

Prerequisites

The RDS instance must meet the following requirements:

The source and destination RDS instances belong to the same Alibaba Cloud account.
The region and network type of the source RDS instance must be the same as those of the destination RDS instance. The zones of the source and destination RDS instances can be different.
The destination RDS instance does not have databases whose names are the same as those of the databases that you want to replicate from the source RDS instance.
The available storage of the destination RDS instance is larger than the total size of the databases that you want to replicate from the source RDS instance. For more information about how to expand the storage capacity, see Change the specifications of an ApsaraDB RDS for SQL Server instance.

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Databases.

Click Replicate to Another Instance. In the dialog box that appears, configure the parameters.

Parameter	Description
Source Instance Name	The name of the source RDS instance. The name is automatically displayed.
Destination Instance Name	The name of the destination RDS instance. Note The region and network type of the source RDS instance must be the same as those of the destination RDS instance. The zones of the source and destination RDS instances can be different.
Source Databases	The databases that you want to replicate to the destination RDS instance. click the or icon to select the databases. If you select more than one database or all databases, make sure that the following conditions are met: The available storage of the destination RDS instance is larger than the total size of the databases that you want to replicate from the source RDS instance. The destination RDS instance does not have databases whose names are the same as those of the databases that you want to replicate from the source RDS instance. Note If the source and destination RDS instances have databases whose names are the same, these databases are not replicated. If you select a database, the schemas and data of tables in the database are replicated.
Copy Users and Permissions	Specify whether to replicate accounts and account permissions to the destination RDS instance. Users and permissions are also replicated.: Accounts and account permissions are replicated to the destination RDS instance. Take note of the following two scenarios: If the destination RDS instance has accounts whose usernames are the same as those of accounts on the source RDS instance, the accounts on the destination RDS instance are granted the same permissions as the accounts on the source RDS instance. If the destination RDS instance does not have the same accounts, the accounts are first created on the destination RDS instance and then granted the same permissions as the accounts on the source RDS instance. Only databases are replicated. Users and permissions are not replicated.: Accounts and account permissions are not replicated to the destination RDS instance. This option is the default value. After replication is complete, you can create accounts on the destination RDS instance and grant permissions on the selected databases. For more information, see Create accounts and databases.

Click OK.

References

You can replicate databases between RDS instances by calling an API operation. For more information see CopyDatabaseBetweenInstances.
If a backup file is created for an RDS instance, you can restore the backup data to an existing RDS instance or a new RDS instance. For more information, see Restore the data of an ApsaraDB RDS for SQL Server instance.

FAQ

How much time is required to restore data to another RDS instance?

Estimated time required for restoration

The following table describes the estimated time required to restore data to another RDS instance. The speeds of data backup and restoration are estimated based on the size of uncompressed data.

Note

Backup compression is not supported for RDS instances that run SQL Server Web. This may reduce the speeds of data backup and restoration to less than 100 GB per hour.

Operation	Required	Estimated time	Description
Back up full data	No	200 GB per hour	If no full backups are performed on the RDS instance within 36 hours, a full backup is performed during the restoration process to balance the time required for data restoration from incremental transaction log backups and full backups. We recommend that you manually perform a full backup before data restoration or initiate a restoration task within 36 hours after the system automatically completes the full backup. This reduces the total amount of time required for restoration. For more information, see Back up an ApsaraDB RDS for SQL Server instance. The backup speed may vary based on the region and time period. To obtain more accurate information about backup and restoration performance, refer to the data volume and period of time for the most recent full backup.
Restore a full backup on the destination RDS instance	Yes	200 GB per hour	None.
Restore a database	Yes	Within 2 minutes	Resource consumption: Applying an incremental transaction log is a resource-intensive operation. If a large number of transaction logs are generated for an RDS instance that has small specifications, such as 2 CPU cores and 4 GB of memory, the data restoration speeds decreases. Accelerated Database Recovery option: The Accelerated Database Recovery option is supported for RDS instances that run SQL Server 2019 or later. This reduces the time for database restoration. You can evaluate whether to enable the option based on official Microsoft documentation.

Example

Test instance: The RDS instance has 4 CPU cores and 8 GB of memory, and the volume of data on the RDS instance is 600 GB.

Time required to back up full data: approximately 3 hours. The time required is calculated by using the following calculation: Time required = 600 GB/200 GB per hour = 3 hours.
Time required to restore a full backup on the destination RDS instance: approximately 3 hours. The time required is calculated by using the following calculation: Time required = 600 GB/200 GB per hour = 3 hours.
Time required to restore a database: within 2 minutes.

In this example, if no full backups are performed on the RDS instance within 36 hours, the total duration is estimated to be approximately 6 hours and 2 minutes. If a full backup is performed on the RDS instance within 36 hours, the duration is estimated to be approximately 3 hours and 2 minutes.

Restoration suggestions

Maintenance window: We recommend that you perform restoration operations during off-peak hours to minimize the impacts on your workloads.
Long-running transactions: During the restoration process, we recommend that you do not perform long-running transactions, such as creating or rebuilding indexes and archiving data. This helps prevents the time that is required to restore a database from being prolonged.