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, the following requirements must be met:

  • The RAM user is granted the AliyunOSSFullAccess and AliyunRDSFullAccess permissions. For more information about how to grant permissions to a RAM user, see Manage OSS permissions by using RAM and Manage ApsaraDB RDS permissions by using RAM.

  • Your Alibaba Cloud account (primary account) has authorized the ApsaraDB RDS official service account to access your OSS resources.

    Click to view the authorization method

    1. Go to the Restoration page of the ApsaraDB RDS instance, and then click Restore Backup Data from OSS.

    2. On the Import Guide page, click Next twice to go to the 3. Import Data step.

       If You have authorized the ApsaraDB RDS official service account to access your OSS resources appears in the lower-left corner of the page, the authorization is complete. Otherwise, the authorization is not complete. In this case, click Authorization URL on the page to grant the authorization.

       On the 3. Data Import page, configure the following parameters:

       • Database Name: Enter the name of the destination database.

       • OSS Bucket: Select the bucket that stores the uploaded backup file from the drop-down list.

       • OSS File List: Enter a file name prefix to search for the backup file, and then select the file.

       • Migration Method: Select Open Database (Only One Full Backup File) or Do Not Open Database (Differential Backup or Log Files Available).

       • Consistency Check Method: Select Synchronous DBCC or Asynchronous DBCC.

  • Manually create a permission policy in your Alibaba Cloud account (primary account), and then attach the policy to the RAM user.

    Click to view the 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.

   After the backup is complete, .bak backup files are generated in the C:\backup directory, for example, dtstestdata_FULL_20200408154821.bak and testdb_FULL_20200408154821.bak. These files indicate that the backup script ran successfully.

2. Upload backup files to OSS

1. Before you upload backup files to OSS, you must create a bucket in OSS.

   • If a bucket already exists in OSS, make sure that the bucket meets the following requirements:

     • The storage class of the bucket is Standard. The IA, Archive, Cold Archive, and Deep Cold Archive storage classes are not supported.

     • Server-side encryption is not enabled for the bucket.

   • If no bucket exists in OSS, create a bucket first. (Make sure that OSS is activated.)

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

     2. Configure the following key parameters and keep the default values for other parameters.

        Important

        • The bucket is used only for this data migration, so you need to only configure the key parameters. After the migration is complete, delete the bucket in a timely manner to prevent data leaks and additional fees.

        • Do not enable server-side encryption when you create the bucket.

        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 rules: 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 in which the bucket resides. If you upload data to the bucket from an ECS instance over the internal network and restore the data to the ApsaraDB RDS instance over the internal network, make sure that the ECS instance, bucket, and ApsaraDB RDS instance reside in the same region.	China (Hangzhou)
        Storage Type	Select Standard. The migration operation described in this topic does not support buckets of other storage classes.	Standard

2. Upload the backup file to OSS.

   After the backup of the self-managed database is complete, upload the backup file to an OSS bucket that resides in the same region as your ApsaraDB RDS instance. If the bucket and the ApsaraDB RDS instance reside in the same region, the bucket and the instance can communicate over the internal network. In this case, no Internet traffic fees are charged and data is uploaded at a faster speed. You can use one of the following methods:

   Upload files by using ossbrowser (recommended)

   1. Download ossbrowser.

   2. In this example, Windows x64 is used. Decompress the downloaded oss-browser-win32-x64.zip package, and then double-click the oss-browser.exe application.

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

      Note

      An AccessKey is used for identity authentication to ensure data security. Keep your AccessKey confidential.

   4. Click the destination bucket to go to the bucket.

   5. Click , select the backup file that you want to upload, and then click Open. The local file is uploaded to OSS.

   Upload files by using the OSS console

   Note

   If the backup file is smaller than 5 GB in size, upload the backup file directly in the OSS console.

   1. Log on to the OSS console.

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

   3. In the Files, click Upload File.

   4. Drag the backup file to the Files to Upload section, or click Select Files and select the backup file that you want to upload.

   5. Click Upload File at the bottom of the page. The local backup file is uploaded to OSS.

   Upload files by using the OSS API (Python 3 project example)

   Note

   If the backup file is larger than 5 GB in size, call OSS API operations to upload the backup file to the 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.
       - Prioritize using Region (recommended), but also support custom Endpoints (optional).
       - Compatible with both AK and STS:
           * AK: Requires OSS_ACCESS_KEY_ID / OSS_ACCESS_KEY_SECRET
           * STS: Also requires OSS_SESSION_TOKEN (compatible with the old variable OSS_SECURITY_TOKEN)
       """
       # Compatibility: If the user uses the old variable OSS_SECURITY_TOKEN, map it 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 old name OSS_SECURITY_TOKEN).")
   
       # Indicate the type of credential used
       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: Custom Endpoint (e.g., internal network, accelerated, dedicated 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,
   ):
       """
       Implement concurrent multipart upload with resumable upload.
   
       :param client: Initialized oss.Client
       :param bucket_name: Destination bucket name
       :param object_key: Destination object key (without bucket name)
       :param file_path: Full path of the local file
       :param part_size: Part size in bytes, default is 1 MB
       :param parallel_num: Number of concurrent upload threads, default is 4
       :param checkpoint_dir: Directory to store breakpoint information; if 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 based on whether 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 (Uploader automatically chooses between multi/single part concurrent upload based on 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}")
       # CRC-64 checksum; v2 enables data validation by default
       print(f"CRC64: {result.hash_crc64}")
       print("-" * 30)
   
   
   def main():
       # Before running the code example, make sure you have set the corresponding environment variables.
       # macOS/Linux:
       #   AK method:
       #     export OSS_ACCESS_KEY_ID=YOUR_AK_ID
       #     export OSS_ACCESS_KEY_SECRET=YOUR_AK_SECRET
       #   STS method:
       #     export OSS_ACCESS_KEY_ID=YOUR_STS_ID
       #     export OSS_ACCESS_KEY_SECRET=YOUR_STS_SECRET
       #     export OSS_SECURITY_TOKEN=YOUR_STS_TOKEN
       #
       # Windows:
       #   Powershell: $env:OSS_ACCESS_KEY_ID="YOUR_AK_ID"
       #   cmd: set OSS_ACCESS_KEY_ID=YOUR_AK_ID
   
       # ===================== Parameters (modify as needed) =====================
       # Region example: 'cn-hangzhou'; we recommend using Region first
       region = "cn-hangzhou"
   
       # Optional: Custom Endpoint (for internal network, dedicated domain, accelerated domain name, etc.)
       # Example: 'https://oss-cn-hangzhou.aliyuncs.com'
       endpoint = 'https://oss-cn-hangzhou.aliyuncs.com'
   
       # Bucket and Object
       bucket_name = "examplebucket"
       object_key = "test.bak"
   
       # Full path of the local file to upload.
       # Windows example: r'D:\localpath\examplefile.txt'  (note the r at the beginning)
       # macOS/Linux example: '/Users/test/examplefile.txt'
       file_path = r"D:\oss\test.bak"
   
       # Sharding and concurrency
       part_size = 1 * 1024 * 1024  # Default is 1 MB; OSS requires a minimum part size of 100 KB
       parallel_num = 4
   
       # Resumable upload directory (pass 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, make sure the 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:
           # 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:
           # SDK local/serialization/deserialization/credential errors
           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

Related API operations