Orion Availability Groups

edycus

I have been asked to roll out Orion with SQL Availability Groups one active site on-premise and a passive site in the DR site.

This discussion is just related to letting me know any pros or cons for using SQL HA Availability Groups for the Orion database?

If you are using HA AG is your environment let me know your experience and any issue to watch out for.

Thanks

Eric

mesverrum

Orion has a pretty write intensive database with a nonstop stream of small inserts, and SQL AAG forces you to use full recovery model in SQL, which is slower as all write operations have to be pushed to all members of the cluster and each member needs to write the transaction to their log and send back a confirmation before the transaction can finish.  You basically have added your WAN network latency to every insert, plus you are effectively limited by your slowest member server's storage.  It's definitely a measurable reduction in performance, but the kinds of clients I have who run AAG also tend to have pretty highly performant database infrastructure to begin with so their infrastructure can handle the delay and still keep the system within tolerable, but not great, performance.

The plus side should be fairly obvious, with and AG if you lose your active SQL server then all the other cluster members already have synchronized data so they can pick up and failovers can happen pretty quickly, in many cases you wouldn't even notice the issue within the web console, or if you do see an error it is resolved by the time you refresh your page.  If you are doing really fancy stuff like ETL shipping historic stats to a data warehouse then you can set up a readable secondary so you can pull all your reportable data from the secondary without negatively impacting the experience of users on the "live" system, but most people aren't using those kinds of advanced setups.