SQL Server Always On Availability Groups

Glenn Hill

Member
Has anyone tried configuring E1 using SQL Server Always On Availability Groups (AOAG)?

The official line from Oracle is that it is not supported yet – because they have not tested it. (It was only released five years ago!)
But we cannot see why it should not work.
Perhaps we would have to avoid configuring a listener (although it would be nice if it worked) – but we can see no reason why the standard “database mirroring” and manual failover functionality within AOAG would not work; the CNC configuration and JDE middleware would surely be unaware that AOAG was configured beneath the covers?

(We are currently in the design process of a new installation of E1 9.2 on either SQL 2014 or SQL 2016 – and AOAG offers by far the best options for HA, DR and real-time reporting.)
 

ice_cube210

VIP Member
Reviving this thread because I have the same question.

Glenn, did you end up using E1 with SQL Server AOAG?

Does anyone else have any experiences to share on this topic?

Thanks in advance
 

ice_cube210

VIP Member
Hi Jeff,

That is true, it is a vast topic. I guess I am just looking to understand if the overall experience was positive or negative.
Any overheads on performance and lastly how E1 was impacted during a failover.

Thanks
 
Call Orchestrations From Excel – The Easy Way to Make the Orchestrator Work for You.

brother_of_karamazov

Legendary Poster
Hi Jeff,

That is true, it is a vast topic. I guess I am just looking to understand if the overall experience was positive or negative.
Any overheads on performance and lastly how E1 was impacted during a failover.

Thanks

From an E1 perspective it is pretty normal - one creates a listener and E1 connects to that like any other data source.

Beware that the lure of the 'readable' secondary is dangerous. It seems like a great way to offload queries onto the 'non-active' server but in fact, running queries against the secondary in an AG is not similar to running queries against a replication target server. The two nodes in an AG are intimately connected and the negative impact on the secondary can adversely affect the primary.

Index maintenance on large databases is an absolute nightmare. Since an AG mirrors the transactions to the secondary, index maintenance transactions have to be 'replayed' on the secondary, across a wire (LAN for local secondaries, WAN for off-prem secondaries). Imagine transferring 1TB of index rebuild transactions every weekend. It'll take hours for even your local secondary to catch up, days for a slow WAN remote secondary.

You better super-optimize your tempdb because AG mirroring uses Snapshot Isolation, which uses the Version Store, which uses tempdb. IO measurements are through the roof, particularly if you are also utilizing RCSI, which you should be. Be ready to do at least 20,000 IOPS on your data and tempdb drives. Transaction log sizes are going to be large for a whole bunch of very detailed reasons.

Back to readable secondaries - everyone will want to read from your 'readable' secondary. Some software does not do a good job of closing SQL connections. This will wreak havoc on your primary. You've been warned.

Thoughts:

Hard to administer but darn nice to have another live copy of your data.

E1 still cannot handle a failover.


Let me know if you have any more specific questions.
 

schojo44

Active Member
Hi Jeff,

That is true, it is a vast topic. I guess I am just looking to understand if the overall experience was positive or negative.
Any overheads on performance and lastly how E1 was impacted during a failover.

Thanks

I work on an environment that has a clustered MSSQL Server 2014 with AOAG and made availible to EnterpriseOne via logical name, so E1 doesn't know which node it's using. Works like a charm, Enterprise Server sometimes doesn't even notice a failover! ;)
 

brother_of_karamazov

Legendary Poster
I work on an environment that has a clustered MSSQL Server 2014 with AOAG and made availible to EnterpriseOne via logical name, so E1 doesn't know which node it's using. Works like a charm, Enterprise Server sometimes doesn't even notice a failover! ;)

Curious how you set it up so that it works like this.
 

bfcdn1

Member
SQL Server AOAG offers good benefits to E1 - high availability for your databases, disaster recovery, off-load BI workloads onto secondaries & off-load sql backups onto secondaries.
AOAG is an improvement over Database Mirroring & the later versions of SQL Server have optimized the log block process to speed up the transaction commits. You can also operate in Synchronous Mode or Asynchronous Mode.

Index Maintenance - look at fragmentation level & page count to only reorg / rebuild what is actually needed.

Fast TempDB is important and that is accomplished through proper structuring the data & log files & right hardware.

There is some administration needed for AOAG however it is a really great option to have for your data tier.


B. Farrell
www.techdevops.com
SQL Server Experts
 
Top