​SQL 2017 Always-on AG Pt. 7: File Share Witness

​Now that we have the Windows failover cluster service installed and configured with a management point, we need to configure a witness. A witness is a 'third party' that enables monitoring of the cluster node status and assist with failing over SQL services. The witness can live either in the cloud (Azure) or use a generic file share that could reside on a NAS appliance or a Windows server. The file share witness must NOT reside on either SQL node, as that would defeat the purpose of having a witness. In my lab I deployed a bare bones Windows Server VM to host the FSW. 

​Create a File Share

​1. On a Windows member server (not either SQL server) open Server Manager, go to File and Storage Services, click on Shares, then from the Tasks menu select New Share. If you don’t have that option, add the File Server Role and wait for the installation to complete. No reboot is needed.

2. Select the SMB Share – Quick file share profile.

3. Select the appropriate volume the share will be created on.

4. Enter the new share name. ​I suggest this format: <Cluster name>-FSW (e.g. SQL2017CLA-FSW). ​Make note of the remote share path, as we will need this in a couple of minutes.

5. Enter a description in the format of: <Cluster Name> Cluster File Share Witness.

6. Uncheck allow caching of share and enable encrypt data access.

7. Customize the permission, disable inheritance and remove all inherited permissions.

8. Give the cluster computer object (e.g. SQL2017CLA) full control. If you want, you could also give administrators access so they can peek inside. Make sure to enable the search for 'computer' objects when you 'Select a principal' or it won't find your computer account.

​9. Finish the wizard and wait for the share to be created. If you get an access denied message, re-run the wizard with the same settings and see if a second attempt will work.​

​Fileshare Witness Cluster Configuration

1. On either SQL server launch the Windows Failover Cluster Manager.

2. Right click on the root cluster object (e.g. SQL2017CLA), select More Actions and then click Configure Cluster Quorum Settings.

3. Select Select the quorum witness.

4. Select Configure a file share witness.

5. Enter the file share path you made note of from above. Click through the remainder of the wizard and verify the FSW was successfully configured.

6. Verify Quorum configuration is now using a file share witness. Note that you only need to do these steps once per cluster.

​Summary

In this post we configured the Windows Cluster service with a file share witness. The FSW is needed to properly manage node failover. The FSW can be co-located with other services on another server, be a share from a NAS appliance, or use the cloud (Azure). It cannot be created on either SQL node.

Now that the Windows cluster services are fully configured, we will return to configuring SQL. The next installment will configure the pre-reqs for setting up an AAG, and then configure one AAG. You can find Part 8 here (coming).

SQL 2017 Installation Series Index

SQL 2017 Always-on AG Pt. 1: Introduction

SQL 2017 Always-on AG Pt. 2: VM deployment

​SQL 2017 Always-on AG Pt. 3: Service Accounts

SQL 2017 Always-on AG Pt. 4: Node A SQL Install

SQL 2017 Always-on AG Pt. 5: Node B SQL Install

SQL 2017 Always-on AG Pt. 6: Cluster Configuration

SQL 2017 Always-on AG Pt. 7: File Share Witness

​​​​​SQL 2017 Always-on AG Pt. 8: ​AAG Setup (Coming)

​​​​SQL 2017 Always-on AG Pt. 9: Kerberos (Coming)

​SQL 2017 Always-on AG Pt. 10: SSL Certificates (​Coming)

​SQL 2017 Always-on AG Pt. 11: Max Mem & Email Alerts (Coming)

​SQL 2017 Always-on AG Pt. 12: Maintenance Jobs (Coming)

SQL 2017 Always-On AG Pt. 6: Cluster Configuration

Now that SQL 2017 is installed on both nodes, we need to configure the Windows Cluster service. Although SQL AAGs don't use the 'traditional' clustering technology of shared SCSI disks and a quorum disk, the clustering service is required to manage the state and failover the AAG instances.

For this procedure we will need one new IP address and a cluster DNS name. Get those ready before proceeding. The cluster name is only used for management purposes, and is NOT related to the SQL listener name. If DHCP is active on the server subnet the wizard will automatically pull an IP.

Microsoft has a technology called "Cluster Aware Updating" (CAU) which orchestrates the installing of Windows patches on hosts that are a part of a cluster. However, I think its utility is mostly aimed at Hyper-V hosts/clusters and less so at enterprise applications such as SQL. So I won't cover configuring CAU in this series.

​Cluster Role Installation

1. On the first SQL server launch Server Manager and select Add Roles and Features.

2. Select Role-Based or Feature-Based Installation.

3. Select the local server when prompted.

4. Skip the Server Roles page and on the Features page check Failover Clustering. When prompted, add the required features.

6. Continue through the rest of the wizard and wait for the installation process to complete.

7. Install Windows Failover Clustering on the second SQL node.

​Cluster Validation

1. On Node A launch the Failover Cluster Manager, and in the left pane right click on Failover Cluster Manager and select Validate Configuration.

2. Enter the two hostnames of the SQL servers you are configuring.  Run all of the tests.

