How to Refresh DB in High availability and open DB later after applying Tlog

Question

How to Refresh DB in High availability and open DB later after applying Tlog

santosh.mane

SSChasing Mays

Points: 611
More actions
October 20, 2020 at 10:13 am

#3799182

Hi,

We are planning to migrate our SQL server 2012 production database to Azure cloud.
The database size is 3 TB, to save downtime on go-live day we are planning to restore full DB backup and incremental DB backup in advance on Destination DB without opening DB in High availability. On go-live day only the latest transaction logs are applied and database is open on both primary and secondary nodes.
Note : We are using TDE.
It will be great if you please guide me urgently by providing document or links which will help me to open DB with minimum downtime.
Thanks & Regards,
Santosh.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27056 More actions · Answer 1

I'm probably missing some details, but if you have an availability group, make the on-prem the primary, and the Azure instances the secondary(s).

When the cutover needs to occur, fail it over to the Azure instance.

After everything checks out, remove the on-prem server from the availability group.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

If you are migrating to a new system - that is not part of a cluster.

Restore full backup to new server - with no recovery, on day of migration
Perform differential backup prior to migration - how long prior will depend on how long the backup takes, how long it takes to transfer the file to the new system and how long it takes to restore.
Restore differential to new server - with no recovery
Copy transaction log backups from old server to new server - from the differential to current, restore with no recovery
On old server - at cutover, perform tail-log backup of the database. This will be the final transaction log backup and also takes the old database offline.
Restore final transaction log backup on new server - with recovery
Switch connections to use new server
Test and validate new system is working as expected

This is not a detailed outline - but the idea is to reduce the amount of time it takes to get to a point where you can recover the database. It all depends on how long it takes to get the database backup file over to the new system and how long it takes to restore it. You may need to perform multiple differentials...and copy/restore those over to the new system.

No matter what...make sure you document the steps you are going to take and test the process as many times as you can fit into your timeline.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

santosh.mane SSChasing Mays Points: 611 More actions · Answer 3

Thank you for taking time and replying , i will check this option.

stephen.aa SSC Eights! Points: 815 More actions · Answer 4

Jeffrey:

I have to be honest, over the last 90 days I have received over 500 emails from you on these posts. How is this possible?

Steve Anderson

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

No idea...messages are not coming from me, but from the topics you have subscribed. Some of the messages have many, many, many posts and if you are subscribed to one of those you would get multiple emails.

Either way - they are not coming from me...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs