High Avaliablity SQL Server
In this article I an going to discuss the process that I took to configure high availablity in SQL server 2017 using Windows server 2019. All this was done from a fresh install of SQL server 2017 and Server 2019. Below is my current lab setup.
1 x Server 2019 configured as a domain controller for my domain called SQL.LOCAL
3 x Server 2019 with SQL Server 2017 Developer Edition installed with default settings
1 x Server 2019 used as a File Server (This is optional you don’t need this you can use any server provding that your SQL servers can access the share, it’s only used to store SQL Backups on)
Installing Windows Clustering
Before we can configure SQL server for High Avaliablity we first need to configure the Windows Failover Clustering service, this feature will help facilitate high availablity and automatic failover. We need to install the Windows Failover Clustering on all 3 SQL Server Nodes.
Logon to the first SQL server node, Open Server Manager, Click the Manage dropdown menu and select Add Roles And Features.
In the Add Roles and Features Wizzard, click Next, and Next, Next, and when at the Features section click Failover Clustering to install this feature. The image below shows that it’s already installed on my server, however you will need to install by selecting this option and follow the rest of the instructions on screen. You will need to repeat the above steps for all three nodes.
Configure Windows Clustering
Once you have installed the Windows Clustering, the next stage is to configure it.
Open the Failover Cluster Manager.
Click on Create Cluster from the Action menu.
Click on Next button in the Before You begin page.
Enter the name of the server that you want to add to the failover cluster, click add button. do this again for all the SQL server nodes.
Click Next Button.
Next stage is to validate you Windows servers to ensure they are capable of running a failover cluster that will be supported by Microsoft. Click Next Button. and follow on screen instructions.
Create an Availablity Group
Now that we have Installed and Configured a Windows Failover Cluster we can now enable Avaliablity Groups for SQL server 2019, the below steps will need to be done for each instance of SQL server. the next stage is before continuing is to change the SQL server service account to be a domain user that has admin rights. I needed to do this option as I ran into some difficulty down the line when my systems where not syncronising and showing as offline. We also need to enable TCP/IP and Named Pipes.
Open SQL Server Configuration Manager.
Right Click on the SQL server Instance and Select Properties.
Select Enable AlwaysOn Avaliablity Group, you will notice that it shows the Cluster Name of the Windows Failover Cluster.
Restart the SQL Server Instance.
Install an Avaliablity Group within SQL Server
Before we can install an avaliablity group within our SQL server instances you need to make sure you have done the following:
The Database must be set to full recovery mode
Full database backup has been performed
Database cannot be in read only mode, single user mode or in auto-close mode
Database Cannot be part of an existing group.
You can backup the database to nothing (NUL Device) just so you can get to the stage of setting up an avaliablity group.
BACKUP DATABASE <dbname> TO DISK = 'nul'
The next steps show how to configure an Avaliablity group for our 3 instances.
- Open SQL Server management studio
- Expand the AlwaysOn High Availablity folder
- Right-click on the Availablity Groups folder and select New Availablity Group Wizard.
- Click on the Next Button on the Introduction page of the New Availablity Group Wizard.
- Enter a name for your Availablity Group, and click on Next Button.
- Enable the option Database Level Health Detection if you want the Availablity Group to auto-matically failover if the Database Engine notcies that any database wihtin the Availablity group is no longer online.
- Click Next Button.
- Select the Database that you would like added to this Group.
- Click Next
- Specify the Replicas.
- Check the Automatic Failover (Up to 3) check box.
- Select Redable Secondaries. Redable secondaries have the following options.
- Yes – when in a secondary role, allow all connections from all applications to access this secondary in a readbale fashion.
- Read-only Intent – When in a secondary role, only allow connections from “Modern” applications that support the ApplicationIntent=ReadOnly connection string parameter
13. Click Next Button.
14. Review the endpoint configuration for your replicas, note that by default the endpoints will be encrypted.
15. Define which replicas you want your backups to be peformed on and their relative prioirty weight.
16. Click the Listener tab.
17. Configure the listener with a DNS name, IP address and Port Number.
18. The create new Availablity Wizard by default will synchronize the database from the primary replica to all of the secondary replicas through backup and restore operations.
19. Click on the Next Button.
20. Review the Availablity group configuration.
21. Click Finish.
22. This will take some time to complete.
23. Configuration Completed.
In my next post I will talk about how we can test the availablity and Quorum.