3. Review the cluster report for any errors. A warning regarding non-redundant NICs is normal, and can be ignored. If there are any other warnings/errors (such as a pending reboot due to patching), take the required action to remediate.

4. On the Validation summary screen check the box next to "Create a cluster now using the validated hosts..." and click Finish.

​Cluster Configuration

1. ​Enter the Cluster name (e.g. SQL2017CLA). 

​2. In my case DHCP was active on the server subnet, so the wizard automatically pulled an IP from the pool and assigned it to the cluster hostname. If DHCP is unavailable the wizard will prompt you for an IP. 

3. Validate that all of the cluster information is correct and UN-check the box next to the "Add all eligible storage to the cluster" option and click Next. Wait for the cluster to be built.

Note: If you forget to UN-check​ the storage box below, the cluster service may claim all of your SQL disks and they could disappear from Explorer. If they have disappeared, then go into the cluster manager and remove all drives as a cluster resource. Then open the Computer Manager and online each of the drives. Reboot the SQL server if you had to do this procedure.

​4. Review the summary screen to make sure all is well. You can safely ignore any warnings regarding a disk witness. We will configure a File Share Witness (FSW) in the next post.

5. Do a forward and reverse DNS lookup of the new cluster name to verify A and PTR records were created. Correct any issues.

​Summary

In this installment we configured the Windows failover cluster service, and created a new cluster. This cluster name is used only for management purposes, and not used for the SQL listener. Next up in Part 7 we will configure the File Share Witness (FSW).

SQL 2017 Installation Series Index

SQL 2017 Always-on AG Pt. 1: Introduction

SQL 2017 Always-on AG Pt. 2: VM deployment

​SQL 2017 Always-on AG Pt. 3: Service Accounts

SQL 2017 Always-on AG Pt. 4: Node A SQL Install

SQL 2017 Always-on AG Pt. 5: Node B SQL Install

SQL 2017 Always-on AG Pt. 6: Cluster Configuration

SQL 2017 Always-on AG Pt. 7: File Share Witness

​​​​​SQL 2017 Always-on AG Pt. 8: ​AAG Setup (Coming)

​​​​SQL 2017 Always-on AG Pt. 9: Kerberos (Coming)

​SQL 2017 Always-on AG Pt. 10: SSL Certificates (​Coming)

​SQL 2017 Always-on AG Pt. 11: Max Mem & Email Alerts (Coming)

​SQL 2017 Always-on AG Pt. 12: Maintenance Jobs (Coming)


SQL 2017 Always-On AG Pt. 5: Node B SQL Installation

​This is the fifth installment of the SQL 2017 AAG series, where we do an unattended install of SQL on node B. We will use a modified configuration INI file that was generated from the node A installation. This reduces human error and makes your installs repeatable. With the use of gMSAs, it makes the unattended a bit easier since we don't have to supply passwords for the service accounts. If you are using "normal" AD service accounts two command line switches are needed to supply the proper credentials. 

​Unattended SQL 2017 Installation

​1. Create a folder on the second SQL server and copy the ConfigurationFile.INI​ from node A to the ​this folder on node B. I used C:\SQL. 

2. Open your favorite editor and change the specified parameters to the new values in the images below​​​. The QUIET and UIMODE parameters should be commented out, as shown.

​3. Mount the SQL 2017 Enterprise Edition ISO to the second SQL server. Open an elevated command prompt and change directory to the root of the SQL ISO. Enter the following command:

setup /configurationfile=c:\SQL\ConfigurationFile.ini /iacceptsqlserverlicenseterms

​Note: If you are not using gMSAs, you will need two switches to supply the credentials for the service accounts:

​​​/SQLSVCPASSWORD="YourPassword"
/AGTSVCPASSWORD="YourPassword"

​4. Wait a few minutes for the installation to complete. After the install has completed, you can list the services running on the server and validate that SQL is using the proper gMSA service accounts.

​5. We aren't quite done yet, as you have to now install the SQL management tools just as we did on node A. Launch the SQL installer from the ISO, click on the SQL management tools link and download/install as you did before.

6. After the SQL tools have been installed, launch the SQL Server Management Studio and ensure you can login to the SQL server. If you have login issues, reboot the VM and try again.

​Summary

​Using the SQL 2017 unattended installation is very straight forward. Only a few parameters needed to be changed from the INI that the installer creates. Next up in Part 6 we will configure Windows clustering.

SQL 2017 Installation Series Index

SQL 2017 Always-on AG Pt. 1: Introduction

SQL 2017 Always-on AG Pt. 2: VM deployment

​SQL 2017 Always-on AG Pt. 3: Service Accounts

SQL 2017 Always-on AG Pt. 4: Node A SQL Install

SQL 2017 Always-on AG Pt. 5: Node B SQL Install

SQL 2017 Always-on AG Pt. 6: Cluster Configuration

SQL 2017 Always-on AG Pt. 7: File Share Witness

​​​​​SQL 2017 Always-on AG Pt. 8: ​AAG Setup (Coming)

​​​​SQL 2017 Always-on AG Pt. 9: Kerberos (Coming)

​SQL 2017 Always-on AG Pt. 10: SSL Certificates (​Coming)

