How to Add Database in AlwaysOn Availability Group - using TSQL Script
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect <Primary Replica>
USE [master]
GO
/* Here we are Altering Availability group on primary replica and telling primary replica to add that database to Existing Availability group. */
ALTER AVAILABILITY GROUP [AG-Name] ADD DATABASE [TestAG];
GO
:Connect <Primary Replica> /* Take Full backup of Database */
BACKUP DATABASE [TestAG] TO DISK = N'\\backupServerName\Alwayson\TestAG.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
:Connect <Secondary Replica> /*Connect to First secondary Replica and restore database --there*/
RESTORE DATABASE [TestAG] FROM DISK = N'\\backupServerName\Alwayson\TestAG.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect <Connect to second secondary replica>
RESTORE DATABASE [TestAG] FROM DISK = N'\\backupServerName\Alwayson\TestAG.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect <Connect to Third Secondary Replica>
RESTORE DATABASE [TestAG] FROM DISK = N'\\backupServerName\Alwayson\TestAG.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect <Connect to primary Replica> /*For taking log backup */
BACKUP LOG [TestAG] TO DISK = N'\\BackupServerName\Alwayson\TestAG_20150911072538.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
:Connect <First Secondary replica> /* for restore of log file.
RESTORE LOG [TestAG] FROM DISK = N'\\S263642RGVW06\Alwayson\TestAG_20150911072538.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect <Connect to First Secondary replica>
-- 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'AG-Name'
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
/* joins the secondary database, TestAG, to the local secondary replica of the availability group. */
ALTER DATABASE [TestAG] SET HADR AVAILABILITY GROUP = [AG-Name];
GO
:Connect <Second secondary replica>
RESTORE LOG [TestAG] FROM DISK = N'\\backupServerName\Alwayson\TestAG_20150911072538.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect <Second Secondary replica>
-- 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'AGName'
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
/* joins the secondary database, TestAG, to the local secondary replica of the availability group. */
ALTER DATABASE [TestAG] SET HADR AVAILABILITY GROUP = [AG-Name];
GO
:Connect <Third Secondary Replica >
RESTORE LOG [TestAG] FROM DISK = N'\\BackupServer\Alwayson\TestAG_20150911072538.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect <Third Secondary Replica>
-- 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'AG-Name'
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 [TestAG] SET HADR AVAILABILITY GROUP = [AG-Name];
GO
GO