Migrate data from an on-premises SQL Server instance to an ApsaraDB RDS for SQL Server instance

Precautions

• Only full backup files can be used for the data migration.
• To migrate data by using a RAM user, you must grant the RAM user the AliyunOSSFullAccess and AliyunRDSFullAccess permissions. For more information, see Grant permissions to a RAM user.

Before you begin

1. Install Python 2.7.10. For more information, visit the Python official website.
2. Check whether Python 2.7.10 is installed.
   • For Windows operating systems

     Run the C:\>c:\Python27\python.exe -V command to check the Python version. If Python 2.7.10 is displayed, Python 2.7.10 is installed.

     If the system states that the preceding command is not an internal or external command, add the Python installation path and the pip command path to the Path environment variable.

     
   • For Mac, Linux, or Unix operating systems

     Run the python -V command to check the Python version. If Python 2.7.10 is displayed, Python 2.7.10 is installed.

3. Use one of the following two methods to install the SDK dependency package:
   • Run the pip command.

     pip install aliyun-python-sdk-rds
     pip install oss2

   • Use the source code.

     # Clone the API repository.
     git clone https://github.com/aliyun/aliyun-openapi-python-sdk.git
     # Install the SDK core repository of Alibaba Cloud.
     cd aliyun-python-sdk-core
     python setup.py install
     # Install the RDS SDK of Alibaba Cloud.
     cd aliyun-python-sdk-rds
     python setup.py install
     # Clone the OSS SDK.
     git clone https://github.com/aliyun/aliyun-oss-python-sdk.git
     cd aliyun-oss-python-sdk
     # Install oss2.
     python setup.py install

4. Create an OSS bucket. Make sure that the OSS bucket resides in the same region as the RDS instance. For more information, see Create buckets.
   Note Skip this step if an OSS bucket that resides in the same region as the RDS instance already exists.
5. Create databases on the RDS instance. Make sure that each database whose data you want to migrate from the on-premises instance has a counterpart with an identical name on the RDS instance. In addition, keep the created databases empty.
   • If the RDS instance runs SQL Server 2012 or later, skip this step.
   • If the RDS instance runs SQL Server 2008 R2, see Create databases and accounts for an ApsaraDB for RDS instance running SQL Server 2008 R2.
6. Back up some or all of the databases on the on-premises instance.
   Warning

   • To ensure data consistency, we recommend that you do not write data to these databases during the full backup.
   • If you do not use the backup script to perform the full backup, the names of the generated backup files must follow the database name_backup type_backup time. bak format. Example: Testdb_FULL_20180518153544.bak.
   1. Download the backup script file.
   2. Double-click the backup script file and open it by using the Microsoft SQL Server Management Studio (SSMS) client.
   3. Modify the following parameters.

      Parameter	Description
      @backup_databases_list	The databases that you want to back up. Separate them with semicolons (;) or commas (,).
      @backup_type	The type of the backup. Valid values: FULL: full backup DIFF: incremental backup LOG: log backup Notice In this example, the value must be FULL.
      @backup_folder	The directory that stores the backup files on the RDS instance. If you do not specify this parameter, a directory will be automatically created.
      @is_run	Specifies whether to perform a backup. Valid values: 1: specifies to perform a backup. 0: specifies only to perform a check.

      Example:

      
   4. Execute the backup script to back up the specified databases and store the backup files to the specified directory.
      
7. Use one of the following three methods to upload the backup files to the OSS bucket that you created.
   Warning Make sure that the OSS bucket and the RDS instance reside in the same region. This allows the RDS instance to read the backup files at faster speeds. This also allows you to avoid task failures that may occur if the backup files cannot be downloaded.

   Method	Description
   Use ossbrowser to upload backup files	Use ossbrowser to upload the backup files to OSS. For more information, see Quick start. This is the recommended method. Notice If the on-premises instance is deployed in an Elastic Compute Service (ECS) instance that resides in a Virtual Private Cloud (VPC), you can use the OSS endpoint for access over a VPC to upload the backup files. This improves upload efficiency.
   Use the OSS console to upload backup files	If the backup files are smaller than 5 GB in size, upload them by using the OSS console. For more information, see Upload an object.
   Call OSS API operations to upload backup files	If you want to upload the backup files unattended, use the OSS API to perform a resumable upload. For more information, see Multipart upload and resumable upload.

Procedure

1. Download the migration script package RDSSQLCreateMigrateTasksBatchly.py.
2. Decompress the migration script package and run the following command to view the parameters that you need to specify:

   python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -h

   The following information is displayed:

   ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -e <oss_endpoint> -b <oss_bucket> -d <directory>

   Table 1. Parameters

   Parameter	Description
   access_key_id	The AccessKey ID of the Alibaba Cloud account to which the RDS instance belongs.
   access_key_secret	The AccessKey secret of the Alibaba Cloud account to which the RDS instance belongs.
   rds_instance_id	The ID of the RDS instance.
   oss_endpoint	The endpoint of the OSS bucket that stores the backup files. For more information about how to obtain the endpoint, see Bucket overview.
   oss_bucket	The name of the OSS bucket that stores the backup files.
   directory	The folder that stores the backup files in the OSS bucket. If the backup files are stored in the root folder, enter a forward slash (/).

3. Execute the migration script to complete the migration task.

   Example:

   Migrate all of the backup files that meet the specified conditions from the Migrationdata folder in the OSS bucket named testdatabucket to the RDS instance whose ID is rm-2zesz5774ud8s****.

   python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -k LTAIQ**** -s BMkIUhroub******** -i rm-2zesz5774ud**** -e oss-cn-beijing.aliyuncs.com -b testdatabucket -d Migrationdata

4. View the progress of the migration task.
   1. Log on to the ApsaraDB for RDS console.
   2. In the top navigation bar, select the region where y RDS instance resides.
      
   3. Find your RDS instance and click its ID.
   4. Perform the following steps based on the SQL Server version of your RDS instance:
      • SQL Server 2008 R2

        In the left-side navigation pane, click Database Migration to Cloud. You can view all the submitted migration tasks.

        Note You can also click Refresh in the upper-right corner of the page to view the latest statuses of migration tasks.
        
      • SQL Server 2012 and later

        In the left-side navigation pane, click Backup and Restoration. Then, click the Backup Data Upload History tab.

        Note To view the migration records of the last seven days. You can specify the time range to view the migration tasks over a specific period of time.

Common errors

Related operations