​SQL 2017 Always-on AG Pt. 11: Max Mem & Email Alerts (Coming)

​SQL 2017 Always-on AG Pt. 12: Maintenance Jobs (Coming)

SQL 2017 Always-On AG Pt. 4: Node A SQL Installation

​This is Part 4 of the SQL 2017 Always-On Availability Group installation series. In this post we will manually install SQL 2017 on the first node of the cluster. We will then capture an answer file for SQL, which will be used ​for an unattended SQL installation on Node B (Part 5).

​SQL 2017 Node A Installation

​1. Mount the SQL 2017 Enterprise Edition ISO to the first VM and launch the installer.

2. On the left side click on Installation, then click on the top option "New SQL server stand-alone.."

3. On the Product Key screen accept/enter your key.

4. On the License Terms screen check the box to accept the terms.

5. On the ​Microsoft Update​ screen configure as you see fit.

6. On the ​Install Rules​ screen verify everything is green, with the possible exception of the Windows firewall which may be yellow (normal).

7. ​​​​​​On the ​Feature Selection​ screen select at least the Database Engine Services​​​​ ​option. If you need additional features, select them. At the bottom of the window change the SQL binary installation paths as needed.​​​

​8. On the ​Instance Configuration​ screen configure your instance as needed. For this series I'm leaving the default settings.

9. On the ​Server Configuration​ set the agent and database engine accounts to their respective gMSA. ​​​​​​No password is needed since that is handled by Windows. There's no need to check the box for granting Volume Maintenance privileges as we manually did that in a previous post by configuring user rights.

​10. On the ​Database Engine Configuration​ screen select the authentication mode and add the AD SQL Admin group you are using to the list of administrators.​​​ Windows authentication mode is the most secure, so only select mixed mode if you know an application requires it.

​11. Configure the SQL database engine directories as desired. I'm using the drives covered in previous posts.

​12. TempDB configuration will vary wildly between instances. Some applications make very heavy use of TempDB, while others barely use it. SQL 2017 also bases the initial number of TempDB files on the quanity of CPUs assigned to the VM. The key here is to make sure you have an adequate number of TempDB files, and if you are using Nutanix storage, put them on separate disks. Make sure you set all the proper paths including the Log directory. 

​13. On the ​Ready to Install ​screen copy the configuration file path to the clipboard, as we will need it later.​​​ ​Once the installation starts, browse to the INI file and copy it to a safe location so we can use it in the next post.

​14. After the installation completes for the database engine, we now need to install the SQL Server Management Tools. Launch the SQL installer again but this time select ​Install SQL Server Management Tools​.​​​ This will actually redirect you to a live web page where you have to download the latest 800MB+ tool package. Install the package. 

15. From the Start menu launch the SQL Server Management Studio and validate that you can successfully login. If your login does not work reboot the SQL VM and try again.

​Summary

​In this post we installed the first instance of SQL 2017 using the manual method. We were careful to select the proper paths for all the files, and copied the SQL configuration INI file to a safe place. In Part 5, we will use this INI for an unattended installation on the second node. This reduces human error and ensures both nodes are configured the same. Unlike SQL 2014 and earlier, the management tools are a separate download and install process. 

SQL 2017 Installation Series Index

SQL 2017 Always-on AG Pt. 1: Introduction

SQL 2017 Always-on AG Pt. 2: VM deployment

SQL 2017 Always-on AG Pt. 3: Service Accounts

SQL 2017 Always-on AG Pt. 4: Node A SQL Install

SQL 2017 Always-on AG Pt. 5: Node B SQL install

SQL 2017 Always-on AG Pt. 6: Cluster configuration

​SQL 2017 Always-on AG Pt. 7: Fileshare Witness (coming)

​​​​​SQL 2017 Always-on AG Pt. 8: Max memory and Email (coming)

​​​​SQL 2017 Always-on AG Pt. 9: SQL Maintenance (coming)

​SQL 2017 Always-on AG Pt. 10: AAG Setup (coming)

​SQL 2017 Always-on AG Pt. 11: Kerberos (coming)

SQL 2017 Always-On AG Pt. 3: Service Accounts

This is Part 3 of the SQL 2017 Always-On Availability group series where we setup two service accounts and a security group. One account is for the database engine and the other is for the SQL agent. In order for Kerberos to work properly the database engine account must be Active Directory based. We will also be observing the rule of least privilege. The less privileges the accounts have the more secure you are. So these accounts won’t be given local administrator rights. You don’t want your SQL service running with admin rights!

The service account that most people are familiar with are a standard Active Directory user, with a complex password, that is used for a specific application such as SQL. Active Directory service accounts are required if you want to use Kerberos authentication. However, these standard service accounts have some disadvantages. ​The primary issue is the lack of automatic password management. An administrator manually sets the password, and should periodically change it for security reasons. Restarting the SQL service is needed after the password is changed. 

A few years ago Microsoft introduced "Group Managed Service Accounts". The gMSA accounts are a unique object type in AD that has automated password management and the account can be used on more than one server. You can read a bit more about gMSA accounts here. Particularly noteworthy is that the password is automatically changed every 30 days, the accounts cannot be used for interactive logins, and the account can't be locked out. 

