SQL Server VM 的存储配置Storage configuration for SQL Server VMs

在 Azure 中配置 SQL Server 虚拟机映像时,可以借助门户自动完成存储配置。When you configure a SQL Server virtual machine image in Azure, the Portal helps to automate your storage configuration. 这包括将存储附加到 VM、使该存储可供 SQL Server 访问,并对其进行配置以根据特定的性能要求优化。This includes attaching storage to the VM, making that storage accessible to SQL Server, and configuring it to optimize for your specific performance requirements.

本主题介绍 Azure 如何在预配期间针对 SQL Server VM 以及针对现有的 VM 配置存储。This topic explains how Azure configures storage for your SQL Server VMs both during provisioning and for existing VMs. 此配置基于运行 SQL Server 的 Azure VM 的性能最佳做法This configuration is based on the performance best practices for Azure VMs running SQL Server.

Note

Azure 提供了可以用来创建和处理资源的两个不同部署模型:Azure 资源管理器部署模型和经典部署模型Azure has two different deployment models you can use to create and work with resources: Azure Resource Manager and classic. 本文介绍了资源管理器部署模型的使用。This article covers the use of the Resource Manager deployment model. 对于新部署,建议使用资源管理器部署模型而非经典部署模型。We recommend the Resource Manager deployment model for new deployments instead of the classic deployment model.

先决条件Prerequisites

若要使用自动存储配置设置,虚拟机需有以下特征:To use the automated storage configuration settings, your virtual machine requires the following characteristics:

新的 VMNew VMs

以下部分介绍了如何为新的 SQL Server 虚拟机配置存储。The following sections describe how to configure storage for new SQL Server virtual machines.

Azure 门户Azure portal

使用 SQL Server 库映像预配 Azure VM 时,可以选择自动为新的 VM 配置存储。When provisioning an Azure VM using a SQL Server gallery image, you can choose to automatically configure the storage for your new VM. 可以指定存储大小、性能限制和工作负荷类型。You specify the storage size, performance limits, and workload type. 以下屏幕截图显示了在预配 SQL VM 期间使用的“存储配置”边栏选项卡。The following screenshot shows the Storage configuration blade used during SQL VM provisioning.

预配期间的 SQL Server VM 存储配置

存储优化下选择要为其部署 SQL Server 的工作负荷类型。Select the type of workload you're deploying your SQL Server for under Storage optimization. 使用“常规”优化选项,默认情况下,你将拥有一个最大 IOPS 为 5000 的数据磁盘,并且你将使用此同一驱动器放置数据、事务日志和 TempDB 存储。With the General optimization option, by default you will have one data disk with 5000 max IOPS, and you will use this same drive for your data, transaction log, and TempDB storage. 你还可以根据业务选择“事务处理”(OLTP) 或“数据仓库”。You can also select either Transactional processing (OLTP) or Data warehousing according to your bussiness.

预配期间的 SQL Server VM 存储配置

根据所做的选择,Azure 会在创建 VM 后执行以下存储配置任务:Based on your choices, Azure performs the following storage configuration tasks after creating the VM:

  • 创建高级 SSD 盘并将其连接到虚拟机。Creates and attaches premium SSDs to the virtual machine.
  • 配置 SQL Server 可访问的数据磁盘。Configures the data disks to be accessible to SQL Server.
  • 根据指定的大小和性能(IOPS 和吞吐量)要求,在存储池中配置数据磁盘。Configures the data disks into a storage pool based on the specified size and performance (IOPS and throughput) requirements.
  • 将存储池与虚拟机上的新驱动器相关联。Associates the storage pool with a new drive on the virtual machine.
  • 根据指定的工作负荷类型(“数据仓库”、“事务处理”或“常规”)优化此新驱动器。Optimizes this new drive based on your specified workload type (Data warehousing, Transactional processing, or General).

有关 Azure 如何配置存储设置的详细信息,请参阅 存储配置部分For further details on how Azure configures storage settings, see the Storage configuration section. 有关如何在 Azure 门户中创建 SQL Server VM 的完整演练,请参阅预配教程For a full walkthrough of how to create a SQL Server VM in the Azure portal, see the provisioning tutorial.

