Menu Close

SQL Always On Step by Step

image

This article will walk through a basic install and configuration for SQL Always On, to be leveraged by SCOM for higher availability.

This is no way aligned to Clustering and SQL best practices – just an example on how to set one up for a simple testing deployment.

 

The Server OS used will be Windows Server 2022.

The SQL version will be SQL 2019.

I’ll need to plan for some names and IP’s up front:

Server Name Role IP Address
SQL01 Virtual Machine, Node 10.10.10.150
SQL02 Virtual Machine, Node 10.10.10.151
SQLCL01 Cluster Name Object (CNO) 10.10.10.152
SQLAGL01 SQL Availability Group Listener 10.10.10.153

 

First, deploy two Virtual Machines running Windows Server 2022, with the following names:

  • SQL01
  • SQL02

 

I’ll set the Disk Configuration to have the following volumes:

  • C: 128GB (OS)
  • D: 128GB (APPS)
  • G: 200GB (DATA)
  • L: 100GB (LOGS)

 

I’ll format the D:, G: and L: volumes with 64k allocation unit sizes, which is optimized for SQL.

image

 

Next I will:

  • Assign each server a name
  • Set the IP address
  • Set the DNS settings
  • Reboot
Rename-Computer -NewName "SQL01" -Force New-NetIPAddress -InterfaceAlias "Ethernet" -IPAddress 10.10.10.150 -PrefixLength 16 -DefaultGateway 10.10.10.1 Set-DnsClientServerAddress -InterfaceAlias "Ethernet" -ServerAddresses 10.10.10.10,10.10.10.11 Restart-Computer

Next I will:

  • Add the computer to my domain
  • Disable the Windows Firewall
  • Set the Time Zone
  • Enable Remote Desktop
  • Add my SQL Administrators group to the Local Administrators group
  • Reboot
Add-Computer -DomainName "opsmgr.net" Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled False Set-TimeZone -Id "Central Standard Time" Set-ItemProperty -Path 'HKLM:\System\CurrentControlSet\Control\Terminal Server' -name "fDenyTSConnections" -value 0 Add-LocalGroupMember -Group "Administrators" -Member "OPSMGR\sqladmins" Restart-Computer

Now I will log on to each server using a domain account that is a SQL Administrator, so I can Install SQL and configure the Cluster.

I will map a drive to my SQL media, and install SQL server.

NET USE Y: '\\server\software\sql\2019\2019 ENT' Y:\setup.exe /qs /ACTION=Install /FEATURES=SQLEngine,FullText /INSTANCENAME=INST01 /SQLSVCACCOUNT='OPSMGR\sqlsvc' /SQLSVCPASSWORD='password' /SQLSYSADMINACCOUNTS='OPSMGR\sqladmins' /AGTSVCACCOUNT='OPSMGR\sqlsvc' /AGTSVCPASSWORD='password' /AGTSVCSTARTUPTYPE=Automatic /TCPENABLED=1 /SQLSVCINSTANTFILEINIT=True /INSTALLSQLDATADIR='D:\Program Files\Microsoft SQL Server' /SQLBACKUPDIR='G:\SQL\INST01\Backup' /SQLUSERDBDIR='G:\SQL\INST01\DATA' /SQLUSERDBLOGDIR='L:\SQL\INST01\LOGS' /USESQLRECOMMENDEDMEMORYLIMITS /IACCEPTSQLSERVERLICENSETERMS

Then I will install the Failover Clustering Feature

Install-WindowsFeature Failover-Clustering –IncludeManagementTools

Next I will pre-create the Cluster Computer Account in the domain for the Cluster Name Object (CNO).  Normally your Active Directory administrators would do this.

I need to disable the account and assign FULL CONTROL to the user account performing the cluster installation on the CNO.  My CNO will be named “SQLCL01”.  My user account doing the install of SQL and Clustering is OPSMGR\sqladmin.

image

 

Then I can create the cluster.  Run this ONLY ON ONE OF THE NODES.

# ONLY ON ONE NODE # Precreate the Virtual Cluster Computer Account (CNO) in the domain and disable computer account # Assign the user installing the cluster to have FULL CONTROL to Cluster Computer Account in the domain. New-Cluster –Name SQLCL01 –StaticAddress 10.10.10.152 –Node SQL01,SQL02

You can open Failover Cluster Administrator and see your new Cluster at this point:

image

 

We need to configure a File Share Witness for the cluster.  Create a share and a folder for this cluster.  Assign FULL CONTROL rights to the folder, for the CNO:

image

Set-ClusterQuorum -FileShareWitness "\\SERVER\QUORUM\SQLCL01"

At this point I will enforce my TLS 1.2 only communications, which is a best practice.  This is not a requirement.

# Disable everything except TLS 1.2 $RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client" IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null} New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null $RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server" IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null} New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null $RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Client" IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null} New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null $RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server" IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null} New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null $RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client" IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null} New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null $RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null} New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null $RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client" IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null} New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null $RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server" IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null} New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null $RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client" IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null} New-ItemProperty -Path $RegPath -Name "Enabled" -Value "1" -PropertyType DWORD -Force | Out-Null New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "0" -PropertyType DWORD -Force | Out-Null $RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server" IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null} New-ItemProperty -Path $RegPath -Name "Enabled" -Value "1" -PropertyType DWORD -Force | Out-Null New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "0" -PropertyType DWORD -Force | Out-Null

 

