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

Can you explain a bit more about WHY you want to do this - i.e. what's the end goal or what you do expect to be able to achieve?

I ask because tickling these roles within Active Roles itself is really not very meaningful as the real core engine behind this whole Publisher / Subscriber concept rests within SQL and modifying the role "labels" which is what Powershell is doing will only get you so far.

Thanks for the response.
We need to perform a annual disaster recovery exercise and as part of that we need to move both roles from one datacenter to another, test and revert to original state. So i was wondering if I could script that instead of having to manually switch roles from ARS mmc console.

Hi pramkris 

The below link give you details of the way you'd manually move the publisher role.

Active Roles 7.3.3 - Replication: Best Practices and Troubleshooting (oneidentity.com)

Hope this helps.

Hi Stu.Pollock 

That's more likely doing it using ARS mmc console and or in a force recovery scenario as stated.

I'm looking for a planned failover/ failback option of publisher and subscriber roles between two datacenters using powershell.

Hi pramkris ,

Could I ask a couple of questions if that's ok?

1) How are the failovers between data centre's handled? Is it just a manual and/or scripted process, or do you have special product in place like Site Recovery Manager?

2) Do you know what the rational was for choosing the Publisher Subscriber topology, over using something like SQL Always On or Clustering (etc)?

3) How big is the (on publisher and subscriber(s) SQL Servers:

a) Configuration Database

b) History Database

c) [on publisher] Distributor database size.

Hi Stu.Pollock 

1.  As of today, failover/ failback is manual. We have a custom web portal (used for trigger) and I plan to script the ARS failover/ failback process (if at all supported/ possible).

2.  Not sure really on that.... Been like this for a very long time. I wanted to test SQL Always On option but ran into some internal 'approved/ supported build' type issues.  I think I need to revisit that again now.

3.  They are about 1.5gb and 3gb in size respectively.

Thanks again.

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. 

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? 

Hi pramkris 

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 JohnnyQuest has mentioned) , SQL Always on would be my preferred HA and DR solution for 99% of customers.

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.