Instance-level migration of SQL Server to the cloud

Prerequisites

• The source must be a self-managed SQL Server database.

• The destination ApsaraDB RDS for SQL Server instance must meet the following requirements:

  • The available storage space on the instance must be greater than the size of the data files to be migrated. If the available storage space is insufficient, upgrade the instance specifications.

  • For SQL Server 2008 R2, you must create a database on the destination instance with the same name as the source database. This requirement does not apply to other versions.

• If you log on as a RAM user, you must meet the following requirements:

  • The RAM user must have the AliyunOSSFullAccess and AliyunRDSFullAccess permissions. For more information, see Control access to OSS by using RAM and Control access to RDS by using RAM.

  • Ensure that your Alibaba Cloud account has granted the official RDS service account access to your OSS resources.

    Authorization method

    1. Go to the Restoration page of the RDS for SQL Server instance and click Restore Backup Data from OSS.

    2. In the Import Guide, click Next twice to reach step 3. Import Data.

       If the message You have authorized RDS official service account to access your OSS appears in the lower-left corner of the page, authorization has been granted. Otherwise, click the Authorization URL on the page to grant it.

       

  • You must manually create an access policy in your Alibaba Cloud account and then attach it to the RAM user.

    Policy content

    {
        "Version": "1",
        "Statement": [
            {
                "Action": [
                    "ram:GetRole"
                ],
                "Resource": "acs:ram:*:*:role/AliyunRDSImportRole",
                "Effect": "Allow"
            }
        ]
    }

Limitations

This solution supports only full migration; incremental migration is not currently supported.

Billing

If you use the method described in this topic to migrate data, you are charged only for the use of OSS buckets.

Prerequisites

python --version
python3 --version
py --version

python --version
python3 --version

pip install alibabacloud_rds20140815
pip install alibabacloud-oss-v2
pip install alibabacloud_tea_openapi
pip install alibabacloud_tea_util

1. Back up self-managed SQL Server databases

1. Download the backup script.

2. Double-click the backup script to open it in Microsoft SQL Server Management Studio (SSMS). For instructions on how to connect using SSMS, see the official documentation.

3. Modify the following parameters as needed.

   Click to expand example

   SELECT
       /**
       * The list of databases to back up. Use a semicolon (;) or a comma (,) as the delimiter.
       * An empty value ('') or null backs up all databases except for system databases.
       * example: '[testdb]: TestDR, Test, readonly'
       **/
       @backup_databases_list = N'[dtstestdata],[testdb]'
       @backup_type = N'FULL',                    -- The backup type. Valid values: FULL (full backup), DIFF (differential backup), LOG (log backup).
       @backup_folder = N'C:\BACKUP'              -- The folder to store the backup files.
       @is_run = 1                                -- Specifies whether to run the backup. 1: Runs the backup. 0: Performs a check only.

   Parameter	Description
   @backup_databases_list	The databases to back up. Use a semicolon (;) or a comma (,) to separate multiple database names.
   @backup_type	The backup type. Valid values: FULL: full backup. DIFF: differential backup. LOG: log backup. Important For this procedure, you must set this parameter to FULL.
   @backup_folder	The local directory to store backup files. This directory is automatically created if it does not exist.
   @is_run	Specifies whether to run the backup. Valid values: 1: Runs the backup. 0: Performs a check only.

4. Run the backup script to back up the databases to the specified directory.

   

2. Upload backup files to OSS

