1

I am setting up AlwaysOn availability group for Disaster Recovery (DR) in the test environment. For AG1, node1 and node2 (synchronous commit/automatic failover) are in DC1; node 3 and node 4 (Asynchronous commit/manual failover) are in DC2 and file share witness is in DC3. When DR event occurs, 2 nodes in DC1 will be gone. Since DR nodes are set up as an Asynchronous commit, AG status is resolving. I will need to failover manually to bring AG group to a synchronized state. I want to use powershell to do failover with data loss. When I run the following powershell to failover, I am getting the error.

Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\SQLDRPOCA03\DEFAULT\AvailabilityGroups\SQLDRPOCAG

Switch-SqlAvailabilityGroup : The availability replica for availability group 'SQLDRPOCAG' on this instance of SQL Server cannot become the primary replica. One or more databases are not synchronized or have not joined the availability group.

If the availability replica uses the asynchronous-commit mode, consider performing a forced manual failover (with possible data loss). Otherwise, once all local secondary databases are joined and synchronized, you can perform a planned manual

failover to this secondary replica (without data loss). For more information, see SQL Server Books Online.

Is it possible to use powershell to do manual failover?

  • This looks like it needs an sql server tag of some sort – Joe W Jul 29 at 16:49
1

I think what you are trying to achieve via powershell is for planned manual failover as mentioned in msdn link. here

A planned manual failover is supported only when the primary replica and the target secondary replica are running in synchronous-commit mode and are currently synchronized. A planned manual failover preserves all the data in the secondary databases that are joined to the availability group on the target secondary replica

And what you exactly are trying to achieve " failover between async replicas " is more of forced manual failover with/without data loss which can be achieved via TSQL as mentioned in same msdn link. I don't see PS there but may be someone here who have used can answer. I am not sure but may be you can check dbatools if it provides one

Go through the steps as mentioned laster in that article

Manual failover without data loss

Use this method when the primary replica is available, but you need to temporarily or permanently change the configuration and change the SQL Server instance that hosts the primary replica. To avoid potential data loss, before you issue the manual failover, ensure that the target secondary replica is up to date.

| improve this answer | |
  • I was able to failover to DR node(Asynchronous commit) using the following PS Script. Thank you for your post. – AMMH Jul 29 at 16:18
0

Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\SecondaryServer\InstanceName\AvailabilityGroups\MyAg
-AllowDataLoss -Force

I was able to failover to a DR node alwayson availablity group (Asynchronous commit) using the above PS script. it is forced mannual failover with Data Loss.

Thank you for your feedback on the question. AMMH

| improve this answer | |

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.