Microsoft SQL Standard Clustering Across AWS Availability Zones with Zadara Storage as a Service

By Tom Tasker, EMEA Storage Partner Solutions Architect at AWS

Microsoft SQL (MSSQL) is an enterprise-level database application used by some of the world’s largest companies. With business-critical data housed in MSSQL databases, high availability (HA) and disaster recovery (DR) are essential features of any MSSQL deployment that businesses have to architect for.

With Amazon Web Services (AWS), server resources for large MSSQL deployments can be taken out of the data center and run on Amazon Elastic Compute Cloud (Amazon EC2) instances. The ability to boot these instances in minutes provides ease and flexibility, and the inexpensive nature of cloud computing is an ideal fit for an enterprise’s MSSQL deployments.

To enable HA and DR across multiple AWS Availability Zones (AZs) for MSSQL installations, it’s been necessary to use the Microsoft SQL Server Enterprise Edition license that enables the use of Always On Availability Groups.

This has a cost implication of a 3X increase in license (list) price compared to Microsoft SQL Server Standard Edition, as well as a 2X increase in the amount of Amazon Elastic Block Store (Amazon EBS) disk that’s needed.

With the changes in Microsoft Licensing for applications such as SQL Server in a public cloud, there are now two options for licensing MSSQL in a cloud environment:

Use Amazon EC2 instances with the AWS “License Included” pay-as-you-use model.
Use Software Assurance License mobility for the SQL Server licenses as part of a Microsoft Enterprise Agreement, or other license agreement that supports Software Assurance.

With Zadara offering Storage-as-a-Service (STaaS) across AZs, the platform’s centralized storage services release the ability to connect multiple MSSQL servers in a standard Windows Server Failover Cluster (WSFC) model to a single set of shared storage volumes. This removes the need for MSSQL Enterprise Edition licensing and the doubling up of Amazon EBS disk for Amazon EC2 instances.

The Zadara STaaS subscription centralizes MSSQL server disk and data for AWS, VMware Cloud on AWS, and data center deployments. This enables data mobility and flexibility for different use cases, such as migration from data center compute to Amazon EC2. It also allows for test and development environments to be spun up in minutes using a copy of production data.

In this post, I will explore the use of high availability Microsoft SQL Standard Clustering on AWS with Zadara STaaS.

Zadara Enterprise Storage-as-a-Service Architecture

Zadara is an AWS Partner Network (APN) Advanced Technology Partner with the AWS Storage Competency. An OpEx-based data storage company, Zadara enables storage to be consumed as a service on AWS.

The Zadara platform has a mix of block, file, and object storage services, with data protection (RAID), deduplication and compression, snapshots, mirroring, and antivirus capabilities built in to the service.

Zadara Storage Cloud is built in data centers adjacent to AWS and connected via AWS Direct Connect. Using an Amazon Virtual Private Cloud (VPC), the data path from Amazon EC2 to Zadara is secure and fast over a 10Gbps direct connect network link.

The Zadara Virtual Private Storage Array (VPSA) provides dedicated HA controllers, disk drives, CPU, and RAM so there’s no resource contention with other tenants or storage arrays.

A combination of All-Flash VPSA enables deduplication and compression that’s selectable per volume utilizing Intel Optane and NVMe. This also enables a hybrid VPSA of HDD and SSD drives to provide a flexible configuration.

Figure 1 – Zadara and multi-Availability Zone MSSQL architecture.

With Zadara, data can be automatically backed up to Amazon Simple Storage Service (Amazon S3), which provides a second copy of the data off-site and realizes the cost benefits of using S3 object storage rather than production SAN disk.

Data stored on S3 can be instantly accessed by Zadara services to avoid lengthy restoration times using instant clone mount capabilities.

Zadara also takes advantage of VMware Software Defined Data Center (SDDC) clusters and enables data access from VMware Cloud on AWS over any storage protocol, while concurrently serving Amazon VPC environments.

To get started, you can purchase Zadara through AWS Marketplace, choosing from various flexible offerings.

Deployment and Configuration of the MSSQL Solution

Once the supporting services in steps 1-3 are configured, a strict order must be followed to maintain technical coherence.

