Real-time SQL Server Migration to Alibaba Cloud ApsaraDB RDS for SQL Server

Sample On-Premises Environment

Source Database

Transactional Database

Network

Customer has a VPN link between on-premises IDC and Alibaba Cloud VPC.

Compatibility Evaluation

Alibaba Cloud ApsaraDB RDS for SQL is a managed database service in Alibaba Cloud. It is mostly compatible with all feature of SQL Server of different versions. However, as this is a managed database service on the cloud which has certain limitations, you should evaluate the differences between the on-premises and RDS before making a right migration decision in terms of database version and features.

  • Table partition
  • CDC
  • Column store
  • TDE
  • Index compression
  • Link server
  • Distributed transaction
  • Total number of databases
  • Account number
  • Job number
  • TraceFlag
  • Sys db Collation is different from the default Collation of RDS
  • Extended event
  • Maintenance plan
  • Server audit
  • Old syntax (such as *=)
  • Offline space utilization estimation
  • FileStream
  • FileTable
  • In-memory database
  • Using CMD or PowerShell in jobs
  • Using CLR
  • DBMail
  • Service Broker
  • Policy-Base Management
  • Copy

Migration Plan

There are two different migration plans that can be potentially applied to this case, and both methods have their unique advantages and disadvantages.

Option 1: Physical Migration

Physical migration will take the full database copy file and log copy files from the existing database instance, move them to cloud and load them into the Alibaba Cloud ApsaraDB RDS for SQL Server instance.

Migration Steps

Migration flowchart

Option 2: Logical Migration

Logical migration will use Alibaba Cloud Data Transmission Service (DTS) to fetch the full amount and incremental data entries from the source database instance and use DML to insert/delete/update those entries to the destination Alibaba Cloud RDS instance.

  • Currently the following SQL Server versions support migrations of structures and existing data: SQL Server 2005, 2008, 2008 R2, 2012, and 2016; the following SQL Server versions support migrations of data changes: SQL Server 2008, 2008 R2, 2012, and 2014.
  • If the migrated objects use the object name mapping function, the objects dependent on these objects may fail to be migrated.
  • The sql_variant data type is not supported.
  • Structure migrations does not support migrations of assemblies, database-level stored procedures, Service Broker, full-text indexes, full-text catalogs, distributed functions, CLR scalar-valued functions, CLR table-valued functions, inner tables, aggregate functions, and systems.
  • If you choose all of the three migration types, do not perform DDL operations after the structure migration and before the data change replication; otherwise the migration may fail.
  • Does not support migrations of all DDL statements. For details, see the list of DDL operations that can be migrated in the list below.
  • Does not support Update statements that update only large object data types.
  • Does not support tables containing computed columns.
  • Supports only tables that use the clustered index as the primary key.
  • Each migration task with data change replication supports the migration of only one database. To replicate data changes for multiple databases, create multiple migration tasks.
  • CREATE TABLE (Functions, partitions, and default values are not supported.)
  • ALTER TABLE … ADD COLUMN (Default values are not supported.)
  • ALTER TABLE … DROP COLUMN
  • ALTER TABLE … ALTER COLUMN (Default values are not supported.)
  • CREATE INDEX (Index properties are not supported.)
  • SP_RENAME table_name
  • SP_RENAME column_name

Migration Steps

Migration flowchart using DTS:

  1. Migrate the structure objects: tables, views, synonyms, user-defined data types, rules, defaults, and PlanGuid.
  2. Migrate existing data.
  3. Migrating the structure objects: stored procedures, functions, triggers, and foreign keys.
  4. Replicate data changes.

Creating a Database on the RDS Instance

During a data migration, if the database to be migrated does not exist in the target RDS instance, DTS automatically creates one in the target RDS instance. However, in either of the following cases, you need to manually create the database before configuring a migration task:

  • The database name does not comply with the RDS defined specification (that is, the name has a maximum of 64 characters, starts with a letter, ends with a letter or digit, and consists of lowercase letters, digits, underscores and hyphens).
  • The database name on the source instance is different from that on the target instance.

Creating Migration Accounts

When configuring a migration task, you need to provide the migration accounts of the source and target instances. The accounts must have the permissions listed in the preceding Migration permissions section.

  • If the on-premises SQL Server instance does not have a migration account with required permissions, create one
  • If the target RDS for SQL Server instance does not have a migration account with required permissions, create one

Other Preparations Before Launching DTS

To perform the migration without stopping services, you need to set the log format of the source database to full by running the following two commands in the source database:

  • alter database database_name set recovery_model_desc='full'
    database_name is the name of the database to be migrated.
  • BACKUP LOG database_name to DISK= backup_place WITH init
    database_name is the name of the database to be migrated, and backup_place is the place storing backup files.

Configuring a Migration Task in the DTS Console

After the databases and migration accounts are created, configure a migration task as follows:

  • Instance type: Select On-permises databases
  • Instance region: Select the region closest to the source instance.
  • Database engine: Select SQL Server
  • Host name or IP address: Enter the public network IP address of the on-premises SQL Server instance (in case of VPN, should enter the IP of the proxy, in case of lease line, should enter the IP of the source database instance).
  • Port: Enter the listening port number of the on-premises SQL Server instance.
  • Database account: Enter the username of the migration account of the on-premises SQL Server instance.
  • Database password: Enter the password of the migration account of the on-premises SQL Server instance.
  • Instance type: Select RDS instance
  • Instance region: Select the region where the target instance is located.
  • RDS instance ID: Enter the ID of the target RDS instance. The target RDS instance can be in a classic network or a VPC network.
  • Database account: Enter the username of the migration account of the target RDS instance.
  • Database password: Enter the password of the migration account of the target RDS instance.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com