Install and configure SQL AlwaysON AG with Listener on Azure

AlwaysOn Configuration - Initial Creation - Full

This can be Full, Join Only or Skip initial data synchronisation. The processes are documented below.

Select Full and browse to the UNC path where you want to place the temp backup of the DB. Click Next

A Validation runs. If there are no errors, click Next

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

Once that has completed. Click Close.

The databases now reside on both all nodes and display (Synchronized)