导入或导出 Azure SQL 数据库但不允许 Azure 服务访问服务器Import or export an Azure SQL Database without allowing Azure services to access the server

适用于:是Azure SQL 数据库 APPLIES TO: yesAzure SQL Database

本文介绍如何在服务器上将“允许 Azure 服务”设置为“关闭”的情况下导入或导出数据库 。This article shows you how to import or export an Azure SQL Database when Allow Azure Services is set to OFF on the server. 工作流使用 Azure 虚拟机运行 SqlPackage 来执行导入或导出操作。The workflow uses an Azure virtual machine to run SqlPackage to perform the import or export operation.

登录到 Azure 门户Sign in to the Azure portal

登录到 Azure 门户Sign in to the Azure portal.

创建 Azure 虚拟机Create the Azure virtual machine

创建一个 Azure 虚拟机。Create an Azure virtual machine.

有关详细信息,请参阅创建 Windows VMFor more information, see Create a Windows VM.

连接到虚拟机Connect to the virtual machine

以下步骤说明如何使用远程桌面连接连接到虚拟机。The following steps show you how to connect to your virtual machine using a remote desktop connection.

  1. 部署完成后,转到虚拟机资源。After deployment completes, go to the virtual machine resource.

    VM

  2. 选择“连接” 。Select Connect.

    此时会显示远程桌面协议文件(.rdp 文件)窗体,其中包含虚拟机的公共 IP 地址和端口号。A Remote Desktop Protocol file (.rdp file) form appears with the public IP address and port number for the virtual machine.

    RDP 窗体

  3. 选择“下载 RDP 文件”。Select Download RDP File.

    备注

    也可使用 SSH 连接到 VM。You can also use SSH to connect to your VM.

  4. 关闭“连接到虚拟机”窗体。Close the Connect to virtual machine form.

  5. 若要连接到 VM,请打开下载的 RDP 文件。To connect to your VM, open the downloaded RDP file.

  6. 出现提示时,选择“连接”。When prompted, select Connect. 在 Mac 上,需要一个 RDP 客户端,例如 Mac 应用商店提供的这个远程桌面客户端On a Mac, you need an RDP client such as this Remote Desktop Client from the Mac App Store.

  7. 输入在创建虚拟机时指定的用户名和密码,然后选择“确定”。Enter the username and password you specified when creating the virtual machine, then choose OK.

  8. 你可能会在登录过程中收到证书警告。You might receive a certificate warning during the sign-in process. 选择“是”或“继续”以继续连接。 Choose Yes or Continue to proceed with the connection.

安装 SqlPackageInstall SqlPackage

下载并安装最新版本的 SqlPackageDownload and install the latest version of SqlPackage.

有关更多信息,请参阅 SqlPackage.exeFor additional information, see SqlPackage.exe.

创建防火墙规则以允许 VM 访问数据库Create a firewall rule to allow the VM access to the database

将虚拟机的公共 IP 地址添加到服务器的防火墙。Add the virtual machine's public IP address to the server's firewall.

以下步骤针对虚拟机的公共 IP 地址创建服务器级 IP 防火墙规则,并启用从虚拟机的连接。The following steps create a server-level IP firewall rule for your virtual machine's public IP address and enables connectivity from the virtual machine.

  1. 在左侧菜单中选择“SQL 数据库”,然后在“SQL 数据库”页上选择你的数据库。 Select SQL databases from the left-hand menu and then select your database on the SQL databases page. 此时会打开数据库的概述页,其中显示了完全限定的服务器名称(例如 servername.database.chinacloudapi.cn),并提供了用于完成进一步配置的选项。The overview page for your database opens, showing you the fully qualified server name (such as servername.database.chinacloudapi.cn) and provides options for further configuration.

  2. 请复制此完全限定的服务器名称,以便在连接到服务器及其数据库时使用。Copy this fully qualified server name to use when connecting to your server and its databases.

    服务器名称

  3. 在工具栏上选择“设置服务器防火墙”。Select Set server firewall on the toolbar. 此时会打开服务器的“防火墙设置”页面。The Firewall settings page for the server opens.

    服务器级别 IP 防火墙规则

  4. 在工具栏上选择“添加客户端 IP”,将虚拟机的公共 IP 地址添加到新的服务器级 IP 防火墙规则。Choose Add client IP on the toolbar to add your virtual machine's public IP address to a new server-level IP firewall rule. 服务器级 IP 防火墙规则可以针对单个 IP 地址或一系列 IP 地址打开端口 1433。A server-level IP firewall rule can open port 1433 for a single IP address or a range of IP addresses.

  5. 选择“保存” 。Select Save. 此时会针对虚拟机的公共 IP 地址创建服务器级 IP 防火墙规则,在服务器上打开端口 1433。A server-level IP firewall rule is created for your virtual machine's public IP address opening port 1433 on the server.

  6. 关闭“防火墙设置”页。Close the Firewall settings page.

