Advanced Data Solutions : Deploying SQL Server on Azure using Azure NetApp Files

Abstract

This blog provides guidance on how to deploy Microsoft SQL Server leveraging Azure NetApp Files on virtual machines in Azure.

Co-author(s): Pat Sinthusan, NetApp

Intended audience

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.

SQL Server on Microsoft Azure overview

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 begin

Before you complete the steps in this document, you should already have the following items:

A Microsoft Azure subscription.
A Windows Active Directory domain on Azure VMs.
A domain user account that has permissions to create objects on both Azure VMs and in Active Directory.
A domain user account to run the SQL Server service and login capability into the VM to mount file shares.
A DNS configured on the Azure network, pointing to the domain controller.

Make sure all SQL Server nodes can reach each other before continuing:

For SQL Server Always On Availability Groups (AOAG) within a region in a Hub-spoke topology, see Hub-spoke network topology in Azure - Azure Reference Architectures.
For SQL Server AOAG between regions, see VNet-to-VNet: Connecting Virtual Networks in Azure across Different Regions.

Prerequisites

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:

SQL Server over SMB requirements

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:

The installer has been granted the SeSecurityPrivilege setting through Azure Portal.
The installer has read/write permission to the share.
The installer is a member of the local administrator to the window host.

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.

Creating Azure NetApp Files volumes for SQL Server

SMB and continuous availability

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.

Enable continuous availability on Azure NetApp Files

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.

Create Azure NetApp Files volumes for use with SQL Server

To create Azure NetApp Files volumes, complete the following steps:

In the Azure portal, select Azure NetApp Files in the Azure services or enter Azure NetApp Files
in the search box.
Select or create an Azure NetApp account, if not done already.
Select or create a capacity pool, if not done already.
Understand the services level aspects of capacity pools.
To create an SMB volume, select Volumes and Add Volume.
Provide a volume name, capacity pool, quota, and subnet.
Click Next: Protocol at the bottom of the screen.
Select SMB → Active Directory.
You must create this Active Directory connection beforehand. Make sure to select the Enable Continuous Availability option.
Select Review + Create, and then select Create.

Assigning permission and creating a database over SMB on Azure NetApp Files

To properly secure and assign permissions to Azure NetApp Files, complete the following steps:

Under Storage Service → Mount Instructions find the SMB path that has just been created.
On the Azure VM, open Windows Explorer and right-click This PC. Select Map Network Drive.
Paste the copied SMB path in the Folder field, clear the Reconnect at Sign-in checkbox, and then select Finish.
Right click on the mapped network drive and select Properties.
Select the Security tab and click Edit. Remove Everyone and add your SQL Server service account with Full Control permissions.
Create the database with data and log files residing on the Azure NetApp Files SMB shares by using SQL Server Manage Studio.

Deploy Single and Always On Failover Cluster (AOFC) Instances

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.

Create a Windows failover cluster

Add the Failover Clustering feature by using Server Manager.

You can also use PowerShell with administrator rights with the following command:
```
Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools -Restart
```
Validate the configuration and create a Windows failover cluster by adding participating servers.
If you are planning to use SQL Server 2016, do not use the UI to create a Windows failover cluster because this causes dictionary key errors. For more information about this issue, see Error, (The given key was not present in the dictionary), and SQL Server FCI installation fails on an Azure VM in Server 2019. Use the following PowerShell command script instead to create and validate a Windows failover cluster and skip the UI step:
```
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 
```
In the Testing Options window, select Run Only Tests I Select.
In the next screen, clear the Storage checkbox because shared storage is used rather than local storage.
Complete the validation configuration.
Select Create Cluster and provide a cluster name.
Clear the Add All Eligible Storage to the Cluster checkbox.
Click Finish on the Summary window.

Install a new SQL Server

The following steps describe how to install a new SQL Server for either a single instance or failover cluster.

