In my environment, I have Db01 and Db02 setup with SQL 2012 AlwaysOn. Db01 is the preferred master as that is where all of the backup jobs reside. Typically, the only time that Db01 is unavailable is during maintenance period of installing Windows Update.

Is there a way to always have Db01 be the primary when it is a member of the cluster? Currently, when Db01 rejoins the cluster after a reboot, it is marked as the secondary, since Db02 has become the primary. I would like to see Db01 join the cluster and then automatically (or via a command executed on startup) be designated primary.

Db01 and Db02 are both set to SYNCHRONOUS_COMMIT and Automatic Failover Mode

  • Well once you join Db01 to cluster you just need to failover and it will be primary. – Kuba Miazek Mar 19 '14 at 7:24
  • @Kuba Right, but I'm looking for that to be automated, not manual – Jim Geurts Mar 19 '14 at 17:54
  • Maybe system trigger with powershell etc will help you out – Kuba Miazek Mar 20 '14 at 7:58

You need to set that in the AG ROLE properties as below :

Windows server 2012

enter image description here

  • 1
    Thought I had set the preferred owners in the past, but they were both checked again. I've unchecked Db02 from the preferred owners and hopefully it takes this time. – Jim Geurts Mar 19 '14 at 4:20
  • 1
    The reason you thought you set them before (you probably did) and it reverted can be found here: blogs.msdn.com/b/alwaysonpro/archive/2014/02/28/…. These shouldn't be modified directly within WSFC. – Thomas Stringer Mar 19 '14 at 13:49
  • @ThomasStringer Based on that, would I be further ahead to mark DB01 as automatic failover and DB02 as manual, to achieve the desired behavior? – Jim Geurts Mar 19 '14 at 17:53

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.