Configuration Guide for Microsoft Hyper-V and SQL Server. Best Practices Guide for Microsoft SQL Server. To create a SQL Server over SMB solution, you must first configure ONTAP to provide storage services for the Microsoft SQL Server application. Additionally, you must also configure Microsoft. Microsoft SQL Server works on the backend of your critical applications, making it imperative to have a strategy set in place in case something happens to your server. Veeam specifically has tools to back up your SQL Server and restore it when needed.

NetApp a Microsoft SQL Server 2012, Platinum Sponsor, delivers a comprehensive portfolio of storage and data management solutions to help you get the full value of your Microsoft technology investments. With our joint solutions, you can efficiently store and manage data for your Microsoft applications and databases. Customers can secure and protect their data assets, reduce costs, and simplify your IT environment. They can also help improve productivity across the organization through increased data availability and streamlined management of your storage systems.

Join the Win with Microsoft and NetApp Webcast Series on SQL Server 2012 and learn more about innovative storage and data management solutions that can help you drive down costs while providing a highly reliable infrastructure that can help your organization keep pace with a constantly changing business environment. This informative Webcast series will give you tips and best practices on how to transform your business results with an efficient and flexible infrastructure for Microsoft SQL Server environments.

Join the Empowering DBAs to manage storage for SQL Server 2012, on Wednesday, March 14, 2012 by – Registering Here!

Several tests were performed on NetApp storage to compare the allocation unit sizes of 4K and 64K, and the impact on SQL Server, Exchange 2007 and Exchange 2010 performance. Iometer was used to simulate common data access patterns of SQL Server, Exchange 2007 and Exchange 2010. The results are summarized below. 6 NetApp Storage Best Practices for Microsoft Virtualization 1 EXECUTIVE SUMMARY Server virtualization is a major component of data center virtualization and plays a key role in this initiative, with Microsoft being a lead player with its server virtualization solutions.

Join Get the most out of SQL Server 2012 Availability Groups, on Wednesday, May 23, 2012 by – Registering Here!

Learn more about NetApp and other upcoming events by visiting our Community Forum.

Overview

This article provides guidance for optimizing SQL Server performance in Microsoft Azure Virtual Machine. While running SQL Server in Azure Virtual Machines, we recommend that you continue using the same database performance tuning options that are applicable to SQL Server in on-premises server environment. However, the performance of a relational database in a public cloud depends on many factors such as the size of a virtual machine, and the configuration of the data disks.

SQL Server images provisioned in the Azure portal follow general storage configuration best practices (for more information on how storage is configured, see Storage configuration for SQL Server VMs). After provisioning, consider applying other optimizations discussed in this article. Base your choices on your workload and verify through testing.

Tip

There is typically a trade-off between optimizing for costs and optimizing for performance. This article is focused on getting the best performance for SQL Server on Azure VMs. If your workload is less demanding, you might not require every optimization listed below. Consider your performance needs, costs, and workload patterns as you evaluate these recommendations.

Quick check list

The following is a quick check list for optimal performance of SQL Server on Azure Virtual Machines:

For more information on how and why to make these optimizations, please review the details and guidance provided in following sections.

VM size guidance

For performance sensitive applications, it’s recommended that you use the following virtual machines sizes:

• SQL Server Enterprise Edition: DS3_v2 or higher
• SQL Server Standard and Web Editions: DS2_v2 or higher

DSv2-series VMs support premium storage, which is recommended for the best performance. The sizes recommended here are baselines, but the actual machine size you select depends on your workload demands. DSv2-series VMs are general-purpose VMs that are good for a variety of workloads, whereas other machines sizes are optimized for specific workload types. For example, the M-series offers the highest vCPU count and memory for the largest SQL Server workloads. The GS-series and DSv2-series 11-15 are optimized for large memory requirements. Both of those series are also available in constrained core sizes, which saves money for workloads with lower compute demands. The Ls-series machines are optimized for high disk throughput and IO. It is important to consider your specific SQL Server workload and apply this to your selection of a VM series and size.

Storage guidance

DS-series (along with DSv2-series and GS-series) VMs support premium SSDs. Premium SSDs are recommended for all production workloads.

Warning

Standard HDDs and SSDs have varying latencies and bandwidth and are only recommended for dev/test workloads. Production workloads should use premium SSDs.

In addition, we recommend that you create your Azure storage account in the same data center as your SQL Server virtual machines to reduce transfer delays. When creating a storage account, disable geo-replication as consistent write order across multiple disks is not guaranteed. Instead, consider configuring a SQL Server disaster recovery technology between two Azure data centers. For more information, see High Availability and Disaster Recovery for SQL Server in Azure Virtual Machines.

Disks guidance

There are three main disk types on an Azure VM:

• OS disk: When you create an Azure Virtual Machine, the platform will attach at least one disk (labeled as the C drive) to the VM for your operating system disk. This disk is a VHD stored as a page blob in storage.
• Temporary disk: Azure virtual machines contain another disk called the temporary disk (labeled as the D: drive). This is a disk on the node that can be used for scratch space.
• Data disks: You can also attach additional disks to your virtual machine as data disks, and these will be stored in storage as page blobs.

