AlwaysOn Availability Group Design

SQL Server 2012/2014 AlwaysOn Availability Groups:

• Article Summary
• Part 1 – AlwaysOn Introduction
• Part 2 – AlwaysOn Design
• Part 3 – Install and Configure Windows Server 2012 R2 in Core mode
• Part 4 – WSFC Cluster Creation
• Part 5 – Install SQL Core on Windows Core Server
• Part 6 – AlwaysOn Availability Groups Creation
• Part 7 – AlwaysOn Availability Groups Creation (Advanced, with dedicated Replication Network)
• Part 8 – Methods to add Database on Availability Groups (SCOM Example)
• Part 9 – AlwaysOn Availability Groups – PowerShell Monitoring
• ANNEX (Part 6/7) – Manage SQL Endpoint

 

---

This part describe the design of the environment.

Article summary: SQL Server 2012-2014 AlwaysOn Availability Group

For the tests, I will create an “AlwaysOn Availability Group” cluster with four nodes and four AAG. Each AAG has two SQL Server Instance members, so each SQL node participate to two AAG.

The first two AAG will be used to host only test Databases. The other two will be used to host databases for SCOM 2012 R2 and SCO 2012 R2.

AlwaysOn Cluster – Physical View

 

LAB Requirements

Three Networks are required.

Note: I use same subnet for all nodes, I’ll write an article for WSFC Cluster Administration/Troubleshoot which also cover cross-subnet configuration.

Infra server:

 

SQL Servers / Instances Configuration

The lab will be composed on a four node WSFC cluster:

 

M-SQLA1 OS will be installed in full GUI mode with the SQL Feature “Management Tools – Complete” (include “Management Studio”; it’s not compatible with a Core installation). This server will be used to manage SQL AAG and WSFC cluster.

Note: In a production environment, all servers must be identical (all in core mode, or full/minimal) and a dedicated “management/tools” server with consoles is used for administration.

• reduce the space required on disk.
  
• reduce the potential attack surface.
  
• reduce the overhead of updating patches.
  
• minimize the requirements for servicing and restarting the server.

 

We need to install one named-instance per SQL Server:

 

Note Port Instances/Listener:

For an AAG Environment, you have to choice Ports for instances (here x4) and Ports for AAG-Listener (also x4 in my lab). I choose to use the same port (but not the default 1433) for all instances and all AAG Listeners, but there is no restriction. You can use different ports for each instance, different ports for each Listener, same port for all instances and another port for all Listeners, etc…

 

Availability Groups Configuration

I will create four Availability Groups:

AAG-1 and AAG-2 will serve for tests only. AAG-3SCOM and AAG-4SCOM will be used for my SCOM and Orchestrator Labs.

In this configuration, in nominal mode each instance hosts an “Active” Primary Replica.

The simulation is m-sql1 and m-sql2 in the same room and the two others in another room.

So I can lose one room (all my AAG/Databases remain available)

 

AAG Listener (VNN – Virtual Network Name)

For reminder, on the WSFC cluster side an AAG is a cluster Resource Group and the VNN is two cluster resources:

• Virtual Name
  
• Virtual IP
  

Example:

When you configure an application to host its Database on a SQL Availability Group you have to specify the Listener name for the instance name and the Listener port for the Instance port.

 

AAG Implementation – Version 1

This is the first version that will be configured in the next parts of the article:

 

AAG Implementation – Version 2

In another part, to simulate a Remote DRP Site, I will add and additional Instance (with two Replicas in Asynchronous mode on the AAG-1 and the AAG-2):

 

Availability Replicas Configuration

The next part is to specify the detailed availability replica (two per AAG) configuration:

All replicas will be configured in “Automatic” failover mode and so in “Synchronous” availability mode.

For more information see TechNet: Failover and Failover Modes (AlwaysOn Availability Groups) – https://technet.microsoft.com/en-us/library/hh213151

 

Readable Secondary Option:

For future tests, I enable Readable Secondary option.

 

Primary Role Connections:

I use the default settings (Allow all connections).

 

Endpoints Configuration

There is one Endpoint per SQL Server Instance.

During AAG Creation (via Wizard), Endpoint URL is configured with the SQL Instance FQDN. With this default option, instances will communicate over the Public Network (for reminder: 10.0.1.0/24).

