Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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

1 Answer 1

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

Windows server 2012

enter image description here

share|improve this answer
    
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 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 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 at 17:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.