All Products
Search

This Product

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

    Document Center

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

    Last Updated:Dec 20, 2023

    This topic describes how to migrate data from a self-managed SQL Server database whose server cannot access the Internet to an ApsaraDB RDS for SQL Server instance.

    Scenarios

    If the server on which a self-managed SQL Server database resides cannot access the Internet but uses a bastion host for network communication, you can refer to this topic to migrate data from the self-managed SQL Server database to an RDS instance.

    1. Install a proxy gateway on the bastion host.

    2. Install the backup gateway that is provided by Database Backup (DBS) on the server on which the self-managed SQL Server database resides.

    3. Use the physical gateway-based migration feature to migrate data from the self-managed SQL Server database to the RDS instance.

    Note

    If the server on which the self-managed SQL Server database resides can access the Internet, you can select another migration method. For more information, see Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance by using a physical gateway.

    Prerequisites

    • The self-managed SQL Server database meets the following requirements:

      • The self-managed SQL Server 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 self-managed SQL Server database resides on an ECS instance, in a data center, or on a third-party cloud server. The self-managed SQL Server 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 preceding 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 is not used, and network connections fail.

    • The 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 to authenticate, register, and publish your backup gateway in the DBS console. For more information, see Create an AccessKey pair.

      • If you use a RAM user to add a backup gateway, make sure that the AliyunDBSFullAccess policy is attached to the RAM user. For more information, see Grant permissions to RAM users.

        Note

        • By default, the preceding permissions are granted to your Alibaba Cloud account when you activate DBS.

        • After you add a backup gateway, the backup gateway is available in the DBS console to all RAM users that belong to your Alibaba Cloud account.

    • The Sysadmin role is assigned to the NT AUTHORITY\SYSTEM account if you want to back up an SQL Server database. You can execute the following SQL statements to assign the Sysadmin role to the NT AUTHORITY\SYSTEM account:

      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.

    Procedure

    image

    Step

    Description

    Step 1: Install a proxy gateway on a bastion host

    Install a proxy gateway on the bastion host.

    During data backup, the proxy gateway receives data from the database server on which the backup gateway is located and forwards the data to DBS cloud storage over the Internet. During restoration or database download, the proxy gateway receives data from DBS cloud storage and forwards the data to the database server.

    Step 2: Install a backup gateway on the server of your database

    Install a backup gateway on the database server.

    The backup gateway is the backup software that is installed on the database server. During data backup, the database server connects to and queries the database, and then sends data to the bastion host on which the proxy gateway is installed. During restoration, the database server obtains data over the Internet by using the proxy gateway on the bastion host and writes the data to the database by using the backup gateway.

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

    In the Data Transmission Service (DTS) console, set the Access Method parameter of the migration task to Physical Protocol to migrate data from the self-managed SQL Server database to the RDS instance.

    Note

    For more information about the usage notes, migration description, and supported migration relationships in the DTS console, see Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance by using a physical gateway.

    Step 1: Install a proxy gateway on a bastion host

    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
    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 Databases 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 Deployment 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 > Done.

    6. Open Task Manager and check whether the proxy gateway is successfully installed.

    Linux

    Log on to the DBS console and obtain the download URL of the backup gateway. Then, install the backup gateway on the Linux bastion host.

    1. Log on to the Database Backup (DBS) console.

    2. In the left-side navigation pane, click Backup Gateways. In the top navigation bar, select a region.

      Select a region to install a backup gateway

      Note

      We recommend that you select a region that is nearest to your database. For example, if your database resides in the China (Hangzhou) region, we recommend that you install the 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 the backup gateway. Then, 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.

      1. Run the installation command in the Linux CLI. The system downloads and runs the installation package. Sample code:

        
[root@iZbp****** ~]# wget -O aliyunDBSAgentInstaller.jar https://aliyun-dbs.oss-cn-hangzhou-internal.aliyuncs.com/installer/0.0.141/aliyunDBSAgentInstaller-0.0.141.jar && sudo java -Dregion=cn-hangzhou -jar aliyunDBSAgentInstaller.jar
--2023-08-25 16:04:52--  https://aliyun-dbs.oss-cn-hangzhou-internal.aliyuncs.com/installer/0.0.141/aliyunDBSAgentInstaller-0.0.141.jar
Resolving aliyun-dbs.oss-cn-hangzhou-internal.aliyuncs.com (aliyun-dbs.oss-cn-hangzhou-internal.aliyuncs.com)... 100.XXX.XX.XX, 100.XXX.XX.XX, 100.XXX.XX.XX, ...
Connecting to aliyun-dbs.oss-cn-hangzhou-internal.aliyuncs.com (aliyun-dbs.oss-cn-hangzhou-internal.aliyuncs.com)|100.XXX.XX.XX|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 247955671 (236M) [application/x-java-archive]
Saving to: 'aliyunDBSAgentInstaller.jar'

