I have been working with an Availability Group with an extra asyncronous secondary, that is always set to manual failover.

I am worried that if I ran:

Get-DbaAgReplica -SqlInstance x -AvailablityGroup y | Set-DbaAgReplica -FailOver Automatic

it would then set all the sqlinstances to auto failover, I was wondering if you can ignore an instance?

  • Hi, I am not 100% with you on the extra database part. Do you want to filter out a database, or that secondary async replica? You can always add -WhatIf to see what would happen. – Randi Vertongen Mar 27 '19 at 15:47
  • sorry @RandiVertongen I've updated the question, you are correct I meant sqlinstance (i think) – Luke Duddridge Mar 28 '19 at 16:17

The command works at the Availability Group level - not database level. Therefore ALL databases that have been added to that Availability Group will failover at the same time. You cannot have some of them on the Primary and some of them on the Secondary.

If you need to have different databases failover at different times then put them in different Availability Groups.

So NO, it doesn't set all the databases to auto-failover, it sets the Availability Group to auto-failover.

  • sorry @martin, I just realised I said databases, I meant database servers, sorry for the confusion. i'll update the question – Luke Duddridge Mar 28 '19 at 16:14
  • Again, @LukeDuddridge - it is the AG level .... not the database or database server. Think of this as an intermediate scope - less than or equal to all of the databases on a specific database server. All the databases in the AG will fail over together, but other databases on that server that are not in the AG will remain where they are. – Martin Cairney Apr 29 '19 at 7:08
  • ok,I think I am having trouble conveying what is happening, as I can tell from you starting your comment with "again", means you think I am being a complete idiot? We have 3 servers in the HA, 1 is currently for disaster recovery, so should not be used as a failover option, a copy of the databases in the AG replica (for TFS in this instance) need to be transmitted to the disaster recovery server, which is why it has been set to manual and async on the replication. I want to ignore this box when I change the state of the failover type. have I missed anything or am I not making sense? – Luke Duddridge May 2 '19 at 16:01
  • Hi Luke. Each AG replica can be defined separately as manual/automatic, so yes you should be able to define that the 2 servers involved in the AG for HA can be changed independently. Your command "Get-DbaAgReplica" is only impacting the single replica that you specify so the other will not be affected. – Martin Cairney Jul 25 '19 at 2:13
  • its ok I found the work around, had to filter it out of the list, i could identify the offsite secondary by name – Luke Duddridge Mar 18 '20 at 9:37

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.