This is the script to include database in existing Always on availability group by passing below parameters.
1. Primary server name, port number
2.Secodnary server name , port number
3. Database name
4.Availability group name
5.Shared path on primary server with read and write access for dba id required to take backup from primary server for HADR configuration.
6. This script to be executed in SQLCMD only.
Hope adding database to an existing Always on availability group can be done in TSQL script.
If more number of databases to be added in Always on availability group, We can do it by repeating above steps in Sql server management studio new query window .
1. Primary server name, port number
2.Secodnary server name , port number
3. Database name
4.Availability group name
5.Shared path on primary server with read and write access for dba id required to take backup from primary server for HADR configuration.
6. This script to be executed in SQLCMD only.
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect Primary-Server_Name,5008
USE [master]
GO
ALTER AVAILABILITY GROUP [Availability group NAME]
ADD DATABASE [Database Name];
GO
:Connect Primary-Server_Name,Port_Number
BACKUP DATABASE [Database Name] TO DISK = N'\\Primary-Server_Name\Always_on_Backup
\Database Name.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD,
COMPRESSION, STATS = 5
GO
:Connect Secondary-Server_Name,Port_Number
RESTORE DATABASE [Database Name] FROM DISK = N'\
\Primary-Server_Name\Always_on_Backup\Database Name.bak' WITH NORECOVERY, NOUNLOAD,
STATS = 5
GO
:Connect Primary-Server_Name,Port_Number
BACKUP LOG [Database Name] TO DISK = N'\\Primary-Server_Name\Always_on_Backup
\Database Name_20170609165021.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND,
NOUNLOAD, COMPRESSION, STATS = 5
GO
:Connect Secondary-Server_Name,Port_Number
RESTORE LOG [Database Name] FROM DISK = N'\\Primary-Server_Name\Always_on_Backup
\Database Name_20170609165021.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
:Connect Secondary-Server_Name,Port_Number
-- 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'Availability group 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 [Database Name] SET HADR AVAILABILITY GROUP = [Availability group NAME];
GO
GO
