AlwaysOn – Create a Basic Availability Group

AlwaysOn Basic Availability Groups (BAGs) are available with SQL Server 2016 and 2017 Standard edition.  The functionality is generally the same as database mirroring (which has been deprecated).  This feature replicates transactions to a database on a secondary server, and is useful for disaster recovery should something happen to the primary server.

If you have a database that requires an extra layer of protection or ‘BAG of tricks’, deploying a Basic Availability Group is useful for providing disaster recovery and high availability for the one database.  Also there is major cost savings since it is not necessary to purchase SQL Enterprise edition…this can be done in Standard edition of SQL Server.

BAGs provide a failover environment for only one database, and there can only be two replicas in the group.  Replication can be synchronous or asynchronous, and there is no read access, no backups and no integrity checks on the secondary.   The secondary replica remains inactive unless there is a failover, and Basic AGs can remain on-premises or span from on-prem to Azure.

Create Basic Availability Group using Wizard

To create a Basic Availability Group you must first install SQL Standard edition on each replica.  You must also enable the AlwaysOn feature in SQL Configuration Manager.  With the configuration manager opened, highlight SQL Server Services and right click on SQL Server in the right panel to open the properties dialog box.  On the AlwaysOn High Availability tab, mark the Enable AlwaysOn checkbox:

In preparation for creating your AlwaysOn group, you must take a full and tlog backup of the database on your primary, and restore that full and tlog backup to the secondary, in no-recovery mode.

After taking backups of all databases you want to include in the AlwaysOn group, right click on AlwaysOn High Availability in your Object Explorer in SSMS and open the New Availability Group Wizard.

Specify a name for your AlwaysOn group, and select Database Level Health Detection if desired

The wizard will take you through steps to select the database you want to add to the AG, select the secondary replica and connect to it, and specify automatic failover and synchronous commit mode.  There is no option in ‘Readable Secondary’ other than ‘No’ as the secondary replica is not accessible in a Basic Availability Group unless failover occurs.

Next click on the Endpoints tab to select the port number that will be used by your AlwaysOn group.  If you have more than one instance on your server, you will need to create another endpoint.  Click here for further explanation:  https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/create-a-database-mirroring-endpoint-for-windows-authentication-transact-sql   

CREATE ENDPOINT endpoint_mirroring  
    STATE = STARTED  
    AS TCP (LISTENER_PORT = 7022)  
    FOR DATABASE_MIRRORING (ROLE=PARTNER);  
GO 

In clicking on the Backup Preferences tab, you will notice there is no option to choose Backup Preferences, because performing backups on the secondary replica is not an option for Basic AGs.  All options and backup priority settings are greyed out:

On the Listener tab, specify the port number for the AlwaysOn Listener.  If you have more than one SQL instance installed on your server, choose a different port number from any other AG if necessary:

You can find the port number by going into SQL Server Configuration Manager and looking at the properties for each SQL instance installed:

In the Select Data Synchronization page of the wizard, you will notice that Automatic Seeding is not available for Basic AG setup.  Select whichever option for synchronization that is appropriate for your environment.  Full requires a shared network location that is accessible from each replica.  Join Only requires that you restore your database(s) backups on the secondary replica prior to joining the databases to the AlwaysOn group.

Click Next through the remainder of the wizard to validate and create the AlwaysOn Group:

Create Basic Availability Group using TSQL

If you prefer to use TSQL statements to create your AlwaysOn group instead of using the wizard, click here https://docs.microsoft.com/en-us/sql/t-sql/statements/create-availability-group-transact-sql   for more detailed information.

CREATE AVAILABILITY GROUP SQLRxBAG
   WITH (  
      BASIC,
	  AUTOMATED_BACKUP_PREFERENCE = SECONDARY,  
      FAILURE_CONDITION_LEVEL = 3,   
      HEALTH_CHECK_TIMEOUT = 600000,
	  DB_FAILOVER = ON,
	  DTC_SUPPORT = NONE
       )  

   FOR   
      DATABASE AW2016   

   REPLICA ON   
      'SQLRXDEV1\SQLSTANDARD' WITH   
         (  
         ENDPOINT_URL = 'TCP://SQLRXDEV1:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC,  
         BACKUP_PRIORITY = 50,  
         SESSION_TIMEOUT = 15  --10 seconds is default
         ),   

      'SQLRXDEV2\SQLSTANDARD' WITH   
         (  
         ENDPOINT_URL = 'TCP://SQLRXDEV2:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC,  
         BACKUP_PRIORITY = 50,  
         SESSION_TIMEOUT = 15  
         );
GO  
--https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql 

--Add Listener
USE [master]
GO
ALTER AVAILABILITY GROUP [SQLRxBAG]
ADD LISTENER N'BasicListener' (
WITH DHCP ON (N'10.0.0.0', N'255.255.255.0'), PORT=62234);
GO

--Join secondary replica to AG
--to be run on the secondary instance
ALTER AVAILABILITY GROUP SQLRxBAG JOIN;  

--Join database to AG 
--to be run on the secondary instance
--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'SQLRxBAG'
	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 [AW2016] SET HADR AVAILABILITY GROUP = [SQLRxBAG];

GO

Conclusion

Basic Availability Groups provide an added layer of disaster recovery protection in your SQL environment.    Deploying a BAG is beneficial when you have one database that needs to be protected in multiple ways.  This should not be used as your only method of DR, but is a reliable way to help protect your data in the event of disaster to your primary production server.  Another benefit is definitely the cost saving because it is available with SQL Server Standard edition, and does not require duplicate processing power on the secondary replica.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!