The key to understand this solution is to understand how the rows in configuration tables are populated and used by the migration PowerShell scripts. The best way to explain it is to use an example from a real migration task which is described below:
Database Information:
Database Function Category: Production
Number and List of Source SQL Servers: 8
MySourceSQLServer01,38052
MySourceSQLServer02,38053
MySourceSQLServer03,38048
MySourceSQLServer04,38043
MySourceSQLServer05,38067
MySourceSQLServer06,38060
MySourceSQLServer07,38062
MySourceSQLServer08,38063
Number of Source Databases: 305 of 5000+ production databases
Number and List of Target SQLMIs: 15
MyTargetSQLMIName01
MyTargetSQLMIName02
MyTargetSQLMIName03
MyTargetSQLMIName04
MyTargetSQLMIName05
MyTargetSQLMIName06
MyTargetSQLMIName07
MyTargetSQLMIName08
MyTargetSQLMIName09
MyTargetSQLMIName10
MyTargetSQLMIName11
MyTargetSQLMIName12
MyTargetSQLMIName13
MyTargetSQLMIName14
MyTargetSQLMIName15
Login Credentials (faked for demo purpose):
Azure Key Vault Name: "MyDMSAKVName"
Source SQL Server Login name: "glindba.com\glin"
SQL Login Password AKV Secret: "glindba.com\glin" (same as the login name)
SMB Network Share Login name: "glindba.com\glin"
SMB Login Password AKV Secret: "glindba.com\glin" (same as the login name)
SQLMI Login Name: "azdms"
SQLMI Login Password AKV Secret: "azdms" (same as the login name)
DMS Application ID: "12345678-1234-5678-90ab-453e2c106971"
DMS App ID Key AKV Secret: "12345678-1234-5678-90ab-453e2c106971" (same as App ID)
For this migration, we will use 100, which is maximum number the quota allows in the subscription, DMS instances that all use the credentials above when accessing source SQL Servers, SMB network share, Azure storage containers and target SQLMIs.
Since these 305 databases are in the first batch(migration group) of the production databases being migrated to Azure, we should accordingly name the DMS database/activity mapping tables as 'TaskConfig_Prod01' and 'MigGrp_Prod01'
Basing on the information above, run script below to insert a record into the DMSConfig table for this migration group:
USE [Your_DMS_Database_Name]
GO
INSERT INTO [DMS].[DMSConfig]
([AppId]
,[AppIdAKVSecret]
,[SourceUserName]
,[SourceUserAKVSecret]
,[TargetUserName]
,[TargetUserAKVSecret]
,[BackupUserName]
,[BackupUserSecret]
,[MigrationGroupTableName]
,[DMSTaskConfigTableName]
,[AKVName])
VALUES
('12345678-1234-5678-90ab-453e2c106971'
,'12345678-1234-5678-90ab-453e2c106971'
,'glindba.com\glin'
,'glindba.com\glin'
,'azdms'
,'azdms'
,'glindba.com\glin'
,'glindba.com\glin'
,'MigGrp_Prod01'
,'TaskConfig_Prod01'
,'MyDMSAKVName')
GOQuery the DMSConfig table to verify the row insertion:
The solution PowerShell scripts (details in Part3) use ConfigId column value to identify and retrieve the record in DMSConfig table for a specific migration group. For this current case, the ConfigId "1" is for the DMS migration group Prod01 that contains 305 production databases. These 305 databases will be migrated online all together on the same day within the same outage window. New records will be inserted to DMSConfig table for each and every subsequent migration groups that contains different sets of databases.
Populating data in table TaskConfig_Pro01 and MigGrp_Prod01 could be time consuming depending on the number of databases in the migration group and the number of DMS instances available for the migration. Microsoft doesn't recommend assigning more than four (4) database to a single DMS instance. With a limited number (100 in this case) of available DMS instances, a proper arrangement of mapping a certain database to a certain DMS instance is critical for achieving a smooth migration and this mapping relationship is established through a foreign key defined between the table TaskConfig_Prod01(parent) and MigGrp_Prod01(child) on the following three columns:
MigrationId
SourceServerName
TargetSQLName
Basically, each row in TaskConfig_Prod01 table defines one DMS activity that may migrate one (larger) or multiple (smaller) databases. Accordingly, all rows in MigGrp_Prod01 tables must have a value for MigrationId column that specifies which DMS activity, defined in TaskConfig_Prod01 as just mentioned, will be used to migrate the corresponding database. The FK also ensure no database in MigGrp_Prod01 would be missed from the migration.
We will use four(4) Azure storage accounts, which are shared by 100 DMS instances, for this migration. Obviously, we need to distribute the I/O workload across these 4 storage accounts as evenly as possible.
MyTargetSAName01
MyTargetSAName02
MyTargetSAName03
MyTargetSAName04
To better understand the concepts mentioned above, let's review the records in both tables. Run this query to review the content in TaskConfig_Prod01 table:
USE [Your_DMS_Database_Name]
GO
SELECT [MigrationId]
,[ServiceId]
,[ProjectId]
,[ActivityId]
,[IsActiveForMigration]
,[SourceServerName]
,[TargetSQLResourceGroup]
,[TargetSQLName]
,[BackupFileShare]
,[TargetSAResourceGroup]
,[TargetSAName]
FROM [DMS].[TaskConfig_Prod01]
GOThe query returns 100 rows. Each row defines one DMS activity by providing service Id, project Id and activity Id. The screenshot below shows part of the query result:
You can download the Excel file below to see the full query results
Run another query below to check the content of MigGrp_Prod01 table:
USE [Your_DMS_Database_Name]
GO
SELECT [MemberId]
,[migrationId]
,[SourceServerName]
,[SourceDBName]
,[SourceDBSizeGB]
,[TargetDBName]
,[TargetSQLName]
FROM [DMS].[MigGrp_Prod01]
ORDER BY [migrationId]
GOThe query returns 305 rows corresponding to 305 database in this migration group (Prod01). The screenshot below shows part of the query result:
You can download the Excel file below to see the full query results:
By looking at and comparing the records in both tables, we can tell which DMS activity migrates which databases. For example, DMS1-Project1-Activity1 (MigrationId = 1) is used to migrate both ProductDB17 and ProductDB18 (Note: ProductDB17 is large enough to deserve a dedicate DMS instance, but the application SQL queries need these two databases to be on the same SQLMI) and DMS15-Project1-Activity1 takes care of three databases: ProductDB31,ProductDB32 and ProductDB33
The solution PowerShell scripts use the information in TaskConfig_Prod01 and MigGrp_Prod01 to create DMS instances, projects and activities. The tables are also used to track the DMS runtime states. See next part for details.
Comments