1

In our application there is a data conversion process that brings all the legacy data to the production database.

Since our database is on availability group and the migration might bring 100GB + worth of data, my plan was to remove all the secondary replica from the availability group and then set the primary database offline.

Restore the production database into a staging server, then complete the migration/conversion and then restore it back to the Production and syn all the secondary replicas

Please advise if there is a better way to do the migration.

3

my plan was to remove all the secondary replica from the availability group and then set the primary database offline.
Restore the production database into a staging server, then complete the migration/conversion

This seems like a really reasonable plan. You'd likely have lots of issues trying to load 100 GB+ of data to the AG quickly, especially if they are sync replicas. So this avoids the network and redo bottlenecks you would have likely encountered.

and then restore it back to the Production and syn all the secondary replicas

It looks like you're on SQL Server 2014, so this also makes sense - as your only option at this point would be to do "manual seeding" (restore the database and log on the secondary replica).

If you were on SQL Server 2016 or higher, I warn against using "automatic seeding" with such a large amount of data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.