SQL Publisher/Subscriber mode

Hello, Justin.

1. Your question is less about Active Roles, per se, and more about general SQL database practices. My point is simply that you may find more relevant expertise on this in a SQL discussion forum. That said, I did come across a couple of articles that may be relevant:
   • https://docs.microsoft.com/en-us/sql/relational-databases/replication/initialize-a-transactional-subscription-from-a-backup?view=sql-server-2017
   • https://repltalk.com/2019/01/03/deep-dive-on-initialize-from-backup-for-transactional-replication/
2. To the best of my knowledge, there is no way to "ignore" the history DB. Using a blank one of course introduces the fact that you then have some history being stored in this new database with no easy way to get back into your production database when it comes back online.

Overall, it sounds like you may be relying "too heavily" on Change History (which is common). The original design intent was that the event logs, Collector, and report packs would be used for the long-term storage of audit data. That's why, for example, Change History is configured to store a paltry 30-days' worth of data out-of-the-box. Of course, in practice, the Collector has its own challenges and the lesser accessibility of the reporting interface really drives people to rely almost exclusively on Change History.

At the risk of sounding like a Sales rep, the next best option that I can suggest is to use Change Auditor with Active Roles integration enabled. It's much better at storing long-term data. Even that's not perfect, though, since that won't show Active Roles specific content such as workflow results, deprovisioning reports, and virtual attribute content changes.

The SQL replication design is indeed intended to be used exactly in the scenario that you describe. Over the past few years, however, the trend seems to be moving away from this strategy in favour of relying on either AlwaysOn distributed availability groups and/or virtualisation infrastructure to handle the same.

Finally, if you wanted to get intense about it, you could do some analysis of what exactly is contributing to the volume of Change History data. For example, maybe you have the Sync Service performing daily updates so Change History is getting spammed by that activity. If that were the case, then with the right SQL queries, you could selectively purge that content (so, for example, keep Change History for 6 months for all changes, but purge activity initiated by the Sync Service older than 3 months).

Sorry to have been so long-winded. Hope something there is useful!

Cheers,
Shawn.

EDIT: By coincidence, I was just reading some conversations with some of my peers, and one of them (Stu, if you're reading!) was discussing the fact that he has witnessed the exact opposite that I described. Specifically, I had mentioned that my experience had been to see a trend towards moving to AlwaysOn replication groups, whereas he mentions experiencing the exact opposite: an increase towards SQL-based replication. So, take my words with a grain of salt and choose whichever option makes the best sense for you!

Hi Justin

I've done quite a bit with Pub/Sub in the past,  including resolving some issues with a configuration similar to what you're suggesting (one publisher and two subscribers in APAC/EMEA & AMER). But I do agree with Shawn on this.

1) In terms of "pre-staging" the databases on the subscribers. This is not something I've done. But leave that one with me as I'd personally be interested to work out if this is possible (and more importantly supported by us). As I have a customer is a similar boat to you in terms of database size.

However if would be useful for you to work out why your history database is so large, could you confirm your Change Tracking Log Configuration? 

The default is 30 days (Log Settings) and 7 days (Log Record Size). 

Could you also confirm the number of deprovision users you currently have?

As Shawn mentioned, the change tracking is not designed for long terms storage of change history, and other products including Collector (within the ARS install media) or Quest Change Auditor may be a better option for you.

2) Also not aware if this would be possible. I suspect it wouldn't be as a lot of Active Roles functionality requires access to the history database (including things like Deprovision and undo deprovision, and many other things).

There are pro's and con's for the different ways of deploying Active Roles, and would depend on your goals and how the Adminstrative responsibility are split (if at all). Some of the questions to ask yourself would include:

• Do I need high availability in each site (HA), IE clustered
• Does Active Roles need to be highly available (HA/DR)  (located in multiple sites, the solution your looking at)
• What number of users from each region will be using Active Roles
• How many requests (operations) will be Active Roles be expected to handle (by region or data centre)
• Do all regions need to have the same configuration and data stored within Active Roles (IE could you have three seperate deployments of Active Roles with the same configuration, but the data is not replicated between one another via Pub/Sub)?
• Are you going to be using Quick Connect or Synchronization services?

Are you able to share any additional information regarding your requirements?

Cheers

Stu

Hey Stu,

I've been able to get the history DB to synch correctly. In the end it was pretty easy once I knew what to look for.

Changing the SQL Merge Agent default profile to "Slow link agent profile" fixed the timeout issues I was having when setting up the initial synch across the WAN link.

It took 3 or 4 days to get the database to completely synchronize. I've been able to test removing it from the "Master ARS server" and re-attaching which does not have to reproduce the entire SQL DB. It just synchronizes it again.

To answer some of your questions.

We currently have 2318 deprovisioned users, changes on a daily basis :)

Change tracking log is kept at the default of 30 days. We do have Change Auditor in the environment as well but find ARS history is easier for the admins to use.

We do use Quick Connect (needed for OpenLDAP connector) as well ARS Synch. These jobs will stay on the "main" servers in EMEA.

The basis of the idea behind the distributed solution started out due to a slowness in admin tools for admins in APAC and NORAM/LATAM due to the servers (both Web and MMC) being housed in EMEA. After some testing we discovered that it could in theory be used in Disaster Recovery as well, by demoting one of the subscribers into Standalone Mode.

I would have replied sooner but I never got a notification of activity on the thread.

Justin