There's a little extra configuration up front for a gMSA, but then you no longer have to worry about password changes for that application. This SQL 2017 installation series will use gMSAs so you can see how to configure them. 'Standard' AD based service accounts will work as well, but will have manual password management.

​Domain Controller Configuration

​In order to use a gMSA you need at least one domain controller that is running Windows Server 2012. If you have never used a gMSA then it's likely you will need run a command to enable gMSA accounts within the domain. This only needs to be run once, so skip if your domain has already been configured. Open an elevated PowerShell on a domain controller and run this command:

Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10))

It may take up to 10 hours for the change to take effect. So if you have issues creating a gMSA, wait and try again.

​gMSA Account Creation

​There are two primary ways to create a gMSA account. The first and most cumbersome is using various PowerShell commands. It's very tedious and error prone. Or, the quick and easy method via a free GUI tool. ​You can download the free tool here​. Install the tool on your first SQL server using all defaults.

I recommend using two gMSAs for each SQL AAG instance. One is for the database engine and the other is for the agent service. Use a logical naming standard for the accounts, and make sure the account name is 14 or less characters.

1. Launch the free gMSA tool and and click on New. Enter the appropriate 14 character or less account name for the agent service account. Add a useful description. Select the appropriate AD container.

​2. When you are prompted to assign computers to the gMSA click Yes. Add the two SQL servers to the gMSA, validating each one. After you add them and click OK​ and the home page of the tool should now show one gMSA and the assigned computers.

​3. Repeat this procedure for the database engine gMSA account. I called this account MSA-SQL01-D. The GUI tool should now show two gMSA accounts on the home page.

SQL Server Preparation

1. On both of the SQL servers run the following elevated Powershell command to enable the usage of a gMSA.

Enable-WindowsOptionalFeature -FeatureName ActiveDirectory-Powershell -Online –All

​2. On both of the SQL servers run the following elevated Powershell command to 'install' the gMSA account on the server, using your service account names. Run for both of the gMSAs (database engine and agent service).

Install-AdServiceAccount MSA-SQL01-A
Install-AdServiceAccount MSA-SQL01-D

​3. You should now test the service accounts via Powershell to make sure they work. The command should return 'true' for both accounts.

Test-ADServiceAccount -Identity MSA-SQL01-A
Test-ADServiceAccount -Identity MSA-SQL01-D

​User Rights

​In order to support large page tables and instant database initialization we need to configure a couple of user rights in the Local Security Policy of each node. Add to the Lock Pages in Memory and Perform Volume maintenance tasks user rights the database engine service account. ​Do this on both SQL VMs, and reboot.

Do NOT add either of the SQL service accounts to the local administrators group. This is very bad for security, and not required for proper SQL operation.

​SQL Administrator Group

​I recommend configuring an Active Directory security group that will contain all of the SQL administrators. This makes adding/removing of SQL administrators much easier. If you already have an appropriate security group in AD, use it. For this exercise I will use SQL-Admins. Add the appropriate users to this group before proceeding.

​Summary

​In this post we created two group managed service accounts (gMSA) that we will be using for SQL 2017. These accounts require a bit more up front work to configure, but have the advantage of never needing to change the password. If you do not wish to use gMSA accounts, feel free to use the 'standard' AD user object for the service accounts. This guide is based on gMSA accounts, 

​Next up in Part 4, we will install SQL 2017 on the first node. We will capture this configuration, and use it as a template for an unattended installation of SQL on the second node.

​SQL 2017 Installation Series Index

​SQL 2017 Always-on AG Pt. 1: Introduction

SQL 2017 Always-on AG Pt. 2: VM deployment

​SQL 2017 Always-on AG Pt. 3: Service Accounts

SQL 2017 Always-on AG Pt. 4: SQL Node A Install

SQL 2017 Always-on AG Pt. 5: SQL Node B install

SQL 2017 Always-on AG Pt. 6: Cluster configuration

​SQL 2017 Always-on AG Pt. 7: Fileshare Witness (coming)

​​​​​SQL 2017 Always-on AG Pt. 8: Max memory and Email (coming)

​​​​SQL 2017 Always-on AG Pt. 9: SQL Maintenance (coming)

​SQL 2017 Always-on AG Pt. 10: AAG Setup (coming)

​SQL 2017 Always-on AG Pt. 11: Kerberos (coming)

SQL 2017 Always-On AG Pt. 2: VM Deployment

First up in this series is deploying your two Windows Server 2016 VMs, which will be the SQL 2017 Always-on availability group nodes. Each VM should be configured with the same VM hardware properties. Selecting the right virtual hardware configuration is important, regardless of which hypervisor you are using. Memory, compute, networking and storage can all impact SQL 2017 performance. Remember that more is NOT always better, and in fact consuming more physical resources than you need (e.g. vCPUs) can actually hurt performance. Always take the time to 'right size' your VMs.

​Memory

