Deploying Microsoft Dynamics GP on an Azure SQL Managed Instance - Part 2/3
In Part 1 of this series, I walked through the process and experience of signing up for the Azure SQL Managed Instance preview and the deployment process once approved. I also covered some of the issues I ran into.
In this installment, I will take a look at deploying Microsoft Dynamics GP and, again, looking at the process and experiences I encountered while doing so.
Pre-Microsoft Dynamics GP Deployment
One of the steps within the deployment of a Managed Instance is the provisioning of a VM to be able to connect to the service. The instructions call for downloading and installing Microsoft SQL Server Management Studio on the VM. I figured, this is the VM I would also be using to install Dynamics GP, IIS, and Web Client.
NOTE: Currently, you cannot connect to a Managed Instance from outside of the Azure vNet infrastructure, and frankly it makes a lot of sense.
A few things I noticed right off the bat after connecting to the Managed Instance:
a) A Managed Instance is configured to support Mixed Mode Authentication. This is good news for Microsoft Dynamics GP as it is been a long established requirement.
b) The SQL Server system administrator (sa) user is disabled, but exists, unlike Azure SQL. When creating the Managed Instance, you are required to set up a separate SQL administrative account (more on this later).
c) You cannot use SQL Server Configuration Manager to make any changes to the deployed configuration, hence the "Managed" in Managed Instance.
I also downloaded Dynamics GP 2018 from PartnerSource, along with the January Hotfix. In addition, I added .NET Framework 3.5 (using Server Administrator) as it is a prerequisite needed by the Dynamics GP Bootstrap setup program.
Deploying Microsoft Dynamics GP
After running the setup and laying down the Microsoft Dynamics GP program files, I proceeded to launch Dynamics GP Utilities to create the system database, configure the account framework, and add the sample company database, Fabrikam. I tried login in with the SQL administrative account I created during the provisioning of the Managed Instance, and referenced herein above in point (b) only to get the following familiar error:
I ran through the tricks book on this one to no avail and could not get past this problem. Next, I proceeded to enable the SQL Server sa account and was able to log into Dynamics GP Utilities without any issues.
After selecting the Advanced configuration in Dynamics GP Utilities, the first screen of interest was the Database Setup window for the system database:
Note the location of both the data and log files.
Continuing with the instance configuration, I got to the Confirmation window without any issues and with everything looking good. After clicking Finish, I immediately got the following error:
The following SQL statement produced an error:
create database [DYNTEST] ON (NAME = 'GPSDYNTESTDat.mdf', FILENAME = 'C:\WFRoot\DB.0\Fabric\work\Applications\Worker.CL_App13\work\data\GPSDYNTESTDat.mdf', SIZE = 50, FILEGROWTH = 20% ) LOG ON (NAME = 'GPSDYNTESTLog.ldf', FILENAME = 'C:\WFRoot\DB.0\Fabric\work\Applications\Worker.CL_App13\work\data\GPSDYNTESTLog.ldf', SIZE = 20, FILEGROWTH = 25% )
On the surface, there's nothing wrong with this statement as it is very known to Microsoft Dynamics GP professionals. However, I decided to see what exactly would be the problem, so I fired up SQL Server Management Studio and ran the same statement and, voila! The errors was now very clear:
Msg 41918, Level 16, State 1, Line 1
Specifying files and filegroups in CREATE statement is not supported on SQL Database Managed Instance.
Thinking through this, a Managed Instance stores its databases on a Azure storage container. If Dynamics GP Utilities' Database Setup window had a radio button option for On-premise vs Azure, then the T-SQL CREATE DATABASE statement could be tailored to ignore the data file and log options. In fact, this statement worked just fine when I executed it in SSMS:
create database [DYNTEST]
Once again, not all was lost because, while I could not get Dynamics GP Utilities to create the system and company databases due to how the CREATE DATABASE is constructed, I was certain of another method that would work. Find out in my last installment of this series what I did to get things going -- yes, this story has a happy ending 😊.
Until next post,
MG.-
Mariano Gomez, MVP
In this installment, I will take a look at deploying Microsoft Dynamics GP and, again, looking at the process and experiences I encountered while doing so.
Pre-Microsoft Dynamics GP Deployment
One of the steps within the deployment of a Managed Instance is the provisioning of a VM to be able to connect to the service. The instructions call for downloading and installing Microsoft SQL Server Management Studio on the VM. I figured, this is the VM I would also be using to install Dynamics GP, IIS, and Web Client.
NOTE: Currently, you cannot connect to a Managed Instance from outside of the Azure vNet infrastructure, and frankly it makes a lot of sense.
A few things I noticed right off the bat after connecting to the Managed Instance:
a) A Managed Instance is configured to support Mixed Mode Authentication. This is good news for Microsoft Dynamics GP as it is been a long established requirement.
b) The SQL Server system administrator (sa) user is disabled, but exists, unlike Azure SQL. When creating the Managed Instance, you are required to set up a separate SQL administrative account (more on this later).
c) You cannot use SQL Server Configuration Manager to make any changes to the deployed configuration, hence the "Managed" in Managed Instance.
I also downloaded Dynamics GP 2018 from PartnerSource, along with the January Hotfix. In addition, I added .NET Framework 3.5 (using Server Administrator) as it is a prerequisite needed by the Dynamics GP Bootstrap setup program.
Deploying Microsoft Dynamics GP
After running the setup and laying down the Microsoft Dynamics GP program files, I proceeded to launch Dynamics GP Utilities to create the system database, configure the account framework, and add the sample company database, Fabrikam. I tried login in with the SQL administrative account I created during the provisioning of the Managed Instance, and referenced herein above in point (b) only to get the following familiar error:
 |
