使用 PowerShell 和 Azure 数据库迁移服务将 SQL Server 脱机迁移到 SQL 托管实例Migrate SQL Server to SQL Managed Instance offline with PowerShell & Azure Database Migration Service

在本文中,我们将使用 Azure PowerShell 将还原为 SQL Server 2005 或更高版本的本地实例的 Adventureworks2016 数据库脱机迁移到 Azure SQL 托管实例。In this article, you offline migrate the Adventureworks2016 database restored to an on-premises instance of SQL Server 2005 or above to an Azure SQL SQL Managed Instance by using Azure PowerShell. 可以使用 Azure PowerShell 中的 Az.DataMigration 模块,将数据库从 SQL Server 实例迁移到 SQL 托管实例。You can migrate databases from a SQL Server instance to an SQL Managed Instance 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 Azure Database Migration Service.
  • 在 Azure 数据库迁移服务实例中创建迁移项目。Create a migration project in an instance of Azure Database Migration Service.
  • 运行脱机迁移。Run the migration offline.

提示

使用 Azure 数据库迁移服务将数据库迁移到 Azure 时,可以进行脱机或联机迁移。 When you migrate databases to Azure by using Azure Database Migration Service, you can do an offline or an online migration. 使用脱机迁移时,应用程序停机时间从迁移开始时算起。With an offline migration, application downtime starts when the migration starts. 使用联机迁移时,停机时间仅限在迁移结束时进行转换的那段时间。With an online migration, downtime is limited to the time to cut over at the end of migration. 建议对脱机迁移进行测试,以便确定其停机时间是否可以接受;如果不能接受,请进行联机迁移。We suggest that you test an offline migration to determine whether the downtime is acceptable; if not, do an online migration.

本文提供脱机迁移的步骤,但也可以进行联机迁移。This article provides steps for an offline migration, but it's also possible to migrate online.

先决条件Prerequisites

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

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

使用 PowerShell 登录到 Azure 订阅。Sign in to your Azure subscription by using PowerShell. 有关详细信息,请参阅使用 Azure PowerShell 登录一文。For more information, see the article Sign in with Azure PowerShell.

创建资源组Create a resource group

Azure 资源组是在其中部署和管理 Azure 资源的逻辑容器。An Azure resource group is a logical container in which Azure resources are deployed and managed.

使用 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 an 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 数据中心位置,例如“ChinaEast 2”。Specify an Azure data center location, such as ChinaEast 2.
  • Sku。Sku. 此参数对应于 DMS Sku 名称。This parameter corresponds to DMS Sku name. 目前支持的 SKU 名称为 Basic_1vCoreBasic_2vCoresGeneralPurpose_4vCoresCurrently supported Sku names are Basic_1vCore, Basic_2vCores, GeneralPurpose_4vCores.
  • 虚拟子网标识符。Virtual Subnet Identifier. 可以使用 cmdlet New-AzVirtualNetworkSubnetConfig 创建子网。You can use the 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 a subnet named 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, which 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 instance.
  • AuthType。AuthType. 连接的身份验证类型,可以为 SqlAuthentication 或 WindowsAuthentication。The authentication type for connection, which can be either SqlAuthentication or WindowsAuthentication.
  • TrustServerCertificateTrustServerCertificate. 此参数设置的值用于指示在绕过验证信任的证书链时,是否对通道加密。This parameter sets a value that indicates whether the channel is encrypted while bypassing walking the certificate chain to validate trust. 值可为 $true$falseThe value can be $true or $false.

以下示例使用 SQL 身份验证为名为 MySourceSQLServer 的源 SQL Server 创建连接信息对象:The following example creates a Connection Info object for a source SQL Server called MySourceSQLServer using sql authentication:

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

以下示例演示如何为名为“targetmanagedinstance”的 Azure SQL 托管实例创建连接信息:The next example shows creation of Connection Info for a Azure SQL Managed Instance named 'targetmanagedinstance':

$targetResourceId = (Get-AzSqlInstance -Name "targetmanagedinstance").Id
$targetConnInfo = New-AzDmsConnInfo -ServerType SQLMI -MiResourceId $targetResourceId

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

创建 AzDataMigrationDatabaseInfo 对象的列表,以便指定 Azure 数据库迁移服务项目中的数据库(可以作为创建项目所需的参数提供)。Create a list of AzDataMigrationDatabaseInfo objects that specifies databases as part of the Azure Database Migration Service project, which can be provided as parameter for creation of the project. 可以使用 cmdlet New-AzDataMigrationDatabaseInfo 创建 AzDataMigrationDatabaseInfoYou can use the cmdlet New-AzDataMigrationDatabaseInfo to create AzDataMigrationDatabaseInfo.

以下示例为 AdventureWorks2016 数据库创建 AzDataMigrationDatabaseInfo 项目,并将其添加到列表,以便在创建项目时作为参数提供。The following example creates the AzDataMigrationDatabaseInfo project for the AdventureWorks2016 database and adds it to the list to be provided as parameter for project creation.

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

创建项目对象Create a project object

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

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