For the deployment detailed in this post, the main steps are:

Subscribe and log in to the Zadara service through AWS Marketplace.
Deploy two MSSQL Standard Edition Servers on AWS.
Install and configure the Microsoft Active Directory and DNS services within the VPC.

Steps 4-10 must be completed in the following order:

Add the MSSQL servers to the Microsoft Domain.
Enable iSCSI services on each MSSQL server.
Install the Windows Failover Cluster Role onto each MSSQL server.
Connect and configure the MSSQL servers to the Zadara VPSA.
Create and configure the cluster disk resources.
Run the Cluster Installation Wizard.
Install MSSQL Server as a Cluster Role.

Registration is done through the Zadara portal. Upon completion, you’ll need to connect your AWS account and corresponding Amazon VPC to the VPSA. Learn more about setting up the Virtual Private Gateway and VPSA connection.

Deploy MSSQL Standard Edition Servers in AWS

Microsoft SQL Amazon Machine Images (AMIs) can be deployed from the Amazon EC2 console to speed up deployment.

If using Software Assurance licenses, Windows Server-only AMIs are needed, and there’s the additional step of installing MSSQL components onto each server and supplying your MSSQL licenses.

Figure 2 – AWS SQL Server Standard AMI deployment.

In Figure 2 above, you can search for and narrow down the AMI list for a chosen compute instance.

Each MSSQL server needs to be deployed into a different AZ to increase site failure resilience. Figure 3 below shows the MSSQL servers distributed across the AZs, and the Zadara Management Server can be placed in any AZ.

Figure 3 – Amazon EC2 multi-Availability Zone deployment.

Installing and Configuring Microsoft Directory Services

Microsoft Directory Services are mandatory for the Windows Server Failover Clustering Service (WSFC) to function correctly in this scenario. Although WSFC supports non-Directory Services installation, it introduces unnecessary complexities that limit MSSQL login and security options.

I used AWS Directory Services in this deployment, but a self-managed Amazon EC2 installation would also work. Figure 4 is a snapshot of the AWS Directory Services service used during this setup.

Figure 4 – AWS Directory Services.

Next, you’ll need to add the MSSQL Servers to the Microsoft Domain using standard join rules for Windows servers.

Enable iSCSI Services on Each MSSQL Server

Before enabling the iSCSI service on the MSSQL servers, the following configuration elements for additional Amazon EC2 network interfaces needs to be completed.

Separating iSCSI storage traffic and application traffic is a best practice for traffic congestion mitigation and monitoring. This is achieved by having an additional Elastic Network Interface (ENI) on the Amazon EC2 instances.

Figure 5 – ENI configuration.

The MSSQL Amazon EC2 instance will have an eth0 and eth1 network interface. In this deployment, eth0 will be used for the iSCSI traffic towards the Zadara VPSA, and eth1 for Windows Failover Cluster and MSSQL traffic.

Figure 6 – Network interface and DNS setup.

Allocate additional IPs to the ENI nominated for cluster resources and SQL traffic, ensuring this interface has the Register in DNS checkbox set, as shown in Figure 6 above.

Note the Register in DNS option should be unchecked for the iSCSI NIC. Having the iSCSI IP registered in DNS can cause cluster configuration issues.

VPC Security Groups should be configured to allow traffic to move between both AZs; it’s recommended to follow AWS security best practices for this configuration.

VPSA Management and iSCSI uses ports 443 and 3260, respectively. If NAS services are also required, Microsoft provides guidelines on the ports required.

Next, ensure both nodes can access the VPSA management IP:

<<ping –s “iSCSI IP NIC IP” “VPSA Management IP”>>

Now, ensure both Cluster nodes can ping each other:

<<ping –s “Cluster Node1 Client Network IP” “Cluster Node2 Client IP Network”>>

By default, the iSCSI service are not enabled. Learn more about how to set this up and enable MPIO to a VPSA, if desired. However, no configuration of iSCSI targets is necessary and should not be done at this point.

Install Windows Failover Cluster onto Each MSSQL Server

It’s always recommended to plan for the Windows Failover Custer Service installation using the Microsoft documentation. Through the MSSQL Server Manager, add the Failover Clustering feature to both MSSQL servers.