| Login failed error |
I ran through the tricks book on this one to no avail and could not get past this problem. Next, I proceeded to enable the SQL Server sa account and was able to log into Dynamics GP Utilities without any issues.
After selecting the Advanced configuration in Dynamics GP Utilities, the first screen of interest was the Database Setup window for the system database:
 |
| Database Setup |
Note the location of both the data and log files.
Continuing with the instance configuration, I got to the Confirmation window without any issues and with everything looking good. After clicking Finish, I immediately got the following error:
The following SQL statement produced an error:
create database [DYNTEST] ON (NAME = 'GPSDYNTESTDat.mdf', FILENAME = 'C:\WFRoot\DB.0\Fabric\work\Applications\Worker.CL_App13\work\data\GPSDYNTESTDat.mdf', SIZE = 50, FILEGROWTH = 20% ) LOG ON (NAME = 'GPSDYNTESTLog.ldf', FILENAME = 'C:\WFRoot\DB.0\Fabric\work\Applications\Worker.CL_App13\work\data\GPSDYNTESTLog.ldf', SIZE = 20, FILEGROWTH = 25% )
On the surface, there's nothing wrong with this statement as it is very known to Microsoft Dynamics GP professionals. However, I decided to see what exactly would be the problem, so I fired up SQL Server Management Studio and ran the same statement and, voila! The errors was now very clear:
Msg 41918, Level 16, State 1, Line 1
Specifying files and filegroups in CREATE statement is not supported on SQL Database Managed Instance.
Thinking through this, a Managed Instance stores its databases on a Azure storage container. If Dynamics GP Utilities' Database Setup window had a radio button option for On-premise vs Azure, then the T-SQL CREATE DATABASE statement could be tailored to ignore the data file and log options. In fact, this statement worked just fine when I executed it in SSMS:
create database [DYNTEST]
Once again, not all was lost because, while I could not get Dynamics GP Utilities to create the system and company databases due to how the CREATE DATABASE is constructed, I was certain of another method that would work. Find out in my last installment of this series what I did to get things going -- yes, this story has a happy ending 😊.
Until next post,
MG.-
Mariano Gomez, MVP
Comments