Resource Manager 模板Resource Manage templates

如果使用以下 Resource Manager 模板,则会默认附加两个不带存储池配置的高级数据磁盘。If you use the following Resource Manager templates, two premium data disks are attached by default, with no storage pool configuration. 但是,可以自定义这些模板,更改附加到虚拟机的高级数据磁盘的数目。However, you can customize these templates to change the number of premium data disks that are attached to the virtual machine.

Note

必须修改从 GitHub 存储库“azure-quickstart-templates”下载或参考的模板,以适应 Azure 中国云环境。Templates you downloaded or referenced from the GitHub Repo "azure-quickstart-templates" must be modified in order to fit in the Azure China Cloud Environment. 例如,替换某些终结点(将“blob.core.windows.net”替换为“blob.core.chinacloudapi.cn”,将“cloudapp.azure.com”替换为“chinacloudapp.cn”);必要时更改某些不受支持的 VM 映像、VM 大小、SKU 以及资源提供程序的 API 版本。For example, replace some endpoints -- "blob.core.windows.net" by "blob.core.chinacloudapi.cn", "cloudapp.azure.com" by "chinacloudapp.cn"; change some unsupported VM images, VM sizes, SKU and resource-provider's API Version when necessary.

快速入门模板Quickstart template

可以使用以下快速入门模板通过存储优化来部署 SQL Server VM。You can use the following quickstart template to deploy a SQL Server VM using storage optimization.

现有 VMExisting VMs

对于现有的 SQL Server VM,可以在 Azure 门户中修改某些存储设置。For existing SQL Server VMs, you can modify some storage settings in the Azure portal. 选择 VM,转到“设置”区域,并选择“SQL Server 配置”。Select your VM, go to the Settings area, and then select SQL Server Configuration.

为现有 SQL Server VM 配置存储

显示的配置选项会有所不同,这取决于以前是否用过此功能。The configuration options that you see varies depending on whether you have used this feature before. 首次使用时,可以指定新驱动器的存储要求。When using for the first time, you can specify your storage requirements for a new drive. 如果以前曾经使用此功能创建了驱动器,可以选择扩展该驱动器的存储。If you previously used this feature to create a drive, you can choose to extend that drive's storage.

首次使用Use for the first time

如果首次使用此功能,可以指定新驱动器的存储大小和性能限制。If it is your first time using this feature, you can specify the storage size and performance limits for a new drive. 这种体验与预配类似。This experience is similar to what you would see at provisioning time. 主要差别在于无法指定工作负荷类型。The main difference is that you are not permitted to specify the workload type. 此限制可防止中断虚拟机上任何现有的 SQL Server 配置。This restriction prevents disrupting any existing SQL Server configurations on the virtual machine.

Azure 根据规范创建新驱动器。Azure creates a new drive based on your specifications. 在此方案中,Azure 将执行以下存储配置任务:In this scenario, Azure performs the following storage configuration tasks:

  • 创建高级存储数据磁盘并将其连接到虚拟机。Creates and attaches premium storage data disks to the virtual machine.
  • 配置 SQL Server 可访问的数据磁盘。Configures the data disks to be accessible to SQL Server.
  • 根据指定的大小和性能(IOPS 和吞吐量)要求,在存储池中配置数据磁盘。Configures the data disks into a storage pool based on the specified size and performance (IOPS and throughput) requirements.
  • 将存储池与虚拟机上的新驱动器相关联。Associates the storage pool with a new drive on the virtual machine.

有关 Azure 如何配置存储设置的详细信息,请参阅 存储配置部分For further details on how Azure configures storage settings, see the Storage configuration section.

添加新驱动器Add a new drive

如果已在 SQL Server VM 上配置存储,则展开存储会显示两个新选项。If you have already configured storage on your SQL Server VM, expanding storage brings up two new options. 第一个选项是添加新驱动器以提升 VM 的性能级别。The first option is to add a new drive, which can increase the performance level of your VM.

但是,添加驱动器后,必须执行一些附加的手动配置才能提升性能。However, after adding the drive, you must perform some extra manual configuration to achieve the performance increase.

扩展驱动器Extend the drive

