Nov 8, 20215 min read

Migrating Thousands Databases To Azure ONLINE? Here Is How We Got It Done - Part1: Set It Up(B)

Updated: Nov 16, 2021

Part1(A), Part2 ,Part3(A), Part3(B), Part3(C), Part4(A), Part4(B)

8. Configure Azure Key Vault

The PowerShell scripts used by this database migration solution retrieve all the credentials as secrets from an Azure key vault which can be a new, dedicated one (recommended) or any one of the existing Azure key vaults in the subscription. Ensure the KV access policies only allow secret queries from the DMS console VM which is authenticated by Azure AD through a system assigned managed identity. The screenshot shows how the correct KV access policies should be set. The DMS console VM is represented as an "Application".

9.DMS Console VM

This could be a new Azure VM or any existing machine in your environment as long as it can:

Install and run PowerShell 6.0+ with the following modules
1. Az
2. Az.DataMigration
3. AzureAD
4. SQLServer
Access all source SQL Servers and SMB network share through Windows authentication
Access Azure portal, DMS and storage accounts
Retrieve the secrets from the Azure key vault
Login on all the target SQL Managed Instances through Azure AD and SQL authentication
Have a system assigned managed identity for configuring Access Policies in the Azure key vault. The VM Identity pane below shows how the VM principal ID having been correctly generated:

10. DMS Application ID & Key

An Azure DMS instance needs to be attached (represented) with an Azure AD Application ID so that it can be authenticated to connect to the target SQLMIs and Azure Storage Containers. The App ID used by DMS supports secret (password-based) authentication for service principals. It does not support certificate-based authentication.

Important: the value of the client secret(key) is only displayed at creation time. Copy this value and save it to the Azure key vault as the secret before you leave the Certificate & secrets page because you won't be able to retrieve the key later. You will provide the key value with the application ID when configuring DMS project target.

To avoid any potential "permission issue", We granted Azure DMS the Contributor permission on the subscriptions for the specified Application ID. Alternatively, you can create custom roles that grant the specific permissions that Azure DMS requires. For step-by-step guidance about using custom roles, see the article Custom roles for SQL Server to SQL Managed Instance online migrations.

11.DMS Configuration Database

This so-called DMS configuration database doesn't come with Azure DMS and is only required by our in-house solution discussed here. It could be a new or any existing SQL database where a new schema(optional) and some small tables (listed below) for configuring DMS objects (services, projects and activities) can be deployed.

DMS task (activity) configuration tables such as:
- TaskConfig_Staging
- TaskConfig_Prod01
- TaskConfig_Preview
- etc
Migration group tables such as:
- MigGrp_Staging
- MigGrp_Prod01
- MigGrp_Preview
- etc
DMS Configuration table:
- DMSConfig

With the limited Azure DMS resources (250 instances), it's not practical to migrate all 7115 databases within one day. We decided to split the databases into multiple migration groups basing on its function category, for example, Staging, Development, QA, Production1 and Production2 and etc. Each migration group can contain 100 to 800 databases depending on which category/environment the databases belong to.

TaskConfg_XXX table is created for each migration group and contains information needed to deploy DMS instances, DMS projects and DMS activates.

MigGrp_XXX table is also created per migration group and contains records for all the source/target database pairs. DMS PowerShell scripts write migration runtime information to this table as well

DMSConfig table is created once and shared by all the migration groups. A new record (which specify the ConfigId, the TaskConfig table name and the MigGrp table name) is inserted into this table. The same record also contains information about Azure key vault name, DMS AppId/AKV secret, source SQL login name/AKV secret, target SQLMI login name/AKV secret and SMB network share user name/AKV secret.

Below is the script to create tables mentioned above. replace "XXX" in TaskConfig_XXX and MigGrp_XXX table name with the migration group name, for example, Staging, QA,Prod01 and etc. More detailed information about how to use these tables is covered in Part2

