Migrate SQL Server on-premises to Azure SQL DB using Azure PowerShell

In this article, you migrate the Adventureworks2012 database restored to an on-premises instance of SQL Server 2016 or above to an Azure SQL Database by using Microsoft Azure PowerShell. You can migrate databases from an on-premises SQL Server instance to Azure SQL Database by using the AzureRM.DataMigration module in Microsoft Azure PowerShell.

In this article, you learn how to:

  • Create a resource group.
  • Create an instance of the Azure Database Migration Service.
  • Create a migration project in an Azure Database Migration Service instance.
  • Run the migration.

Prerequisites

To complete these steps, you need:

Log in to your Microsoft Azure subscription

Use the directions in the article Log in with Azure PowerShell to log in to your Azure subscription by using PowerShell.

Create a resource group

An Azure resource group is a logical container into which Azure resources are deployed and managed. Create a resource group before you can create a virtual machine.

Create a resource group by using the New-AzureRmResourceGroup command.

The following example creates a resource group named myResourceGroup in the EastUS region.

New-AzureRmResourceGroup -ResourceGroupName myResourceGroup -Location EastUS

Create an Azure Database Migration Service instance

You can create new instance of Azure Database Migration Service by using the New-AzureRmDataMigrationService cmdlet. This cmdlet expects the following required parameters:

  • Azure Resource Group name. You can use New-AzureRmResourceGroup command to create Azure Resource group as previously shown and provide its name as a parameter.
  • Service name. String that corresponds to the desired unique service name for Azure Database Migration Service
  • Location. Specifies the location of the service. Specify an Azure data center location, such as West US or Southeast Asia
  • Sku. This parameter corresponds to DMS Sku name. Currently supported Sku names are Basic_1vCore, Basic_2vCores, GeneralPurpose_4vCores
  • Virtual Subnet Identifier. You can use cmdlet New-AzureRmVirtualNetworkSubnetConfig to create a subnet.

The following example creates a service named MyDMS in the resource group MyDMSResourceGroup, which is located in East US region using a virtual network named MyVNET and subnet called MySubnet.

 $vNet = Get-AzureRmVirtualNetwork -ResourceGroupName MyDMSResourceGroup -Name MyVNET

$vSubNet = Get-AzureRmVirtualNetworkSubnetConfig -VirtualNetwork $vNet -Name MySubnet

$service = New-AzureRmDms -ResourceGroupName myResourceGroup `
  -ServiceName MyDMS `
  -Location EastUS `
  -Sku Basic_2vCores `  
  -VirtualSubnetId $vSubNet.Id`

Create a migration project

After creating an Azure Database Migration Service instance, create a migration project. An Azure Database Migration Service project requires connection information for both the source and target instances, as well as a list of databases that you want to migrate as part of the project.

Create a Database Connection Info object for the source and target connections

You can create a Database Connection Info object by using the New-AzureRmDmsConnInfo cmdlet. This cmdlet expects the following parameters:

  • ServerType. The type of database connection requested, for example, SQL, Oracle, or MySQL. Use SQL for SQL Server and SQL Azure.
  • DataSource. The name or IP of a SQL instance or SQL Azure server.
  • AuthType. The authentication type for connection, which can be either SqlAuthentication or WindowsAuthentication.
  • TrustServerCertificate parameter sets a value that indicates whether the channel is encrypted while bypassing walking the certificate chain to validate trust. Value can be true or false.

The following example creates Connection Info object for source SQL Server called MySQLSourceServer using sql authentication

$sourceConnInfo = New-AzureRmDmsConnInfo -ServerType SQL `
  -DataSource MySQLSourceServer `
  -AuthType SqlAuthentication `
  -TrustServerCertificate:$true

Next example shows creation of Connection Info for SQL Azure database server called MySQLAzureTarget using sql authentication