So to configured instance communication on the Replication Network (10.0.20.0/24) I have to set my endpoint to: TCP://10.0.20.x:5022.

For tests, I will configure two instances (AOI1 and AOI3) on the Public Network (with FQDN) and the two other instances (AOI2 and AOI4) on the Replication Network.

 

Note: 5022 is the default port, you can use another port.

 

Service Accounts Requirement

Isolate Instance Services

Isolating services reduces the risk that one compromised service could be used to compromise others.

At the Instance level, each SQL Service (SQL Server, SQL Agent …) must be configured with different account.

Isolate Instances

A Security Best Practice is to use different accounts for each instance, but considers these points:

• Microsoft recommends to use the same account for all instances of an AlwaysOn Cluster (it’s more simple to assign rights to Endpoints)
• If you want to use Kerberos, instances must use the same account:

   

Service Accounts – Solutions

Use the same account for all Instances (enable Kerberos authentication):

• gMSA (Group Managed Service Accounts): the best solution for the AlwaysOn Availability Group is to use a gMSA (same as a MSA account but available on multiple host). But it’s not supported for the moment on SQL Server…
  

 

Status about  gMSA/MSA accounts for SQL

https://blogs.msdn.com/b/sqlosteam/archive/2014/02/19/msa-accounts-used-with-sql.aspx

Group Managed Service Accounts Overview

https://technet.microsoft.com/en-us/library/hh831782.aspx

 

• “Classic” Domain Account:
  you can use the same domain account for all instances (this works), but when you have to change the password account you have to program an interruption of service (all node will be affected at the same time by the password change…)

 

Use different accounts for all Instances (disable Kerberos authentication):

• MSA (Managed Service Account): you can use a MSA account per Instance (MSA is a domain account; password is managed automatically by the domain controller; a MSA is assigned to only one host)

 

• Virtual Accounts: you can use a virtual account per Instance (the functioning is identical to a MSA except it’s a local account managed by the host, not by the DC). This is the default option during a SQL Instance installation.

 

For more information, see TechNet article: Configure Windows Service Accounts and Permissions – https://msdn.microsoft.com/en-us/library/ms143504.aspx

So actually, there is no possible solution for use Kerberos with AAG in a production environment. I will use MSA account for my lab.

 

Permission needed for Service Account:

Notes: During installation, these permissions are granted by the SQL setup.

 

Storage

Disk configuration per node:

 

Notes about Storage:

If your SQL Servers are virtualized, for production environment you shouldn’t use Virtual Disk (except for OS). You have to use Pass-through (via Virtual FC) for Hyper-V, or RDM LUN for VMware.

In addition for better performance you must use a dedicated disk for TempDB.

Install SQL Server (SQL Server Directory) on a separate disk (D:).

You can also add a separate disk for pagefile, but if the SQL server is correctly sized it should not have to swap.

 

Example of a Production configuration

 

Note for Databases/Logs path on AAG:

If you use the default instance path (which contains the instance name) for Databases and Logs, the paths on all the nodes participating to the AAG are different. This has an impact on AlwaysOn AG.

TechNet:

 

So it is recommended to use the same path on all instances:

 

Note about TempDB

• The TempDB shouldn’t be store on the same disk as your Databases
• In Production, autogrow operations can affect performance so preallocate space to allow for the expected workload (autogrow should be used to increase disk space for unplanned exceptions)
• SQL CAT team recommends one file per CPU Core. Microsoft Note:

But this recommendation is subject to discussion and depends of your SQL environment (and the TempDB Contention). I’m not going to analyze this in this article, but I invite you to read the great articles of Paul Randal:

A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core:
https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention: https://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/

Another “General” Recommendation:

As a general rule, if the number of logical processors is less than 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dceea24c-7a53-4450-94cd-8327b5daa759/what-is-the-best-practice-for-configuring-tempdb

 

Security

Firewall

These ports (incoming) must be opened:

For more information about Microsoft Products Port requirements see MS KB “Service overview and network port requirements for Windows” – https://support.microsoft.com/kb/832017/en-us#method70

 

Antivirus Exclusion

Configure these exclusions on your Antivirus:

Exclusions for Cluster:

Exclusions for SQL Server:

 

Next

Next par covers the installation and configuration of servers in core mode: AlwaysOn Availability Group – Part 3 – Install WS2012 R2 Core Server