100%[=======================================================================================================>] 247,955,671 19.9MB/s   in 11s    

2023-08-25 16:05:03 (21.4 MB/s) - ‘aliyunDBSAgentInstaller.jar’ saved [247955671/247955671]

      2. Select an installation language. Enter 0 to select Chinese. Enter 1 to select English. 

        Select your language
0  [x] chn
1  [ ] eng
Input selection: 
0

      3. Enter 1 and read the gateway protocol. 

        Press 1 to continue, 2 to quit, 3 to redisplay
1

      4. Enter 1 to agree to the gateway protocol. 

        Press 1 to accept, 2 to reject, 3 to redisplay
1

      5. Select the component that you want to install. If you select Y, the component is a backup gateway. If you select N, the component is a proxy gateway. In this example, a proxy gateway is installed. Select N and enter Y to check the component information. 

        Enter Y for Yes, N for No: 
N
--
DG
--
Enter Y for Yes, N for No: 
Y
Done!

      6. Enter 1 to continue the installation. 

        Press 1 to continue, 2 to quit, 3 to redisplay
1

      7. Select an installation path. If you press the Enter key, the default path /usr/local/aliyun/dbs_agent is used.

        If you want to use a custom path, enter the custom path and enter 1 to confirm it. In this topic, the custom path /usr/local/aliyun/daili_dbs_agent is used as an example to show how to install the proxy gateway. 

        Select an installation path: [/usr/local/aliyun/dbs_agent] 
/usr/local/aliyun/daili_dbs_agent

Press 1 to continue, 2 to quit, 3 to redisplay
1

      8. Confirm the component and enter 1 to start the installation.

        The installation takes about one to five minutes to complete. 

        Select the package that you want to install:

  [x] Pack 'Proxy gateway base file' required
Done!

Press 1 to continue, 2 to quit, 3 to redisplay
1

└──────────────┴─────────────┴─────────────────┴───────────────────────────────────┴─────────────┴────────────────────┴─────────────┘
Installing
└──────────────┴─────────────┴─────────────────┴───────────────────────────────────┴─────────────┴────────────────────┴─────────────┘

====================
Installation started

Platform: linux,version=3.10.0-1XXX.XX.X.el7.x86_64,arch=x64,symbolicName=null,javaVersion=1.8.0_372
[ Starting to unpack ]
[Processing package: Proxy gateway base file (1/1) ]
Cleaning up the target folder ...
[ Unpacking finished ]
Installation finished

└──────────────┴─────────────┴─────────────────┴───────────────────────────────────┴─────────────┴────────────────────┴─────────────┘
Installed
└──────────────┴─────────────┴─────────────────┴───────────────────────────────────┴─────────────┴────────────────────┴─────────────┘

Installation was successful
Application installed on /usr/local/aliyun/daili_dbs_agent
[ Writing the uninstaller data ... ]
[ Console installation done ]

    5. Run the following command to check whether the proxy gateway is installed: 

      ps aux | grep app_aliyun_proxy

      The following shows that the proxy gateway is installed. If you have questions about installation, contact technical support in the DingTalk group (ID: 35585947). 

      [root@iZbp****** ~]# ps aux | grep app_aliyun_proxy
root     1****  0.0  0.5 7*****  9*** ?        Ssl  16:06   0:00 /usr/local/aliyun/daili_dbs_agent/dist/app_aliyun_proxy/app_aliyun_proxy -addr :9797 -logdir /usr/local/aliyun/daili_dbs_agent/logs
root     2****  0.0  0.0 1*****   9** pts/1    S+   16:08   0:00 grep --color=auto app_aliyun_proxy

    Step 2: Install a backup gateway on the server of your database

    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 cannot 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 information is stored in the plaintext format in the .\config\dbs-agent.conf file of the installation directory.

        • You must specify the IP address of the proxy gateway that is installed in Step 1 as the endpoint of the proxy gateway and 9797 as the port number of the proxy gateway. 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. Process heartbeat of the gateway

    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

    For more information, see Procedure.

    Note

    When you configure the source database, you must set the Physical Protocol Gateway (DBS Backup Gateway) parameter to the new backup gateway.