​Memory configuration is very dependent on the SQL workload and your applications. In order for SQL server to use large page tables, which can boost performance, the VM needs at least 8GB of RAM. In general I don't reserve VM memory at the hypervisor level, but for tier-1 SQL VMs I would reserve the memory. This is an additional step in VMware environments, and is automatic with AHV as it doesn't overcommit VM memory as of the date of this post. 

Database workloads love memory, as it is vastly faster to access data in memory than on disk. So you should not skimp on VM memory allocation, but neither should you over provision. Mentally reserve 2-4GB of VM memory for the OS, and then add on top of that how much memory you want to dedicate to SQL. Later in this series we will optimally configure SQL memory usage by adjusting SQL runtime parameters.

If your hypervisor supports hot-plug of memory, you can enable that feature without negatively impacting performance. However, remember that if you do increase the VM memory that the SQL memory runtime parameter we cover later in this series needs adjustment as well or the additional RAM will not be used by SQL. This requires a restart of the SQL instance.

Keep in mind that Microsoft SQL is NUMA (non-uniform memory access) aware, so there's not a major performance hit if you configure the VM for more memory than is in one NUMA node. But if you can keep VM memory allocation within a single NUMA node, that will maximize VM memory performance.

​vCPUs

​vCPU allocation is very dependent on the expected workload. If you allocate more vCPUs than exists on a NUMA node, performance may be negatively impacted. For example, if your server is dual socket and each socket has 8 physical cores, try and allocate 8 or fewer vCPUs to the VM.

Understanding NUMA can be a bit complicated, as different hypervisor versions and vendors handle it differently.  NUMA configuration can also potentially impact SQL licensing, as the guest OS ​will see a differing number of sockets and CPUs depending on how the hypervisor presents the NUMA topology to the VM. 

If you are using VMware vSphere, check out this blog post to get a good understanding of how they handle vNUMA in various vSphere versions. If you are using Nutanix AHV, check the latest version of the AHV Administration Guide on the support portal for current guidance and configuration steps. Tip: Search on the Nutanix portal for "vNUMA".

If you are using VMware vSphere, take note that enabling CPU hot add will disable vNUMA. So for all SQL VMs, I would NOT configure CPU-hot add. Schedule VM downtime to manually reconfigure the vCPUs and reboot the guest OS. 

2018 has seen a number of CPU security issues arise, such as Spectre, Meltdown, and L1TF. Mitigations for these security issues can negatively impact CPU performance and even reduce the total number of usable logical CPU threads in a physical server. Make sure to take these mitigations into consideration when sizing your SQL VMs.

​Networking

Networking configuration is very straight forward. For Nutanix AHV there's only one virtual NIC type, so you can't go wrong. Just add a NIC to the VM, and you are set. If you are using VMware vSphere, use the VMXNET3 NIC. A single vNIC per VM is sufficient. Be sure to use the latest VMware tools or Nutanix AHV NIC drivers.

​Storage

​Properly configuring storage for SQL is a bit more complicated than networking. Configuration recommendations will differ for both the hypervisor you are using, as well as the underlying storage platform (e.g. SAN, Nutanix, etc.). If your storage vendor has recommendations for optimal SQL configuration disk configuration, follow them. 

​If you are using Nutanix AHV, use the "SCSI" disk type for all of the VM disks. If you are using VMware vSphere, I recommend using the default LSI SAS controller for the OS boot disk and the PVSCSI controller for all other disks. ​​​You can​ use the PVSCSI for the boot disk as well, but that disk should not see very many IOPS and requires additional manual steps to install Windows on a PVSCSI disk. ​​​See my blog post here for injecting the VMware drivers into your Windows image here

As a starting point for disk configuration, the following is a good baseline for a basic SQL server using a generic storage back-end. You will very likely have a different configuration, but consider the disk layout below. Many times I will use mount points so you don't run out of drive letters, but for this example lets keep it simple. I also recommend unique drive sizes (even if just a 1GB difference) so you can easily match up hypervisor disks and in-guest disks. The drive letters below are what I'll be using through the rest of this series.

C: 40GB - OS Drive
D: 20GB - SQL Binaries
​G: SQL Data Files 1
​​H: TempDB data 1
I: TempDB data 2
​J: TempDB Logs
​K: SQL DB Logs
​L: SQL Backups

The Windows boot drive for SQL does not need to be huge. SQL binaries, databases and logs should all be stored on disks other than the C drive. So the boot disk should just be large enough for the OS, future patches/upgrades, swap space, etc. 40GB seems to be a fair number. Adjust as you see fit. All of the other disks ​are extremely environment specific and should be tailored to your requirements for both size and quantity.