1. To upload the backup file to OSS, you must first create a bucket.

   • If you use an existing OSS bucket, ensure it meets the following requirements:

     • The storage class of the bucket is Standard. Other storage classes, such as Infrequent Access, Archive, Cold Archive, and Deep Cold Archive, are not supported.

     • server-side encryption is disabled for the bucket.

   • If no bucket exists in OSS, you must create one. (Make sure that you have activated OSS.)

     1. Log on to the OSS console, click Buckets, and then click Create bucket.

     2. Configure the following key parameters. You can retain the default values for other parameters.

        Important

        • This bucket is created only for this migration, so you only need to configure the key parameters. You can delete the bucket after the migration is complete to prevent data breaches and reduce costs.

        • When you create the bucket, do not enable server-side encryption.

        Parameter	Description	Example
        Bucket Name	The name of the bucket. The name must be globally unique and cannot be changed after the bucket is created. Naming conventions: The name can contain only lowercase letters, digits, and hyphens (-). The name must start and end with a lowercase letter or a digit. The name must be 3 to 63 characters in length.	migratetest
        Region	The region where the bucket is located. For internal network transfers, the bucket, ECS instance, and ApsaraDB RDS instance must all be in the same region.	China (Hangzhou)
        Storage Type	Select Standard. This migration supports only the Standard storage class.	Standard