$targetConnInfo = New-AzureRmDmsConnInfo -ServerType SQL `
  -DataSource "mysqlazuretarget.database.windows.net" `
  -AuthType SqlAuthentication `
  -TrustServerCertificate:$false

Provide databases for the migration project

Create a list of AzureRmDataMigrationDatabaseInfo objects that specifies databases as part of the Azure Database Migration project that can be provided as parameter for creation of the project. Cmdlet New-AzureRmDataMigrationDatabaseInfo can be used to create AzureRmDataMigrationDatabaseInfo.

The following example creates AzureRmDataMigrationDatabaseInfo project for database AdventureWorks2016 and adds it to the list to be provided as parameter for project creation.

$dbInfo1 = New-AzureRmDataMigrationDatabaseInfo -SourceDatabaseName AdventureWorks2016
$dbList = @($dbInfo1)

Create a project object

Finally you can create Azure Database Migration project called MyDMSProject located in East US using New-AzureRmDataMigrationProject and adding the previously created source and target connections and the list of databases to migrate.

$project = New-AzureRmDataMigrationProject -ResourceGroupName myResourceGroup `
  -ServiceName $service.Name `
  -ProjectName MyDMSProject `
  -Location EastUS `
  -SourceType SQL `
  -TargetType SQLDB `
  -SourceConnection $sourceConnInfo `
  -TargetConnection $targetConnInfo `
  -DatabaseInfo $dbList

Create and start a migration task

Finally, create and start Azure Database Migration task. Azure Database Migration task requires connection credential information for both source and target and list of database tables to be migrated in addition to the information already provided with the project created as a prerequisite.

Create credential parameters for source and target

Connection security credentials can be created as a PSCredential object.

The following example shows the creation of PSCredential objects for both source and target connections providing passwords as string variables $sourcePassword and $targetPassword.

secpasswd = ConvertTo-SecureString -String $sourcePassword -AsPlainText -Force
$sourceCred = New-Object System.Management.Automation.PSCredential ($sourceUserName, $secpasswd)
$secpasswd = ConvertTo-SecureString -String $targetPassword -AsPlainText -Force
$targetCred = New-Object System.Management.Automation.PSCredential ($targetUserName, $secpasswd)

Create a table map and select source and target parameters for migration

Another parameter needed for migration is mapping of tables from source to target to be migrated. Create dictionary of tables that provides a mapping between source and target tables for migration. The following example illustrates mapping between source and target tables Human Resources schema for the AdventureWorks 2016 database.

$tableMap = New-Object 'system.collections.generic.dictionary[string,string]'
$tableMap.Add("HumanResources.Department", "HumanResources.Department")
$tableMap.Add("HumanResources.Employee","HumanResources.Employee")
$tableMap.Add("HumanResources.EmployeeDepartmentHistory","HumanResources.EmployeeDepartmentHistory")
$tableMap.Add("HumanResources.EmployeePayHistory","HumanResources.EmployeePayHistory")
$tableMap.Add("HumanResources.JobCandidate","HumanResources.JobCandidate")
$tableMap.Add("HumanResources.Shift","HumanResources.Shift")

The next step is to select the source and target databases and provide table mapping to migrate as a parameter by using the New-AzureRmDmsSqlServerSqlDbSelectedDB cmdlet, as shown in the following example:

$selectedDbs = New-AzureRmDmsSqlServerSqlDbSelectedDB -Name AdventureWorks2016 `
  -TargetDatabaseName AdventureWorks2016 `
  -TableMap $tableMap

Create and start a migration task

Use the New-AzureRmDataMigrationTask cmdlet to create and start a migration task. This cmdlet expects the following parameters:

  • TaskType. Type of migration task to create for SQL Server to SQL Azure migration type MigrateSqlServerSqlDb is expected.
  • Resource Group Name. Name of Azure resource group in which to create the task.
  • ServiceName. Azure Database Migration Service instance in which to create the task.
  • ProjectName. Name of Azure Database Migration project in which to create the task.
  • TaskName. Name of task to be created.
  • Source Connection. AzureRmDmsConnInfo object representing source connection.
  • Target Connection. AzureRmDmsConnInfo object representing target connection.
  • SourceCred. PSCredential object for connecting to source server.
  • TargetCred. PSCredential object for connecting to target server.
  • SelectedDatabase. AzureRmDmsSqlServerSqlDbSelectedDB object representing the source and target database mapping.

The following example creates and starts a migration task named myDMSTask:

$migTask = New-AzureRmDataMigrationTask -TaskType MigrateSqlServerSqlDb `
  -ResourceGroupName myResourceGroup `
  -ServiceName $service.Name `
  -ProjectName $project.Name `
  -TaskName myDMSTask `
  -SourceConnection $sourceConnInfo `
  -SourceCred $sourceCred `
  -TargetConnection $targetConnInfo `
  -TargetCred $targetCred `
  -SelectedDatabase  $selectedDbs`

Monitor the migration

You can monitor the migration task running by querying the state property of the task as shown in the following example:

if (($mytask.ProjectTask.Properties.State -eq "Running") -or ($mytask.ProjectTask.Properties.State -eq "Queued"))
{
  write-host "migration task running"
}

Next steps