​As mentioned above a production quality SQL server should have a number of guest disks ​for optimal performance. In vSphere environments all of the SQL-specific disks (e.g. data files, log files, temp DB, backups, etc.) should use the PVSCSI controller. However, for best performance you should add 3 PVSCSI controllers to the SQL VM and then evenly distribute the SQL disks over those three virtual controllers. If you are using Nutanix AHV no virtual controller card configuration is required as the SCSI disks are optimally configured under the covers. However, it is advised that you use the latest Nutanix AHV VirtIO drivers for best performance. 

 If you are using Nutanix based storage, regardless of hypervisor choice, the proper disk layout is needed for optimal performance. On Nutanix it is important to spread the SQL database files and TempDB files across multiple disks. For example, if you have a database that needs medium to high performance I/O it could be beneficial to spread the database files over 4 or more virtual disks. If the DB is 1TB in size, then configure it for 4x 256GB files on four separate disks as a starting point. The same goes for TempDB, using multiple TempDB files on several disks could increase performance. See the Nutanix SQL Best Practices Guide for more details.

​The C and D (SQL binaries) drives should use the default NTFS allocation size. However the remaining SQL disks should use 64K. Many moons ago partition alignment was an issue, but modern OSes are smarter and manual configuration is no longer needed. In virtual environments you have a choice of disk provisioning type (e.g. thin, thick, EZT, etc.). In nearly all cases thin provisioning is the preferred disk format, particularly on Nutanix storage. If your storage vendor has a different recommendation, follow it.

Finally, if you are using the Nutanix platform, follow the most current Nutanix SQL Best Practices guide for optimal container (datastore) configuration in terms of data reduction services such as compression, de-dupe, and EC-X. Recommendations can change over time as the Nutanix platform evolves.

Pro Tip: When creating the SQL VMs add the boot disk first, so that it has the lowest SCSI ID. This will enable Windows to put all of the needed partitions on your boot disk. Otherwise you may lose 500MB on another disk and create future headaches if you want to make disk changes down the road.

​Summary

​Optimizing the VM hardware configuration is very important for peak performance. VM configuration will vary slightly based on hypervisor and underlying storage platform. I strongly recommend creating a standard SQL VM configuration baseline, and using it as a template for all other SQL VMs. Then adjust CPU/Memory/Disks as needed for a particular instance. For VMware environments you can easily use VM templates for this purpose. Also remember to use the latest VM hardware version, if you have a choice. 

Once you have installed Windows Server 2016 on two VMs, configured all of the drives, patched, and joined to the domain, then proceed to Part 3. Also make sure you have the SQL 2017 ISO handy and mounted to each VM. If you don't have another Windows VM in your environment that can host a simple and tiny file share (for the AAG file share witness) then deploy a third Windows Server 2016 VM with a single disk.

​SQL 2017 Installation Series Index

​SQL 2017 Always-on AG Pt. 1: Introduction

​SQL 2017 Always-on AG Pt. 2: VM deployment

​SQL 2017 Always-on AG Pt. 3: Service Accounts

SQL 2017 Always-on AG Pt. 4: Node A SQL Install

SQL 2017 Always-on AG Pt. 5: Node B SQL Install

SQL 2017 Always-on AG Pt. 6: Cluster configuration

​SQL 2017 Always-on AG Pt. 7: Fileshare Witness (coming)

​​​​​SQL 2017 Always-on AG Pt. 8: Max memory and Email (coming)

​​​​SQL 2017 Always-on AG Pt. 9: SQL Maintenance (coming)

​SQL 2017 Always-on AG Pt. 10: AAG Setup (coming)

​SQL 2017 Always-on AG Pt. 11: Kerberos (coming)

SQL 2017 Always-On AG Pt. 1: Introduction

SQL 2017

​In this series of posts I will show you how to configure SQL 2017 on Windows Server 2016 with Always-on Availability Groups. I will also be pointing out Nutanix AHV and VMware vSphere configuration best practices along the way. As you will see, 98% of the configuration is hypervisor independent.

​SQL Always-on Availability Groups is no longer "new", and is now the preferred HA option for SQL (assuming your applications support it). Gone are the days of shared SCSI disks/LUNs! SQL AAGs were introduced with SQL 2012, and have been enhanced to various degrees with each release. For "full feature" AAGs, you must use the Enterprise edition of SQL. "Basic" availability groups are available in SQL 2016 and 2017 standard edition, but are quite feature limited. You can read about basic AAGs here, directly from Microsoft. This installation series will only cover the enterprise edition of AAGs, as basic AAGs have quite limited features. A few years ago I wrote a similar set of blogs posts for SQL 2014, which you can find here

Most virtualization or general IT administrators do not have deep DBA skills, so this series is aimed at the general IT administrator and makes general configuration recommendations along the way. Each SQL instance is unique, and will likely deviate from generic settings in this series. So just take the sizings in this series as an example, and adjust accordingly for your needs. CPU, disks, memory and such are all workload and application dependent. Follow your application best practice recommendations, when available.

As previously mentioned, the vast majority of the settings in this guide are hypervisor and storage platform independent. If, however, you are a Nutanix customer I strongly suggest you download our SQL Best Practices Guide, which I was a contributing author. If you are using the VMware platform, I also recommend buying the "Virtualizing SQL Server with VMware." The book has a plethora of advanced SQL advice, which is applicable to any hypervisor. So even if you aren't using vSphere, the book has valuable configuration and performance tuning advice that pairs nicely with Nutanix AHV or Microsoft Hyper-V. 

