Assume customer comes and say they want to refresh database companytest database from companyprod database.
An existing AlwaysOn Availability Group with at least one
Primary[Win2k12-2] and one Secondary replica [Win2k12-3] instance.
A TDE encrypted database on the same instance as the primary replica,
online and accessible.
A Database Master Key on all replica servers hosting the availability
group (the primary will already have one since it has a TDE encrypted
database).
Here:-
Win2k12-2 = Primary replica
Win2k12-3 = Secondary replica
MyTestAg = Availability group Name
\\PDOMAINDC\AlwaysOnBackup = Backup shared folder..
and Task here is to refresh CompanyTest database from CompanyProd Both
Databases have TDE already Enabled.
The broad steps which we need to carry out to meet our requirement is
as:-
- Remove
a Secondary Database from Availability Group
- Remove
the specified primary database and the corresponding secondary databases
from the availability group.
- Drop
database from a secondary replica
- Take
full backup of companyprod from primary replica and restore into
compantest database on primary replica with norecovery
- Take
Transaction log backup of companyprod from primary replica and restore
into compantest database on primary replica with recovery.
- On
primary replica we specify user database ‘compnaytest’ as we want to add
to the availability group.
- Again,
take Fullbackup of companytest from primary replica with copy_only
option.
- Restore
companytest to secondary replica in norecovery mode
- Take
T-log backup of companytest from primary replica
- Restore
T-log backup of companytest to secondary replica in norecovery mode
- Join
the secondary database ‘companytest’ to the local secondary replica.
- Remove a Secondary Database from Availability Group
: connect WIN2k12-3
Use master
Go
ALTER DATABASE
[companytest] SET HADR OFF;
GO
- Remove the specified primary database and the corresponding secondary databases from the availability group.
: connect Win2k12-2
USE [master]
GO
ALTER AVAILABILITY
GROUP [MyTestAg] REMOVE DATABASE [companytest];
GO
: connect WIN2k12-3
drop database
companytest
GO
: Connect WIN2K12-2
BACKUP DATABASE
[companyprod] TO DISK =
N'\\PDOMAINDC\AlwaysOnBackup\companyprod_full.bak' WITH COPY_ONLY, FORMAT, INIT, COMPRESSION, STATS = 1
GO
RESTORE DATABASE
[companytest] FROM DISK =
N'\\PDOMAINDC\AlwaysOnBackup\companyprod_full.bak' WITH RECOVERY,replace
, NOUNLOAD, STATS = 5,
move
N'AdventureWorks2014_Data' to N'C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\companytest.mdf',
move
N'AdventureWorks2014_Log' to N'C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\companytest_log.ldf'
GO
: Connect WIN2K12-2
USE [master]
GO
ALTER AVAILABILITY
GROUP [MyTestAg] ADD DATABASE [companytest];
GO
: Connect WIN2K12-2
BACKUP DATABASE
[companytest] TO DISK =
N'\\PDOMAINDC\AlwaysOnBackup\companytest_full_Again.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND,
NOUNLOAD, COMPRESSION, STATS = 5
GO
: Connect WIN2K12-3
RESTORE DATABASE
[companytest] FROM DISK = N'\\PDOMAINDC\AlwaysOnBackup\companytest_full_Again.bak'
WITH NORECOVERY, NOUNLOAD, STATS = 5
GO
: Connect WIN2K12-2
BACKUP LOG
[companytest] TO DISK =
N'\\PDOMAINDC\AlwaysOnBackup\companytest_Again_1.trn' WITH NOFORMAT, INIT,
NOSKIP, REWIND, NOUNLOAD, COMPRESSION,
STATS = 5
GO
: Connect WIN2K12-3
RESTORE LOG
[companytest] FROM DISK =
N'\\PDOMAINDC\AlwaysOnBackup\companytest_Again_1.trn' WITH NORECOVERY,
NOUNLOAD, STATS = 5
GO
-- Wait for the replica to
start communicating
: Connect WIN2K12-3
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'MyTestAg'
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 the
execution of the alter database statement
end catch
Join the secondary database
‘companytest’ to the local secondary replica.
ALTER DATABASE
[companytest] SET HADR AVAILABILITY GROUP = [MyTestAg];
GO
Thanks for reading..
and
How to add a TDE encrypted database to an Availability Group
Thanks for reading..
and
How to add a TDE encrypted database to an Availability Group
refer this hyper link..
