使用 Azure PowerShell 将 SQL Server 数据库迁移到 Azure SQL 数据库Migrate a SQL Server database to Azure SQL Database using Azure PowerShell

在本文中,我们使用 Azure PowerShell 将还原为 SQL Server 2016 或更高版本的本地实例的 Adventureworks2012 数据库迁移到 Azure SQL 数据库。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 Azure PowerShell. 可以使用 Azure PowerShell 中的 Az.DataMigration 模块,将数据库从 SQL Server 实例迁移到 Azure SQL 数据库。You can migrate databases from a SQL Server instance to Azure SQL Database by using the Az.DataMigration module in Azure PowerShell.

在本文中,学习如何:In this article, you learn how to:

  • 创建资源组。Create a resource group.
  • 创建 Azure 数据库迁移服务的实例。Create an instance of the Azure Database Migration Service.
  • 在 Azure 数据库迁移服务实例中创建迁移项目。Create a migration project in an Azure Database Migration Service instance.
  • 运行迁移。Run the migration.

先决条件Prerequisites

若要完成这些步骤,需满足以下条件:To complete these steps, you need:

登录到 Azure 订阅Log in to your Azure subscription

根据使用 Azure PowerShell 登录一文中的说明,使用 PowerShell 登录到 Azure 订阅。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

Azure 资源组是在其中部署和管理 Azure 资源的逻辑容器。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.

使用 New-AzResourceGroup 命令创建资源组。Create a resource group by using the New-AzResourceGroup command.

以下示例在“中国东部 2”区域创建名为“myResourceGroup” 的资源组。The following example creates a resource group named myResourceGroup in the China East 2 region.

New-AzResourceGroup -ResourceGroupName myResourceGroup -Location ChinaEast2

创建 Azure 数据库迁移服务的实例Create an instance of Azure Database Migration Service

可以通过 New-AzDataMigrationService cmdlet 创建 Azure 数据库迁移服务的新实例。You can create new instance of Azure Database Migration Service by using the New-AzDataMigrationService cmdlet. 此 cmdlet 需要以下必需参数:This cmdlet expects the following required parameters:

  • Azure 资源组名称。Azure Resource Group name. 可以使用 New-AzResourceGroup 命令创建前述 Azure 资源组,并提供其名称作为参数。You can use New-AzResourceGroup command to create Azure Resource group as previously shown and provide its name as a parameter.
  • 服务名称。Service name. 与 Azure 数据库迁移服务的所需唯一服务名称相对应的字符串String that corresponds to the desired unique service name for Azure Database Migration Service
  • 位置Location. 指定服务的位置。Specifies the location of the service. 指定 Azure 数据中心位置,例如“中国东部 2”Specify an Azure data center location, such as China East 2
  • Sku。Sku. 此参数对应于 DMS Sku 名称。This parameter corresponds to DMS Sku name. 当前支持的 Sku 名称是 GeneralPurpose_4vCoresThe currently supported Sku name is GeneralPurpose_4vCores.
  • 虚拟子网标识符。Virtual Subnet Identifier. 可以使用 cmdlet New-AzVirtualNetworkSubnetConfig 创建子网。You can use cmdlet New-AzVirtualNetworkSubnetConfig to create a subnet.

以下示例使用名为 MyVNET 的虚拟网络和名为 MySubnet 的子网,在位于“中国东部 2”区域的资源组 MyDMSResourceGroup 中创建名为 MyDMS 的服务。The following example creates a service named MyDMS in the resource group MyDMSResourceGroup located in the China East 2 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 ChinaEast2 `
  -Sku Basic_2vCores `  
  -VirtualSubnetId $vSubNet.Id`

创建迁移项目Create a migration project

在创建 Azure 数据库迁移服务实例以后,创建迁移项目。After creating an Azure Database Migration Service instance, create a migration project. Azure 数据库迁移服务项目需要源和目标实例的连接信息,以及要在项目中迁移的数据库的列表。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

可以使用 New-AzDmsConnInfo cmdlet 创建数据库连接信息对象。You can create a Database Connection Info object by using the New-AzDmsConnInfo cmdlet. 此 cmdlet 需要以下参数:This cmdlet expects the following parameters:

  • ServerType。ServerType. 请求的数据库连接的类型,例如 SQL、Oracle 或 MySQL。The type of database connection requested, for example, SQL, Oracle, or MySQL. 将 SQL 用于 SQL Server 和 Azure SQL。Use SQL for SQL Server and Azure SQL.
  • DataSource。DataSource. SQL Server 实例或 Azure SQL 数据库的名称或 IP。The name or IP of a SQL Server instance or Azure SQL Database.
  • AuthType。AuthType. 连接的身份验证类型,可以为 SqlAuthentication 或 WindowsAuthentication。The authentication type for connection, which can be either SqlAuthentication or WindowsAuthentication.
  • TrustServerCertificate 参数设置的值用于指示在绕过验证信任的证书链时,是否对通道加密。TrustServerCertificate parameter sets a value that indicates whether the channel is encrypted while bypassing walking the certificate chain to validate trust. 可以是 true 或 false。Value can be true or false.

以下示例使用 SQL 身份验证为名为 MySourceSQLServer 的源 SQL Server 创建连接信息对象: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

下一示例显示如何使用 SQL 身份验证为名为 SQLAzureTarget 的服务器创建连接信息:The next example shows creation of Connection Info for a server called SQLAzureTarget using sql authentication:

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

为迁移项目提供数据库Provide databases for the migration project

创建 AzDataMigrationDatabaseInfo 对象的列表,以便指定 Azure 数据库迁移项目中的数据库(可以作为创建项目所需的参数提供)。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. Cmdlet New-AzDataMigrationDatabaseInfo 可用于创建 AzDataMigrationDatabaseInfo。The Cmdlet New-AzDataMigrationDatabaseInfo can be used to create AzDataMigrationDatabaseInfo.

以下示例为 AdventureWorks2016 数据库创建 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

最后即可使用 New-AzDataMigrationProject 并添加以前创建的源和目标连接以及要迁移的数据库的列表,在“中国东部 2”创建名为“MyDMSProject”的 Azure 数据库迁移项目。 Finally you can create Azure Database Migration project called MyDMSProject located in China East 2 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 ChinaEast2 `
  -SourceType SQL `
  -TargetType SQLDB `
  -SourceConnection $sourceConnInfo `
  -TargetConnection $targetConnInfo `
  -DatabaseInfo $dbList