​Today virtualizing SQL is no longer considered unusual, and in fact is one of the most popular applications to virtualize. However, that doesn't mean you can just "click next" or use all the defaults and expect tier-1 performance or availability. This series will give you a good starting point for your enterprise SQL 2017 deployments. 

​What to Expect

​This is not a "click next" installation. Topics covered in this series include:

  • vSphere and Nutanix AHV configuration best practices
  • Windows firewall configuration
  • SQL SSL certificates (optional)
  • Configuring e-mail alerts and notifications
  • Implementing maintenance and backup plans

​SQL 2017 Installation Series Index

​SQL 2017 Always-on AG Pt. 1: Introduction

SQL 2017 Always-on AG Pt. 2: VM deployment

SQL 2017 Always-on AG Pt. 3: Service Accounts

SQL 2017 Always-on AG Pt. 4: Node A SQL Install

SQL 2017 Always-on AG Pt. 5: Node B SQL install

SQL 2017 Always-on AG Pt. 6: Cluster configuration

​SQL 2017 Always-on AG Pt. 7: Fileshare Witness (coming)

​​​​​SQL 2017 Always-on AG Pt. 8: Max memory and Email (coming)

​​​​SQL 2017 Always-on AG Pt. 9: SQL Maintenance (coming)

​SQL 2017 Always-on AG Pt. 10: AAG Setup (coming)

​SQL 2017 Always-on AG Pt. 11: Kerberos (coming)

VMworld 2015: vCenter Server HA

Session INF4945

Why is vCenter HA important?

  • Primary administrative console
  • Critical component in end-to-end cloud provisioning
  • Foundation for VDI
  • Backup and DR solutions rely on vCenter
  • vCenter target availability is 99.99% from VMware’s design perspective (5 min a month)

 

Make every layer of the vCenter stack HA

  • vCenter DB
  • Host
  • SAN
  • Network
  • DC power and cooling

Reduce dependencies to improve nines

  • In moving from 5.1 and 5.5 to 6.0 you see a consolidation of vCenter services into VMs (e.g. just PSC and vCenter in 6.0)
  • vCenter 5.5 U3 supports SQL AAGs
  • vCenter 6.0 U1 supports SQL AAGs

Hardware/Host Failure protection: vSphere HA

  • Test tested solution
  • Protects against hardware failures
  • Some downtime for failover
  • Easy to setup and manage
  • DRS rules can be leveraged
  • High restart priority for vCenter components

Hardware/host failure protection: vSphere FT

  • Continuous availability with zero downtime and data loss
  • vCenter tested with FT for 4 vCPUs or less (only the ‘tiny’ and ‘small’ deployments fit)
  • About 20% overhead
  • Downtime during guest OS patching

Application failure protection: Watchdog

  • Watchdog monitors and protects vCenter applications
  • Automatically enabled on install on VCSA and Windows
  • On failure watchdog attempts to restart processes, if restart fails then VM is rebooted
  • Separate watchdog per vCenter server component

Application failure protection: Windows Server Failover clustering

  • Provides protection against OS level and application downtime
  • Provides protection for database
  • Some downtime during failure
  • Reduces downtime during OS patching
  • Tested with vCenter 5.5 and 6.0

Platform Services Controller HA

  • Two models: Embedded PSC or external PSC
  • PSC high availability in 6.0 requires a third party load balancer (removed in future vSphere versions)
  • Multiple PSC nodes in same site

vCenter Backup

  • Backup both embedded PSC and external PSC configurations
  • Recover from failures to vCenter node, PSC node or both
  • When vCenter node restored, it connects to PSC and reconciles the differences
  • When PSC node restored, it replicates from the other nodes
  • Uses VADP
  • Out-of-the box integration with VMware VDP

Tech Preview (vSphere 6.1?): Native HA

  • Native active-passive HA
  • Uses witness
  • No third party technology needed
  • Recover in minutes (target is 15 minutes), not hours
  • Protects against hardware, host and application failures
  • No shared storage required
  • 1-click automated HA setup
  • Fully integrated into the product
  • Out of box for the VCSA

VMworld 2015: 5 Functions of SW Defined Availability

Session: INF4535

Duncan Epping, Frank Denneman

Introduction to SDA (Software defined availability): VM, server, storage, data center, networking, management. Business only cares about the application, not the underlying infrastructure.

vSphere HA

  • Configured through vCenter but not dependent on it
  • Each host has an agent (FDM) will be installed for monitoring state
  • HA restarts VMs when failure impacts those VMs
  • Heartbeats via network and storage to communicate availability
  • Can use management network or VSAN network if VSAN is enabled
  • Need spare resources
  • Admission control – Allows you to reserve resources in case of a host failure
  • Admission control guarantees VM receives their reserved resources after a restart, but does not guarantee that VMs perform well after a restart.
  • Best practices: Select policy that best meets your needs, enable DRS, simulate failures to test performance
  • Percentage based is by far the most used and is Duncan recommended
  • Duncan went through various failure scenarios (host failure, host isolation, storage failure) and how HA restarts the VMs.
  • Use VMCP (new in 6.0) [VM component protection]. Helps protects against storage connectivity loss.
  • Generic recommendations: disable “host monitoring”; make sure you have redundant management network; enable portfast; use admission control

