ApsaraDB RDS：Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance by using a physical gateway

1. Install the backup gateway (database server)

You need to first install the backup gateway on the server where the source self-managed database resides. This section describes how to install the backup gateway.

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO

2. Create a DTS migration task

1. Visit the Data Transmission Service (DTS) console.

2. In the left-side navigation pane, click Data Migration and select a region at the top of the page.

3. Click Create Task and configure the source and destination databases.

   Category	Parameter	Description
   N/A	Task Name	Configure a task name that has business significance (no uniqueness requirement) for easy identification. You can also use the task name that is automatically generated by the system.
   Source Database	Select Existing Connection	If you have added the source database information to the Database Connection Management page of DTS, you can select the database from the drop-down list. This way, you do not need to enter the source database information again.
   	Database Type	Select SQL Server.
   	Access Method	Select Physical Protocol.
   	Instance Region	The region in which the self-managed SQL Server database resides.
   	Physical Protocol Gateway (DBS Backup Gateway)	Select the backup gateway that you installed in 1. Install the backup gateway (database server).
   	Domain Name Or IP	Default value: localhost.
   	Port	Enter the service port of the self-managed SQL Server database. Default value: 1433.
   Destination Database	Select Existing Connection	If you have added the destination database information to the Database Connection Management page of DTS, you can select the database from the drop-down list. This way, you do not need to enter the destination database information again.
   	Database Type	Default value: SQL Server.
   	Access Method	Default value: Alibaba Cloud Instance.
   	Instance Region	The region in which the RDS for SQL Server instance resides.
   	Instance ID	The ID of the RDS for SQL Server instance.
   	Database Account	Enter the account of the RDS for SQL Server instance. The account must have read and write permissions. Note You can create a standard account or a privileged account in the RDS console and modify the permissions of the account as needed.
   	Database Password	Enter the password of the account that is used to connect to the database of the destination RDS instance.
   	Connection Method	If Secure Sockets Layer (SSL) encryption is disabled for the destination database, select Non-encrypted. If SSL encryption is enabled for the destination database, select SSL-encrypted. DTS automatically trusts the server certificate.

4. After you complete the configuration, click Test Connectivity and Proceed at the bottom of the page.

   Important

   Make sure that you have added the CIDR blocks of DTS servers to the whitelist of the self-managed SQL Server database. Adding the CIDR blocks of DTS servers may pose security risks. Make sure that you have implemented security measures such as enhancing password strength and limiting ports.

5. Configure the objects for the task.

   Parameter	Description
   Task Stages	If you want to perform only full data migration, select Full Data Migration. Full Data Migration is selected by default. To ensure business continuity during data migration, select Full Data Migration and Incremental Data Migration. Note If you do not select Incremental Data Migration, we recommend that you do not write data to the source instance during data migration to ensure data consistency.
   Source Objects	Select one or more objects from the Source Objects section. Click the icon to add the objects to the Selected Objects section. Note You can migrate a single database, multiple databases, or the entire instance.
   Selected Objects	The objects that you want to migrate.

6. Click Next: Advanced Settings to configure advanced settings.

   Parameter	Description
   Monitoring and Alerting	Specifies whether to configure alerting for the data migration task. If the task fails or the migration latency exceeds the specified threshold, the alert contacts receive notifications. Valid values: No: does not configure alerting. Yes: configures alerting. In this case, you must also configure the alert threshold and alert notification settings. For more information, see the Configure monitoring and alerting when you create a DTS task section of the Configure monitoring and alerting topic.
   Retry Time For Failed Connections	The retry time range for failed connections. If the source or destination database fails to be connected after the data migration task is started, DTS immediately retries a connection within the retry time range. Valid values: 10 to 1,440. Unit: minutes. Default value: 720. We recommend that you set the parameter to a value greater than 30. If DTS is reconnected to the source and destination databases within the specified retry time range, DTS resumes the data migration task. Otherwise, the data migration task fails. Note If you specify different retry time ranges for multiple data migration tasks that share the same source or destination database, the value that is specified later takes precedence. When DTS retries a connection, you are charged for the DTS instance. We recommend that you specify the retry time range based on your business requirements. You can also release the DTS instance at the earliest opportunity after the source database and destination instance are released.

7. In the lower-right corner of the page, click Next: Save Task Settings and Precheck.

   Note

   • DTS performs a precheck before a data migration task starts. You can start a data migration task only after the task passes the precheck.

   • If the precheck fails, click View Details next to the failed item, fix the issue based on the instructions, and then run the precheck again.

   • If an alert is generated during the precheck:

     • For items that cannot be ignored, click View Details next to the failed item, fix the issue based on the instructions, and then run the precheck again.

     • For items that can be ignored without fixing, click View Alert Details, Confirm And Ignore, OK, and Run Precheck Again in sequence to skip the alert item and run the precheck again. If you ignore the alert item, data inconsistency may occur and your business may be exposed to potential risks.