扩展存储的另一个选项是扩展现有驱动器。The other option for expanding storage is to extend the existing drive. 此选项会增加驱动器的可用存储,但不提升性能。This option increases the available storage for your drive, but it does not increase performance. 对于存储池,在创建存储池后无法更改列数。With storage pools, you cannot alter the number of columns after the storage pool is created. 列数决定了可跨数据磁盘条带化的并行写入数。The number of columns determines the number of parallel writes, which can be striped across the data disks. 因此,添加的任何数据磁盘均无法提升性能。Therefore, any added data disks cannot increase performance. 它们只能为写入的数据提供更多的存储空间。They can only provide more storage for the data being written. 这种限制也意味着,在扩展驱动器时,列数决定了可以添加的数据磁盘的最小数目。This limitation also means that, when extending the drive, the number of columns determines the minimum number of data disks that you can add. 因此,如果创建的存储池包含四个数据磁盘,则列数也是四个。So if you create a storage pool with four data disks, the number of columns is also four. 每次扩展存储时,必须至少添加四个数据磁盘。Every time you extend the storage, you must add at least four data disks.

扩展 SQL VM 的驱动器

存储配置Storage configuration

本部分提供有关在 Azure 门户中预配或配置 SQL VM 期间,Azure 自动执行的存储配置更改的参考信息。This section provides a reference for the storage configuration changes that Azure automatically performs during SQL VM provisioning or configuration in the Azure portal.

  • Azure 通过从 VM 中选择的存储配置存储池。Azure configures a storage pool from storage selected from your VM. 本主题的下一部分提供了有关存储池配置的详细信息。The next section of this topic provides details about storage pool configuration.
  • 自动存储配置始终使用高级 SSD P30 数据磁盘。Automatic storage configuration always uses premium SSDs P30 data disks. 因此,所选 TB 数目与附加到 VM 的数据磁盘数目之间存在 1:1 映射。Consequently, there is a 1:1 mapping between your selected number of Terabytes and the number of data disks attached to your VM.

有关价格信息,请参阅“磁盘存储”选项卡上的存储定价页。For pricing information, see the Storage pricing page on the Disk Storage tab.

创建存储池Creation of the storage pool

Azure 使用以下设置在 SQL Server VM 上创建存储池。Azure uses the following settings to create the storage pool on SQL Server VMs.

设置Setting ValueValue
条带大小Stripe size 256 KB(数据仓库);64 KB(事务)256 KB (Data warehousing); 64 KB (Transactional)
磁盘大小Disk sizes 每个磁盘 1 TB1 TB each
缓存Cache 读取Read
分配大小Allocation size 64 KB NTFS 分配单元大小64 KB NTFS allocation unit size
恢复Recovery 简单恢复(不可复原)Simple recovery (no resiliency)
列数Number of columns 数据磁盘数最多 8 个1Number of data disks up to 81

1 创建存储池后,无法更改存储池中的列数。1 After the storage pool is created, you cannot alter the number of columns in the storage pool.

工作负荷优化设置Workload optimization settings

下表描述了三个可用的工作负荷类型选项及其对应的优化:The following table describes the three workload type options available and their corresponding optimizations:

工作负荷类型Workload type 说明Description 优化Optimizations
常规General 支持大多数工作负荷的默认设置Default setting that supports most workloads None
事务处理Transactional processing 针对传统数据库 OLTP 工作负荷优化存储Optimizes the storage for traditional database OLTP workloads 跟踪标志 1117Trace Flag 1117
跟踪标志 1118Trace Flag 1118
数据仓库Data warehousing 针对分析和报告工作负荷优化存储Optimizes the storage for analytic and reporting workloads 跟踪标志 610Trace Flag 610
跟踪标志 1117Trace Flag 1117

Note

只有通过在预配 SQL 虚拟机时,在存储配置步骤中进行选择,才能指定工作负荷类型。You can only specify the workload type when you provision a SQL virtual machine by selecting it in the storage configuration step.

后续步骤Next steps

有关其他与在 Azure VM 中运行 SQL Server 相关的主题,请参阅 SQL Server on Azure Virtual Machines(Azure 虚拟机上的 SQL Server)。For other topics related to running SQL Server in Azure VMs, see SQL Server on Azure Virtual Machines.