SQL Publisher/Subscriber mode

Does anyone have any experience with running ARS in a distributed architecture? I'm currently testing a viable solution and have a few questions. 

1) I have run into an issue with syncing the History database. Our lab history database is 50gb and the initial sync is not complete before the next snapshot is created. This causes the sync to fail. History database on our production environment is 83gb. Is there a better way to do the initial sync? Can the database be backed up, copied across the WAN to the target SQL server, brought online, joined to the local ARS Server and then set as a sync database? Will any changes in the master DB be synchronized or will the file be re-created? 

2) Due to number 1 I configured ARS to use Config DB in Publisher/Subscriber mode and History DB pointing to a single server. The issue with this solution is the MMC is unusable if the History DB is lost or unavailable. Is there a way to tell ARS to ignore the History DB? Or would a blank/fresh database be needed until the master is back online? 

We have users spread across the globe and i'd like to split our ARS infrastructure across 3 continents. 1 x Americas, 1 x EMEA, 1 x APAC.  Mainly for speed where I can point each local ARS server to it's closest SQL server, as well as redundancy in case of failures.

Parents
  • 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:
    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!

Reply
  • 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:
    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!

Children