• State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 11 replies
  • Subscribers 63 subscribers
  • Views 3281 views
  • Users 0 members are here
Options
Related

Looking for a way to manage ARS replication roles using powershell | Standalone < > Publisher < > Subscriber

pramkris
over 2 years ago

I found this to promote using powershell but does not talk about transitioning to other roles:  https://support.oneidentity.com/active-roles/kb/311101/how-to-promote-an-active-roles-publisher-using-powershell

Please advise if this is even possible using powershell and if so I'd appreciate some pointers.

Thanks,

Parents
  • 0 over 2 years ago

    I have been recommending SQL Always On / Clustering for Active Roles HA / DR for a few years now.

    The biggest day-to-day benefit IMO has to do with the ease of performing Active Roles upgrades.  Publisher / Subscriber forces you to break the relationship in order to perform the upgrade which is a pain and tends to require longer outage windows. 

    Always On is not without its quirks of course but it does make the above (typically annual) scenario much easier. 

Reply
  • 0 over 2 years ago

    I have been recommending SQL Always On / Clustering for Active Roles HA / DR for a few years now.

    The biggest day-to-day benefit IMO has to do with the ease of performing Active Roles upgrades.  Publisher / Subscriber forces you to break the relationship in order to perform the upgrade which is a pain and tends to require longer outage windows. 

    Always On is not without its quirks of course but it does make the above (typically annual) scenario much easier. 

Children
  • 0 over 2 years ago in reply to JohnnyQuest

    Sure. Thanks.

    Let me revisit the SQL Always On with our internal teams.

    As for my original question, is there a way to achieve that using powershell reliably? 

  • 0 over 2 years ago in reply to pramkris

    Hi  

    I'm personally not aware of a method to automatically fail over the Publisher role between SQL servers, as its more complex that just changing a flag from "Subscriber" to "Publisher", its a similar (if not slight more complex) scenario to moving (more to the point seizing) a FSMO role in AD,

    At a high level the steps (without best practices like backups) would be:

    1) Break all subscribers our of the Pub / Sub topology, making them all standalone

    2) Promote one of the standalone instances to be a publisher (which would create a new distributor DB)

    3) Add subscribers

    4) Clean up original SQL server acting as Publisher

    So for temporary or planned downtime for your Publisher, I'd personally set user/customer expectations rather than "fail over". The effort and risk  involve outweighs the benefit. Pub Sub is more a DR solution, where you can add HA with say SQL clustering.

    I could suggest (as  has mentioned) , SQL Always on would be my preferred HA and DR solution for 99% of customers.

  • +1 over 2 years ago in reply to pramkris

    I'll chime in:

    No: there is no easy method using the Active Roles Management Shell to trigger change a SQL publisher to a subscriber or vice-versa.

    This is not possible for a number of reasons, but most importantly:

    Microsoft SQL doesn't support any easy method to change roles when using SQL Merge Replication. A publisher is a publisher and a subscriber is a subscriber until a DBA breaks replication and re-creates it in a different configuration. This is an impactful change and not a simple change of roles. If not done properly, there is a high risk of data loss.

    In simple terms, SQL Merge Replication does not offer any real-time redundancy options for the Publisher. If the Publisher is down, changes can be committed into the Subscriber databases and will only be replicated when the Publisher has been restored or when the DBA has manually intervened.