GP Support for Azure SQL Managed Instance

(0) Share

Report

Posted on by Community Member

My company is looking to migrate our on-prem infrastructure for Dynamics GP 2016/2018 over to Microsoft Azure. We are looking to try to move what doesn't need to be in IaaS over to PaaS. I have seen information about Azure SQL Database not being supported due to issues with the database and tempdb but when reading through the Azure SQL Managed Instance documentation it looks like this may solve those issue. Has the Dynamics GP team said anything about this flavor of SQL or has anyone starting experimenting with this? We primarily use Dynamics GP for its HR components so its user base is about 6 concurrent users.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Community Member on at

Like (0)

Report

RE: GP Support for Azure SQL Managed Instance

Does anybody happen to have an update on this? We're getting ready to migrate our Dynamics GP databases to SQL in Azure and I'd love to use a Managed Instance if possible instead of an plain ol' VM. Thanks!

Was this reply helpful? Yes No
Tim Wappat 5,703 on at

Like (0)

Report

RE: GP Support for Azure SQL Managed Instance

Interesting update there. thank you!

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report
RE: GP Support for Azure SQL Managed Instance

Reviving this thread after a few months now that I have some more time to work on this. Using Azure SQL Managed Instance for Dynamics GP I have found a few items that are roadblocks to long term use.

Unable to upgrade from 2016 R2 to 2018 due to how GP registers its CLR assemblies. Currently GP tries to access the .Net framework on the SQL Server using the SQL Server's path information to then import the assemblies and dependencies from the file system. This isn't supported on Azure SQL MI so the update fails at this step. This could be resolved manually if one can hack the installer's SQL script to use a different loading methodology. Its possible but very time consuming.

Attempting to install Dynamics GP web client will result in the web client being installed but for some reason there are parts of the Web Client services that will use Windows Authentication even thought you provide a SQL user to the installer. This can be worked around by editing the .config files on the services to change the connection strings to use SQL username and password but that does result in the password sitting in clear text in the config file.

Attempting to install Dynamics GP Web Services fails due to deeply embedded Windows Authentication that cannot be overridden in the Installer. This is probably a hard coded legacy requirement that will require a change in how the Web Services runtime interacts with the DB in order to support using a SQL user for the connection string to the database.

Overall Azure SQL Managed Instance would make a very attractive database option for Dynamics GP being hosted on Azure if these issues can be resolved. I read through all of your blog posts about Azure SQL MI but I did notice that there hasn't been anything new since then. Do you have any thoughts on these issues or have you found any workarounds that might be easier to implement while still using Azure SQL MI? I also did talk to the Azure SQL MI team about the temp tables being dropped when the server node is being migrated to a different node and they did say that might not be intended so that item might be resolved in the future.

Was this reply helpful? Yes No
Mariano Gomez 26,225 on at

Like (0)

Report

RE: GP Support for Azure SQL Managed Instance

The key word here is "Managed" - no server changes can be performed. I ran into the tempdb issue as well with the temp tables. It would be nice if you had the ability to run a SQL startup script that would recreate those, or simply, not drop permanent tables created in tempdb.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

RE: GP Support for Azure SQL Managed Instance

So I recently setup a test environment for Dynamics GP on Azure SQL Managed Instance. One thing I have found is that when Azure SQL MI restarts an instance it doesnt recreate the DEX temp tables. I found that the following blog post from the MSDN Developing for Dynamics GP blog referenced the issue but not directly for MI.

After restarting SQL Server, only ‘sa’ user can log into Microsoft Dynamics GP

When attempting to run sp_procoption 'smDEX_Build_Locks','startup','true' on Azure SQL MI the following error occurs. "Changes to server configuration option scan for startup procs are not supported in SQL Database Managed Instances." I will have to raise this as an application compatibility issue with Azure SQL MI team and see what options are available to allow the tables to be recreated by the instance after a restart.

Was this reply helpful? Yes No
Suggested answer

Mariano Gomez 26,225 on at

Like (0)

Report

RE: GP Support for Azure SQL Managed Instance

Troy,

I am currently working through a series of articles on this very same subject. I have one more to go and will be happy to address any questions after you read. Also, please be aware that the Azure SQL Managed Instance is still in preview mode, which means limited support at the moment and probably not recommended for production environments.

Deploying Microsoft Dynamics GP on an Azure SQL Managed Instance - Part 1/3

Deploying Microsoft Dynamics GP on an Azure SQL Managed Instance - Part 2/3

I am working on Part 3 and should have that one out on Wednesday of this week.

I had also released a video blog series on why Dynamics GP cannot be deployed on the standard Azure SQL database environment. Please feel free to check those out at:

Microsoft Dynamics GP: Running System and Company Databases on Azure SQL - Part 1

Microsoft Dynamics GP: Running System and Company Databases on Azure SQL - Part 2

Microsoft Dynamics GP: Running System and Company Databases on Azure SQL - Part 3

Was this reply helpful? Yes No