Use SSMS to migrate data to Alibaba Cloud ApsaraDB RDS for SQL Server-ApsaraDB RDS(RDS)-阿里云帮助中心

Prerequisites

You have created a destination ApsaraDB RDS for SQL Server instance with a storage capacity larger than the source database. The instance must meet the following requirements:

Note
The destination instance's storage capacity should be at least 1.2 times that of the source database. If your existing ApsaraDB RDS for SQL Server instance has insufficient storage capacity, you must increase its storage capacity.
- Edition: Basic Edition, High-availability Edition (SQL Server 2012 or later), or Cluster Edition
- Instance specification: General-purpose or Dedicated. Shared instances are not supported.
- Billing method: subscription or pay-as-you-go. Serverless instances are not supported.
- Network type: Virtual Private Cloud (VPC). If you need to change the network type, see Change the network type.
- Creation time:
  - High-availability Edition and Cluster Edition instances must be created on or after January 1, 2021.
  - Basic Edition instances must be created on or after September 2, 2022.
  Note
  You can view the Creation Time on the Basic Information page under Status.
You have installed the SSMS tool on a local machine or an Elastic Compute Service (ECS) instance. If you use an ECS instance, it must be in a VPC, use a Windows Server image, and have a public IP address.

Note
This topic uses SSMS 19.1 as an example. The exact steps may vary depending on your SSMS version, settings, and installation environment.

Usage notes

To prevent data inconsistency, you must stop writing data to the source database. The required downtime depends on the amount of data to be migrated.
Data export speed primarily depends on the source instance specifications.

Before you begin

Enable public access for your Azure SQL Database. In the firewall settings, allow the public IP address of your local machine or ECS instance to access Azure services and resources.

Note
For specific instructions, see the official Azure documentation or contact Azure technical support.
Verify that the source database contains no constraints, views, or other objects that could cause the export to fail.
Log on with your Alibaba Cloud account and create a system admin account for your ApsaraDB RDS for SQL Server instance.
Execute the SELECT name, compatibility_level FROM sys.databases; command in the source and destination databases to confirm whether the destination database is compatible with the source database.
Configure a whitelist for the ApsaraDB RDS for SQL Server instance to allow access from the client on your ECS instance or local device.
Note
- To connect from an ECS instance over the internal network, ensure that the ECS instance and the ApsaraDB RDS for SQL Server instance are in the same region and VPC. Add the private IP address of the ECS instance to the whitelist of the RDS instance.
- To connect from a local device, add the public IP address of the device to the whitelist of the RDS instance.

1. Export data from Azure SQL Database

Stop writing data to your Azure SQL Database and then export the data.

Use SSMS to connect to Azure SQL Database.
Navigate to the data export wizard.
1. In Object Explorer, expand Database.
2. Right-click the source database.
3. Select Task > Export Data-tier Application.
Note
For more information, see Export Data-tier Application.
Click Next.
Configure the export settings.
1. On the Settings tab of the Export Settings page, select Save to local disk.
2. Click Browse to select a file path and enter a file name.
3. On the Advanced tab, select the tables that you want to export.
  
  Note
  If you need to export other objects such as triggers or stored procedures, right-click the source database in Object Explorer, select Task > Generate Scripts, and follow the prompts to complete the wizard. For more information, see Use SSMS and BCP to migrate an SQL Server database.
4. Click Next.
Click Done.
After the export completes, click Close.

2. Import data to ApsaraDB RDS for SQL Server

Import the exported data into your ApsaraDB RDS for SQL Server instance.

Use SSMS to connect to your ApsaraDB RDS for SQL Server instance.

Open SSMS.

In the Connect to Server dialog box, configure the following settings.

Parameter	Description
Server type	Select Database Engine.
Server name	Enter the Internal Endpoint or Public Endpoint of your ApsaraDB RDS for SQL Server instance. To find the endpoint, see View or change endpoints and ports.
Authentication	Select SQL Server Authentication.
Username	Enter the username of the system admin account.
Password	Enter the password for the system admin account.

Click Connection.

In Object Explorer, right-click Database.
Select Import Data-tier Application.
Click Next.
Configure the Import Settings.
1. Select Import from local disk.
2. Click Browse and select the .bacpac file that you exported from Azure SQL Database.
3. Click Next.
Configure the Database Settings.
1. In the New database name field, enter a name for the new database on the ApsaraDB RDS for SQL Server instance.
  Important
  - Use the same name as the source database. Using a different name may cause application features to fail after you switch your workloads to ApsaraDB RDS for SQL Server.
  - If a database with the name you enter already exists on the ApsaraDB RDS for SQL Server instance, the import might fail or cause data inconsistency.
2. In the SQL Server Settings section, change the Data file path and Log file path to E:\SQLDATA\DATA.
3. Click Next.
Click Done.
After the import completes, click Close.

3. Verify data consistency

After the import completes, run the following commands on both the source and destination databases to verify data consistency. The data is consistent if the results match.

Query the number of rows in the database. This command returns the total number of rows across all business tables.

Important
Do not modify the source tables or snapshot data during the migration. Otherwise, the row counts may not match.
```
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 the data size. This command returns the size and space usage of the data files.

USE <database_name>;

SELECT a.name AS [File Name]
  ,CAST(a.[size]*1.0/128 AS DECIMAL(12,1)) AS [File Size (MB)]
  ,CAST(FILEPROPERTY(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [Space Used (MB)]
  ,CAST((FILEPROPERTY(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0 AS DECIMAL(12,1)) AS [Space Usage %]
  ,CASE WHEN a.growth = 0 THEN 'Fixed size, will not grow' ELSE 'Will autogrow' END AS [Growth Mode]
  ,CASE WHEN a.growth > 0 AND a.is_percent_growth = 0 THEN 'Grows by a fixed amount'
    WHEN a.growth > 0 AND a.is_percent_growth = 1 THEN 'Grows by a percentage'
    ELSE 'Fixed size, will not grow' END AS [Increment Mode]
  ,CASE WHEN a.growth > 0 AND a.is_percent_growth = 0 THEN CAST(CAST(a.growth*1.0/128 AS DECIMAL(12,0)) AS VARCHAR)+'MB'
    WHEN a.growth > 0 AND a.is_percent_growth = 1 THEN CAST(CAST(a.growth AS DECIMAL(12,0)) AS VARCHAR)+'%'
    ELSE 'Fixed size, will not grow' END AS [Increment Value (% or MB)]
  ,a.physical_name AS [File Path]
  ,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 you verify data consistency, you can switch your business applications to the ApsaraDB RDS for SQL Server instance and test that all features work as expected.