Co-author(s): Pat Sinthusan, NetApp
This technical report provides an overview of the configuration of an Azure environment to run SQL Server with SMB file share storage using Azure NetApp Files with the highest levels of resiliency. This document is intended for Azure NetApp Files and/or SQL Server database administrators who are responsible for deploying SQL Server in Azure. We assume that the reader is familiar with the various components of the solution.
Today, many customers migrate to Microsoft Azure to accelerate their SQL Server deployments, reduce cost, and provide increased agility for their business processes. All these benefits are important to SQL Server IT leaders who use a cloud-first strategy. Moreover, moving the SQL Server estate to Azure and integrating SQL Server with Azure’s vast array of platform-as-a-service (PaaS) features such as Azure Data Factory, Azure IoT Hub, and Azure Machine Learning creates business value to support digitalization ambitions.
Many customers are embracing the dev/ops paradigm by first moving their development and test SQL Server systems to Azure. However, more customers are now choosing to migrate their complete SQL Server infrastructure, including production, into the cloud as well. Azure’s vast SQL Server offering ranges from small virtual machines (VMs) up to large instances of SQL Server. These instances can scale from small single-host configurations to the largest multiple-host configurations.
Businesses have been increasingly migrating on-premises workloads to Azure for several reasons, including data center consolidation and cost effectiveness. For like-for-like, lift-and-shift scenarios with database sizes in the double-digit TB range, VM-based, infrastructure-as-a-service (IaaS) architectures are a viable option. Memory-optimized Azure VMs like the E-series and M-series offer excellent compute capabilities for demanding database workloads such as SQL VLDBs or SAP HANA. Smaller databases run equally well on smaller D-series or E-series VM SKUs. However, those SKUs are often limited in terms of disk IOPs resources, which can be overcome by using SMB networked storage.
Azure NetApp Files is an Azure-native NAS storage solution for running high performance SQL workloads in combination with Azure VMs (Figure 1).
Figure 1) Azure NetApp Files.
To understand the value of Azure NetApp Files for SQL Server deployments, read about the Benefits of using Azure NetApp Files for SQL Server deployment.
Before you complete the steps in this document, you should already have the following items:
Make sure all SQL Server nodes can reach each other before continuing:
There are a few things you need to know and have in place before you start.
You should have an account to create Azure NetApp Files and a basic understanding of how to install a single instance of SQL Server.
Before creating a volume in Azure NetApp Files, you must register for the service and set up a pool for provisioned capacity. The following resources contain information about capacity pools:
Starting with SQL Server 2012 (11.x), system databases (Master, Model, MSDB, and TempDB), and Database Engine user databases can be installed with an SMB file server as a storage option. This applies to both stand-alone SQL Server and SQL Server failover cluster installations. This enables you to use Azure NetApp Files with all its performance and data management capabilities, including volume capacity, performance scalability, and data protection features, which SQL Server can take advantage of.
To install SQL Server over SMB shares, the installer must meet the following requirements:
Figure 2) Add Security privilege user entries in the Active Directory settings.
The installation process is the same as for the typical block storage. The only exception is that the data root directory for system database files can be pointed to an SMB share during the database engine configuration step.
Figure 3) Configure data directories using a Unique Naming Convention (UNC) path to an Azure NetApp Files SMB volume.
SMB3 with the Continuous Availability share property enabled provides a very high level of resiliency between the VMs and the storage service. SMB transparent failover enables maintenance operations on the Azure NetApp Files service without interrupting connectivity to server applications storing and accessing data on SMB volumes. To support SMB transparent failover, Azure NetApp Files supports the SMB Continuous Availability shares option for use with SQL Server applications over SMB running on Azure VMs. SMB continuous availability shares enable SQL server workloads on Azure NetApp Files, which provides performance improvements, scale, and cost benefits for a single instance, an always-on failover cluster instance, and always-on availability group deployments.
It is recommended that you enable continuous availability for all the volumes that host SQL Server data and log files. You can set the Enable Continuous Availability option during volume creation.
Figure 4) Make sure the Enable Continuous Availability option is set during SMB volume creation.
To create Azure NetApp Files volumes, complete the following steps:
To properly secure and assign permissions to Azure NetApp Files, complete the following steps:
The following steps describe how to deploy a single instance SQL Server or an Always On Failover Cluster Instance over SMB on Azure NetApp Files. When deploying a single instance SQL Server, you can omit the cluster creation steps. During VM creation, Microsoft and NetApp recommend creating a VM with availability set for all VMs in the Windows failover cluster.
Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools -Restart
ClusterName = "SQLProdCluster" $node1 = "SQLProd01" $node2 = "SQLProd02" $sqlvip = "10.1.1.68" $witnessshare = ‘ANFServerName\FolderName’ #ANF path provide here New-Cluster -Name $ClusterName -Node $node1,$node2 -NoStorage -StaticAddress $sqlvip -managementpointnetworktype singleton Set-ClusterQuorum -FileShareWitness $witnessshare\ -Credential $(Get-Credential) Test-Cluster -Node $node1, $node2
The following steps describe how to install a new SQL Server for either a single instance or failover cluster.
One of the requirements of AOAG databases is that the UNC file path of the secondary database matches the UNC file path from the corresponding primary database (For more information, see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups). However, each Azure NetApp Files SMB share gets a virtual SMB server name assigned automatically with a random suffix. Therefore, all SMB shares get a unique UNC path consisting of \\<servername-xxx>\sharename. This can impact on add-file operations, as described here: Restrictions (Availability Databases).
To solve this issue, you can use two separate Azure Virtual Networks (VNets) for the AOAG cluster nodes and associated Azure NetApp Files SMB volumes (Figure 5), and you can use the servers’ hosts file to redirect the SMB volumes to the individual SMB virtual servers to create virtually identical UNC paths.
Figure 5) High level configuration overview for AOAG on SMB volumes.
You can make sure the UNC file paths of the secondary and corresponding primary databases match by following these steps before deploying AOAGs:
attrib -r C:\Windows\System32\drivers\etc\hosts
# localhost name resolution is handled within DNS itself. # 127.0.0.1 localhost # ::1 localhost 10.10.1.4 sql.contoso.com
After you have completed the preceding steps, you can deploy AOAGs by following the steps in Getting Started with Always On Availability Groups. During database and AOAG creation, the virtual path must be used on all servers instead of the direct UNC path for the Azure NetApp Files volumes; that is, \\sql.contoso.com\sqlprod-data and \\sql.contoso.com\sqlprod-log.
During the attach or restore database process using T-SQL, a message like the following example might appear in the query result and login SQL Server.
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "\\servername\sharename\filename.mdf". Operating system error 5: (Access is denied.).
If you use SQL Server Management Studio, an error message such as the one shown in Figure 6 might occur.
Figure 6) An error message that might appear in SQL Server Management Studio.
To resolve this issue, complete these steps:
exec sp_attach_db DatabaseName, '\\Network-attached storage_Path\DatabaseMDFFile.mdf', '\\Network-attached storage_Path\DatabaseLDFFile.ldf' go
To learn more about the information that is described in this document, review the following documents and/or websites:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.