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 存储配置

根据所做的选择,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.

现有 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 SQL Server Configuration blade shows the current storage usage of your VM. 此图显示了 VM 上存在的所有驱动器。All drives that exist on your VM are displayed in this chart. 每个驱动器的存储空间都分四个部分显示:For each drive, the storage space displays in four sections:

  • SQL 数据SQL data
  • SQL 日志SQL log
  • 其他(非 SQL 存储)Other (non-SQL storage)
  • 可用Available

为现有 SQL Server VM 配置存储

若要配置存储以添加新驱动器或扩展现有的驱动器,请单击图表上方的“编辑”链接。To configure the storage to add a new drive or extend an existing drive, click the Edit link above the chart.

显示的配置选项会有所不同,这取决于以前是否用过此功能。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.

配置 SQL Server 存储滑块

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.

将新驱动器添加到 SQL 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. 每当扩展存储时,就必须添加至少四个数据磁盘。Any 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.

  • 如果为 VM 选择了至少 2 TB 的存储,则 Azure 将配置存储池。If you have selected at least two TBs of storage for your VM, Azure configures a storage pool. 本主题的下一部分提供了存储池配置详细信息。The next section of this topic provides the details of the 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 Value
条带大小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
即时文件初始化Instant file initialization EnabledEnabled
在内存中锁定页面Lock pages in memory EnabledEnabled
恢复Recovery 简单恢复(不可复原)Simple recovery (no resiliency)
列数Number of columns 数据磁盘数1Number of data disks1
TempDB 位置TempDB location 存储在数据磁盘上2Stored on data disks2

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

2 此设置仅适用于使用存储配置功能创建的第一个驱动器。2 This setting only applies to the first drive you create using the storage configuration feature.

工作负荷优化设置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.