使用 SqlPackage 导出数据库Export a database using SqlPackage

若要使用 SqlPackage 命令行实用工具导出 Azure SQL 数据库,请参阅导出参数和属性To export an Azure SQL Database using the SqlPackage command-line utility, see Export parameters and properties. SqlPackage 实用工具随附了最新版本的 SQL Server Management StudioSQL Server Data Tools;你也下载最新版本的 SqlPackageThe SqlPackage utility ships with the latest versions of SQL Server Management Studio and SQL Server Data Tools, or you can download the latest version of SqlPackage.

我们建议在大多数生产环境中使用 SqlPackage 实用工具来实现缩放和提高性能。We recommend the use of the SqlPackage utility for scale and performance in most production environments. 如需 SQL Server 客户顾问团队编写的有关使用 BACPAC 文件进行迁移的博客,请参阅 Migrating from SQL Server to Azure SQL Database using BACPAC Files(使用 BACPAC 文件从 SQL Server 迁移到 Azure SQL 数据库)。For a SQL Server Customer Advisory Team blog about migrating using BACPAC files, see Migrating from SQL Server to Azure SQL Database using BACPAC Files.

此示例演示如何通过 Active Directory 通用身份验证,使用 SqlPackage.exe 来导出数据库。This example shows how to export a database using SqlPackage.exe with Active Directory Universal Authentication. 请将占位符替换为环境特定的值。Replace with values that are specific to your environment.

SqlPackage.exe /a:Export /tf:testExport.bacpac /scs:"Data Source=<servername>.database.chinacloudapi.cn;Initial Catalog=MyDB;" /ua:True /tid:"apptest.partner.onmschina.cn"

使用 SqlPackage 导入数据库Import a database using SqlPackage

若要使用 SqlPackage 命令行实用程序导入 SQL Server 数据库,请参阅导入参数和属性To import a SQL Server database using the SqlPackage command-line utility, see import parameters and properties. SqlPackage 包含最新的 SQL Server Management StudioSQL Server Data ToolsSqlPackage has the latest SQL Server Management Studio and SQL Server Data Tools. 也下载最新版本的 SqlPackageYou can also download the latest version of SqlPackage.

在大多数生产环境中,建议使用 SqlPackage 而不是 Azure 门户来实现缩放和性能。For scale and performance, we recommend using SqlPackage in most production environments rather than using the Azure portal. 有关 SQL Server 客户咨询团队使用 BACPAC 文件进行迁移的博客,请参阅使用 BACPAC 文件从 SQL Server 迁移到 Azure SQL 数据库For a SQL Server Customer Advisory Team blog about migrating using BACPAC files, see migrating from SQL Server to Azure SQL Database using BACPAC Files.

以下 SqlPackage 命令将 AdventureWorks2017 数据库从本地存储导入到某个 Azure SQL 数据库。The following SqlPackage command imports the AdventureWorks2017 database from local storage to an Azure SQL Database. 它将创建名为 myMigratedDatabase 的新数据库,其中包含“高级”服务层级和 P6 服务目标。It creates a new database called myMigratedDatabase with a Premium service tier and a P6 Service Objective. 根据你的环境更改这些值。Change these values as appropriate for your environment.