The following sections describe recommendations for using these different disks.

Operating system disk

An operating system disk is a VHD that you can boot and mount as a running version of an operating system and is labeled as C drive.

Default caching policy on the operating system disk is Read/Write. For performance sensitive applications, we recommend that you use data disks instead of the operating system disk. See the section on Data Disks below.

Temporary disk

The temporary storage drive, labeled as the D: drive, is not persisted to Azure blob storage. Do not store your user database files or user transaction log files on the D: drive.

For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (such as temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency. For an example scenario, see the TempDB discussion in the following blog post: Storage Configuration Guidelines for SQL Server on Azure VM.

For VMs that support premium SSDs (DS-series, DSv2-series, and GS-series), we recommend storing TempDB on a disk that supports premium SSDs with read caching enabled.

There is one exception to this recommendation: if your TempDB usage is write-intensive, you can achieve higher performance by storing TempDB on the local D drive, which is also SSD-based on these machine sizes. Review the Using SSDs with TempDB blog for more information.

Data disks

• Use data disks for data and log files: If you are not using disk striping, use two premium SSD P30 disks where one disk contains the log file(s) and the other contains the data and TempDB (except for mission critical and write-heavy workloads as mentioned above). Each premium SSD provides a number of IOPs and bandwidth (MB/s) depending on its size, as depicted in the article, Select a disk type. If you are using a disk striping technique, such as Storage Spaces, you achieve optimal performance by having two pools, one for the log file(s) and the other for the data files. However, if you plan to use SQL Server Failover Cluster Instances (FCI), you must configure one pool.

  Tip

  • For test results on various disk and workload configurations, see the following blog post: Storage Configuration Guidelines for SQL Server on Azure VM.
  • For mission critical performance for SQL Servers that need ~ 50,000 IOPS, consider replacing 10 -P30 disks with an Ultra SSD. For more information, see the following blog post: Mission critical performance with Ultra SSD.

  Note

  When you provision a SQL Server VM in the portal, you have the option of editing your storage configuration. Depending on your configuration, Azure configures one or more disks. Multiple disks are combined into a single storage pool with striping. Both the data and log files reside together in this configuration. For more information, see Storage configuration for SQL Server VMs.

• Disk Striping: For more throughput, you can add additional data disks and use Disk Striping. To determine the number of data disks, you need to analyze the number of IOPS and bandwidth required for your log file(s), and for your data and TempDB file(s). Notice that different VM sizes have different limits on the number of IOPs and bandwidth supported, see the tables on IOPS per VM size. Use the following guidelines:

  • For Windows 8/Windows Server 2012 or later, use Storage Spaces with the following guidelines:

    1. Set the interleave (stripe size) to 64 KB (65536 bytes) for OLTP workloads and 256 KB (262144 bytes) for data warehousing workloads to avoid performance impact due to partition misalignment. This must be set with PowerShell.
    2. Set column count = number of physical disks. Use PowerShell when configuring more than 8 disks (not Server Manager UI).

    For example, the following PowerShell creates a new storage pool with the interleave size to 64 KB and the number of columns to 2:

  • For Windows 2008 R2 or earlier, you can use dynamic disks (OS striped volumes) and the stripe size is always 64 KB. Note that this option is deprecated as of Windows 8/Windows Server 2012. For information, see the support statement at Virtual Disk Service is transitioning to Windows Storage Management API.

  • If you are using Storage Spaces Direct (S2D) with SQL Server Failover Cluster Instances, you must configure a single pool. Note that although different volumes can be created on that single pool, they will all share the same characteristics, such as the same caching policy.

  • Determine the number of disks associated with your storage pool based on your load expectations. Keep in mind that different VM sizes allow different numbers of attached data disks. For more information, see Sizes for Virtual Machines.

  • If you are not using premium SSDs (dev/test scenarios), the recommendation is to add the maximum number of data disks supported by your VM size and use Disk Striping.

• Caching policy: Note the following recommendations for caching policy depending on your storage configuration.

  • If you are using separate disks for data and log files, enable read caching on the data disks hosting your data files and TempDB data files. This can result in a significant performance benefit. Do not enable caching on the disk holding the log file as this causes a minor decrease in performance.

  • If you are using disk striping in a single storage pool, most workloads will benefit from read caching. If you have separate storage pools for the log and data files, enable read caching only on the storage pool for the data files. In certain heavy write workloads, better performance might be achieved with no caching. This can only be determined through testing.

  • The previous recommendations apply to premium SSDs. If you are not using premium SSDs, do not enable any caching on any data disks.

  • For instructions on configuring disk caching, see the following articles. For the classic (ASM) deployment model see: Set-AzureOSDisk and Set-AzureDataDisk. For the Azure Resource Manager deployment model see: Set-AzOSDisk and Set-AzVMDataDisk.

    Warning

    Stop the SQL Server service when changing the cache setting of Azure VM disks to avoid the possibility of any database corruption.

• NTFS allocation unit size: When formatting the data disk, it is recommended that you use a 64-KB allocation unit size for data and log files as well as TempDB.

• Disk management best practices: When removing a data disk or changing its cache type, stop the SQL Server service during the change. When the caching settings are changed on the OS disk, Azure stops the VM, changes the cache type, and restarts the VM. When the cache settings of a data disk are changed, the VM is not stopped, but the data disk is detached from the VM during the change and then reattached.

  Warning

  Failure to stop the SQL Server service during these operations can cause database corruption.

I/O guidance

• The best results with premium SSDs are achieved when you parallelize your application and requests. Premium SSDs are designed for scenarios where the IO queue depth is greater than 1, so you will see little or no performance gains for single-threaded serial requests (even if they are storage intensive). For example, this could impact the single-threaded test results of performance analysis tools, such as SQLIO.

• Consider using database page compression as it can help improve performance of I/O intensive workloads. However, the data compression might increase the CPU consumption on the database server.

• Consider enabling instant file initialization to reduce the time that is required for initial file allocation. To take advantage of instant file initialization, you grant the SQL Server (MSSQLSERVER) service account with SE_MANAGE_VOLUME_NAME and add it to the Perform Volume Maintenance Tasks security policy. If you are using a SQL Server platform image for Azure, the default service account (NT ServiceMSSQLSERVER) isn’t added to the Perform Volume Maintenance Tasks security policy. In other words, instant file initialization is not enabled in a SQL Server Azure platform image. After adding the SQL Server service account to the Perform Volume Maintenance Tasks security policy, restart the SQL Server service. There could be security considerations for using this feature. For more information, see Database File Initialization.

• autogrow is considered to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow. If autogrow is used, pre-grow the file using the Size switch.

• Make sure autoshrink is disabled to avoid unnecessary overhead that can negatively affect performance.

• Move all databases to data disks, including system databases. For more information, see Move System Databases.

• Move SQL Server error log and trace file directories to data disks. This can be done in SQL Server Configuration Manager by right-clicking your SQL Server instance and selecting properties. The error log and trace file settings can be changed in the Startup Parameters tab. The Dump Directory is specified in the Advanced tab. The following screenshot shows where to look for the error log startup parameter.

• Setup default backup and database file locations. Use the recommendations in this article, and make the changes in the Server properties window. For instructions, see View or Change the Default Locations for Data and Log Files (SQL Server Management Studio). The following screenshot demonstrates where to make these changes.

• Enable locked pages to reduce IO and any paging activities. For more information, see Enable the Lock Pages in Memory Option (Windows).

• If you are running SQL Server 2012, install Service Pack 1 Cumulative Update 10. This update contains the fix for poor performance on I/O when you execute select into temporary table statement in SQL Server 2012. For information, see this knowledge base article.

• Consider compressing any data files when transferring in/out of Azure.

Feature-specific guidance

Some deployments may achieve additional performance benefits using more advanced configuration techniques. The following list highlights some SQL Server features that can help you to achieve better performance:

Backup to Azure Storage

When performing backups for SQL Server running in Azure virtual machines, you can use SQL Server Backup to URL. This feature is available starting with SQL Server 2012 SP1 CU2 and recommended for backing up to the attached data disks. When you backup/restore to/from Azure storage, follow the recommendations provided at SQL Server Backup to URL Best Practices and Troubleshooting and Restoring from Backups Stored in Azure Storage. You can also automate these backups using Automated Backup for SQL Server in Azure Virtual Machines.

Best Practice Guide For Microsoft Sql Server On Netapp Storage System

Prior to SQL Server 2012, you can use SQL Server Backup to Azure Tool. This tool can help to increase backup throughput using multiple backup stripe targets.

SQL Server Data files in Azure

This new feature, SQL Server Data Files in Azure, is available starting with SQL Server 2014. Running SQL Server with data files in Azure demonstrates comparable performance characteristics as using Azure data disks.

Failover cluster instance and Storage Spaces

If you are using Storage Spaces, when adding nodes to the cluster on the Confirmation page, clear the checkbox labeled Add all eligible storage to the cluster.

If you are using Storage Spaces and do not uncheck Add all eligible storage to the cluster, Windows detaches the virtual disks during the clustering process. As a result, they do not appear in Disk Manager or Explorer until the storage spaces are removed from the cluster and reattached using PowerShell. Storage Spaces groups multiple disks in to storage pools. For more information, see Storage Spaces.

Next steps

Best Practice Guide For Microsoft Sql Server On Netapp Storage

For more information about storage and performance, see Storage Configuration Guidelines for SQL Server on Azure VM

Best Practice Guide For Microsoft Sql Server On Netapp Storage Software

For security best practices, see Security Considerations for SQL Server in Azure Virtual Machines.

Best Practice Guide For Microsoft Sql Server On Netapp Storage Manager

Review other SQL Server Virtual Machine articles at SQL Server on Azure Virtual Machines Overview. If you have questions about SQL Server virtual machines, see the Frequently Asked Questions.