ApsaraDB RDS:Use SSMS to migrate data to an RDS for SQL Server instance

Prerequisites

Before you begin, make sure you have:

• An RDS for SQL Server instance that meets all of the following requirements:

  • Runs RDS Basic Edition, RDS High-availability Edition, or RDS Cluster Edition. If it runs RDS High-availability Edition, it must use SQL Server 2012 or later.

  • Belongs to the general-purpose or dedicated instance family. The shared instance family is not supported.

  • Uses the subscription or pay-as-you-go billing method. Serverless instances are not supported.

  • Resides in a virtual private cloud (VPC). To change the network type, see Change the network type.

  • Has a storage capacity larger than the source database. Size the destination instance to at least 1.2x the source storage. If the current capacity is insufficient, expand the storage capacity before proceeding.

  • Was created on or after the following dates, depending on its edition:

    • RDS High-availability Edition or RDS Cluster Edition: January 1, 2021

    • RDS Basic Edition: September 2, 2022

  To check the creation date, go to the Basic Information page of your instance in the ApsaraDB RDS console and look for the Creation Time field in the Status section.

• SSMS installed on your local computer or on an Elastic Compute Service (ECS) instance. If you use an ECS instance, it must reside in a VPC, run a Windows Server image, and have a public IP address assigned.

  The examples in this guide use SSMS 19.1. Steps may vary depending on your SSMS version and settings.

• A system admin account on the RDS instance. Log in to the instance with your Alibaba Cloud account and create a system admin account.

• An IP address whitelist on the RDS instance that allows connections from your client:

  • To connect an ECS instance over the internal network, the ECS instance and the RDS instance must reside in the same region and the same VPC. Add the private IP address of the ECS instance to the whitelist.

  • To connect from a local computer, add its public IP address to the whitelist. See Configure an IP address whitelist.

Limitations

Review these limitations before starting the migration:

• The source database must be offline during export. The export captures a transactionally consistent snapshot of the database, so no write activity can occur while the export runs. Plan a maintenance window that accounts for both export duration and import duration. Export speed depends on the source instance specifications.

• Constraints and views must not cause export failures. Validate the source database schema before exporting.

• Database name conflicts block the import. If a database with the same name already exists on the destination RDS instance, the import fails or produces inconsistent data.

• Compatibility levels must match. Run the following query on both the source and destination instances to verify compatibility:

  SELECT name, compatibility_level FROM sys.databases;

  For more information, see ALTER DATABASE compatibility level.

Prepare the source database (Azure SQL Database)

Enable Internet access to Azure SQL Database and configure its firewall to allow connections from the public IP address of your ECS instance or local computer. For instructions, see the Azure documentation or contact Azure support.

Step 1: Export data from Azure SQL Database

Stop all writes to the source database before exporting. This ensures the exported .bacpac file is transactionally consistent.

1. Connect to Azure SQL Database using SSMS.

2. In Object Explorer, expand Databases, right-click the database you want to migrate, and choose Tasks > Export Data-tier Application.

3. On the introduction screen, click Next.

Step 2: Import data into the RDS instance

1. Open SSMS and connect to the RDS instance. In the Connect to Server dialog box, configure the following parameters:

   Parameter	Value
   Server type	Database Engine
   Server name	The internal endpoint or public endpoint of the RDS instance. To find the endpoint, see View and change the endpoints and port numbers.
   Authentication	SQL Server Authentication
   Login	Username of the system admin account
   Password	Password of the system admin account

2. Click Connect.

3. In Object Explorer, right-click Databases and select Import Data-tier Application.

4. Click Next.

Step 3: Verify data consistency

Run the following queries on both the source and destination databases and compare the results. Matching results confirm a consistent migration.

Query 1: Check total row count

USE <database name>;

SELECT SUM(b.rows) AS 'RowCount'
FROM sysobjects AS a INNER JOIN
sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))

Query 2: Check data file sizes

USE <database name>;

SELECT a.name [File name]
  ,cast(a.[size]*1.0/128 as decimal(12,1)) AS [Specified file size (MB)]
  ,CAST( fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [Storage occupied by the file (MB)]
  ,CAST( (fileproperty(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0  AS DECIMAL(12,1)) AS [Percentage of the storage occupied by the file to the total storage capacity]
  ,CASE WHEN A.growth =0 THEN 'Fixed file size' ELSE 'Increment at which the storage that is allocated to the file increases' end [Increase mode]
  ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN 'Fixed increment'
    WHEN A.growth > 0 AND is_percent_growth = 1 THEN 'Increment represented by an integer percentage'
    ELSE 'Fixed file size' END AS [Increase mode]
  ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128as decimal(12,0)) AS VARCHAR)+'MB'
    WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+'%'
    ELSE 'Fixed file size' end AS [Increment (% or MB)]
  ,a.physical_name AS [File directory]
  ,a.type_desc AS [File type]
FROM sys.database_files  a
INNER JOIN sys.sysfiles AS s ON a.[file_id]=s.fileid
LEFT JOIN sys.dm_db_file_space_usage b ON a.[file_id]=b.[file_id]
ORDER BY a.[type]

After data consistency verification is complete, you can switch your workloads to the RDS for SQL Server instance and test whether features run as expected.