Install and configure SQL AlwaysON AG with Listener on Azure

AlwaysOn Configuration - Initial Creation - Full

This can be FullJoin Only or Skip initial data synchronisation. The processes are documented below.
enable_always_on_step_14
Select Full and browse to the UNC path where you want to place the temp backup of the DB. Click Next
enable_always_on_step_15
A Validation runs. If there are no errors, click Next
enable_always_on_step_16
Here you can export the script. If you want multiple Availability groups, it might just be quicker to script the process. Click Next

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect SQL-NODE-A

USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
 
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 [domain\svc-name-acc]
GO
 
:Connect SQL-NODE-B
 
USE [master]
GO
 
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
 
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 [domain\svc-name-acc]
 
GO

:Connect SQL-NODE-A
 
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 SQL-NODE-B
 
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 SQL-NODE-A
 
USE [master]
 
GO
 
CREATE AVAILABILITY GROUP [SQL-CLUSTER-ag-01]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [Test_AlwaysON_AG_DB-Full]
REPLICA ON N'SQL-NODE-A' WITH (ENDPOINT_URL = N'TCP://SQL-NODE-A.domain.local:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
    N'SQL-NODE-B' WITH (ENDPOINT_URL = N'TCP://SQL-NODE-B.domain.local:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
 
GO
 
:Connect SQL-NODE-B
 
ALTER AVAILABILITY GROUP [SQL-CLUSTER-ag-01] JOIN;
 
GO
 
:Connect SQL-NODE-A
 
BACKUP DATABASE [Test_AlwaysON_AG_DB-Full] TO  DISK = N'\\SQL-NODE-A\SQLBackup\Test_AlwaysON_AG_DB-Full.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
 
GO
 
:Connect SQL-NODE-B
 
RESTORE DATABASE [Test_AlwaysON_AG_DB-Full] FROM  DISK = N'\\SQL-NODE-A\SQLBackup\Test_AlwaysON_AG_DB-Full.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
 
GO
 
:Connect SQL-NODE-A
 
BACKUP LOG [Test_AlwaysON_AG_DB-Full] TO  DISK = N'\\SQL-NODE-A\SQLBackup\Test_AlwaysON_AG_DB-Full_20160622104328.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
 
GO
 
:Connect SQL-NODE-B
 
RESTORE LOG [Test_AlwaysON_AG_DB-Full] FROM  DISK = N'\\SQL-NODE-A\SQLBackup\Test_AlwaysON_AG_DB-Full_20160622104328.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
 
GO
 
:Connect SQL-NODE-B
 
 
-- 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'SQL-CLUSTER-ag-01'
    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 [Test_AlwaysON_AG_DB-Full] SET HADR AVAILABILITY GROUP = [SQL-CLUSTER-ag-01];
 
GO
 
GO

enable_always_on_step_17
Once that has completed. Click Close.
enable_always_on_step_18
The databases now reside on both all nodes and display (Synchronized)

Leave a Reply

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

*
*