As a Senior Database Consultant with over 20 years experience I’ve configured Always On more times than I can count and I find myself making the same recommendations over and over again. So, I did the sensible thing – I wrote myself an Always On checklist, and I’d like to share that checklist with you today. If you are in a hurry and you want to check a specific section, please click the anchor buttons above.
#1. GENERAL ARCHITECTURE PLANNING
As with everything in life, you’ve got to have a plan in place before you configure anything. I always find it best to ask the following questions in the first instance. These particular questions will help determine whether asynchronous or synchronous replication is best suited to your business needs.
First things first, replicas:
- How many do you need?
- Will they be in the same data centre or different ones?
- Check the network latency
- Will they be accessible for reading?
Next, nodes:
- Will the nodes be in the same sub-net or in two sub-nets?
And finally, databases:
- How many do you need?
- How big are they going to be?
- What are the planned activities for the databases?
#2. NETWORK
Here are the key requirements to send over and discuss with the network team:
- 1 IP dedicated by sub-net for each node
- 1 IP dedicated to the failover cluster
For each SQL Server Always On Availability Group (AAG) you’ll also need:
- 1 port number for the listener
- 1 endpoint port number (the default is 5022)
- A share folder in which the SQL engine service account has read/write permissions (used to initialise the replication when adding a database in an Always On AG)
All ticked off? Great, let’s have a look at DNS and IP addresses next.
#3. SERVICE ACCOUNTS
Quick reminder as we move onto service accounts – the account that runs SQL Services must be a domain account, not the local machine service account, as it needs to be able to access every replica. Also, this account must belong to the administrators group on each of the replicas. So, for my service account checklist:
- Create a SQL engine service account and generate a password for SQL Server
- Create a SQL agent service account and generate a password for SQL Server
Before we jump to our next step I’ve got a question for you – have you installed any SSIS, SSAS or SSRS service accounts? If ‘yes’ you’ll need to create additional SQL service accounts for each one.
#4. INSTALLATION & SERVER ADMINISTRATION ACCOUNTS
Now let’s take a look at the admin accounts, you’ll need a:
- Sysadmin AD account (to manage the servers)
- Failover cluster account (linked to failover cluster)
Once installed you’ll need to set permissions for your failover cluster account in AS on the folder containing the failover cluster computer object:
- Create computer objects
- Read all properties
If the above two permissions aren’t set, an error will pop up when generating the Always On listener: Create failed for Availability Group Listener.
Source: Microsoft
Here’s where I give you two options – there’s no right or wrong way to generate the listener, so opt for whichever makes the most sense to you.
You can pre-stage the listener:
- Find the OU with the CNO in it
- Create a computer object, name it your DNS listener name
- Set the security on the new VCO and give the CNO full control over it (full control isn’t required but the list of needs is fairly long so full control is simpler)
- Create the listener through SSMS/TSQL/Powershell
Or you can allow the cluster to create the listener itself:
- Give the CNO: create computer objects, list properties, read properties, write properties over the OU it resides in
- Create the listener through SSMS/TSQL/Powershell
For further reading on automatic listener creation click here.
#5. SERVERS
If you are working with an infrastructure team, run them through the below as there are some non-negotiable requirements here:
- All replicas in your Availability Group must be in the same Windows domain and must be able to communicate with each other
- Must not be a domain controller
- Instant File Initialisation must be enabled
- Same volumes disks with same folders structure (having the same disk structure avoids some « path not found » error as SQL files are replicated between primary server to replica)
- .Net Framework must be installed (3.5 for SQL Server 2014)
- WSFC must be installed on every replica (primary and secondaries) in the Availability Group
- Each node must be added to the failover cluster
- Install all available Windows hotfixes on every replica prior to deploying Always On (I recommend you use Windows Server 2012 on all your replicas – the available hotfixes can be found here)
- If using Kerberos, register the Server Principal Names (SPN) with Active Directory using the SQL engine service account
#6. FAILOVER CLUSTER
A few comments on the failover cluster.
- Assign a cluster name and provide a dedicated IP for the cluster
- Add each SQL server node to the newly created cluster
- It is recommended to use a Quorum for determining the nodes health (by setting the majority of the voting nodes)
If Node and Share File Majority configuration is selected, the Share File must not be located on any node in the cluster.
#7. STORAGE (DISKS)
Not exclusive to Always On, but here’s my approach for the storage allocation.
- Format your database files, logs and TempDB with 64KB allocation units (another value is optimum for some workloads but 64KB gives a good compromise)
- Put your TempDB on a separate volume (an SSD is well suited for it)
- Ensure the database files are in separate volumes
- All databases in your Always On Availability Group must be in full recovery model (Note: – ensure that there’s enough space for LOG files and that a sufficient maintenance plan is in place to look after LOG file growth)
- Ensure disk volume sizes can be increased while the drives are online (always helpful when running out of space and keeping downtime / server outages to a minimum)
SUMMARY
Configuring Always On can be as easy or as difficult as you make it, a big part of the challenge is communication with the infrastructure and network teams to make sure they know the requirements. Hopefully this checklist has given you the tools you need to get the job done!
Grahame Dyce
Team Lead Database Engineer
Grahame is an SC cleared Oracle Certified and SQL Server Certified Professional with nearly two decades of Oracle and SQL Server Database Administration and Application Server experience.
He loves to cook and play basketball but can't seem to figure out the Rubik's Cube!
To find out more about Grahame click here!