Set up Basic Always-on availability & disaster recovery
In SQL Server Standard Edition
Index
Contents
List of acronyms used in this document: 2
A brief explanation of what BADR is and why we use it: 2
Prerequisites for setting up BADR 4
Now we need to do the actual Basic Availability Setup 4
Step 1: Enable Always On in all participating instances of SQL Server 4
Step 2: Restart SQL Server instance Service 6
Step 3: Configure AG per database per node 6
Step 4: Adding more databases to AG 14
Introduction
List of acronyms used in this document:
| Acronym | Description |
| WSFC | Windows Server Failover Cluster (A group of connected and interdependent servers used for reliability and availability of an environment) |
| HA | High Availability |
| AG | Availability Groups |
| BAG | Basic Availability Groups |
| HADR | High Always-On Availability and Disaster Recovery |
| FCI | Failover Cluster Instances |
| BADR | Basic Always-on Availability and Disaster Recovery |
Explanation
In this walkthrough, we will be setting up BADR in SQL Server Standard, as a base for HA.
A brief explanation of what BADR is and why we use it:
This is a group of SQL servers that work together to ensure high availability of applications and services.
Any HA platform in SQL Server runs in WSFC (albeit for FCI or AG).
To begin, let us provide a succent breakdown of the various components, and compare between SQL Server Standard and Enterprise editions:
| Component | Standard | Enterprise |
| Always On failover cluster instances | Yes | Yes |
| High Always On availability groups | No | Yes |
| Basic Always-On availability groups | Yes | No |
| Contained availability groups | No | Yes |
| Distributed availability groups | No | Yes |
| Number of Availability Groups per Database Server | Limited to number of databases in Primary | Limited to system resources |
| Number of Databases per availability group | 1 | Number of databases in Primary |
| Maximums: | ||
| Nodes | 2 | 9 |
| Of which: | ||
|
1 | 1 |
|
0 | 2 |
|
1 | 3 |
|
1 | 5 |
|
1 | 5 |
This functionality therefore ensures that a copy of the current primary is always available, with the following caveats:
-
For Synchronised Sencondary replicas, the more replicas in the group, the longer it will take to finalise each entry (incrementally higher latency), i.e. all secondaries in the chain need to have hardened their logs before the primary records the transaction as successful. Therefore:
- Location is important, the closer to the primary the better.
- Connectivity speeds between members of the WSFC makes a difference (only use in a high-speed intranet environment).
-
For Asynchronous Secondary replicas, commit occurs on the Primary and responds as such, before sending the logs to the secondary replicas. Therefore:
- Location and network speed do not matter. However, stability does.
- However, the probability of data loss in case of forced failover is greater than with Synchronised Secondary Replicas.
Limitations
Basic availability groups use a subset of features compared to advanced availability groups on SQL Server Enterprise Edition. Basic availability groups include the following limitations:
- Limit of two replicas (primary and secondary). Basic Availability Groups for SQL Server on Linux support an additional configuration only replica.
- No read access on secondary replica.
- No backups on secondary replica.
- No integrity checks on secondary replicas.
- No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).
- Support for one availability database.
- Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server Enterprise Edition.
- Basic availability groups are only supported for Standard Edition servers.
- Basic availability groups cannot be part of a distributed availability group.
- You may have multiple Basic availability groups connected to a single instance of SQL Server.
Prerequisites for setting up BADR
- Ensure both nodes are on the same domain.
- Both nodes have the same version and edition of SQL server installed, with the same level of patching.
- The hard drive locations are the same on both replicas.
- WSFC has been set up and is correctly configured, and both nodes are members thereof.
Now we need to do the actual Basic Availability Setup
To showcase this, I have the following setup in my environment:
-
Host: Windows Server 2022 Standard with Hyper-V
-
Virtual Servers:
- Active Directory Services. Domain Controller and DNS Services (In the real world, these would be separate servers.)
- DB Server 1, SQL Server 2022 with 3 databases
- DB Server 2, SQL Server 2022.
- File Server (this will be the tiebreaker instance)
-
Virtual Servers:
Step 1: Enable Always On in all participating instances of SQL Server
- Open SQL Server Configuration Manager (as Administrator).
- Select SQL Server Services.
- Right Click on the instance of SQL server and select Properties.
- In the properties form, select Always On Availability Groups tab.
- Tick Enable Always On Availability Groups.
- A popup notification will appear to state that the changes will be applied but will only take effect after the service is restarted. Click on OK.
Step 2: Restart SQL Server instance Service
Right Click on the SQL Server instance and select Restart. The Stopping and starting pane will briefly appear
Step 3: Configure AG per database per node
To create a basic availability group, use the CREATE AVAILABILITY GROUP Transact-SQL command and specify the WITH BASIC option (the default is ADVANCED). You can also create the basic availability group using the UI in SQL Server Management Studio.
For the HR database, we opt for Asynchronous Secondary:
Endpoints:
Backup Preference:
Listener:
Once that is done, we proceed to Data Synchronisation:
We are going with Join only, as a backup and restore was done on the secondary. This will take us to the validation page:
Summary:
Pressing Finish will execute setup at the various locations.
The full detail of what was done is reflected.
Now, looking at the primary DB server in SSMS, we can see:
The Secondary Replica reflects:
We can check that the Virtual Network Node for the listener has been created in the domain:
Step 4: Adding more databases to AG
Adding the other databases to availability groups reflects as follows:
-
On the primary replica:
- In SSMS Connected database list:
-
- AG Dashboard:
-
On the secondary replica:
- In SSMS Connected database list:
-
- AD Dashboard:
-
On Active Directory:
- Computers:
The beauty of BADR:
- Connections to the database can now be made using the listener, and not directly to the primary and/or secondary replica’s (even though an instance of SQL server had not been installed on the listener (which is, in and of itself, a virtual network node, with no OS or applications installed)). Thus, in the case of a failover, it is not necessary to change the connection string in the application.
- The Always-On Dashboard gives a birds-eye view of the synchronisation status of participating databases
- Manual Failover can take place with the click of a button.
Here is the entire script applied, at each server as highlighted, to activate BADR:
— YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect DBSERV01
IF (SELECT state FROM sys.endpoints WHERE name = N’Hadr_endpoint’) <> 0
BEGIN
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [ACSCS\SQLService]
GO
:Connect DBSERV01
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’AlwaysOn_health’)
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=’AlwaysOn_health’)
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect DBSERV02
IF (SELECT state FROM sys.endpoints WHERE name = N’Hadr_endpoint’) <> 0
BEGIN
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [ACSCS\SQLService]
GO
:Connect DBSERV02
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’AlwaysOn_health’)
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=’AlwaysOn_health’)
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect DBSERV01
USE [master]
GO
CREATE AVAILABILITY GROUP [HR_AG]
WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
BASIC,
DB_FAILOVER = ON,
DTC_SUPPORT = NONE,
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0)
FOR DATABASE [HR]
REPLICA ON N’DBServ01′ WITH (ENDPOINT_URL = N’TCP://DBServ01.ACSCS.arturicast.co.za:5022′, FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
N’DBServ02′ WITH (ENDPOINT_URL = N’TCP://DBServ02.ACSCS.arturicast.co.za:5022′, FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO
:Connect DBSERV01
USE [master]
GO
ALTER AVAILABILITY GROUP [HR_AG]
ADD LISTENER N’Listener_HR_AG’ (
WITH IP
((N’172.26.92.14′, N’255.255.0.0′)
)
, PORT=1433);
GO
:Connect DBSERV02
ALTER AVAILABILITY GROUP [HR_AG] JOIN;
GO
:Connect DBSERV01
BACKUP DATABASE [HR] TO DISK = N’\\DBSERV01\UserDB\HR\HR.bak’ WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
:Connect DBSERV02
RESTORE DATABASE [HR] FROM DISK = N’\\DBSERV01\UserDB\HR\HR.bak’ WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect DBSERV01
BACKUP LOG [HR] TO DISK = N’\\DBSERV01\UserDB\HR\HR.trn’ WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
:Connect DBSERV02
RESTORE LOG [HR] FROM DISK = N’\\DBSERV01\UserDB\HR\HR.trn’ WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect DBSERV02
— Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 — wait for 5 minutes
if (serverproperty(‘IsHadrEnabled’) = 1)
and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty(‘ComputerNamePhysicalNetBIOS’) as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N’HR_AG’
select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
while @conn <> 1 and @count > 0
begin
set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
if @conn = 1
begin
— exit loop when the replica is connected, or if the query cannot find the replica status
break
end
waitfor delay ’00:00:10′
set @count = @count – 1
end
end
end try
begin catch
— If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE [HR] SET HADR AVAILABILITY GROUP = [HR_AG];
GO
GO