Database migration

Database migration allows you to seamlessly move your on-premises databases to Azure. Before beginning the migration process, it is crucial to assess your current database environment. You can perform this assessment using the Data Migration Assistant (DMA), which not only evaluates your environment but also helps in migrating the data. Alternatively, the Azure Database Migration Service (DMS) enables streamlined migration. This guide covers both the theoretical background and a hands-on demonstration of installing DMA on an on-premises database, assessing it, and finally migrating it to Azure using DMS.

Types of Database Migration

There are two primary migration strategies:

1. Offline Migration:
   In this approach, you need to shut down the source server at the start of the migration, which results in downtime.

2. Online Migration:
   With this method, continuous data replication occurs from on-premises to Azure without shutting down the server, effectively eliminating downtime. The migration can be finalized with a cutover at any time.

On-Premises Database Setup

In this demonstration, our on-premises database includes two tables: one for departments and another for employees.

For the employee table, the following SQL query is used:

SELECT TOP (1000) [empno]
      ,[ename]
      ,[job]
      ,[mgr]
      ,[hiredate]
      ,[sal]
      ,[comm]
      ,[dept]
FROM [customers].[dbo].[emp]

Similarly, to query the department table, use:

SELECT TOP (100) [deptno]
      ,[dname]
      ,[loc]
FROM [customers].[dbo].[dept]

Installing and Using Data Migration Assistant (DMA)

In this section, we install DMA on a Windows server running SQL Server:

1. Open the Edge browser and search for "Data Migration Assistant."
2. Download and run the installer.
3. Once installed, launch DMA and create a new project with these settings:
   • Project Name: on-prem to Azure
   • Assessment Type: Database Engine
   • Source Server Type: SQL Server
   • Target Server: Azure SQL Database

Before creating the project, note that DMA provides two options: one for assessment and the other for migration. In this demonstration, select the "Assessment" option to evaluate database compatibility and feature parity.

Enter the required server name and credentials to connect to your on-premises database. DMA will scan the databases and identify the "customers" database, similar to what is visible in SQL Server Management Studio.

Click "Connect" (ensure you check the "Trust server certificate" box, if applicable) and select the "customers" database. Then, start the assessment.

During the assessment, DMA might flag unsupported items such as trace flags for Azure SQL Database. Refer to the provided recommendations; however, if these issues are minor and the compatibility check passes, you can proceed with the migration.

Preparing Database Migration Using Azure DMS

After completing the assessment and addressing any compatibility issues, you can initiate the migration process. There are two options:

1. Start the migration directly from DMA.
2. Use the Azure portal with the Database Migration Service (DMS).

To use the Azure portal:

• Create a DMS instance.
• Specify the source server as SQL Server and the target server as Azure SQL Database.
• Choose the appropriate service tier. For example, opting for the premium tier for online migration is available free for six months.

After configuring settings such as location (e.g., East US) and selecting the appropriate virtual network, review your settings and create the migration service.

Once the migration service instance is deployed, create a migration project within DMS by providing:

• Project Name
• Source Type: SQL Server
• Target Type: Azure SQL Database
• Selection for migrating data

While following the migration wizard, input the necessary details for your on-premises server (even if it is hosted on Azure for demonstration purposes). Use SQL Server authentication by supplying the correct username and password. DMS will then attempt to connect to your on-premises database and retrieve the list of available databases.

Migrating the Database Using DMA

If you encounter connection issues with DMS, switch back to DMA to create a migration project by following these steps:

1. Select "SQL Server Database" as the source.
2. Choose to migrate both the schema and data.
3. Provide your on-premises source server details using SQL Server authentication.

After connecting, DMA displays the list of databases, including the "customers" database.

Next, enter your target server details (the Azure SQL Database). Ensure that you use the correct authentication credentials and verify connectivity.

After setting up the connection, select the objects (tables) you wish to migrate. In this demonstration, both the "department" and "employee" tables are selected.

Once you generate the deployment script, it may appear as follows:

/****** DMA Schema Migration Deployment Script ******/
-- Script Date: 1/11/2023 8:22:57 AM
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[emp]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[emp] (
        [empid] INT NOT NULL,
        [name] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [job] [varchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [hire] [datetime] NULL,
        [sal] [numeric](7,2) NULL,
        [cnum] [numeric](2) NULL,
        [dept] [int] NULL,
        CONSTRAINT [PK__emp__AF43E3A298D68] PRIMARY KEY CLUSTERED 
        (
            [empid] ASC
        )
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_DUP_KEY = OFF, ALLOW_PAGE_LOCKS = ON)
    )
END
GO

After deploying the schema, proceed to run the data migration process. Once the migration completes successfully, verify the tables in your Azure SQL Database using the query editor.

To do a final verification, run the following queries against the migrated tables:

For the employee table:

SELECT * FROM [dbo].[emp]

For the department table:

SELECT TOP (100) [deptno]
    , [dname]
    , [loc]
FROM [customers].[dbo].[dept]

The query results should match the data from your original on-premises database.

Conclusion

In this guide, we demonstrated the process of assessing and migrating an on-premises SQL database to Azure. Both the Data Migration Assistant (DMA) and Azure Database Migration Service (DMS) were used to perform assessments, deploy the schema, and migrate the data. Although this demonstration uses an Azure-based SQL server to simulate an on-premises environment, the same steps apply when migrating from a true on-premises setup.

For further reading, consider the following resources:

• Azure Database Migration Guide
• Data Migration Assistant Overview
• Microsoft Azure Documentation

Happy migrating!