From the SQL Server Installation Center, select Installation → New SQL Server Failover Cluster Installation
Provide the product key and accept the license terms.
Select Database Engine Services under Instance Features.
Provide the SQL Server Network Name.
During database engine configuration, select the Data Directories tab and provide the data root directory with Azure NetApp Files SMB volume paths.
Click Next and accept the information dialog box.
Finish the installation and click Close.

Install an additional SQL Server failover cluster node

To install SQL Server on the second node, from the SQL Server Installation Center, select Installation → Add Node to a SQL Server Failover Cluster.
Provide the product key and accept the license terms.
Provide a password for the SQL Server service account for both the Database Engine and the SQL Server Agent.
To finish the installation, click Install.
Enable the Virtual IP for SQL Server failover cluster instance by starting the SQL Server Configuration Manager.
Select SQL Server Network Configuration and then select Protocols for MSSQLSERVER. Right-click TCP/IP and select Properties.
Select IP Addresses and enable IP3 for virtual IP address.
Make sure that the port is set to 1433 or the default port of SQL Server during installation.
Restart SQL Server by selecting Role in Failover Cluster Manager. Click Stop Role and then click Start Role.
Create the Azure load balancer by following the steps in Configure Azure Load Balancer for an FCI VNN.

Deploy Always On Availability Groups (AOAG)

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:

Create two VNets and peer those to the VNet on which the Active Directory domain server resides.
This enables all SQL Server nodes to communicate with the domain server or servers. Make sure to delegate a subnet to the Azure NetApp Files service in each.
Create two capacity pools to allow for duplicate volume names in the same NetApp account.
For each SQL server node, create data and log volumes with the same volume names in their corresponding capacity pool and VNet.
Modify the SQL server nodes’ hosts files to point to the corresponding SMB virtual server name in order to create virtually similar UNC paths on all SQL servers by following these steps:
1. Change the attribute of the hosts file in C:\Windows\System32\drivers\etc by issuing the attrib command.
```
attrib -r C:\Windows\System32\drivers\etc\hosts
```
2. For each AOAG node, take note of the virtual SMB server name in the UNC path of the Azure NetApp Files volume on which the database files will reside. For information about how to find the SMB UNC paths for the volumes, see Mount or unmount a volume for Windows or Linux virtual machines. The UNC path contains two or more path components; <servername> is referred to as the "first pathname component", <share> as the "second pathname component". The virtual SMB server name is the string between the \\ and \ UNC path designators. For example, in the \\contoso-de7a\sql-data UNC path, the virtual server name is contoso-de7a.
3. Use the command line ping or PowerShell Test-Connection command to determine the virtual server’s IP address, using the “first pathname” component.
4. Modify the C:\Windows\System32\drivers\etc\hosts file by adding the corresponding IPv4 address from previous step to direct to create a virtually identical server name.
```
# 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.

Common errors

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:

Verify that the SQL Server service account has full access to the Azure NetApp Files volumes.
Verify that SQL Server service has full access to the data and log files.
Use this startup option to turn on trace flag 1802 in SQL Server Configuration Manager (see Trace Flags for information on 1802). For more information about how to change the startup parameters, see Database Engine Service Startup Options.

Use the following T-SQL command to reattach the database instead of the SQL Server Management Studio UI.

exec sp_attach_db DatabaseName, '\\Network-attached storage_Path\DatabaseMDFFile.mdf', '\\Network-attached storage_Path\DatabaseLDFFile.ldf'
go

Where to find additional information

To learn more about the information that is described in this document, review the following documents and/or websites:

TR-4833: NetApp SQL Server Database Quiesce Tool
Azure NetApp Files Product Documentation
- Azure NetApp Files documentation | Microsoft Docs
- Solution architectures using Azure NetApp Files | SQL server | Microsoft Docs

Posted at https://sl.advdat.com/3zzAurC

Thursday, January 6, 2022

Deploying SQL Server on Azure using Azure NetApp Files