Create an empty AlwaysOn Availability Group

Last week I had to install a 6 node failover cluster at a customer. 4 nodes were used for high availability solution in site A and 2 nodes were in site B for disaster recovery. Each site was hosting 9 SQL Server instances.

On my last day, I had to configure the AlwaysOn Availability Groups (AVG) as disaster recovery solution.  I have chosen the following settings for my AVG’s

  • Asynchronous commit mode
  • Backups on primary replica
  • Readable secondary

There was just one small “problem”. There were no databases to include in the AVG ‘s because this was a complete new platform. I couldn’t use the AlwaysOn Availability Group Wizard because that expects a database. But I wanted to prepare the AVG’s in advance so that the customer only needed to add the databases to the groups at a later stage.
Since I had to install 9 AVG’s I created a script that I could reuse every time I want to create an empty availability group.
The script will create an Availability Group on the primary replica, will add a group listener and will join one secondary replica all in one execution.

You can find the script below but I must mention a few remarks and restrictions

  • You have to execute the script in the SQLCMD mode in the SQL Server Management Studio. To do this go in the main menu to Query –> SQLCMD Mode
  • Only one secondary replica can be used
  • The settings for the replica’s are the one mentioned above
  • Only one IP address can be linked to the group listener
  • Named instances are supported

Before you can execute the script you only need to specify a few parameters:

Parameter Description
PrimaryReplica The primary replica. Default and Named instances are supported eg. ServerInst01
SecondaryReplica The secondary replica. Default and Named instances are supported eg. ServerInst01
EndPointPort Specify the port for the endpoints
AvailabilityGroup The name of the availability Group
Listener The name of the group listener
IPListener The IP address of the listener
ListenerPort Specify the listener port
ListenerSubnet Specify the subnet of the listerner IP Address

You can find my script below. If you should have questions, remarks or suggestions, please do leave a comment so that I can improve it.

Have fun!

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:setvar PrimaryReplica Node1 --Specify the primary replica
:setvar SecondaryReplica Node2 --Specify the secondary replica
:setvar EndPointPort 5022 --Specify the port for the endpoints
:setvar AvailabilityGroup AVG_Group --Specify the name of the availability group
:setvar Listener AGL --Specify the name of the group listener
:setvar IPListener 192.168.0.20 --Specify the IP Address of the group listener
:setvar ListenerPort 1433 -- Specify the listener port
:setvar ListenerSubnet 255.255.240.0 --Specify the subnet of the listeren IP Address

:Connect $(PrimaryReplica)

USE [master]
GO
declare @AVGLogin as sysname
declare @CMD nvarchar(max)

select @AVGLogin=service_account from sys.dm_server_services
where servicename like 'SQL Server (%'

if not exists(select name from sys.syslogins where name=@AVGLogin)
begin
 set @CMD=N'CREATE LOGIN [' + @AVGLogin + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
 print 'Create service account as server login...'
 exec sp_executesql @CMD
end

if not exists (select name from sys.endpoints where name='Hadr_endpoint')
begin
print 'Create HA/DR endpoint...'
CREATE ENDPOINT [Hadr_endpoint] 
 AS TCP (LISTENER_PORT = $(EndPointPort))
 FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
end

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
 ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END


set @CMD=N'GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [' + @AVGLogin + ']'
print 'GRANT CONNECT ON ENDPOINT...'
exec sp_executesql @CMD

GO

:Connect $(SecondaryReplica)

USE [master]
GO

declare @AVGLogin as sysname
declare @CMD nvarchar(max)

select @AVGLogin=service_account from sys.dm_server_services
where servicename like 'SQL Server (%'

if not exists(select name from syslogins where name=@AVGLogin)
begin
 set @CMD=N'CREATE LOGIN [' + @AVGLogin + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
 print 'Create service account as server login...'
 exec sp_executesql @CMD
end

if not exists (select name from sys.endpoints where name='Hadr_endpoint')
begin
print 'Create HA/DR endpoint...'
CREATE ENDPOINT [Hadr_endpoint] 
 AS TCP (LISTENER_PORT = $(EndPointPort))
 FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
end

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
 ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END

set @CMD=N'GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [' + @AVGLogin + ']'
print 'GRANT CONNECT ON ENDPOINT...'
exec sp_executesql @CMD

GO

:Connect $(PrimaryReplica)

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 $(SecondaryReplica)

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 $(PrimaryReplica)

USE [master]
GO

declare @SystemAddressPrimary nvarchar(256)
declare @SystemAddressSecondary nvarchar(256)

if charindex('','$(PrimaryReplica)')=0
 set @SystemAddressPrimary='$(PrimaryReplica)'
else
 select @SystemAddressPrimary=substring('$(PrimaryReplica)',0,charindex('','$(PrimaryReplica)'))

if charindex('','$(SecondaryReplica)')=0
 set @SystemAddressSecondary='$(SecondaryReplica)'
else
 select @SystemAddressSecondary=substring('$(SecondaryReplica)',0,charindex('','$(SecondaryReplica)'))

declare @CMD nvarchar(max)
set @CMD='
CREATE AVAILABILITY GROUP [$(AvailabilityGroup)]
WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY)
FOR 
REPLICA ON N''$(PrimaryReplica)'' WITH (ENDPOINT_URL = N''TCP://' + @SystemAddressPrimary + ':$(EndPointPort)'', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)),
 N''$(SecondaryReplica)'' WITH (ENDPOINT_URL = N''TCP://' + @SystemAddressSecondary + ':$(EndPointPort)'', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY));'

print 'Create the availability group...'
exec sp_executesql @CMD

print 'Create the availability group listener...'
ALTER AVAILABILITY GROUP [$(AvailabilityGroup)]
ADD LISTENER N'$(Listener)' (
WITH IP
((N'$(IPListener)', N'$(ListenerSubnet)')
)
, PORT=$(ListenerPort));

GO

:Connect $(SecondaryReplica)

ALTER AVAILABILITY GROUP [$(AvailabilityGroup)] JOIN;

GO
3 comments on “Create an empty AlwaysOn Availability Group
  1. Hi,

    Any tips for working around multiple instances, and instances with multiple availability groups?

    We are constantly finding we get:

    sg 35237, Level 16, State 1, Line 116
    None of the specified replicas for availability group “xxx1” maps to the instance of SQL Server to which you are connected. Reenter the command, specifying this server instance to host one of the replicas. This replica will be the initial primary replica.
    Msg 15151, Level 16, State 1, Line 141
    Cannot alter the availability group ‘xxx1’, because it does not exist or you do not have permission.

    Thanks

    • Hi Dave,

      Not clear what you exactly mean? Can you explain a little bit more please?

      Regards
      Pieter

Leave a Reply

Your email address will not be published. Required fields are marked *