[David Morinello]

I would like to dig deeper into SQL AlwaysOn, as I have not had the chance to implement it before.

As part of our recent GP 2018 upgrade, our DB Team set up a SQL Availability Group. So, we already have a secondary SQL Server with the all the GP data. Our DBA team wants me to repoint everything to a SQL alias so we could fail-over if it became necessary. I would like to hear of any gotchas, issues, show-stoppers, or just good advice on what to watch out for when implementing this (in test first, of course).

Areas I have concerns about

• GP ODBC (and GP passwords)
• SmartConnect 2018
• Integration Manager
• Management Reporter cu16 (no Datamart)
• Infinia Integrator
• eConnect
• GP Web Services
• GP Web Client

I have read that GP supports AlwaysOn and have the SQL 2012 document “Guidance for running Microsoft Dynamics GP with Microsoft SQL Server AlwaysOn Availability Groups“.

We are on GP 2018 R2 and SQL 2014. An upgrade to SQL 2017 is being planned for early next year.

——————————
David Morinello
Senior Dynamics GP Systems Architect
Ascend Learning, LLC
Leawood KS
——————————

[Jeff Pfershy]

Hi ,

At a previous company a few years back we had two large datacenters and used different flavors of DR for many of our servers.

For GP and many other business-critical applications we used ‘homegrown’ log shipping (automated to backup and restore on offset 5 minute increments) to read-only replicas in the ‘dark’ datacenter, and on a periodic basis (quarterly was ideal but usually it was less frequent), we would test our DR by failing over all related applications to the other datacenter.

We used aliases for our SQL servers, including GP, and during the failover process, once all connections to the primary server were stopped and we had taken final tlog backups and restored those on the replica, cnames were updated to point at the new primary server and there was a waiting period while the cname changes propagated through the network.?

Our biggest gotchas when we started implementing AlwaysOn and AGs for various applications and related databases were two things:

Synchronous vs Asynchronous communication and the impact on application users (through latency issues) – below is a link to a good article on the differences between the two.

• With Synchronous a transaction isn’t committed until the secondary server writes the transaction to the log and returns a ‘hey I got your transaction’ message back to the primary. So if you have a high volume or lots of users pounding away, your users will likely have latency issues. 
• With Asynchronous the transaction is committed when the primary records the transaction in the log, and any secondary(s) get the transaction after the primary has committed the transaction.
• Microsoft article: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-2017
• Brent Ozar article: https://www.brentozar.com/sql/sql-server-alwayson-availability-groups/

The other big gotcha for us (at the time) was in the Failover modes – the automatic failover mode was such that if there wasn’t a heartbeat detected on the primary (for any reason), your application automatically moved. We always opted for the manual failover so we could at least control any fallout from a related application that didn’t play nice with a change of that nature.

I’m sure there’s more, but I don’t recall us having any problems with the aliases, AS LONG AS ALL RELATED APPLICATIONS WERE SET UP TO USE THE ALIASES.

PS – we also didn’t use a standard SQL port for any of our sensitive applications – no problems there either.

Hope this helps!

——————————
Jeff Pfershy
Sr Business Analyst
BluJay Solutions Inc
Holland MI
——————————
——————————————-

[Jeff Woodard]

My comment is not specific to SQL AlwaysOn, but one gotcha that you need to look out for when you start testing changing names and aliases is GP’s unique password management.

It was a while back, but as I recall, one of our users had a local GP2018R2 install, but had an ODBC connection name that differed only in case from the connection name on our terminal server (i.e. GP2018 vs gp2018). When the user changed their password on the local station, their password no longer worked on the terminal server.

——————————
Jeff Woodard
Chief Technical Officer
Transportation Financial Services, Inc.
West Palm Beach FL
——————————
——————————————-