We often deal with SQL databases that it would make sense to protect with a high availability solution. Microsoft offers several ways to do this, including Always On. This mode is considered the easiest and most convenient one, and here I’ll try to show you how to configure it.
To start, we need to prepare two servers (nowadays almost always virtual) with a reasonably modern operating system (as of today I obviously recommend either Windows Server 2016 or 2019); once that’s done, we need to install SQL Server Standard or Enterprise on both servers (Standard clearly has some features fewer than Enterprise, but I won’t list them here). In my case I installed SQL Server 2019 Enterprise.
The SQL nodes can be installed as if they were standalone nodes, so without any special precautions (the default settings are fine).
Once the installation is complete, you need to perform the following steps:
Open the SQL Server Configuration Manager console, right-click the instance and select Properties:
On the “Always On Availability Groups” tab, select the “Enable Always On Availability Groups” check box:
Set the “SQL Server Agent” service to start automatically and start it:
Connect to the instance with “SQL Management Studio”, select the existing databases (in my case the first server already existed and was serving 4 DBs) and under Properties–>Options change the recovery model to “Full”:
Back up the existing DBs using the dedicated function in the Tasks–>Back Up menu:
Using “SQL Management Studio”, on each instance you need to grant permissions to the other servers that will make up the Always On group; for example, in my case I granted SERVER01 permissions to access SERVER02 and vice versa:
use [master]
GO
CREATE LOGIN [DOMINIO\SERVER02$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMINIO\SERVER02$]
GO
At this point you can start the wizard to create the Always On group:
Select the DBs you want to put in Always On:
Connect to the second instance set up to work in Always On:
Choose the data synchronization preference; in my case the DBs are small, so I let the system handle the synchronization; with large DBs you can select the second option, “Full database and log backup”, and manually restore the DB on the secondary instance (from the backups we took earlier); Always On will then take care of bringing them in line with any changes made to the DBs in the meantime.
When the synchronization finishes, you should get 4 successful results, as shown in the figure:
Right-click the “Availability Groups” folder and click “Show Dashboard”:
If everything is validated correctly, the system is working properly and the replicas are active:
At this point all that’s left is to create the Always On listener, and the system is ready to use. Start the “Add Listener” wizard:
Define the port and IP of the listener, which will act as the frontend VIP for the pair of servers in the Always On group.
Applications can then be configured to point to this VIP and the defined port, and will therefore reach whichever database instance is active at that moment. If the active instance fails, the secondary will take over the VIP and immediately start serving the workload on the protected DBs.