skip to Main Content
SQL2016 Always On Availability And Cluster Aware Updating

SQL2016 Always On Availability And Cluster Aware Updating

Contents

1. Introduction

2. Prerequisites

3. Base SQL Installation Guide

4. Cluster Aware Updating

5. Always-On Availability Group Preparation (Per-Instance)

6. Always-On Availability Group Configuration

7. Failover Testing

8. Additional Instances

9. SQL 2014 Build – Script to optimise the TempDB files

10. Reference Documents & Sites

Introduction

This is a comprehensive document that details every step to installing and configuring SQL2016 with always on availability groups.

SQL servers and first instance setup

SQL Server 1

  • Server 2012R2
  • IP Address: 192.168.0.1

SQL Server 2

  • Server 2012R2
  • IP Address: 192.168.0.2

New Instance in SQL:

  • Instance Name = INSTANCENAME
  • Install locations for the SQL Instance:
    • D:\INSTANCENAME\Data
    • E:\INSTANCENAME\Logs
    • F:\INSTANCENAME\TempDB\Logs
    • G:\INSTANCENAME\TempDB
  • Port number: 1433
  • SQL Service Account: DOMAIN\SERVICE_ACCOUNT
  • Listener Port number: 5022

New SQL Cluster:

  • Cluster Name = SQL-APP-CLUSTERNAME
  • Cluster IP: 192.168.0.3

New Availability Group:

  • A file share for initial replications (Only on the initial server):
    • H:\REP-INSTANCE
  • Computer Object for the Listener Account and Availability Group Account:
    • Name: SQL-INSTANCENAME-L1

Desc: SQL Cluster SQL-APP-CLUSTERNAME Listener Object 2

IP: 192.168.0.4

    • Name: SQL-APP-CLUSTERNAME-AG1

Desc: SQL Cluster SQL-APP-CLUSTERNAME Availability Group 2

Prerequisites

Things you’ll need prior to starting:

  • Cluster name
  • Instance Names and ports
  • Listener Names (Always connect applications to the Listener of an AO Group)
  • SQL Service Account Credentials
  • IP Address for Cluster on the same subnet as the servers/members
  • IP Address for the Listeners per instance.
  • Appropriate licensing and install media

For basic optimisation purposes (ie temp db, mem config etc)

  • What is the expected size of the DB and how heavy is it going to be accessed?
  • Any required advanced features such as Full-Text Search, analysis etc?
  • .Net Framework 3.5 SP1 installation

For the 3.5 SP1 Installation you will require the Server 2012r2 iso to be mounted to the server, in our case the ISO is available from the same folder as the SQL ISO. The below assumes that you have already mounted it.

Open Server Manager and click on “Add Roles and Features”

Click “Next” on the before you begin page

Select “Role-Based or feature-based installation” and click “Next”

Accept the default selection and click “Next”

Ignore the Server Roles and click on “Next”

Tick the “.NET Framework 3.5 Features” box and click “Next”

Now specific an alt path by clicking on “Specify an alternate source path” .

In our case the ISO is mounted as the “I” drive so the path is as below. And click on OK.

Now click on “Install”, no restart is required so don’t bother ticket that box.

When if finally finished, just click on “Close”

Base SQL Installation Guide

Install SQL on the First Node

We are installing a live cluster using the Enterpriser version of SQL 2016 downloaded from our MS portal.
You will need to ensure you have the appropriate license for the version of SQL you will be using.

You will also need to download the latest cumulative update from MS and extract it to a location on disk.

Ensure that the Windows Firewall is not protecting the heartbeat or private networks.

Log onto the first node and copy the ISO to the local machine then mount the ISO.

BootNote: You can see from above we have already created and allocated dedicated drives as per best practice, this is not strictly required but “is” recommended (even in a lab we would always recommend at least separate drives for OS and SQL) also as this is a Virtual Machine the OS drive can be thin provisioned, but the Log and data drives should be thick provisioned eager zero’d. You should lso ensure that when formatting the drives, they are formatted with 64k block sizes as this then aligns to the databases adds a performance increase.

Open the mounted disk and run the setup wizard

When the setup wizard launches:

Select “Installation”

Then click on “New SQL Server stand-alone installation or add features to existing installation”

Always-On AGs does NOT use old school clustering or clustering services, so do NOT use “New SQL Server failover cluster installation”

When the setup screen appears, enter the product key if required, ours has been prepopulated as we downloaded the iso from our MS Portal

Product Key

Click On “Next”

License Terms

For License Terms tick “I accept license terms.”

Now click on “Next”

Global Rules

The “Global Rules” page will come up with a quick scrolling progress bar then resolve to the next screen “Microsoft Updates” automatically

Microsoft Updates

Microsoft Updates, leave the tick box to use MS update to check for updates as part of the install, we have left it “unticked” as in our case the servers do not have external access to the internet. (ticking the box will generate a failure error on “Next”)

Now click on “Next”

If you see the below error, ignore it, MS install sometimes decides to try and search for updates regardless of your previous selection .

Just click on “Next” if you see the above. Alternatively, if the server can see the internet, you may also see this, just click next regardless:

Install Setup Files

The Install Setup files will proceed on its own and skip to the next page automatically

Feature Selection

For our purposes we have selected the below options, note that we have also changed the default installation directory’s to the “Application D:\” drive as per our design.