USE [Your_DMS_Database_Name]
GO
CREATE SCHEMA [DMS]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [DMS].[DMSConfig](
	[ConfigId] [int] IDENTITY(1,1) NOT NULL,
	[AppId] [varchar](40) NULL,
	[AppIdAKVSecret] [varchar](40) NULL,
	[SourceUserName] [varchar](32) NULL,
	[SourceUserAKVSecret] [varchar](32) NULL,
	[TargetUserName] [varchar](32) NULL,
	[TargetUserAKVSecret] [varchar](32) NULL,
	[BackupUserName] [varchar](32) NULL,
	[BackupUserSecret] [varchar](32) NULL,
	[MigrationGroupTableName] [varchar](32) NOT NULL,
	[DMSTaskConfigTableName] [varchar](32) NOT NULL,
	[AKVName] [varchar](32) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[ConfigId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [DMS].[TaskConfig_XXXX](
	[MigrationId] [int] IDENTITY(1,1) NOT NULL,
	[ServiceId] [tinyint] NULL,
	[ProjectId] [tinyint] NULL,
	[ActivityId] [tinyint] NULL,
	[IsActiveForMigration] [char](1) NULL,
	[DMSState] [varchar](16) NULL,
	[DMSProjectState] [varchar](16) NULL,
	[DMSTaskState] [varchar](32) NULL,
	[DMSTaskErrors] [varchar](1024) NULL,
	[DMSTaskErrorDetails] [varchar](2048) NULL,
	[UpdateTimeStamp] [datetime2](7) NULL,
	[SourceServerName] [varchar](32) NOT NULL,
	[TargetSQLResourceGroup] [varchar](32) NULL,
	[TargetSQLName] [varchar](72) NOT NULL,
	[BackupFileShare] [varchar](120) NULL,
	[TargetSAResourceGroup] [varchar](32) NULL,
	[TargetSAName] [varchar](32) NULL,
 CONSTRAINT [PK_MigrationId_SourceServerName_XXXX] PRIMARY KEY CLUSTERED 
(
	[MigrationId] ASC,
	[SourceServerName] ASC,
	[TargetSQLName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [DMS].[TaskConfig_XXXX] ADD  CONSTRAINT [Default_DMSTaskState_XXXX]  DEFAULT ('NotStarted') FOR [DMSTaskState]
GO

CREATE TABLE [DMS].[MigGrp_XXXX](
	[UpdateTimeStamp] [datetime2](7) NOT NULL,
	[MemberId] [int] IDENTITY(1,1) NOT NULL,
	[migrationId] [int] NULL,
	[SourceServerName] [varchar](32) NULL,
	[SourceDBName] [varchar](32) NULL,
	[SourceDBSizeGB] [int] NULL,
	[TargetDBName] [varchar](32) NULL,
	[TargetDBState] [varchar](32) NULL,
	[TargetSQLName] [varchar](72) NULL,
	[MigrationState] [varchar](32) NULL,
	[IsFullBackupRestored] [bit] NULL,
	[NumberOfActiveBackupSets] [int] NULL,
	[NumberOfFilesPendingRestoration] [int] NULL,
	[SAContainerName] [varchar](40) NOT NULL,
	[CutoverTime] [datetime2](7) NOT NULL,
	[StartON] [varchar](24) NULL,
	[LastRestoredFile] [varchar](512) NULL,
	[BackupStartDate] [varchar](24) NULL,
	[ErrorCode] [varchar](256) NULL,
	[ErrorMessage] [varchar](5120) NULL,
	[FirstLsn] [varchar](30) NULL,
	[LastLsn] [varchar](30) NULL
) ON [PRIMARY]
GO

ALTER TABLE [DMS].[MigGrp_XXXX] ADD  CONSTRAINT [Default_UpdateTimeStamp_XXXX]  DEFAULT ('') FOR [UpdateTimeStamp]
GO

ALTER TABLE [DMS].[MigGrp_XXXX] ADD  CONSTRAINT [Default_TargetDBState_XXXX]  DEFAULT ('NotCreated') FOR [TargetDBState]
GO

ALTER TABLE [DMS].[MigGrp_XXXX] ADD  CONSTRAINT [Default_MigrationState_XXXX]  DEFAULT ('NotStarted') FOR [MigrationState]
GO

ALTER TABLE [DMS].[MigGrp_XXXX] ADD  CONSTRAINT [Default_SAContainerName_XXXX]  DEFAULT ('') FOR [SAContainerName]
GO

ALTER TABLE [DMS].[MigGrp_XXXX] ADD  CONSTRAINT [Default_CutoverTime_XXXX]  DEFAULT ('') FOR [CutoverTime]
GO

ALTER TABLE [DMS].[MigGrp_XXXX]  WITH CHECK ADD  CONSTRAINT [fk_migrationId_SourceServerName_XXXX] FOREIGN KEY([migrationId], [SourceServerName], [TargetSQLName])
REFERENCES [DMS].[TaskConfig_XXXX] ([MigrationId], [SourceServerName], [TargetSQLName])
GO

ALTER TABLE [DMS].[MigGrp_XXXX] CHECK CONSTRAINT [fk_migrationId_SourceServerName_XXXX]
GO

12. Configure Test Azure VM in DMS subnets

This task is optional and is only needed if you need to troubleshoot the connectivity issue between the following item pairs:

Source SQL Servers and DMS
SMB Network Share and DMS
DMS and the DMS Azure Storage Accounts
DMS and The Target SQL Managed Instances

the connectivity can be tested by running some PowerShell commands or through Azure Network Watcher or any tools suitable for your situation.

13. Get Microsoft Support Onboard

For large database migration activities, you would need lots of support from Microsoft DMS and SQLMI production teams who can not only help troubleshoot any migration runtime issues but also adjust some subscription level configuration parameters in the Azure background to ensure the DMS activities run successfully.

We worked very closely with some awesome Microsoft DMS and SQLMI support engineers throughout our database migration journey. Without their involvement, it's near impossible to get the jobs done. You will understand it better after reading the Part4 of this article.