Open Forum

Expand all | Collapse all

GP 2018 & SQL AlwwaysOn

  • 1.  GP 2018 & SQL AlwwaysOn

    TOP CONTRIBUTOR
    Posted Aug 21, 2019 12:02 PM

    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
    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: GP 2018 & SQL AlwwaysOn

    SILVER CONTRIBUTOR
    Posted Aug 21, 2019 07:09 PM
    Hi @David Morinello,

    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.

    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
    ------------------------------

    Conference-GPUG_200x200


  • 3.  RE: GP 2018 & SQL AlwwaysOn

    Posted Aug 22, 2019 09:16 AM
    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
    ------------------------------

    Conference-GPUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role