Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Upgrade Earlier Versions of SQL Server Instance on Alibaba Cloud to SQL Server 2019 Using Data Migration Assistant

Upgrade Earlier Versions of SQL Server Instance on Alibaba Cloud to SQL Server 2019 Using Data Migration Assistant

This article explains how to upgrade your user databases from previous versions of SQL Server to SQL Server 2019 over Alibaba Cloud using the Data Migration Assistant (DMA).

By M Muzaffer Azam

This article explains how to upgrade your user databases from previous versions of SQL Server to SQL Server 2019 over Alibaba Cloud using the Data Migration Assistant (DMA).

Prepare Target Instance

  • Create a New Target Instance on Alibaba Cloud with the best sizing specifications
  • Join the Instance to the existing Domain
  • Install SQL Server 2019

Assess and Convert

First, assess the previous SQL Server instance using the Data Migration Assistant (DMA) before upgrading your SQL Server instance.

Complete the following steps to use DMA to create an assessment:

  1. Select the New (+) icon, select the Assessment project type, specify a project name, select SQL Server as the source and target, and select Create:

1

  1. Select the target SQL Server version you plan to migrate to and against which you need to run an assessment, select one (or both) of the assessment report types (Compatibility Issues and New features' recommendation), and select Next.

2

  1. In Connect to a server, specify the name of the SQL Server instance to connect to, specify the Authentication type and Connection properties, and select Connect.
  2. In the Add Sources panel, select the database(s) that you want to assess and select Add:

3

  1. Select Start Assessment and wait for the assessment results. The duration of the assessment depends on the number of databases added and the schema size of each database. Results will be displayed per database as soon as they're available.
  2. Select the database that has completed the assessment and then switch between Compatibility issues and Feature recommendations using the switcher:

4

  1. Review the compatibility issues by analyzing the affected object and its details for every issue identified under Breaking changes, Behavior changes, and Deprecated features
  2. Review feature recommendations across the Performance, Storage, and Security areas

Feature recommendations cover various features, such as In-Memory OLTP and Columnstore, Always Encrypted (AE), Dynamic Data Masking (DDM), and Transparent Data Encryption (TDE)

  • Review assessment results
  1. After all database assessments are complete, select Export report to export the results to a JSON or CSV file so you can analyze the data at your convenience.

Convert

After assessing the source database instance(s) you're migrating, you need to convert the schema to work in the target environment for heterogenous migrations. Since upgrading to a newer version of SQL Server would be considered a homogeneous migration, the convert step is unnecessary.

Migration Overview

After you have the necessary prerequisites in place and have completed the tasks associated with the pre-migration stage, you're ready to complete the schema and data migration. A successful migration and upgrade means you've addressed all the issues discovered from the pre-migration stage.

Review the compatibility issues discovered with the DMA tool

Preserve backup logs, maintenance plans, and other automated tasks (including jobs) by creating a backup of the system database msdb

View linked servers by using SQL Server Management Studio. In the Object Explorer, right-click server objects to expand the list.

Additional considerations may be needed based on the complexity of your data and environment:

  • Troubleshoot orphaned users
  • Migrating triggers
  • Migrate system objects using scripts wizard
  • Mirrored backup media sets
  • Backup overview of SQL Server
  • Editions and supported features of SQL Server

Migrate Schema and Data

After assessing your databases, the next step is to begin the process of migrating the schema and database using DMA.

Migrate schema and data sync

Complete the following steps to use DMA to create a migration project:

  • Create a New Migration project
  1. Select the New icon, select the Migration project type, select SQL Server as the source and target types, and select Create:

5

  1. Provide te source and target SQL server connection details and select Next:

6

  1. Select databases from the source to migrate and then specify the Shared location accessible by the source and target SQL servers for the backup operation.

Note: Make sure the service account running the source SQL Server instance has write privileges on the shared location and the target SQL Server service account has read privileges on the shared location.

7

  1. Select Next, select the logins you want to migrate, and then select Start Migration:

8

  1. Now, monitor the progress of migration in the View Results screen
  • Review Migration Results
  1. Select Export report to save the migration results to a .csv or .json file
  2. Review the saved file for details about data and login migration and verify the successful completion of the process.

Data Sync and Cutover

The source you're migrating to continues to change after the one-time migration occurs for minimal-downtime migrations. Data and schema may be different from the target. You need to ensure every change in the source is captured and applied to the target in near real-time during this process. Verify changes in the source have been applied to the target and then cutover from the source to the target environment.

Support for minimal-downtime migrations isn't yet available for this scenario, so the Data sync and cutover plans aren't currently applicable.

Post Migration

After you've successfully completed the Migration stage, you need to go through a series of post-migration tasks to ensure that everything is functioning as smoothly and efficiently as possible. The post-migration is crucial for reconciling any data accuracy issues, verifying completeness, and addressing performance issues with the workload.

Data Synchronization SQL Server Cloud migration Data Migration Data Visualization Tutorials Data Migration Assistant
0 0 0
Share on

Read previous post:

Alibaba Cloud Services in Saudi Arabia

Read next post:

Alibaba Cloud Cloud Box

5544031433091282

14 posts | 2 followers

You may also like

Comments

5544031433091282

14 posts | 2 followers

Related Products

More Posts by 5544031433091282

See All