2. Upload the backup file to OSS.

   After you back up your on-premises database, upload the backup file to an OSS bucket that is in the same region as your ApsaraDB RDS instance. If the bucket and the instance are in the same region, they can communicate over the internal network. This allows for faster uploads and avoids outbound internet traffic fees. You can use one of the following methods:

   Use ossbrowser to upload the file (recommended)

   1. Download ossbrowser.

   2. For example, on Windows x64, decompress the downloaded oss-browser-win32-x64.zip package and double-click the oss-browser.exe application.

   3. Select AK, configure the Access Key ID and Access Key Secret parameters, retain the default values for the other parameters, and then click Log On.

      Note

      An AccessKey pair verifies your identity. Keep your AccessKey pair confidential to ensure data security.

      

   4. Click the destination bucket.

   5. Click , select the backup file that you want to upload, and then click Open.

   Use the OSS console to upload the file

   Note

   If your backup file is smaller than 5 GB, you can upload it directly from the OSS console.

   1. Log on to the OSS console.

   2. Click Buckets and then click the name of the destination bucket.

   3. On the Files tab, click Upload File.

   4. You can drag a backup file to the Files to Upload area, or click Select Files to choose a file.

   5. Click Upload File at the bottom of the page to upload the file to OSS.

   Use the OSS API for multipart upload (Python 3 project example)

   Note

   If your backup file is larger than 5 GB, we recommend using the OSS API to upload the file to an OSS bucket by using multipart upload.

   # -*- coding: utf-8 -*-
   """
   Alibaba Cloud OSS Python SDK v2
   Dependency: pip install alibabacloud-oss-v2
   """
   
   import os
   import sys
   from pathlib import Path
   import alibabacloud_oss_v2 as oss
   from alibabacloud_oss_v2 import exceptions as oss_ex
   
   
   def get_client_from_env(region: str, endpoint: str | None = None) -> oss.Client:
       """
       Create a v2 client from environment variables.
       - Prioritizes Region (recommended) and supports custom Endpoints (optional).
       - Compatible with both AccessKey (AK) and Security Token Service (STS) credentials:
           * AK: Requires OSS_ACCESS_KEY_ID and OSS_ACCESS_KEY_SECRET.
           * STS: Requires OSS_SESSION_TOKEN (compatible with the legacy OSS_SECURITY_TOKEN variable).
       """
       # For compatibility, map the legacy OSS_SECURITY_TOKEN variable to the v2-expected OSS_SESSION_TOKEN.
       sec_token_legacy = os.getenv("OSS_SECURITY_TOKEN")
       if sec_token_legacy and not os.getenv("OSS_SESSION_TOKEN"):
           os.environ["OSS_SESSION_TOKEN"] = sec_token_legacy
   
       ak = os.getenv("OSS_ACCESS_KEY_ID")
       sk = os.getenv("OSS_ACCESS_KEY_SECRET")
       st = os.getenv("OSS_SESSION_TOKEN")  # STS Token (optional)
   
       if not (ak and sk):
           raise ValueError("No valid AK found. Set the OSS_ACCESS_KEY_ID and OSS_ACCESS_KEY_SECRET environment variables. "
                            "If using STS, also set OSS_SESSION_TOKEN (or the legacy name OSS_SECURITY_TOKEN).")
   
       # Indicate the credential type in use.
       if st:
           print("STS Token (OSS_SESSION_TOKEN) detected. Using STS credentials.")
       else:
           print("No STS Token detected. Using AccessKey (AK) credentials.")
   
       credentials_provider = oss.credentials.EnvironmentVariableCredentialsProvider()
       cfg = oss.config.load_default()
       cfg.credentials_provider = credentials_provider
   
       # Basic network configuration.
       cfg.region = region  # Example: 'cn-hangzhou'
       if endpoint:
           # Optional: Specify a custom endpoint (e.g., for an internal network, acceleration, or a private domain).
           cfg.endpoint = endpoint
   
       # You can also add other configurations here, such as cfg.use_accelerate_endpoint = True
       return oss.Client(cfg)
   
   
   def resumable_upload_file_v2(
       client: oss.Client,
       bucket_name: str,
       object_key: str,
       file_path: str,
       part_size: int = 1 * 1024 * 1024,
       parallel_num: int = 4,
       checkpoint_dir: str | None = None,
   ):
       """
       Implements concurrent multipart upload with resumable upload capabilities.
   
       :param client: An initialized oss.Client instance.
       :param bucket_name: The name of the destination bucket.
       :param object_key: The key of the destination object (excluding the bucket name).
       :param file_path: The full path to the local file.
       :param part_size: The size of each part in bytes. Default: 1 MB.
       :param parallel_num: The number of concurrent upload threads. Default: 4.
       :param checkpoint_dir: The directory to store checkpoint information. If set to None, resumable upload is disabled.
       """
       file_path = str(file_path)
       if not Path(file_path).exists():
           raise FileNotFoundError(f"Error: Local file not found. Check the file_path configuration: {file_path}")
   
       # Construct the Uploader. Enable resumable upload if a checkpoint_dir is provided.
       if checkpoint_dir:
           uploader = client.uploader(
               enable_checkpoint=True,
               checkpoint_dir=checkpoint_dir,
               part_size=part_size,
               parallel_num=parallel_num,
           )
       else:
           uploader = client.uploader(
               part_size=part_size,
               parallel_num=parallel_num,
           )
   
       print(f"Starting to upload file: {file_path}")
       print(f"Destination Bucket: {bucket_name}")
       print(f"Destination Object: {object_key}")
       print(f"Part size: {part_size} bytes, Concurrency: {parallel_num}")
       if checkpoint_dir:
           print(f"Resumable upload: Enabled (checkpoint_dir={checkpoint_dir})")
       else:
           print("Resumable upload: Disabled (set checkpoint_dir to enable).")
   
       # Execute the upload. The Uploader automatically selects between multipart and single-part upload based on file size.
       result = uploader.upload_file(
           oss.PutObjectRequest(bucket=bucket_name, key=object_key),
           filepath=file_path,
       )
   
       print("-" * 30)
       print("File uploaded successfully!")
       print(f"HTTP Status: {result.status_code}")
       print(f"ETag: {result.etag}")
       print(f"Request ID: {result.request_id}")
       # The SDK for Python v2 enables CRC-64 data validation by default.
       print(f"CRC64: {result.hash_crc64}")
       print("-" * 30)
   
   
   def main():
       # Before running this code, make sure the required environment variables are set.
       # On macOS/Linux:
       #   For AK authentication:
       #     export OSS_ACCESS_KEY_ID=YOUR_AK_ID
       #     export OSS_ACCESS_KEY_SECRET=YOUR_AK_SECRET
       #   For STS authentication:
       #     export OSS_ACCESS_KEY_ID=YOUR_STS_ID
       #     export OSS_ACCESS_KEY_SECRET=YOUR_STS_SECRET
       #     export OSS_SECURITY_TOKEN=YOUR_STS_TOKEN
       #
       # On Windows:
       #   In PowerShell: $env:OSS_ACCESS_KEY_ID="YOUR_AK_ID"
       #   In Command Prompt: set OSS_ACCESS_KEY_ID=YOUR_AK_ID
   
       # ===================== Parameters (modify as needed) =====================
       # Example region: 'cn-hangzhou'. We recommend prioritizing the region parameter.
       region = "cn-hangzhou"
   
       # Optional: Specify a custom endpoint (e.g., for an internal network, private domain, or accelerated access).
       # Example: 'https://oss-cn-hangzhou.aliyuncs.com'
       endpoint = 'https://oss-cn-hangzhou.aliyuncs.com'  
   
       # Bucket and Object
       bucket_name = "examplebucket"
       object_key = "test.bak"
   
       # The full path to the local file to upload.
       # Windows example: r'D:\localpath\examplefile.txt' (note the 'r' prefix)
       # macOS/Linux example: '/Users/test/examplefile.txt'
       file_path = r"D:\oss\test.bak"
   
       # Part size and concurrency
       part_size = 1 * 1024 * 1024  # Default: 1 MB. OSS requires a minimum part size of 100 KB.
       parallel_num = 4
   
       # The directory for resumable uploads. Set to None to disable. We recommend specifying a writable directory.
       checkpoint_dir = str(Path.cwd() / ".oss_checkpoints")
       # =================== End of Parameters ===================
   
       print("Script execution starts...")
       try:
           client = get_client_from_env(region=region, endpoint=endpoint)
           # If resumable upload is enabled, ensure the checkpoint directory exists.
           if checkpoint_dir:
               Path(checkpoint_dir).mkdir(parents=True, exist_ok=True)
   
           resumable_upload_file_v2(
               client=client,
               bucket_name=bucket_name,
               object_key=object_key,
               file_path=file_path,
               part_size=part_size,
               parallel_num=parallel_num,
               checkpoint_dir=checkpoint_dir,
           )
       except FileNotFoundError as e:
           print(e)
       except oss_ex.ServiceError as e:
           # An error returned by the OSS server.
           print("\nAn OSS server-side error occurred.")
           print(f"HTTP Status: {getattr(e, 'status_code', 'N/A')}")
           print(f"Error Code: {getattr(e, 'code', 'N/A')}")
           print(f"Message: {getattr(e, 'message', 'N/A')}")
           print(f"Request ID: {getattr(e, 'request_id', 'N/A')}")
           print(f"Endpoint: {getattr(e, 'request_target', 'N/A')}")
       except oss_ex.BaseError as e:
           # An SDK client-side error (e.g., local, serialization, deserialization, or credential error).
           print("\nAn OSS SDK client-side error occurred.")
           print(str(e))
       except Exception as e:
           print(f"\nAn unknown error occurred: {e}")
   
   
   if __name__ == "__main__":
       main()

3. Run the migration script

1. Download the SQL Server migration script.

2. After you extract the package, run the following command to view the script's parameters.

   python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -h

   The command returns the following output:

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

   The following table describes the parameters.

   Parameter	Description
   access_key_id	The AccessKey ID of your Alibaba Cloud account.
   access_key_secret	The AccessKey Secret of your Alibaba Cloud account.
   rds_instance_id	The ID of the destination RDS instance.
   oss_endpoint	The endpoint of the OSS region where your bucket is located.
   oss_bucket	The name of the bucket that contains the backup files.
   directory	The directory in the OSS bucket for backup files. For the root directory, enter /.

3. Run the migration script to complete the migration.

   This topic provides an example of fully migrating all backup files that meet specified conditions from the Migrationdata directory of an OSS bucket named testdatabucket to an ApsaraDB RDS for SQL Server instance with the instance ID rm-2zesz5774ud8s****.

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

4. View migration task progress

Follow the instructions for your ApsaraDB RDS instance's SQL Server version.

Common errors

API reference