DRS

  • DRS provides load balancing and initial placement
  • DRS is the broker of resources between producers and consumers
  • DRS goal is to provide the resources the VM demands
  • DRS provides cluster management (maintenance mode, affinity/anti-affinity rules)
  • DRS keeps VM’s happy, it doesn’t perfectly balance each host
  • DRS affinity rules: Control the placement of VMs on hosts within a cluster.
  • DRS highest priority is to solve any violation of affinity rules.
  • VM-host groups configureable in mandatory (must-rule) or preferential (anti-)affinity rules (should-rule)
  • A mandatory (must) rule limits HA, DRS and the user
  • Why use resource pools? Powerful abstraction for managing a group of VMs. Set business requirements on a resource pool.
  • Bottom line is resource pools are complex, and VMs may not get the resources you think they should. Only use them when needed.
  • Try to keep the affinity rules as low as possible. Attempt to use preferential rules.
  • Tweak aggressiveness slider if cluster is unbalanced.

SDRS and SIOC

  • Storage IO control is not cluster aware, it is focused on storage
  • Enabled at the datastore level
  • Detects congestion and monitors average IO latency for a datastore
  • Latency above a particular threshold indicates congestion
  • SIOC throttles IOs once congestion is detected
  • Control IOs issued per host
  • Based on VMs shares, reservations, and limits
  • SDRS runs every 8 hours and checks balance, and looks at previous 16 hours for 90th percentile
  • Capacity threshold per datastore
  • I/O metric threshold per datastore
  • Affinity rules are available
  • SDRS is now aware of storage capabilities through VASA 2.0 (array thin provisioning, dedupe, auto-tiering, snapshot)
  • SDRS integrated with SRM
  • Full vSphere replication full support

vMotion

  • Migrate live VM to a new compute resource
  • vSphere 6.0: cross vCenter vMotion, long-distance vMotion, vMotion to cloud
  • May not realize it, but lots of innovation and new features here since its introduction in 2003
  • Long distance vMotion supports up to 150ms. No WAN acceleration needed.
  • vMotion anywhere: vMotion cross-vCenters, vMotion across hosts without shared storage, easily move VMs across DVS, folders and datacenters.

vSphere Network IO Control

  • Outbound QoS
  • Allows you to partition network resources
  • Uses resource pools to differentiate between traffic types (VM, NFS, vMotion, etc.)
  • Bandwidth allocation: Shares and reservations. NIOC v3 allows configuration of bandwidth requirements for individual VMs
  • DRS is aware of network reservations as well.
  • Bandwidth admission control in HA
  • Set reservations to guarantee minimum amount of bandwidth for performance of critical network traffic. Sparingly use VM level reservations.

 

VMworld 2014: vSphere HA Best Practices and FT Preview

Session BCO2701. This was very fast paced, and I missed jotting down a lot of the slide content. If you attended VMworld then I recommend you listen to the recording to get all of the good bits of information.

vSphere HA – what’s new in 5.5

  • VSAN Support
  • AppHA integration

What is HA? Protects against 3 failures:

  • Host failures, VM crashes
  • host network isolated and datastore incurs PDL
  • Guest OS hangs/crashes and application hangs/crashes

Best Practices for Networking and Storage

  • Redundant HA network
  • Fewest possible hops
  • Consistent portgroup names and network labels
  • Route based on originating port ID
  • Failback policy = no
  • Enable PortFast
  • MTU size the same

Networking Recommendations

  • Disable host monitoring if network maintenanceis going on
  • vmkinics for vSphere HA on separate subnets
  • Specify additional network isolation addresses
  • Each host can communicate with all other hosts

Storage Recommendations

  • Storage Heartbaeats – All hosts in the cluster should see the same datastores

Best Practices for HA and VSAN

  • Heartbeat datastores are not necessary in a VSAN cluster
  • Add a non-VSAN datastore to cluster hosts if VM MAC address collisions on the VM network are a significant concern
  • Choose a datastore that is fault isolated from VSAN network
  • Isolation address – use the default gateways for the VSAN networks
  • Each VSAN network should be on a unique subnet

vSphere HA Admission Control

  • Select the appropriate admission control policy
  • Enable DRS to maximize likelihood that Vm resource demands are met
  • Simulate failures to test and assess performance
  • Use the impact assessment fling
  • Percentage based is often the best choice but need to recalculate when hosts are added/removed

Tech Previews of  FT

  • FT will support up to v 4CPUs and 64GB of RAM per VM
  • FT now uses separate storage for the primary and secondary VMs
  • New FT method does not keep CPUs in lock step, but relies on super fast check pointing

Tech Preview HA

  • VM Component protection for storage is a new feature
  • Problem: Detects APD and PDL situation
  • Solution: Restarts affected VMs on unaffected hosts
  • Shows a GUI with options for what you want to protect against

Tech Preview of Admission control fling

  • Assesses the impact of losing a host
  • Provides sample scenarios to simulate