Introduction

In this walkthrough, we will be setting up a Basic Availability Group in SQL Server Standard edition as a base for HA. 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).

Before we get started, here is a list of acronyms used in this document:

To begin, let us provide a succinct breakdown of the various components, and compare between SQL Server Standard and Enterprise editions:

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 BAG

• 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

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 [MyDomain\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 [MyDomain\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 

  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 
 TO DISK = N'\\DBSERV01\UserDB\HR\HR.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
:Connect DBSERV02
RESTORE DATABASE 
 FROM DISK = N'\\DBSERV01\UserDB\HR\HR.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect DBSERV01
BACKUP LOG 
 TO DISK = N'\\DBSERV01\UserDB\HR\HR.trn' WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
:Connect DBSERV02
RESTORE LOG 
 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 
 SET HADR AVAILABILITY GROUP = [HR_AG];
GO
GO