sqlpackage.exe /a:import /tcs:"Data Source=<serverName>.database.chinacloudapi.cn;Initial Catalog=myMigratedDatabase>;User Id=<userId>;Password=<password>" /sf:AdventureWorks2017.bacpac /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P6

重要

若要从公司防火墙后连接到 Azure SQL 数据库,该防火墙必须打开端口 1433。To connect to tAzure SQL Database from behind a corporate firewall, the firewall must have port 1433 open.

此示例演示如何通过 Active Directory 通用身份验证,使用 SqlPackage 来导入数据库。This example shows how to import a database using SqlPackage with Active Directory Universal Authentication.

sqlpackage.exe /a:Import /sf:testExport.bacpac /tdn:NewDacFX /tsn:apptestserver.database.chinacloudapi.cn /ua:True /tid:"apptest.partner.onmschina.cn"

性能注意事项Performance considerations

许多因素(例如数据形状)会影响导出速度,因此预期的速度不可预测。Export speeds vary due to many factors (for example, data shape) so it's impossible to predict what speed should be expected. SqlPackage 可能需要花费相当长的时间,尤其是针对大型数据库运行时。SqlPackage may take considerable time, particularly for large databases.

若要获得最佳性能,可以尝试以下策略:To get the best performance you can try the following strategies:

  1. 确保数据库上没有运行其他工作负荷。Make sure no other workload is running on the database. 在导出之前创建一个副本可能是确保没有其他工作负荷运行的最佳解决方法。Create a copy before export may be the best solution to ensure no other workloads are running.
  2. 提高数据库服务级别目标 (SLO) 以更好地处理导出工作负荷(主要为读取 I/O)。Increase database service level objective (SLO) to better handle the export workload (primarily read I/O). 如果数据库当前位于 GP_Gen5_4,则使用“业务关键”层也许可为读取工作负荷提供帮助。If the database is currently GP_Gen5_4, perhaps a Business Critical tier would help with read workload.
  3. 确保有尤其适合大型表的聚集索引。Make sure there are clustered indexes particularly for large tables.
  4. 虚拟机 (VM) 应与数据库位于同一区域,以帮助避免网络约束。Virtual machines (VMs) should be in the same region as the database to help avoid network constraints.
  5. 在上传到 Blob 存储之前,应在 VM 中提供足够大小的 SSD 来生成临时项目。VMs should have SSD with adequate size for generating temp artifacts before uploading to blob storage.
  6. VM 应采用足够高的核心和内存配置,可以处理特定的数据库。VMs should have adequate core and memory configuration for the specific database.

存储已导入或导出的 .BACPAC 文件Store the imported or exported .BACPAC file

可将 .BACPAC 文件存储在 Azure BlobAzure 文件存储中。The .BACPAC file can be stored in Azure Blobs, or Azure Files.

若要实现最佳性能,请使用 Azure 文件存储。To achieve the best performance, use Azure Files. SqlPackage 在文件系统中运行,因此它可以直接访问 Azure 文件存储。SqlPackage operates with the filesystem so it can access Azure Files directly.

若要降低成本,请使用成本低于高级 Azure 文件共享的 Azure Blob。To reduce cost, use Azure Blobs, which cost less than a premium Azure file share. 但是,如果使用 Azure Blob,在执行导入或导出操作之前,需要在 Blob 与本地文件系统之间复制 .BACPAC 文件However, it will require you to copy the .BACPAC file between the the blob and the local file system before the import or export operation. 因此,该过程需要更长的时间。As a result the process will take longer.

若要上传或下载 .BACPAC 文件,请参阅使用 AzCopy 和 Blob 存储传输数据以及使用 AzCopy 和文件存储传输数据To upload or download .BACPAC files, see Transfer data with AzCopy and Blob storage, and Transfer data with AzCopy and file storage.

根据具体的环境,可能需要配置 Azure 存储防火墙和虚拟网络Depending on your environment, you might need to Configure Azure Storage firewalls and virtual networks.

后续步骤Next steps