After installing SQL Server 2022, it is time to configure alwayson availability groups for high availability.
Let us understand our setup:
- Windows 2019
- SQL Server 2019
- 2 – node cluster
Before starting the process of Configure alwayson:
- We should have the Windows server admin permission
- We should have SQL Server ‘sysadmin’ permission
- In both nodes, SQL Server services should be running under the same service account
- SQL database mirroring port 5022 should be allowed and also add in the Windows firewall inbound rules (we have an existing rule for SQL default port 1433, just add 5022)
- Both the nodes should be added to the Windows cluster (Should be done by the Windows/platform team). The same can be verified from the failover cluster manager with cluster objects and nodes that are added to it.
- Get an IP (non-pingable) to configure the listener from the network or platform team.
Configure AlwaysOn in SQL Server 2022:
- Launch SSMS (SQL Server Management Studio) and hit ‘Run as administrator’
- Connect to any SQL instance node (node 1 in our case) then expand Always on High Availability then we get an error as the feature is not enabled.
How to enable Always on High Availability Feature in SQL Server 2022?
- Launch SQL Server 2022 Configuration Manager as an administrator
- From the SQL Server properties, in the Always On Availability Groups tab check ‘Enable Always On Availability’ and restart the SQL Server Services
- Refresh the SQL instance and expand Always on High Availability then right-click on the ‘Availability Groups’ to select ‘New Availability Group Wizard’
- Hit next on the wizard
- Give the availability group (AG) name as per your requirement then hit ‘Next’
- In the database selection window, the status shows ‘Full backup is required
- Take full backup and refresh then the status changes to Meets prerequisites. Check the database to add in a SQL availability group (can add multiple databases at once) and hit ‘Next’.
- Add the secondary replica (always on a feature should be enabled like node 1), and change the availability mode to synchronous as it makes sure no data loss.
- Usually synchronous mode will be set between the available servers which are in the same data center or with very good bandwidth
- Asynchronous mode will be set for DR (disaster recovery) node
- Leave the endpoints settings as is [Default], for Backup Preferences you can set as per your preference (for us we chose node 2).
- In the listener tab, give the listener name (our case ‘dblis’), port default 1433, and keep the Network mode ‘Static IP’ then hit ‘Add’ to give IP reserved for the listener
- Select the subnet range from the drop-down and enter the reserved listener IP then hit ‘OK’
- Verify all details one more time and hit ‘Next’
- Ignore the Read-only routing tab
- Select Data Synchronization: Choose how you want to sync data between replicas. For us we chose ‘Automatic Seeding’ and then hit ‘Next’.
- The automatic seeding option will transfer the data as per the bandwidth convenience between the replicas. Database creation and data transfer between primary to secondary will be taken care of with this option.
- Validation Check: All the requirements should be satisfied and show ‘success’, hit ‘Next’
- Check the ‘Summary’ and hit ‘Finish’
- Make sure all steps are successful and then close
- Verify the listener connection by connecting to it. In our case, the SQL server database listener is ‘DBLIS’
- Synchronization between databases takes time depending on the size of the database, once done both the replicas databases should show the status as ‘Synchronized’. Now we have successfully done with configure alwayson.