Make sure you are logged in as a domain user with the required permissions so that all Active Directory objects and permissions are created for that user or account.

Connect and Configure MSSQL Servers to Zadara VPSA

Log in to the VPSA management console using a browser from each of the MSSQL Servers. For more guidance, see the VPSA User Guides.

Note that VPSA Management is only available from inside the VPC’s Amazon EC2 instances, or via a virtual private network (VPN) connection to your VPC and not via the public Internet.

In Figure 7 below, I provide an example of the iSCSI target IP address needed for the MSSQL servers to setup their iSCSI initiators to the Zadara VPSA. This is required if using the Zadara Windows VSS Hardware Provider to create application consistent snapshots.

The Microsoft iSCSI Initiator will register itself using its Fully Qualified Domain Name (FQDN). Joining or leaving an Active Directory domain changes this in the iSCSI setup. Make sure the host is joined to the domain before registering with the VPSA to avoid errors.

Figure 7 – VPSA controllers IP address properties panel.

On the Zadara VPSA Management Console, download the Zadara management scripts to register the MSSQL servers on the VPSA automatically when run.

Figure 8 – VPSA server automatic registration script access.

Download and run the iSCSI configuration script on each MSSQL server, and then select the IP corresponding to the iSCSI NIC/ENI when prompted. The iSCSI connection between the MSSQL server and the Zadara VPSA is now established.

Figure 9 – VPSA server registration and status pane.

On each MSSQL Server, the iSCSI initiator should be automatically set up by the script.

Figure 10 – MSSQL server iSCSI initiator properties.

Create and Configure the Cluster Disk Resources

On the Zadara VPSA management console, create the SQL disk resources for the MSSQL cluster. In this example, the following LUN IDs, disk use, size, and Microsoft drive letters were assigned.

Ensure you select and assign the LUN IDs manually with identical mappings to each MSSQL node.

Figure 11 – Example SQL Cluster volume plan.

In Figure 12 below, you see the corresponding disk mappings on the Zadara VSPA. It’s essential all LUN ID’s and mappings are consistent to each MSSQL server.

Figure 12 – VPSA disk / LUN mappings.

For the first MSSQL server, open the Microsoft Disk Management utility. Displayed should be the four iSCSI disks presented from the Zadara VPSA. If the disks are not displayed, refresh the view.

For each uninitialized disk run through the procedure below:

Online the disks.
Initialize the disks.
Create a filesystem.
Mount the disks to the corresponding drive letter.

Once the above procedure has been done for each disk, offline each disk and repeat steps 1 through 4 on the second MSSQL server. Carefully, ensure the volumes are mounted with the same drive letters to each volume as the first MSSQL server.

Figure 13 below shows the completed disk initialization procedure on the MSSQL server.

Figure 13 – Microsoft Disk Management utility.

Run the Cluster Installation Wizard

Now, run the cluster verification wizard on both MSSQL servers, noting any warnings or errors and fixing them, if appropriate. Depending on the checks selected, some errors may be due to services or functions that are not needed and can be safely ignored.

Next, confirm the cluster IP addresses in the Amazon EC2 console match IP addresses assigned to the cluster network NIC in each of the MSSQL Server network properties. If you don’t do this, there cannot be cluster communications; the IPs must be allowed for the NIC/ENI in the AWS console and set as additional IPs.

To ensure that timely failover, updates of DNS records, and objects in AD are initiated, bring the cluster online, open PowerShell as an Administrator, and then run:

PS C:> Get-ClusterResource

Verify “Cluster Name“ and “Network Name” resources exist.

Get-ClusterResource “Cluster Name” | Set-ClusterParameter -Name HostRecordTTL -Value 60

Repeat this step for each network name resource you add roles for—SQL Server, Failover Clusters, etc. Use the Cluster Admin console to failover resources for the updates to occur.

Failure to set these parameters can lead to delays of five minutes before the cluster resource name is updated, which can lead to application unavailability.

Install SQL Server as a Cluster Role