At this point – reboot each node.

Next – we need to enable Always On for the SQL instance on each node:

$ServerInstance = 'SQL01\INST01' #this should be in format SERVERNAME\INSTANCENAME or just use servername for default instance Enable-SqlAlwaysOn -ServerInstance $ServerInstance -Force

 

Configure SQL Always On Availability Group

Now – we need to configure the Always On availability group

FIRST – we need to pre-stage a Computer Account in the Domain for the Always On Availability Group Listener.  Once we create this account (SQLAGL01$), we need to assign FULL CONTROL on this object to the CNO Computer account (SQLCL01$).

image

image

image

This will allow the Cluster to be able to manage the computer account for the AG Listener.

 

Next – open SQL Management studio from anywhere you have it installed.

Connect to the SQL01\INST01 SQL instance:

image

 

First, we need to create a “dummy” database ONLY on SQL01\INST01 which is required to configure and test Always On. 

Go to Databases, right click, and choose New Database.  Name the Database “TESTDB” and click OK.

image

Before we can use a database in Always On, it has two requirements:

  • The database must be in FULL recovery model.
  • The database must have a FULL Backup.

Right click the TESTDB database, and choose Properties, then select Options.  Make sure the Recovery Model is FULL.  Click OK.

Right click the TESTDB database, and choose Tasks > Backup.  Run a FULL backup.

Now expand “Always On High Availability”, and Right Click “Availability Groups” and choose “New Availability Group Wizard

image

Assign an Availability Group name.  This isn’t terribly important.  I will use “SQLAG01” then check the box next to “Database Level Health Detection” and click Next.

image

 

Check the box next to your Test Database and click Next.

image

 

Add a replica, and choose your other server, SQL02.  Check the boxes next to Auto failover and Synchronous commit on both servers.

image

 

On the Listener tab, select “Create an availability group listener”.  I will use “SQLAG01”  I will use port 1764 (use whatever your standard is). 

Choose Static IP and ADD and input your predetermined static IP for your Availability Group Listener.

image

 

Click Next.

Choose Automatic Seeding to automatically sync the database and click Next:

image

 

We will run some tests:

image

 

Click Next.  Click Finish.

Click “More Details” and see that everything was a success.

image

 

The most common failures are creating the Availability Group Listener.  This is almost always a failure to pre-stage the computer account and apply the rights needed.  Please see the section “To pre-stage an account for a clustered service or application” at:  Failover Cluster Step-by-Step Guide: Configuring Accounts in Active Directory | Microsoft Learn

 

Go into SQL Management studio and look over your configuration:

image

 

Now – lets test a planned failover.

On SQL01\INST01, right click the Availability Group and choose Failover

image

 

You should not have any errors and be able to fail over with no data loss because these are synchronized:

image

 

You will see the availability group on SQL01 switch to Secondary.  SQL02 is now the primary:

image

 

Set up SQL Maintenance

We need to set up SQL maintenance if your DBA’s have no already configured this.  Since our databases will be in an AlwaysOn Availability Group, they will be in FULL recovery model and we require frequent transaction log backups or they will fill.  We also need to configure backups, indexing, etc.

I prefer to use Ola Hallengren’s solution for this:  https://ola.hallengren.com/

Download the MaintenanceSolution.sql from there and then open it in SQL management studio.  Then read the instructions at https://ola.hallengren.com/frequently-asked-questions.html

In SQL Management Studio – we need to edit this file and configure our settings for file cleanup time in hours and backup directory:

Defaults:

image

Changes for this deployment:

image

Then I Execute this file to create all the jobs.  Run this on BOTH SQL servers/instances:

image

Now – we need to test these jobs manually – then schedule them.

Run the “DatabaseBackup – USER_DATABASES – FULL” by right clicking and start job at step….

image

And verify the backup was a success:

image

 

Repeat this for TLOGS, Index Maintenance, and Output File Cleanup jobs.

Now we need to schedule these jobs.

In SQL Management Studio, under SQL Server Agent – Right Click “Jobs” and choose “Manage Schedules

Create 4 new schedules based on the following:

  • Daily at 9 PM
  • Daily at 10 PM
  • Daily at 11 PM
  • Hourly

image

 

Then schedule each job by opening the job, and Picking an existing schedule – according to the following:

  • DatabaseBackup – USER_DATABASES – LOG (Hourly)
  • Output File Cleanup (Daily at 9 PM)
  • DatabaseBackup – USER_DATABASES – FULL (Daily at 10 PM)
  • IndexOptimize – USER_DATABASES (Daily at 11 PM)

Repeat the schedule creation and job scheduling on the second node.

 

Testing connectivity

Lastly – test connectivity to the listener, as all applications connecting to a Database replica in an AG should use the listener.  In my case, my connect string t is “SQLAGL01.opsmgr.net,1764”

image

 

When I install SCOM, I will point the SCOM database setup page to this connect string.  I will still need to go back and set the SCOM database to full recovery model, back it up, and then add it to the Availability group.

 

Great Perth bookkeeping - Here's how it works

3 Comments

Leave a Reply

Your email address will not be published.