创建并启动迁移任务Create and start a migration task

最后,创建并启动 Azure 数据库迁移任务。Finally, create and start Azure Database Migration task. 除了作为先决条件为已创建的项目提供的信息,Azure 数据库迁移任务还需要源和目标的连接凭据信息,以及要迁移的数据库表的列表。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

可以将连接安全凭据作为 PSCredential 对象创建。Connection security credentials can be created as a PSCredential object.

以下示例显示了如何为源和目标连接创建 PSCredential 对象,将密码作为字符串变量 $sourcePassword 和 $targetPassword 提供。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. 以下示例演示了源和目标表之间的映射(针对 AdventureWorks 2016 数据库的“人力资源”架构)。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")

下一步是使用 New-AzDmsSelectedDB cmdlet 选择源和目标数据库并提供要作为参数迁移的表映射,如以下示例所示: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

使用 New-AzDataMigrationTask cmdlet 创建并启动迁移任务。Use the New-AzDataMigrationTask cmdlet to create and start a migration task. 此 cmdlet 需要以下参数:This cmdlet expects the following parameters:

  • TaskType。TaskType. 要创建的迁移任务的类型。对于从 SQL Server 到 Azure SQL 数据库的迁移类型,此项应为 MigrateSqlServerSqlDb。Type of migration task to create for SQL Server to Azure SQL Database migration type MigrateSqlServerSqlDb is expected.
  • ResourceGroupName。Resource Group Name. 要在其中创建任务的 Azure 资源组的名称。Name of Azure resource group in which to create the task.
  • ServiceName。ServiceName. 要在其中创建任务的 Azure 数据库迁移服务实例。Azure Database Migration Service instance in which to create the task.
  • ProjectName。ProjectName. 要在其中创建任务的 Azure 数据库迁移服务项目的名称。Name of Azure Database Migration Service project in which to create the task.
  • TaskName。TaskName. 要创建的任务的名称。Name of task to be created.
  • SourceConnectionSourceConnection. 表示源 SQL Server 连接的 AzDmsConnInfo 对象。AzDmsConnInfo object representing source SQL Server connection.
  • TargetConnectionTargetConnection. 表示目标 Azure SQL 数据库连接的 AzDmsConnInfo 对象。AzDmsConnInfo object representing target Azure SQL Database connection.
  • SourceCred。SourceCred. PSCredential 对象,用于连接到源服务器。PSCredential object for connecting to source server.
  • TargetCred。TargetCred. PSCredential 对象,用于连接到目标服务器。PSCredential object for connecting to target server.
  • SelectedDatabase。SelectedDatabase. 表示源和目标数据库映射的 AzDataMigrationSelectedDB 对象。AzDataMigrationSelectedDB object representing the source and target database mapping.
  • SchemaValidationSchemaValidation. (可选,开关参数)迁移后,在源和目标之间执行架构信息比较。(optional, switch parameter) Following the migration, performs a comparison of the schema information between source and target.
  • DataIntegrityValidationDataIntegrityValidation. (可选,开关参数)迁移后,在源和目标之间执行基于校验和的数据完整性验证。(optional, switch parameter) Following the migration, performs a checksum-based data integrity validation between source and target.
  • QueryAnalysisValidationQueryAnalysisValidation. (可选,开关参数)迁移后,通过从源数据库检索查询并在目标中执行查询来执行快速智能的查询分析。(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.

以下示例创建并启动名为 myDMSTask 的迁移任务: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"
}

正在删除 DMS 实例Deleting the DMS instance

完成迁移后,可以删除 Azure DMS 实例:After the migration is complete, you can delete the Azure DMS instance:

Remove-AzDms -ResourceGroupName myResourceGroup -ServiceName MyDMS