接下来,创建并启动 Azure 数据库迁移服务任务。Next, create and start an Azure Database Migration Service task. 此任务需要源和目标的连接凭据信息、要迁移的数据库表的列表,以及在作为先决条件创建的项目中提供的信息。This task requires connection credential information for both the source and target, as well as the list of database tables to be migrated and the information already provided with the project created as a prerequisite.

创建源和目标的凭据参数Create credential parameters for source and target

创建连接安全凭据作为 PSCredential 对象。Create connection security credentials as a PSCredential object.

以下示例显示了如何为源和目标连接创建 PSCredential 对象,将密码作为字符串变量 $sourcePassword$targetPassword 提供。The following example shows the creation of PSCredential objects for both the 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)

创建备份 FileShare 对象Create a backup FileShare object

现在,使用 New-AzDmsFileShare cmdlet 创建 FileShare 对象,用于 Azure 数据库迁移服务可将源数据库备份迁移到的本地 SMB 网络共享。Now create a FileShare object representing the local SMB network share to which Azure Database Migration Service can take the source database backups using the New-AzDmsFileShare cmdlet.

$backupPassword = ConvertTo-SecureString -String $password -AsPlainText -Force
$backupCred = New-Object System.Management.Automation.PSCredential ($backupUserName, $backupPassword)

$backupFileSharePath="\\10.0.0.76\SharedBackup"
$backupFileShare = New-AzDmsFileShare -Path $backupFileSharePath -Credential $backupCred

创建选定的数据库对象Create selected database object

下一步是使用 New-AzDmsSelectedDB cmdlet 选择源和目标数据库。The next step is to select the source and target databases by using the New-AzDmsSelectedDB cmdlet.

以下示例用于将 SQL Server 中的单一数据库迁移到 Azure SQL 托管实例:The following example is for migrating a single database from SQL Server to a Azure SQL Managed Instance:

$selectedDbs = @()
$selectedDbs += New-AzDmsSelectedDB -MigrateSqlServerSqlDbMi `
  -Name AdventureWorks2016 `
  -TargetDatabaseName AdventureWorks2016 `
  -BackupFileShare $backupFileShare `

如果整个 SQL Server 实例需要直接迁移到 Azure SQL 托管实例,可以使用下面提供的从源提取所有数据库的循环。If an entire SQL Server instance needs a lift-and-shift into a Azure SQL Managed Instance, then a loop to take all databases from the source is provided below. 在以下示例中,请为 $Server、$SourceUserName 和 $SourcePassword 提供源 SQL Server 详细信息。In the following example, for $Server, $SourceUserName, and $SourcePassword, provide your source SQL Server details.