8. Success Rate 100% Next: Purchase InstanceYou can migrate your workloads to the RDS for SQL Server instance.

9. On the Purchase page, read and select Data Transmission Service (Pay-as-you-go) Terms Of Service.

10. Click Buy And Start, and in the OK dialog box that appears, click OK.

    You can click the target migration instance on the Data Migration page, and on the Task Management page, view the migration progress.

    Note

    You can also view the progress of full or incremental migration on the Backup And Restoration > Backup Data Upload History page of the destination instance in the RDS console.

11. When the full migration task progress reaches 100% and the incremental migration task is in progress, go to the Task Management > Incremental Migration page of the target migration task, and click the Migrate To Cloud button.

12. In the Are You Sure You Want To Migrate To The Cloud dialog box, click Switch Now and wait until the migration task is complete.

    Important

    To ensure data consistency, stop writing data to the source instance before you trigger the workload switchover. Wait for the migration task to complete and then switch over your business connections. The entire process requires several minutes.

1. Install a proxy gateway (bastion host)

The following sections describe how to install a proxy gateway on a bastion host that runs Windows or Linux.

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO

2. Install a backup gateway (database host)

Install a DBS backup gateway on the server where the self-managed database is deployed and specify the IP address of the proxy gateway (the internal IP address of the bastion host) during the installation.

1. Copy the gateway download link from Step 4 in 1. Install a proxy gateway (bastion host) and download the gateway on the server where the self-managed database is deployed.

   Important

   The backup gateway download link must be the same as the link in Step 4 of Install a proxy gateway. Otherwise, the database host cannot connect to the external network.

2. Install the DBS backup gateway (AliyunDBSAgent) on a local Windows device or server.

   1. Double-click the setup.exe file in the downloaded file.

   2. Select the installation language, click OK, and then click Next.

   3. Read and accept the terms of the agreement, and click Next.

   4. Select DBS Backup Gateway and click Next.

   5. Select an installation directory, click Next, and then click OK.

   6. Select Agent Region and specify AccessKey ID, AccessKey Secret, Proxy Gateway Address, and Proxy Gateway Port. Then, click Next.

      Important

      • Make sure that the region in which the DBS backup gateway is installed is the same as the region of the destination RDS instance.

      • The AccessKey pair information is stored in the plaintext format in the .\config\dbs-agent.conf file of the installation directory.

      • For Proxy Gateway Address, enter the IP address of the proxy gateway (the internal IP address of the bastion host). For Proxy Gateway Port, the default value is 9797.

      After the configuration is complete, the system attempts to connect to the proxy gateway that is configured in Step 1. If the connection fails, the system reports an error. In this case, check the installation of the proxy gateway.

   7. Confirm the component package that you want to install and click Next.

      The backup gateway starts to be installed. The installation takes about 1 to 5 minutes.

   8. Click Done.

      You can view the installation status of the gateway in the C:\Program Files\aliyun\dbs_agent\logs\agent.log installation directory. If information similar to the following figure is displayed, the gateway is installed.

3. After the DBS backup gateway is installed, click Installed in the Installation Command dialog box of the DTS console.

4. Check whether the DBS backup gateway is started.

   1. In the Run dialog box of Windows, enter services.msc and click OK.

      The service manager dialog box of the system appears.

   2. In the service manager dialog box, check whether the service is started. If the service is not started, right-click AliyunDBSAgent and select Start from the shortcut menu.

      Note

      By default, the system starts the backup gateway. You can also start and stop the AliyunDBSAgent service in the service manager.

5. View the new DBS backup gateway.

   On the Backup Gateways page of the Data Disaster Recovery console, click Refresh in the upper-right corner to view the new DBS backup gateway.

   To go to the Backup Gateways page of the Data Disaster Recovery console, perform the following steps:

   1. Log on to Data Management (DMS) 5.0.

   2. Move the pointer over the icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Data Disaster Recovery (DBS) > Backup Gateway.

      Note

      If you use the DMS console in normal mode, choose Security and disaster recovery (DBS) > Data Disaster Recovery (DBS) > Backup Gateway in the top navigation bar.

   Note

   The name of the new DBS backup gateway starts with DTS_.

3. Create a DTS migration task

1. Visit the Data Transmission Service (DTS) console.

2. In the left-side navigation pane, click Data Migration and select a region in the top navigation bar.

