Data Transmission Service:Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance by using a physical gateway on a bastion host

Scenarios

Prerequisites

• The self-managed SQL Server database meets the following requirements:
  • The source database runs SQL Server 2019, SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, or SQL Server 2005.
    Note The source database resides on an ECS instance, a data center, or on a third-party cloud server. The source database cannot be an RDS instance.
  • The HTTP_PROXY and HTTPS_PROXY environment variables are not configured for the server on which the self-managed SQL Server database resides.
    Important If you configure the environment variables, the system preferentially uses the proxy gateway that is specified by the environment variables. As a result, the proxy gateway that is installed on the bastion host does not take effect, and network connections fail.
• The destination RDS instance must meet the following requirements:
  • The RDS instance runs SQL Server 2019, SQL Server 2017, SQL Server 2016, SQL Server 2012, or SQL Server 2008 R2.
  • The RDS instance runs the same major engine version as the self-managed SQL Server database or a later version.
• Before you begin, make sure that the following operations are complete:
  • An AccessKey pair is created, and the AccessKey ID and AccessKey secret are obtained. The AccessKey pair is used for identity verification when you register with or log on to the DBS console from your backup gateway. For more information, see Create an AccessKey pair.
  • The AliyunDBSFullAccess and AliyunOSSFullAccess permissions are obtained if you want to add a backup gateway as a RAM user. For more information, see Grant permissions to a RAM user.
    Note

    • By default, the preceding permissions are granted to your Alibaba Cloud account when you activate DBS.
    • After a backup gateway is added, the backup gateway is available in the DBS console to all RAM users that belong to your Alibaba Cloud account.
• If you want to back up SQL Server databases, you must assign the Sysadmin role to the NT AUTHORITY\SYSTEM account. You can perform this operation by executing the following SQL statements:

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

  Note By default, the NT AUTHORITY\SYSTEM account is used to start AliyunDBSAgent.
• If the bastion host runs Linux, make sure that the Java Runtime Environment (JRE) 1.8 is installed on the server on which the self-managed SQL Server database resides. You can download JRE 1.8 from the official website and then install JRE 1.8.

Configuration process

Step 1: Install a proxy gateway

This section describes how to install a proxy gateway on a Windows or Linux bastion host.

Windows

1. Go to the Data Migration Tasks page.
   1. Log on to the Data Management (DMS) console.
   2. In the top navigation bar, click DTS.
   3. In the left-side navigation pane, choose DTS (DTS) > Data Migration.
   Note

   • Operations may vary based on the mode and layout of the DMS console. For more information, see Simple mode and Configure the DMS console based on your business requirements.
   • You can also go to the Data Migration Tasks page of the new DTS console.
2. From the drop-down list next to Data Migration Tasks, select the region in which the data migration instance resides.
   Note If you use the new DTS console, you must select the region in which the data migration instance resides in the upper-left corner.
3. Click Create Task. In the Source Database section of the Configure Source and Destination Database step, click Create Physical Protocol Gateway.
   Note The Create Physical Protocol Gateway element appears only if you set the Database Type parameter to SQLServer and the Access Method parameter to Physical Protocol.
4. In the Installation Command dialog box, configure the Region of Backup Gateway and Network Type of Backup Gateway parameters, copy the download link, and then download the installation package.
   Note

   • Internet: DBS accesses the database by using a public IP address.
   • ECS Private Network/VPC: DBS accesses the database by using an Express Connect circuit of Alibaba Cloud.
5. Install the proxy gateway on a Windows bastion host.
   1. Double-click the setup.exe application in the downloaded file.
   2. Select an installation language and click OK.
   3. Click Next.
   4. Read and accept the terms of the agreement and click Next.
   5. Select Proxy Gateway and click Next.
   6. Select an installation directory, click Next, and then click Yes.
      Note By default, the proxy gateway is installed in the C:\Program Files (x86)\aliyun\dbs_agent directory.
   7. Click Next to install the basic file of the proxy gateway.
   8. Click Next and then Done.
6. Open Task Manager and check whether the proxy gateway is successfully installed.
   The following figure shows that the proxy gateway is successfully installed.

Linux

Install the proxy gateway on a Linux bastion host by using the link for downloading the backup gateway in the DBS console.

1. Log on to the Database Backup (DBS) console.
2. In the left-side navigation pane, click Backup Gateways and select a region in the upper-left corner of the page.
   

   Note Select a region nearest to your databases. For example, if your databases reside in Hangzhou, we recommend that you install a backup gateway in the China (Hangzhou) region.
3. Click Install Backup Gateway in the upper-right corner of the page.
   
4. Configure the Backup Gateway Network Type parameter and copy the command that is used to install backup gateways. Run the command on your database server.
   Note

   • Public Network: You can access DBS by using a public IP address.
   • ECS Private Network / VPC: You can access DBS by using an Express Connect circuit from Alibaba Cloud.

   
   1. Run the command on the Linux CLI.
      
      The system downloads and runs the installation package.
   2. Select an installation language. Enter 0 to select English.
      
   3. Enter 1 to read the backup gateway agreement.
      
   4. Enter 1 to accept the backup gateway agreement.
      
   5. Select components that you want to install. By default, the backup gateway (Agent) is installed. Enter N.
      The system will install a proxy gateway.
   6. Enter the Y to confirm the installation.
   7. Confirm the installation directory and enter 0.
      The destination directory is created in /usr/local/aliyun/dbs_agent.
   8. Confirm the component that you want to install. Enter 1 to start the installation.
      Wait for the installation to complete. The installation takes about 1 minutes.
5. Run the following command to check whether the proxy gateway is installed:

   ps aux | grep app_aliyun_proxy

   The following figure shows that the installation process runs as expected and the proxy gateway is installed. If you have questions about installation, contact technical support in the DingTalk group (ID 35585947).

Step 2: Install a backup gateway

This section describes how to install a backup gateway on the server on which the self-managed SQL Server database resides.

1. Copy the download link from Step 4 in the Step 1: Install a proxy gateway section. Then, download the installation package.
   Important The link that is used to download the backup gateway must be the same as the download link obtained from Step 4 in the Step 1: Install a proxy gateway section. Otherwise, the server on which the self-managed SQL Server database resides fails to access the Internet.
2. Install AliyunDBSAgent on a Windows device or Windows server.
   1. Double-click the setup.exe application 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 Agent and click Next.
   5. Select an installation directory, click Next, and then click Yes.
   6. Configure the following parameters: Agent Region, AccessKey ID, Access Key Secret, Proxy Gateway Host, 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 RDS instance.
      • The AccessKey pair is stored in plaintext in the .\config\dbs-agent.conf file in the installation directory.
      • You must set the Proxy Gateway Host parameter to the IP address of the proxy gateway that is installed in Step 1 and the Proxy Gateway Port parameter to 9797. The IP address of the proxy gateway is the private IP address of the bastion host.

      After the configuration is complete, the system attempts to connect to the proxy gateway that is installed on the bastion host in Step 1. If the connection fails, the system reports an error. In this case, you must check whether the proxy gateway is successfully installed.

   7. Confirm the component package that you want to install and click Next.
      The installation starts. The process requires approximately 1 to 5 minutes.
   8. Click Done.
      You can view the installation progress 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.
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 AliyunDBSAgent service is started. If the service is not started, right-click AliyunDBSAgent and select Start.
      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.
   You can click Refresh on the Backup Gateways page of the DBS console to view the new backup gateway.

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

Step 3: Migrate data from the self-managed SQL Server database to the RDS instance