$Query = "(select name as Database_Name from master.sys.databases where Database_id>4)";
$Databases= (Invoke-Sqlcmd -ServerInstance "$Server" -Username $SourceUserName
-Password $SourcePassword -database master -Query $Query)
$selectedDbs=@()
foreach($DataBase in $Databases.Database_Name)
    {
      $SourceDB=$DataBase
      $TargetDB=$DataBase
      
$selectedDbs += New-AzureRmDmsSelectedDB -MigrateSqlServerSqlDbMi `
                                              -Name $SourceDB `
                                              -TargetDatabaseName $TargetDB `
                                              -BackupFileShare $backupFileShare
      }

Azure 存储容器的 SAS URISAS URI for Azure Storage Container

创建包含 SAS URI 的变量,使 Azure 数据库迁移服务能够访问它要将备份文件上传到的存储帐户容器。Create variable containing the SAS URI that provides the Azure Database Migration Service with access to the storage account container to which the service uploads the backup files.

$blobSasUri="https://mystorage.blob.core.chinacloudapi.cn/test?st=2018-07-13T18%3A10%3A33Z&se=2019-07-14T18%3A10%3A00Z&sp=rwdl&sv=2018-03-28&sr=c&sig=qKlSA512EVtest3xYjvUg139tYSDrasbftY%3D"

备注

Azure 数据库迁移服务不支持使用帐户级别 SAS 令牌。Azure Database Migration Service does not support using an account level SAS token. 必须使用存储帐户容器的 SAS URI。You must use a SAS URI for the storage account container. 了解如何获取 Blob 容器的 SAS URILearn how to get the SAS URI for blob container.

其他配置要求Additional configuration requirements

还需要满足一些其他要求:There are a few additional requirements you need to address:

  • 选择登录名Select logins. 按以下示例中所示,创建要迁移的登录名列表:Create a list of logins to be migrated as shown in the following example:

    $selectedLogins = @("user1", "user2")
    

    重要

    目前,Azure 数据库迁移服务仅支持迁移 SQL 登录名。Currently, Azure Database Migration Service only supports migrating SQL logins.

  • 选择代理作业Select agent jobs. 按以下示例中所示,创建要迁移的代理作业列表:Create list of agent jobs to be migrated as shown in the following example:

    $selectedAgentJobs = @("agentJob1", "agentJob2")
    

    重要

    目前,Azure 数据库迁移服务仅支持包含 T-SQL 子系统作业步骤的作业。Currently, Azure Database Migration Service only supports jobs with T-SQL subsystem job steps.

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

使用 New-AzDataMigrationTask cmdlet 创建并启动迁移任务。Use the New-AzDataMigrationTask cmdlet to create and start a migration task.

指定参数Specify parameters

New-AzDataMigrationTask cmdlet 需要以下参数:The New-AzDataMigrationTask cmdlet expects the following parameters:

  • TaskType。TaskType. 要创建的迁移任务的类型。对于从 SQL Server 到 Azure SQL 托管实例的迁移类型,此项应为“MigrateSqlServerSqlDbMi”。Type of migration task to create for SQL Server to Azure SQL Managed Instance migration type MigrateSqlServerSqlDbMi 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. AzDmsConnInfo 对象,表示目标 Azure SQL 托管实例连接。AzDmsConnInfo object representing target Azure SQL Managed Instance 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.
  • BackupFileShareBackupFileShare. 表示 Azure 数据库迁移服务可在其上创建源数据库备份的本地网络共享的 FileShare 对象。FileShare object representing the local network share that the Azure Database Migration Service can take the source database backups to.
  • BackupBlobSasUriBackupBlobSasUri. 为 Azure 数据库迁移服务提供存储帐户容器访问权限的 SAS URI,该服务将备份文件上传到该容器。The SAS URI that provides the Azure Database Migration Service with access to the storage account container to which the service uploads the backup files. 了解如何获取 Blob 容器的 SAS URI。Learn how to get the SAS URI for blob container.
  • SelectedLoginsSelectedLogins. 要迁移的选定登录名列表。List of selected logins to migrate.
  • SelectedAgentJobsSelectedAgentJobs. 要迁移的选定代理作业列表。List of selected agent jobs to migrate.
  • SelectedLoginsSelectedLogins. 要迁移的选定登录名列表。List of selected logins to migrate.
  • SelectedAgentJobsSelectedAgentJobs. 要迁移的选定代理作业列表。List of selected agent jobs to migrate.

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

以下示例创建并启动名为 myDMSTask 的脱机迁移任务:The following example creates and starts an offline migration task named myDMSTask:

$migTask = New-AzDataMigrationTask -TaskType MigrateSqlServerSqlDbMi `
  -ResourceGroupName myResourceGroup `
  -ServiceName $service.Name `
  -ProjectName $project.Name `
  -TaskName myDMSTask `
  -SourceConnection $sourceConnInfo `
  -SourceCred $sourceCred `
  -TargetConnection $targetConnInfo `
  -TargetCred $targetCred `
  -SelectedDatabase  $selectedDbs `
  -BackupFileShare $backupFileShare `
  -BackupBlobSasUri $blobSasUri `
  -SelectedLogins $selectedLogins `
  -SelectedAgentJobs $selectedJobs `

监视迁移Monitor the migration

若要监视迁移,请执行以下任务。To monitor the migration, perform the following tasks.

  1. 将所有迁移详细信息合并到名为 $CheckTask 的变量中。Consolidate all the migration details into a variable called $CheckTask.

    若要合并迁移详细信息(例如,与迁移相关的属性、状态和数据库信息),请使用以下代码片段:To combine migration details such as properties, state, and database information associated with the migration, use the following code snippet:

    $CheckTask= Get-AzDataMigrationTask     -ResourceGroupName myResourceGroup `
                                            -ServiceName $service.Name `
                                        -ProjectName $project.Name `
                                            -Name myDMSTask `
                                            -ResultType DatabaseLevelOutput `
                        -Expand 
    Write-Host '$CheckTask.ProjectTask.Properties.Output'
    
  2. 使用 $CheckTask 变量获取迁移任务的当前状态。Use the $CheckTask variable to get the current state of the migration task.

    可以使用 $CheckTask 变量获取迁移任务的当前状态,并可以通过查询任务的状态属性来监视正在运行的迁移任务,如以下示例所示:To use the $CheckTask variable to get the current state of the migration task, you can monitor the migration task running by querying the state property of the task, as shown in the following example:

    if (($CheckTask.ProjectTask.Properties.State -eq "Running") -or ($CheckTask.ProjectTask.Properties.State -eq "Queued"))
    {
      Write-Host "migration task running"
    }
    else if($CheckTask.ProjectTask.Properties.State -eq "Succeeded")
    { 
      Write-Host "Migration task is completed Successfully"
    }
    else if($CheckTask.ProjectTask.Properties.State -eq "Failed" -or $CheckTask.ProjectTask.Properties.State -eq "FailedInputValidation" -or $CheckTask.ProjectTask.Properties.State -eq "Faulted")
    { 
      Write-Host "Migration Task Failed"
    }
    

删除 Azure 数据库迁移服务的实例Delete the instance of Azure Database Migration Service

完成迁移后,可以删除 Azure 数据库迁移服务实例:After the migration is complete, you can delete the Azure Database Migration Service instance:

Remove-AzDms -ResourceGroupName myResourceGroup -ServiceName MyDMS

后续步骤Next steps

什么是 Azure 数据库迁移服务?一文中详细了解 Azure 数据库迁移服务。Find out more about Azure Database Migration Service in the article What is the Azure Database Migration Service?.