Now click on “next”

Instance Configuration

In most installations you can leave the default instance information in, especially in first installs or if none is specified or required for your design, for this one we are following a best practice design and choosing to name the instance, this will allow us to clearly identify our applications instances and is “the” best practice in a multi developer environment for both security and ease of managment.

Boot Note: “SQL Server supports multiple installations on the same computer, each installation receives the name of “instance”. The default instance gets the name of the computer, but the named instances, receive the name of the computer plus the name provided by the user on this setup page. To access a default instance once it is installed, using SQL Server Management Studio or any other program, you just need provide the computer name. To access a named instance, you will need to provide the name of the instance in the form of computername\nameoftheinstance.” As this is an always on cluster we will also be accessing the instances using their “listener address” but we shall cover that in more detail later.

Now click on “Next”

Server Configuration

Repeat until all have been populated/updated (enter the password for the supplied account into the “Password” field, and leave the startup types set to default.

Note that I ticked the box to perform Volume Maintenance Tasks. This provides a minor improvement in the performance of the instance, but comes with a minor security risk. It reclaims used disk space without filling that space with zeros, so it means that previously deleted data could potentially remain on the disk. Given the way this SQL server is secured, it was deemed appropriate to provide the performance boost. More information can be found here:

https://msdn.microsoft.com/library/ms175935(v=sql.130).aspx

Now Click on “Next” and the usernames/passwords are verified. Here will we will change the account details to the service account that has been setup specifically for this server for the instance services. If its wrong, you will see this error:

Check the account isn’t locked in AD just in case.

DB Engine Configuration

Server Configuration TAB.

For Server admins the below have been added:

  • SQL Service account
  • Domain Admins
  • Application_Environment_Admins
  • Application_Environment_Temp_Admins

These administrators may vary from design to design.

Data Directories TAB

As per the design we have set the directories to the below settings.

Backup goes to a UNC share \\Fileserver\Application\environment

NOTE: As this is 2016 There isn’t any TempDB here, it’s now in a separate tab and has a lot more info!

TempDB TAB

In SQL Server 2016, we have the option to specify tempdb file specifications during the setup. In 2014, we used to have to run a post-deployment script to optimize tempdb files (See end of this document for more details on that if you are inetrested), but this is all done here, and semi-automatically! Just specify the TempDB data and log file locations.

If you are a doing the installation by script, there are also parameters in the command line as shown below:

Parameter Meaning
SQLTEMPDBFILECOUNT The number of Database Engine tempdb data files.
SQLTEMPDBFILESIZE Initial size of a Database Engine tempdb data files in MB.
SQLTEMPDBFILEGROWTH Automatic growth increment of each Database Engine tempdb data file in MB.
SQLTEMPDBLOGFILESIZE Initial size of the Database Engine tempdb log file in MB.
SQLTEMPDBLOGFILEGROWTH Auto-growth increment of the Database Engine tempdb log file (MB).
SQLTEMPDBDIR Directories for Database Engine tempdb files.
SQLTEMPDBLOGDIR Directory for the Database Engine tempdb log files.

Default size change for tempdb:

In earlier versions, the default size of tempdb was taken from the model database. Now, because this is set during installation, the default initial size of tempdb would be 8MB, and the default auto-growth is 64MB. Default size change for tempdb

Default file numbers change for tempdb:

The SQL Server setup is smart enough to identify the number of CPUs on the server, and adds as many tempdb files as the CPU count, or 8, whichever is lower. In earlier versions, there was just one data file and one log file or tempdb database.

We can ignore the “FILESTREAM” TAB as it is not part of the current design.

Now Click on “Next”

Bootnote: Best practice is to not keep the backup directories on the same drive as the Data but to give it its own drive, in our case this is not an issue as our backups will be going to a central network share.

Because we specified a UNC path for the SQL backups, the install verifies the location and supplies this warning:

The setup will then automatically progress to the “Ready To Install” Screen

Bootnote: Best practice is to not keep the backup directories on the same drive as the Data but to give it its own drive, in our case this is not an issue as our backups will be going to a central network share.

Install Screen

The setup will then automatically progress to the “Ready To Install” Screen

Review and click on “Install”

Congratulations! You have complete the SQL Install on the first node. Reboot that server and repeat the above for the second node…

Additional instances Prep – Allocate port numbers

Open SQL Server Configuration Manager, then expand SQL Server Network Configuration and double click the TCP/IP settings.

On the IP Address tab, set the port number (in this case) to 1436 for the IP addresses:

Press OK

Now restart the SQL service for the instance. (this is minor detail often overlooked but essential)

SQL Management studio – Separate Install

As noted in the install above, the SQL Management tools are now a separate install:

This links to a website to download the tools.
(As noted our SQL server has no internet so this was downloaded then copied across for installation)

Click Install:

It just goes ahead and installs the application without being able to configure anything!

Reboot the server if necessary.

SQL Server Memory Management

SQL server is a database server and as such can use considerable memory. The more memory the SQL server process has, the more it can cache and thus improve performance and reduce IOPS. By default when SQL is installed it places no memory cap on itself and could use up nearly all the system memory, leaving precious little for the OS or other apps. This is even more of a problem if you enable large page tables since it could cause more memory starvation problems.

What’s the solution? You should always configure the SQL server’s max memory setting. This sets the upper limit on how much buffer memory the SQL server will use. By configuring this properly you ensure the OS or other local apps are not starved for memory. Set this too small and you could be “wasting” the extra memory. If you are running other services like full text search you will want to assign less memory to SQL.

When provisioning a new SQL server VM you need to be mindful of three memory settings: The guest VM’s memory size you configure in vCenter, SQL server’s maximum memory setting, and any VMware reservations that you configure. For all the gory details on SQL server memory, Brent Ozar has an outstanding post here. But to suffice it to say, if you reserve 100% of the VM’s memory at the VMware layer, then really just need to worry about the SQL maximum memory size. For a barebones SQL server (no other services like full text index, reporting services, etc.) the following table a decent starting place. I like to leave 2-4GB for the OS, and provision the rest to SQL server.

9-26-2013 5-24-58 PM
Ensure that you split this by the number of Instances! So for 3 instances on a box with 32GB RAM, then allocate 9.333GB RAM or 9557MB

Configuring the maximum is easy through the GUI. Open Microsoft SQL Server Management Studio and open the server properties. Go to the Memory configuration page and then enter the appropriate value. Remember if you increase the VM’s memory at the VMware layer, go back and adjust the SQL Max memory size or SQL won’t use it, also remember to change the settings on both servers in the cluster.

SQL Maintenance Tasks

Maintenance tasks are essential to a health environment, the below enables a simple one as we will leave any other requirements to the dedicated DBA’s or vendors as per design requirements.

System Database Maintenance

  1. Expand Management and Maintenance plans
  2. Check for existing maintenance plans to make sure you don’t duplicate.
  3. Right click Maintenance Plans, choose “Maintenance plan wizard”
  4. Click Next
  5. Plan Properties
    • Name: Weekly – System Databases
    • Choose Single schedule for the entire plan
    • Change the schedule to Weekly, Sunday @ 02:00
    • Click OK and next

  1. Tick: Check Database Integrity, Rebuild Index, Update Statistics

  1. Click Next, then Click Next again
  2. Select the “System Databases” option from the dropdown and click ok

  1. Click Next
  2. Select the “System Databases” option from the dropdown and click ok

  1. Leave the rest as default and click next
  2. Select the “System Databases” option from the dropdown and click ok

  1. Leave the rest as default and click next

  1. Untick write report to text file, click next

  1. Check all information is correct and click finish

Daily Backup of all Databases

  • Expand Management and Maintenance plans
  • Check for existing maintenance plans to make sure you don’t duplicate.
  • Right click Maintenance Plans, choose “Maintenance plan wizard”
  • Click Next
  • Plan Properties

Name: Daily – Backups – All Databases

Change the Schedule to Daily @ 02:00 and click “OK”

Then Click “Next”

Tick “Check Database Integrity”, “Back Up Database (Full)”, “Cleanup History” & “Maintenance Cleanup Task” then “Next”

Change the order to the following sequence:

On the Define Database Check Integrity Task, select all databases and leave the “Include Indexes” Check box.

In the Define Maintenance Cleanup Task,

  • Enter the folder where the backups are kept (eg. \\fileserver\Fileshare).
  • Enter the file extension “bak”
  • Tick “Include first-level subfolders”
  • Delete files older than 3 days.

On the General Tab Select “All databases” from the dropdown menu

On the Destination Tab tick the “Create a sub-directory for each database” box, the destination will already be pre-populated with correct location.

Then click on “next”

In the Define History Cleanup Task, set the date older than 3 days and click next:


Untick “Write a report to a text file”

Now simply review and click “Finish”


Wizard should show Completed and all Green

Production Servers Transaction Log backups – all Databases.

In our example, transaction log backups are not required for non-production servers/environments. Please see section below labelled “Non-Production Transaction Log Settings – All Databases”.

  • Expand Management and Maintenance plans
  • Check for existing maintenance plans to make sure you don’t duplicate.
  • Right click Maintenance Plans, choose “Maintenance plan wizard”
  • Click Next
  • Plan Properties

Name: Daily – Backups – Transaction Logs

Change the Schedule to Daily – Occurs every 15 mins and click “OK”

Then Click “Next”

Tick “Clean Up History”, “Back Up Database (Transaction Log)” & Maintenance Cleanup Task”, then “Next”

Change the order to the following sequence:

On the General Tab Select “All databases” from the dropdown menu

On the Destination Tab tick the “Create a sub-directory for each database” box, the destination will already be pre-populated with correct location.

Then click on “next”

In the Define Maintenance Cleanup Task,

  • Enter the folder where the backups are kept (eg. \\fileserver\fileshare).
  • Enter the file extension “trn”
  • Tick “Include first-level subfolders”
  • Delete files older than 1 day.

In the Define History Cleanup Task, set the date older than 3 days and click next:


Untick “Write a report to a text file”

Now simply review and click “Finish”


Wizard should show Completed and all Green

All maintenance plans should now show on the list

 

Non-Production Transaction Log Settings – all Databases.

In our example, these settings are required for “All” non-production servers/environments.

Go to Databases > System Databases, right click on “Model” then select “Properties”

Bootnote: The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database. Some of the settings of model are also used for creating a new tempdb during start up, so the model database must always exist on a SQL Server system.


Now Select “Files” then click on the “…” for the “modelog” row, “Autogrowth/Maxsize” column.

Ensure that “Enable Autogrowth” is ticked then set the Maximum file size limit to 5120MB’s
(5GB) and click “OK”

Now go to “Option” and change the “Recover Model” to “Simple” and click “OK”

Bootnote: The “Simple” recovery model is the most basic recovery model for any SQL Server.
Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions. 

Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed. 

Also, since the space in the transaction log can be reused, the transaction log cannot grow forever as it can in the “Full” recovery model.

Here are some reasons we have chosen this recovery model for non-production:

  • The data is not critical and can easily be recreated
  • The databases are primarily only used for test or development
  • Data is relatively static and does not change on an regular basis
  • Losing any or all transactions since the last backup is not a problem
  • Data is fully backed up in Prod (Live) and can be restored from those backups.

Deadlock Reporting/Alerts

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. For example:

  • Transaction A acquires a share lock on row 1.
  • Transaction B acquires a share lock on row 2.
  • Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the share lock it has on row 2.
  • Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 1.

Transaction A cannot complete until transaction B completes, but transaction B is blocked by transaction A. This condition is also called a cyclic dependency: Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.

Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. The Microsoft SQL Server Database Engine deadlock monitor will check for these and the alert will let a specified admin know about it.

Setup Database Mail

To configure Database Mail using a wizard, open Object Explorer, expand the node for the instance you want to configure Database mail.

  • Expand the Management node.
  • Right-click Database Mail, and then click Configure Database Mail.

Complete the Wizard dialogs:

Select Configuration Task Page

Click Yes:

On New Account Page, click on Add to add a New Database Mail Account:

  • Account Name = Server-InstanceName
  • Description = Server – Instance
  • Email Address = Server-InstanceName@Yourdomain.com (This is the e-mail address that e-mail is sent from)
  • Display Name = Application Instance Description
  • Reply e-mail: = YourIT@Yourdomain.com
  • Server Name = Your-eMail-Server (The server will need to be added to the SMTP service relay permissions via the Service Desk)
  • Enable Anonymous authentication.

Give the profile a name of SQL Alerts and click next:

Manage Profile Security, Public Tab

Profiles are either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using that profile. We are using Public profiles, so put a tick in the box here:

The Manage Profile Security, Private Tab can be ignored.

Configure System Parameters Page can be left as default, click Next:

Complete the Wizard Page by clicking Finish:

Send Test E-mail by right clicking the Database Mail and selecting the option:

Enter the “To:” address and click send test email.

Creating the Alerts

Right Click SQL Server Agent > select Properties

Select Alert System in the left pane

  • Checkmark > Enable mail profile
  • Verify Mail system: Database Mail
  • Verify Mail Profile: SQL Alerts
  • Checkmark > Include body of e-mail in the notification message
  • Click OK.

Within SQL server Agent right click on alerts and New Alert.

On the New Alert – General enter the details as below, 1205 is the event that is logged in event viewer that the alert monitors for.

1205 – SQL Deadlock Alert

Now move to the “Response” page, and tick the “Notify Operators” box then click on “New Operator”

Enter the relevant application support email details into the form by adding a new operator as per below, (YourIT@Yourdomain.com) and ensure that “enabled” is ticked, and just OK it.

Ensure that the E-Mail option is ticked

Click Options in left pane

  • Checkmark > Include alert error text in E-mail
  • Delay between response: 2 minutes

Click OK

Always On Reporting/Alerts

Now as this is an Always On cluster it’s also useful if the Server lets you know about any failovers and/or data flow/sync issues so repeat the above steps to set up alerts for the following error numbers.

1480 – AG Role Change (when the Availability group changes active node)

35264 – AO Data Movement Suspended (when data sync between nodes pauses)

35265 – AO Date Movement Resumed (when data sync between nodes resumes)

RESTART THE SQL SERVER AGENT!!

Once this is setup the email entered will get alerts when any of the specified events occur and also the system will keep count of many.

Cluster Aware Updating

The next step is to create a server cluster, But wait you cry! You said Always on doesn’t use traditional clustering!.. and you are right it doesn’t, the point of the cluster is for Cluster Aware Updating, this will allow the DB’s to gracefully failover during windows update and give the OS awareness when sister nodes are being updated or rebooted.

Boot Note: Check this out for full details and benefits. https://blogs.technet.microsoft.com/mspfe/2013/02/05/what-is-cluster-aware-updating-in-windows-server-2012-part-1/

Install the Failover Cluster Feature

On all servers in the cluster…

Open Server Manager and click on “Add Roles and Features”

Click “Next” on the before you begin page

Select “Role-Based or feature-based installation” and click “Next”

Accept the default selection and click “Next”

Ignore the Server Roles and click on “Next”

Select “Failover Clustering” from the list and tick the box.

Click on “Add Features”

Now click on “Next”

Then “Install”

Repeat the feature install on the other server before continuing!

Validate the servers for clustering

On one of the servers, launch the Failover Cluster Manager, and in the left pane right click on Failover Cluster Manager and select Validate Configuration.

Click on “Next”

Then Click on “Browse”. Enter the two hostnames of the SQL servers you are configuring and click “Next”.

Select “Run all” of the tests.

Review the confirmation, then click next:


Review the cluster report for any errors

Some warnings are normal, such as:

  • Only one pair of network interfaces
  • No disks to perform cluster validation on (and all associated disk tests)

Success!! Click on “Finish” and let’s build that cluster.

Boot Note: Download and install Windows8-RT-KB2803748.x64.msu on both nodes if you are running Windows Server 2012 (not 2012 R2).

Cluster Configuration

On clicking “Finish” as per above you “Create Cluster Wizard” should launch, if not you can manually start it and then re-add the member servers again as per above.

CC Wizard – Click “Next”

Enter a cluster name and the IP address (the “networks” is set automatically from the nic and can’t be changed)of the cluster name. Cluster name follows this convention:

Cluster Type – App – Environment Cluster

Eg. For a SQL cluster for the application in the production environment:

SQL-APP-CLUSTERNAME

Click Next.

Untick the “Add all eligible storage” and click “Next”

Ignore any errors regarding the disks as they are not required for our purposes.

Boot Note: Double Check/Verify all of your drives are still visible. 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, so SQL can startup normally. Gotta love MS

6. Open the DNS Manager console, locate the cluster hostname (e.g. SQL-APP-CLUSTERNAME) in the forward lookup zone, open the record, and uncheck the box to update the pointer record. Re-check the box to create a pointer record. Verify a pointer record was created.

Cluster Aware Updating Configuration

In ADUC create a computer object with cluster name with a –CAU suffix (e.g. SQL-APP-CLUSTERNAME-CAU), in the appropriate server OU.


Change the description on the object to SQL Cluster SQL-APP-CLUSTERNAME Cluster Aware Updating Object.

Modify the security permissions on the CAU computer object to give the cluster object (e.g. SQL-APP-CLUSTERNAME) full control.

Boot Note: Advanced view will need to be on to complete the above

If firewall is enabled then on each server open the Windows Firewall manager and enable the two remote shutdown rules shown below.

2014-09-20_21-00-32

In Server Manager from the Tools menu select Cluster-Aware Updating.

Enter the cluster name (e.g. SQL-APP-CLUSTERNAME) and connect.

In the right pane select Analyze cluster update readiness.

Verify that all results show Passed, except for the machine proxy and the CAU role, which can be ignored. Close the results windows.

In the right pane select Configure cluster self-updating options.

Check the box to add the CAU role, then check the box next to I have pre-staged computer object for the CAU clustered role. Enter the CAU computer object name you created (e.g. SQL-APP-CLUSTERNAMEAU). Click Next.

Configure the schedule as determined by your environment. In our environment this is controlled by a GPO, so it was left as default:

On the Advanced Options screen check the box to require all nodes online. I left the rest as default:

If desired, on the Additional Options screen check the box to install recommended updates.

Click Apply and wait for the installation to complete.

Re-run the Analyse cluster updating readiness. Verify everything passes, except for the proxy warning.

Create File Share

On a WS2012/R2 member server (not either SQL server) in our case I have used a server on the same subnet. This server has the file server role installed as part of our default config. If it doesn’t have that role, then please add it.
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.

Select the SMB Share – Quick file share profile.

Select the appropriate volume the share will be created on. We have used D:\ for this particular one.
Use a share name in the format of: <Cluster name>-FSW

Enter a description: I used SQL-APP-CLUSTERNAME Cluster Witness Share

Uncheck allow caching of share and enable encrypt data access.

Give the cluster computer SQL-APP-CLUSTERNAME full control.

If you want, you could also give administrators access so they can peek inside, in this case I have also given the SQL-APP-CLUSTERNAMES SQL service account access.

Customize the permission, disable inheritance and remove all inherited permissions. It should look similar to below when done. Press OK

Back to the wizard and click Next:

Finish the wizard and wait for the share to be created:

SQL 2016 File Share Witness Configuration

1. On a SQL server launch the Failover Cluster Manager.
2. Right click on the root cluster object, 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. In our case \\FileServer\SQL-APP-CLUSTERNAME-FSW


Click through the remainder of the wizard and verify the FSW was successfully configured.

Verify Quorum configuration is now using a file share witness.

Always-On Availability Group Preparation (Per-Instance)

On the first SQL Server

On the E drive (or another drive of your choosing – where you have your instance data usually) of your first SQL server create a folder called REP-InstanceName. This is where SQL will stage the replication data.

Change the NTFS permissions on the folder and add the SQL service account with full permissions.

Right click on the Replication folder and select the Sharing tab. Click Share. Verify the Database service account is listed as a read/write member. Click on Share.

Launch SQL Manager, right click on Databases and select New Database. Create a new database called SeedDB and leave all other parameters at their default.

Perform a Full backup of the SeedDB using the following script (Adjust to put the bak file alongside the database location:

— Back up the SeedDB database to new media set.

BACKUP DATABASE SeedDB

TO DISK = ‘H:\BackupFolder\SeedDB.bak’

WITH FORMAT;

GO

On Both SQL Servers

Open SQL Server Configuration Manager and locate the SQL Server service. Open the properties click on the AlwaysOn High Availability. Check the box to enable AlwaysOn.

9. Restart the SQL service.

Always-On Availability Group Configuration

Pre-Stage the Listener Account

You can provide client connectivity to the database of a given availability group by creating an availability group listener. An availability group listener is a virtual network name (VNN) to which clients can connect in order to access a database in a primary or secondary replica of an Always On availability group. An availability group listener enables a client to connect to an availability replica without knowing the name of the physical instance of SQL Server to which the client is connecting.

The client connection string does not need to be modified to connect to the current location of the current primary replica when using a listener.

In ADUC create a computer object for the listener computer account:

Us a similar name to the cluster object name with a –L1 suffix (e.g. SQL-INSTANCENAME-L1), in the appropriate server OU.


Change the description on the object to SQL Cluster SQL-APP-CLUSTERNAME Listener Object 1.

Modify the security permissions on the Listener computer object to give the cluster object (e.g. SQL-APP-CLUSTERNAME) full control.

Boot Note: Advanced view will need to be on to complete the above

Pre-Stage the Availability Group Account

Open Active Directory Users and Computers (ADUC) and create a new computer object using the cluster name with a suffix of –AG1 (e.g. SQL-APP-CLUSTERNAME-AG1). This is a bit more simple as you only need to change the description of the object to SQL Cluster SQL-APP-CLUSTERNAME Availability Group, and that is optional

Create the AlwaysOn HA Group in SQL

Ensure that you can connect to each instance from each server before continuing. So Server 1 can see server 1 & 2’s instances and vice versa! Use ServerName\InstanceName,PortNumber (eg. SQLServer\InstanceName,1433) to connect! If you cannot, then please resolve those issue before continuing. One area to check if you cannot see them is that the ports are correct and TCP/IP is enabled in SQL Server Configuration Manager.

Make sure you have created the SeedDB and done a full backup of it before continuing!

On the first SQL server cluster node in SQL Server Management Studio navigate to AlwaysOn High Availability, right click and select New Availability Group Wizard.


For the availability group name use the format of: SQL-APP-CLUSTERNAME-AG1. This should be the same name as the Computer Object we created in AD earlier in this subsection. Go ahead and tick Database Level Health Detection as well.

NOTE: Database Level Health Detection is an improvement in SQL 2016. If an AlwaysOn availability group database health detection has been selected for your availability group, and an availability database transitions out of the ONLINE state (sys.databases.state_desc), then entire availability group will failover automatically, which is pretty awesome

On the Select Databases screen the SeedDB database should be listed, and it should meet the prerequisites. Check the box next to the database and click Next.

On the Specify Replicas screen add the second SQL server cluster node.

Configure the automatic failover and synchronous commit options as shown below. Make the second node a readable secondary – required by the business in this instance and the application/client needs to connect to the Listener. (Don’t click next, click on the Endpoints Tab!)

If you want to configure your availability group replicas to allow for read-only connection requests when in the secondary role set the Readable Secondary property to ‘Read-intent only’. Here is a useful guide on setting up a listener to use Read-Only Routing:

https://blogs.msdn.microsoft.com/alwaysonpro/2013/07/01/end-to-end-using-a-listener-to-connect-to-a-secondary-replica-read-only-routing/

If you have a listener configured and you set this to ‘Yes’ or ‘Read-intent only’, then you could potentially get the following error:

The other alternative is to set the Readable Secondary to ‘No’ which forces connections to only use the Active connection when no listener is configured. If you have a listener configured and you set this to ‘No’, then you could potentially get the following error:

‘No’ is the default setup and allows the connections to use the cluster name without using a listener and without the application potentially maintaining a connection to a readonly database.

On the Endpoints tab, note that the Availability Group uses port 5022 for the end points. This needs to be different for each instance installed on the box. This section only reports the port used (Note the refresh button). If you need to change this port, then you need to run some SQL against the instance (Refer to additional instances section – Step 10).

On the Listener tab create an availability group listener.

For the DNS name, use the same name as the AG (e.g. SQL-APP-CLUSTERNAME-AG1) but replace the AG1 with an “L1” suffix (e.g. SQL-INSTANCENAME-L1), using port 1433 (as this is the port for the Database Instance).

You can choose the listener port numbers in many different ways:

  • Specify different port numbers for each instance and a different port number for the Listener.
  • Specify the same port number for all the instances (as a standard), and a different port number for the Listener.
  • Specify the same port number for all the instances as well as the Listener. This is possible because the IP address of the SQL Server instance is different from the IP address if the Listener.

Assign an IP address on the same subnet as the SQL server (This is a new IP address, not the same IP as the cluster – Just in case you wondered! ). If you don’t see the Add button under the network mode box expand the size of the window. Then click Next

Configure the data replication synchronization using the Replication file share we created on the first SQL server.

Review the validation results and verify all are successes and click Next. Resolve any errors or warnings before proceeding!

NOTE: If you get an error saying “Checking for compatibility of the database file location on the secondary replica resulted in an error.”, then there is something different between the 2 instance creations. Make sure they mirror each other exactly. Including drive letters and folder paths for DB, LOG, TempDB and TempDB logs.

Click on “Finish”:

Congratulations, the SeedDB databse is now “Always On”

Error during the creation wizard? Try this…

During this process I got an error:

TITLE: Microsoft SQL Server Management Studio

——————————

Create failed for Availability Group Listener ‘SQL-INSTANCENAME-L1’. (Microsoft.SqlServer.Management.HadrModel)

https://msdnshared.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/59/63/4505.createlistenerfails19471.pngv

There is some information about this here:

https://blogs.msdn.microsoft.com/alwaysonpro/2013/10/30/create-availability-group-listener-fails-with-message-19471-the-wsfc-cluster-could-not-bring-the-network-name-resource-online/

  1. Launch Failover Cluster Manager.
  2. In the Roles pane, right-click the availability group resource and choose Add Resource and then Client Access Point.

  1. Enter a DNS Name (SQL-INSTANCENAME-L1) and click in the affirmative through the rest of the wizard to create the CAP. The CAP is created, the IP Address resource comes online, but the network name resource fails to come online.

  1. Right-click the Server Name resource and select Bring Online.

  1. If it fails to come on line, then click on the General tab and check the Status.

    • If it reads “DNS handle is invalid.” – exit properties, select the Server Name resource, right-click on the network name resource and navigate to More Actions, choose Repair. Repeat repair until DNS Status reports OK under the General tab of the network name properties.
    • If it reads a Kerberos error – then either:
      • In Active Directory; give the cluster computer object permissions “Read all properties” and “Create Computer objects” to the CNO via the container. OR
      • Pre-stage the Cluster Listener computer object in Active Directory and give the cluster computer object full permission.
  1. Bring the Server Name resource online.
  2. Delete this CAP and try creating the AG Listener from the SQL Server Management Studio.

Failover Testing

Log on using SQL Studio to/on the Primary node, when right clicking on “AlwaysOn High Availability”

you get the option to “Show Dashboard”.

This will show as below, note the green options and the indication that this is the Primary (Active) Node

Logging on to the Secondary node and accessing the Dashboard shows the below, note the “Gray” and minimal options, the options for modifying the Availability Group are also unavailable on the secondary node.

Testing Failover

To test the failover, you can either click on “Start Failover Wizard” on the Dashboard, or right click on the group in object explorer and select “Failover”:

Both Options start the Wizard and take you to the same place.

The Failover Wizards first screen lets you select which member server to failover too, (in our case we only have the one so it is pre-selected.

Click on “Next”

Click Finish:

Validate the failover succeeded automatically and click Close:

If you check on the Dashboards you will see that the active node and roles have now switched to the secondary.

To simulate a “Failure” reboot the 2nd node

The Dashboard on the Primary now shows the following, as you can see the first node comfortably took over the Primary (Active) role with no user input.

And as soon as the 2nd node came back online the health returned to green.

All fully automated!!

Additional Instances

Availability Groups are instance specific, so additional instances need additional Availability Groups! The cluster is only built once.

NOTE: For each additional instance, you need to add a Listener port for the instance. This script will add a listener port on port number 5023. Please change the port number as appropriate.

You must only run this against any new instances you create! Don’t run this against the first instance you installed!

IF NOT EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N’DBMirroringEndPoint’)

CREATE ENDPOINT DBMirroringEndPoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 5024 )

FOR DATABASE_MIRRORING (

AUTHENTICATION = WINDOWS NEGOTIATE,

ENCRYPTION = REQUIRED,

ROLE=ALL);

If you don’t do this, you will get this error when you try and create your cluster:

The Database Mirroring endpoint cannot listen on port 5022 because it is in use by another process.

The Endpoint needs to show a unique port number for the instance during the New Availability Group Wizard:

Here is what I need to create extra per Instance:

Named Instance 2

New Instance in SQL:

  • Instance Name = NEW_INSTANCE
  • Install locations for the SQL Instance:
    • H:\NEW_INSTANCE\Data
    • I:\NEW_INSTANCE\Logs
    • I:\NEW_INSTANCE\TempDB\Logs
    • J:\NEW_INSTANCE\TempDB
  • Port number: 1435
  • SQL Service Account: DOMAIN\SERVICE_ACCOUNT
  • Listener Port number: 5023

New Availability Group:

  • A file share for initial replications (Only on the initial server):
    • H:\REP-NEW_INSTANCE
  • Computer Object for the Listener Account and Availability Group Account:
    • Name: SQL-INSTANCENAME-L2

Desc: SQL Cluster SQL-APP-CLUSTERNAME Listener Object 2

IP: 192.168.0.5

    • Name: SQL-APP-CLUSTERNAME-AG2

Desc: SQL Cluster SQL-APP-CLUSTERNAME Availability Group 2

Named Instance 3

New Instance in SQL:

  • Instance Name = NEW_INSTANCE2
  • Install locations for the SQL Instance:
    • K:\NEW_INSTANCE2\Data
    • L:\NEW_INSTANCE2\Logs
    • M:\NEW_INSTANCE2\TempDB\Logs
    • N:\NEW_INSTANCE2\TempDB
  • Port number: 1436
  • SQL Service Account: DOMAIN\SERVICE_ACCOUNT
  • Listener Port number: 5024

New Availability Group:

  • A file share for initial replications (Only on the initial server):
    • K:\REP- NEW_INSTANCE2
  • Computer Object for the Listener Account and Availability Group Account:
    • Name: SQL-APP-CLUSTERNAME-L3

Desc: SQL Cluster SQL-APP-CLUSTERNAME Listener Object 3

IP: 192.168.0.6

    • Name: SQL-APP-CLUSTERNAME-AG3

Desc: SQL Cluster SQL-APP-CLUSTERNAME Availability Group 3

SQL 2014 Build – Script to optimise the TempDB files

One of the most important aspects of SQL performance is TempDB. Applications can use TempDB as a scratch space, and in most cases you should not rely on just a single TempDB file. If the number of vCPUs is less than 8, then configure an equal number of TempDB files as you have assigned vCPUs. If the number of vCPUs exceeds 8, then start with 8 TempDB files and look for contention for in-memory allocation (PAGELATCH_XX). Scale up the number of TempDB files in increments of four until contention is eliminated.

Another important aspect of TempDB is their size and file growth. SQL will proportionally use the TempDB databases based on their size. So it’s important to create all TempDBs with the same size and not allow for autogrow.

Size them properly up front to accommodate your application requirements. If you do need to grow them in the future, grow all of them equally. All TempDBs will use a single “centralized” log file, so you won’t see multiple log files.

Increase all TempDB sizes equally for your environment. Recommended is 1-5GB per file, if the SQL server will be under light usage and only supporting single applications with relatively small DB sizes. A general TempDB sizing rule of thumb is 1% to 10% of your total database size. Your transaction log file size needs to be of sufficient size to hold all the transactions between backups. This is probably daily, unless you are running a large database with more frequent backups.

The script below creates 2 TempDB files at 2048MB each as the total DB is expected to be approx. 20GB for our implementation and we have 2 vcpu’s, the filepaths have also been updated to match our design. As well as setting the TempDB the below script sets several options to best practice and also enables DAC ‘remote admin connections’ which will allow access in the event of any issues.

Ensure the file name location is correct for the instance and run in SQL Management Studio. Copy & Paste as a new query, then execute it

/* Configures TempDB and other settings */

/* Based on SQL Script by Derek Seaman, derekseaman.com */

/* Configure TempDB */

USE master;

GO

ALTER DATABASE tempdb

ADD FILE

(

NAME = tempdev2,

FILENAME = ‘G:\TempDB_Data\tempdb2.mdf’,

SIZE = 2048MB,

FILEGROWTH = 0

);

ALTER DATABASE tempdb

MODIFY FILE

(

NAME = tempdev,

SIZE = 2048MB,

FILEGROWTH = 0

);

ALTER DATABASE tempdb

MODIFY FILE

(

NAME = templog,

SIZE = 2048MB,

FILEGROWTH = 256MB

);

/* Configure other system databases */

GO

ALTER DATABASE model

MODIFY FILE

(

NAME = modeldev,

SIZE = 10MB,

FILEGROWTH = 10MB

);

ALTER DATABASE model

MODIFY FILE

(

NAME = modellog,

SIZE = 10MB,

FILEGROWTH = 10MB

);

GO

ALTER DATABASE master

MODIFY FILE

(

NAME = master,

SIZE = 10MB,

FILEGROWTH = 10MB

);

ALTER DATABASE master

MODIFY FILE

(

NAME = mastlog,

SIZE = 10MB,

FILEGROWTH = 10MB

);

GO

ALTER DATABASE msdb

MODIFY FILE

(

NAME = msdbdata,

SIZE = 25MB,

FILEGROWTH = 10MB

);

ALTER DATABASE msdb

MODIFY FILE

(

NAME = msdblog,

SIZE = 25MB,

FILEGROWTH = 10MB

);

GO

/* Allow advanced configuration options */

USE master;

GO

exec sp_configure ‘show advanced options’, 1;

RECONFIGURE;

GO

/* Enable DAC */

EXEC sp_configure ‘remote admin connections’, 1

GO

RECONFIGURE

GO

After Running the script you should see the below (It takes about a min to run!)

You can also check the tempdb folder and verify you can see the modified files with their new size

Reference Documents & Sites

GUIDANCE NOTE: References to any external documents and sites.

AlwaysOn Installation http://www.derekseaman.com/2016/09/sql-2016-always-ag-pt-1-introduction.html
AlwaysOn Installation https://devops.profitbricks.com/tutorials/configure-a-sql-2016-alwayson-availability-group-cluster/#networking
AlwaysOn Troubleshooting https://support.microsoft.com/en-us/kb/2829783

https://blogs.msdn.microsoft.com/alwaysonpro/2013/10/30/create-availability-group-listener-fails-with-message-19471-the-wsfc-cluster-could-not-bring-the-network-name-resource-online/

SQL Config https://www.brentozar.com/
Always On Alerting http://sqlmag.com/blog/alwayson-ags-and-sql-server-jobs-setting-failover-alerts

 

Download the full guide

[vcex_button url=”/?ddownload=88″ title=”Download guide” style=”graphical” align=”left” color=”blue” size=”large” target=”self” rel=”none”]Download the full guide here[/vcex_button]

Colin Parke

Colin is an IT architect and leader with 19 years’ experience primarily in Global IT infrastructure and management roles in a variety of sectors including education, employment & manufacturing. He has architected infrastructure solutions and led teams across many distinct geographical regions, including the UK, USA, Tasmania and Saudi Arabia.

“I consistently strive to provide innovative ways of working for the end users that enhance their experience and productivity, whilst keeping to budget.”

Colin is Wintel specialist and a subject matter expert across most of the Microsoft product range. He has been an advisor to Citrix development teams and has been an active member of the VMWare User Group.

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top
×Close search
Search