0

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?

2 Answers 2

2

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.

1

There are 50 databases so the plan is to configure 1 AG per db in the async mode without WSFC.

You cannot have read-scale availability groups ( AG without WSFC) in standard edition. See Features supported by SQL Server 2017. You are better with Basic Availability Groups (BAG) in standard edition.

However, the only option I see in AG is to do the failover but this is not exactly what I want

This may be because you have configured BAG (unknowingly) and BAG does not allow read on secondary replicas. So you need to do a failover to access the secondary. See Limitations of BAG

PS: Make sure your server is adequately provisioned as 50 DB's BAG is something which creates load and needs resources.

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.