3. Click Create Task and configure the source and destination databases.

   Section	Parameter	Description
   N/A	Task Name	Enter a task name that helps identify the task. The task name does not need to be unique. You can also use the default task name that is generated by the system.
   Source Database	Select Existing Connection	If you have added the source database to the DTS Database Connection Management page, you can select the database from the drop-down list. This way, you do not need to manually enter the information about the source database.
   	Database Type	Select SQL Server.
   	Access Method	Select Physical Protocol.
   	Instance Region	The region in which the self-managed SQL Server database resides.
   	Physical Protocol Gateway (DBS Backup Gateway)	Select the DBS backup gateway that is installed in 2. Install a backup gateway (database host).
   	Domain Name Or IP	Default value: localhost.
   	Port	The port number of the self-managed SQL Server database. Default value: 1433.
   Destination Database	Select Existing Connection	If you have added the destination database to the DTS Database Connection Management page, you can select the database from the drop-down list. This way, you do not need to manually enter the information about the destination database.
   	Database Type	Default value: SQL Server.
   	Access Method	Default value: Alibaba Cloud Instance.
   	Instance Region	The region in which the RDS instance resides.
   	Instance ID	The ID of the RDS instance.
   	Database Account	The username of the account of the RDS instance. The account must have read and write permissions. Note You can create a standard account or a privileged account for the RDS instance in the ApsaraDB RDS console and modify the permissions of the account as needed.
   	Database Password	The password of the account.
   	Connection Method	If Secure Sockets Layer (SSL) encryption is disabled for the destination database, select Non-encrypted. If SSL encryption is enabled for the destination database, select SSL-encrypted. By default, DTS trusts the server certificate.

4. After the configuration is complete, click Test Connectivity and Proceed at the bottom of the page.

   Important

   Make sure that you have added the IP addresses of DTS servers to the whitelist of the self-managed SQL Server database. Adding the IP addresses of DTS servers may pose security risks. Make sure that you take security measures, such as enhancing the password strength and limiting the port range.

5. Select objects for the task.

   Parameter	Description
   Task Stages	If you want to perform only full data migration, select Full Data Migration. Full Data Migration is selected by default. To ensure business continuity during data migration, select Full Data Migration and Incremental Data Migration. Note If you do not select Incremental Data Migration, we recommend that you do not write data to the source database during data migration to ensure data consistency.
   Source Objects	Select one or more objects from the Source Objects section. Click the icon to add the objects to the Selected Objects section. Note You can migrate a single database, multiple databases, or the entire instance.
   Selected Objects	The objects that you want to migrate.

6. Click Next: Advanced Settings to configure advanced settings.

   Parameter	Description
   Monitoring and Alerting	Specifies whether to configure alerting for the data migration task. If the task fails or the migration latency exceeds the specified threshold, the alert contacts receive notifications. Valid values: No: does not configure alerting. Yes: configures alerting. In this case, you must also configure the alert threshold and alert notification settings. For more information, see the Configure monitoring and alerting when you create a DTS task section of the Configure monitoring and alerting topic.
   Retry Time For Failed Connections	The retry time range for failed connections. If the source or destination database fails to be connected after the data migration task is started, DTS immediately retries a connection within the retry time range. Valid values: 10 to 1,440. Unit: minutes. Default value: 720. We recommend that you set the parameter to a value greater than 30. If DTS is reconnected to the source and destination databases within the specified retry time range, DTS resumes the data migration task. Otherwise, the data migration task fails. Note If you specify different retry time ranges for multiple data migration tasks that share the same source or destination database, the value that is specified later takes precedence. When DTS retries a connection, you are charged for the DTS instance. We recommend that you specify the retry time range based on your business requirements. You can also release the DTS instance at the earliest opportunity after the source database and destination instance are released.

7. After you complete the preceding configurations, click Next: Save Task Settings and Precheck in the lower-right corner of the page.

   Note

   • DTS performs a precheck before a data migration task starts. You can start a data migration task only after the task passes the precheck.

   • If the precheck fails, click View Details next to the failed check item, and fix the issue according to the instructions before running the precheck again.

   • If an alert is generated for an item during the precheck, perform the following operations based on the scenario:

     • For check items that cannot be ignored, click View Details after the failed check item, and fix the issue according to the prompts before running the precheck again.

     • For check items that can be ignored without fixing, you can click View Alert Details, Confirm Ignore, OK, and Run Precheck Again in sequence to skip the alert check items and run the precheck again. If you ignore the alert item, data inconsistency may occur and your business may be exposed to potential risks.

8. When Success Rate shows 100%, click Next: Purchase Instance.

9. On the Purchase page, read and select Data Transmission Service (Pay-as-you-go) Terms Of Service.

10. Click Buy And Start, and in the OK dialog box that appears, click OK.

    You can click the target migration instance on the data migration interface, and view the instance migration progress on the Task Management page.

    Note

    You can also view the progress of full migration or incremental migration tasks on the Backup And Restoration > Backup Data Upload History page of the destination instance in the RDS console.

11. When the full migration task progress reaches 100% and the incremental migration task is in progress, go to the Task Management > Incremental Migration page of the target migration task, and click the Migrate To Cloud button.

12. In the Do You Want To Migrate To Cloud dialog box, click Start Now and wait for the migration task to complete.

    Important

    To ensure data consistency, stop writing data to the source instance before you trigger the workload switchover. Wait for the migration task to complete and then switch over business connections. The entire process requires several minutes.