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
Right click Databases > Restore Database
Click Device and the browse button.
Click Add
Add the UNC path for the filename. Click OK
Click OK
The information has been populated
Click on Options and make sure RESTORE WITH NORECOVERY has been selected as the Recovery State
Click on OK to restore. Click on OK to complete.
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.
Right click the Availability group previously created. Click on Add Database
Skip this page, click Next
Select the database you restored. Click Next
Click on Join Only. Click Next
We need to connect to all secondary replicas. Click on Connect
Enter the server you wish to connect to. Click Connect
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
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
Once that has completed. Click Close.
The databases now reside on both all nodes and display (Synchronised)