Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Real-time SQL Server Migration to Alibaba Cloud ApsaraDB RDS for SQL Server

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

In this tutorial, we'll show you how to migrate an on-premises Microsoft SQL Server to Alibaba Cloud ApsaraDB for SQL Server with minimal downtime.

Join us at the Alibaba Cloud ACtivate Online Conference on March 5-6 to challenge assumptions, exchange ideas, and explore what is possible through digital transformation.

By Steve Chen, Solutions Architect

For many enterprises, hosting a Microsoft SQL Server on-premises can be a resource intensive endeavor, especially when it comes to operating and maintaining the server. More and more enterprises are migrating their databases to the cloud to fully enjoy the benefits of a cloud-based deployment. This article illustrates a possible solution for customers to migrate all on-premises SQL Server databases to an Alibaba Cloud ApsaraDB RDS for SQL Server instance with minimal down time.

This article will make up a typical sample use case and environment, and then elaborate the migration solutions on the sample environment.

Sample On-Premises Environment

Source Database

Transactional Database

Hot/Standby

CPU: 32 Memory: 98259 (MB) Storage: 800GB

Version: 11.0.5058.0 Microsoft SQL Server Enterprise: Core-based Licensing (64-bit)

Network

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

A physical leased line between them is more favourable.

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.

Alibaba Cloud Database Assessment Expert tool (DAE) is an offline tool to evaluate the compatibility of your existing SQL Server version on-premises and Alibaba Cloud RDS for SQL Server. This DAE tool will potentially look into following features in your database and give report with regard to that. This tool can be deployed on any Windows OS host with .net environment which is accessible to the targeted SQL Server database.

The following features are fully supported if the right version of RDS for SQL Server is chosen in Alibaba Cloud.

  • Table partition
  • CDC
  • Column store
  • TDE
  • Index compression

The following may have small differences between Alibaba Cloud RDS and on-premises SQL Server, but adjustment effort for migration is small.

  • 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

The following features has big gap between on-premises SQL Server and RDS for SQL Server. Certain modifications need to be conducted in the application or databases before migration.

  • FileStream
  • FileTable
  • In-memory database
  • Using CMD or PowerShell in jobs
  • Using CLR
  • DBMail
  • Service Broker
  • Policy-Base Management
  • Copy

The use of DAE is referred in this link, however, it is currently only available in Chinese.

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.

The introduction of both plans and migration steps are elaborated in the following sections.

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.

The advantage of physical migration is that it will make sure that the data in the destination RDS is identical to the source instance, because the physical database backup copies are replicated and loaded.

The disadvantage of physical migration is that the cut over RPO is in > 5 minutes, so it needs to be conducted in the business lull period and interruption of the database in minutes.

Migration Steps

Migration flowchart

1

The following table illustrates the migration steps of a demo project in order to understand the entire migration procedure.

The migration will use Alibaba Cloud RDS for SQL Server and Object Storage Service (OSS).

2

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.

The advantage of logical migration is that the cut over RPO is in seconds-level in the lull period, so that the impact to the business will be minimized.

The disadvantage of logical migration is that because DTS uses DML to insert entries in the destination instance, so some database features in the source database might not be supported by DTS and replicated to the destination, limitation of DTS is listed in the following:

  • 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.

Restrictions of data change replication:

  • 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.

DDL operations that can be migrated by data change replication:

  • 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:

3

The following migration steps are based on the assumption that there is a VPN connection between Hostworks environment and Alibaba Cloud.

In case of a VPN connection, apart from the standard DTS configuration below, we need to introduce an Ngnix proxy in the Alibaba Cloud VPC where the RDS sits, all the DTS data retrieval request to the source database in Hostworks will be forwarded by the proxy through VPN connection.

The following illustrates how to migrate the database using DTS. To improve the success rate of migration from on-premises SQL Server to ApsaraDB RDS for SQL Server, DTS uses the following migration process:

  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.

For a migration task that does not include data change replication, if the migration progress is Structure migration 100%, data migration 100% and the migration status is Migrating, do not end the task manually because the task is performing the preceding step 3. Otherwise, migration data loss may occur.

When DTS is used for data migrations from on-premises SQL Server to ApsaraDB RDS for SQL Server, the migration accounts of the source and target instances must have the following permissions:

Instance Object structure migration Existing data migration Data change replication
On-premises SQL Server Select Select sysadmin
Target ApsaraDB RDS for SQL Server instance Read/Write Permission Read/Write Permission Read/Write Permission

The following describes the procedure of configuring a migration task from on-premises SQL Server to ApsaraDB RDS for SQL Server.

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:

Step 1: Log on to Alibaba Cloud DTS console and click Create Migration Task at the upper right corner.

4

Step 2: Configure the connection information of the on-premises SQL Server and the target RDS instance.

Task name

By default, DTS generates a task name automatically. The task name is not required to be unique. You can modify it as needed.

Source database

5

  • 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.

Target database

6

  • 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.

Step 3: Configure the migration types and objects to be migrated.

Migration types

To perform a migration without stopping services, select all the three migration types.

To migrate only the existing data, select migrate object structure and migrate existing data.

Objects to be migrated

The objects are databases, tables, and columns. By default, after an object is migrated, the object name in the source instance is the same as that in the target instance. If you want them to be different, use the Object name mapping function.

Step 4: Perform a pre-check.

A pre-check is performed before the migration starts. If the pre-check fails, click Failed next to the failure items, solve the problem, and perform the pre-check again.

7

Step 5: Start the migration task.

If the pre-check succeeds, start the migration task. You can view the migration status and progress in the task list.

Data change replication is a process of dynamic synchronization. When the data change replication has no latency, verify the data on the target database. If the data is correct, disable the migration task and switch services to the target database to finish the cut over.

Database Migration SQL Server Tutorial database migration
1 2 2
Share on

Read previous post:

How to Connect Tableau to MaxCompute Using HiveServer2 Proxy

Read next post:

Setting Up a Flask Application on Alibaba Cloud ECS Ubuntu 16.04

Alibaba Clouder

2,599 posts | 762 followers

You may also like

Comments

Raja_KT March 2, 2019 at 5:45 am

Good reference for migration. DTS is a better option in my opinion.

Alibaba Clouder

2,599 posts | 762 followers

Related Products

More Posts by Alibaba Clouder

See All