Skip to content

Files

Latest commit

v-hearyav-hearya
Linkfix: {azure-docs-pr} {2022-04} - 2
Apr 12, 2022
602f645 · Apr 12, 2022

History

History
255 lines (193 loc) · 14 KB

howto-sql-server-to-azure-sql-powershell.md

File metadata and controls

255 lines (193 loc) · 14 KB
title titleSuffix description services author ms.author manager ms.reviewer ms.service ms.workload ms.custom ms.topic ms.date
PowerShell: Migrate SQL Server to SQL Database
Azure Database Migration Service
Learn to migrate a database from SQL Server to Azure SQL Database by using Azure PowerShell with the Azure Database Migration Service.
database-migration
pochiraju
rajpo
craigg
craigg
dms
data-services
seo-lt-2019, devx-track-azurepowershell
how-to
02/20/2020

Migrate a SQL Server database to Azure SQL Database using Azure PowerShell

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

In this article, you learn how to:

[!div class="checklist"]

  • 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:

  • SQL Server 2016 or above (any edition)
  • To enable the TCP/IP protocol, which is disabled by default with SQL Server Express installation. Enable the TCP/IP protocol by following the article Enable or Disable a Server Network Protocol.
  • To configure your Windows Firewall for database engine access.
  • An Azure SQL Database instance. You can create an Azure SQL Database instance by following the detail in the article Create a database in Azure SQL Database in the Azure portal.
  • Data Migration Assistant v3.3 or later.
  • To have created a Microsoft Azure Virtual Network by using the Azure Resource Manager deployment model, which provides the Azure Database Migration Service with site-to-site connectivity to your on-premises source servers by using either ExpressRoute or VPN.
  • To have completed assessment of your on-premises database and schema migration using Data Migration Assistant as described in the article Performing a SQL Server migration assessment
  • To download and install the Az.DataMigration module from the PowerShell Gallery by using Install-Module PowerShell cmdlet; be sure to open the PowerShell command window using run as an Administrator.
  • To ensure that the credentials used to connect to source SQL Server instance has the CONTROL SERVER permission.
  • To ensure that the credentials used to connect to target Azure SQL DB instance has the CONTROL DATABASE permission on the target Azure SQL Database databases.
  • An Azure subscription. If you don't have one, create a free account before you begin.

Log in to your Microsoft Azure subscription

Use the directions in the article Log in with Azure PowerShell to sign 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-AzResourceGroup command.

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

New-AzResourceGroup -ResourceGroupName myResourceGroup -Location EastUS

Create an instance of Azure Database Migration Service

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

  • Azure Resource Group name. You can use New-AzResourceGroup 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. The currently supported Sku name is GeneralPurpose_4vCores.
  • Virtual Subnet Identifier. You can use cmdlet New-AzVirtualNetworkSubnetConfig to create a subnet.

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

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

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

$service = New-AzDms -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-AzDmsConnInfo 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 Azure SQL.
  • DataSource. The name or IP of a SQL Server instance or Azure SQL Database.
  • 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 MySourceSQLServer using sql authentication:

$sourceConnInfo = New-AzDmsConnInfo -ServerType SQL `
  -DataSource MySourceSQLServer `
  -AuthType SqlAuthentication `
  -TrustServerCertificate:$true

The next example shows creation of Connection Info for a server called SQLAzureTarget using sql authentication:

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

Provide databases for the migration project

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

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

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

Create a project object

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

$project = New-AzDataMigrationProject -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-AzDmsSelectedDB cmdlet, as shown in the following example:

$selectedDbs = New-AzDmsSelectedDB -MigrateSqlServerSqlDb -Name AdventureWorks2016 `
  -TargetDatabaseName AdventureWorks2016 `
  -TableMap $tableMap

Create the migration task and start it

Use the New-AzDataMigrationTask 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 Azure SQL Database 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 Service project in which to create the task.
  • TaskName. Name of task to be created.
  • SourceConnection. AzDmsConnInfo object representing source SQL Server connection.
  • TargetConnection. AzDmsConnInfo object representing target Azure SQL Database connection.
  • SourceCred. PSCredential object for connecting to source server.
  • TargetCred. PSCredential object for connecting to target server.
  • SelectedDatabase. AzDataMigrationSelectedDB object representing the source and target database mapping.
  • SchemaValidation. (optional, switch parameter) Following the migration, performs a comparison of the schema information between source and target.
  • DataIntegrityValidation. (optional, switch parameter) Following the migration, performs a checksum-based data integrity validation between source and target.
  • QueryAnalysisValidation. (optional, switch parameter) Following the migration, performs a quick and intelligent query analysis by retrieving queries from the source database and executes them in the target.

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

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

The following example creates and starts the same migration task as above but also performs all three validations:

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

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"
}

Deleting the DMS instance

After the migration is complete, you can delete the Azure DMS instance:

Remove-AzDms -ResourceGroupName myResourceGroup -ServiceName MyDMS

Next step