How to use async AG to test a migration without doing a failover?
irl last edited by
I want to use an async AG to test SQL server migration on SQL server 2017 standard edition. There are 50 databases so the plan is to configure 1 AG per db in the async mode without WSFC.
To test the migration works OK I want to deactivate the AG and activate the databases on the server to test that my apps can connect to the database and everything runs OK.
However, the only option I see in AG is to do the failover but this is not exactly what I want. I want the primary to remain the primary. And the secondary to be a separate sql server on which I can test my applications. Is this possible using an AG?
If it's a "one timmer", you could simply remove your AGs (which will leave all of the secondary node DBs in a "in recovery" state).
From there, you can run backup with recovery for each DB to bring them back online.
If you want to keep your AGs, you'll need to remove the DBs from the AGs (which should leave the DB "in recovery" as well) and once you are done with your test, re-add the DBs in the AGs.
You cannot have 2 nodes with "writable" DB at the same time, so you can't leave the DBs in the AG and have them "primary" on the primary node and "writable" on the secondary node (to test) at the same time.