Install and configure SQL AlwaysON AG with Listener on Azure

AlwaysOn Configuration - Initial Creation - Join

As we already have an Availability group, we don’t need to create one. We just need to restore a database. This can also be done from any of the replica nodes
enable_always_on_step_19
Right click Databases > Restore Database
enable_always_on_step_20
Click Device and the browse button.
enable_always_on_step_21
Click Add
enable_always_on_step_22
Add the UNC path for the filename. Click OK
enable_always_on_step_23
Click OK
enable_always_on_step_24
The information has been populated
enable_always_on_step_25
Click on Options and make sure RESTORE WITH NORECOVERY has been selected as the Recovery State
enable_always_on_step_26
Click on OK to restore. Click on OK to complete.
enable_always_on_step_27
Here you can see the restored database is sitting in a restoring state. (Restoring…). This allows us to still edit and or make changes to the database.

Now we need to add the database to the Availability Group.
enable_always_on_step_28
Right click the Availability group previously created. Click on Add Database
enable_always_on_step_29
Skip this page, click Next
enable_always_on_step_30
Select the database you restored. Click Next
enable_always_on_step_31
Click on Join Only. Click Next
enable_always_on_step_32
We need to connect to all secondary replicas. Click on Connect
enable_always_on_step_33

Enter the server you wish to connect to. Click Connect
enable_always_on_step_35
Click Next
enable_always_on_step_36
A Validation runs. If there are no errors, click Next
enable_always_on_step_37
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

ALTER AVAILABILITY GROUP [SQL-CLUSTER-ag-01]
ADD DATABASE [Test_AlwaysON_AG_DB-Join];

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-Join] SET HADR AVAILABILITY GROUP = [SQL-CLUSTER-ag-01];

GO


GO

enable_always_on_step_38
Once that has completed. Click Close.
enable_always_on_step_39
The databases now reside on both all nodes and display (Synchronised)

Leave a Reply

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

*
*