Script to add database in existing availability group by Tsql.

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.


--- 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

 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 .

Comments

Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)