Step by step how to refresh Databases In AlwaysOn if TDE is also enabled

How to refresh Databases In AlwaysOn Availability  if  TDE is also enabled on those databases.

Assume customer comes and say they want to refresh database companytest database from companyprod database.

Prerequsite:-

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

 refer this hyper link..