On the VPSA management server Servers Section, create a new Server Record for each of the AZ subnets; in this example, 10.0.1.0/24 and 10.0.2.0/24. The Zadara VPSAs work on a deny-by-default basis and only allow all hosts to mount a volume if it’s mapped within the VPSA.

Create a NAS share and allow Guest Access and all hosts in the AZ subnets to mount via SMB (Windows CIFS share). This will be the share used to install the MSSQL Server software and cluster components.

The VPSA can also be joined to the Microsoft Active Directory Service to further control access to NAS resources, which is useful if the file share is holding a software repository or administration scripts.

Next, map a Network drive to the share on each MSSQL server.

Microsoft Active Directory Preparation

During the MSSQL cluster role installation, the account used must have the ability to update Active Directory objects for cluster resources.

Before installation, make sure a user within Active Directory (e.g. SQLAdmin) has been created. If using AWS Directory Services, AWS Delegated Groups are created, ensuring the cluster computer object has delegated access to create and update computer objects in the Organizational Unit (OU) where the computer or cluster resources are created.

You can pre-stage the objects or configure cluster accounts, but this should be determined by organizational security model best practices.

Next, test failover of cluster resources, and check the cluster name and IP address for the cluster resources moves between the two subnets of ENIs in each AZ.

Download and copy the SQL Server Installation media to the Zadara NAS share as detailed above.

SQL Server Failover Cluster Installation

Run the SQL Server Setup installation media from the Zadara file share, and then select installation and New SQL Server Failover Cluster Installation.

Next, select the disks appropriate to their allocation for their services: index, database, etc.

After successful installation on the first MSSQL server, run setup again from the Zadara file share on the second MSSQL server, selecting Add SQL Node Setup.

Upon completion of the SQL Server Failover Cluster installation, test failover via the Cluster Manager.

Log in to Windows using an account with administrator access to SQL Server. There may be login issues due to certificates and TLS setup. Learn more about ensuring the Windows registry is set up for TLS 1.2 support.

Download the Microsoft AdventureWorks test database, import, and then verify failover and database access.

Snapshot and Backups to Amazon S3

Zadara provides a highly available infrastructure and the ability to provide direct backups of the MSSQL databases. By utilizing the Zadara VSS Hardware Provider, Zadara snapshots can be used to create application-consistent database snapshots and clones.

This aids in recovery, and also safeguards that the data is consistent. However, the data is still within the same data disks as the original, mirroring to another VPSA provides an option in having the data in two locations.

A lower cost option is to replicate the data and snapshots to Amazon S3 object storage. This can be further cost optimized, while avoiding internet costs and utilizing the existing VPSA Direct Connect Interfaces, by using a HTTPS proxy server to enable replication traffic to traverse to Amazon S3 from the Zadara VPSA via the proxy.

Read more on enabling backup to Amazon S3 via HTTP proxy.

Summary

Combining Microsoft SQL Server with the agility and scale of the AWS Cloud is a natural progression from onsite hardware deployments, removing the inherent time and space constraints of a traditional data center installation.

Adding Zadara as the data platform to enable this change, as I’ve shown how to do in this post, allows for the production data to be centralized and disturbed as little as possible, if at all.

Once configured and in production, the combination of AWS services with Zadara Storage-as-a-Service (STaaS) offering the unified data plane, all hardware and data center planning is gone.

Enterprise applications can be built and ready for configuration in minutes with zero time wasted on resource planning and contention handling.

Zadara with AWS provides a more complete level of data high availability than most organizations can achieve within their own data centers. Scale compute and storage up and down on demand while being able to choose the capacity and performance to meet those demands.

Zadara provides the same storage capabilities to on-premises data centers, VMware Cloud on AWS, and Amazon Web Services, simplifying replication and migration and the testing of application data.

.

.

Zadara – APN Partner Spotlight

Zadara is an AWS Storage Competency Partner. It provides enterprise data storage as a fully-managed service with consumption-based pricing. Zadara offers resource isolation, exceptional security, and management control.

Contact Zadara | Solution Overview | Customer Success | AWS Marketplace

*Already worked with Zadara? Rate this Partner

*To review an APN Partner, you must be an AWS customer that has worked with them directly on a project.