Monday, April 30, 2018

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:

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.

NOTE: The admin account I created during the Managed Instance deployment worked just fine when the Dynamics GP installer asked for the server and account credentials to create the ODBC driver.

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,

Mariano Gomez, MVP

Friday, April 27, 2018

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

A few weeks aback, I went through a series of video articles explaining the limitations preventing you from deploying Microsoft Dynamics GP on Azure SQL. See my video blog series here:

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

A few days later, my friend and fellow MVP Steve Endow introduced me to a new Azure preview feature called Azure SQL Managed Instance. Being a preview feature, there was a lengthy application process in order to gain access to it.

According to Microsoft, Azure SQL Managed Instance (further referred to herein as "Managed Instance" for simplicity sake) delivers the full capabilities of Microsoft SQL Server running on Azure service infrastructure. This is, all the limitations currently imposed by Azure SQL are, technically speaking, removed from a Managed Instance. The following diagram describes the key features.

Source: Microsoft
At this point, it is probably worth pointing out that prior to the introduction of Managed Instances, they were only two methods of deploying SQL Server in the Azure cloud computing platform:

IaaS: under this deployment model, a Windows Server virtual machine (VM) is provisioned with a license of Microsoft SQL Server. As a result, it is up to you to maintain the VM, applying all applicable service packs, back up and securing your databases, and worry about all the related database maintenance procedures. In essence, server administrative operations are no different than those performed on an on-premise deployment of SQL Server.

PaaS: this deployment model allows you to provision Azure SQL as a service, but presents a wide range of limitations for databases running in a traditional Microsoft SQL Server environment. One such limitation is the ability to run cross-database queries without the complexities introduced by Azure SQL (elastic pools, etc.), which is vital to support the system and company database architectural design of Microsoft Dynamics GP. In addition, Azure SQL features extensive deprecation of traditional system objects and stored procedure calls currently supported by traditional Microsoft SQL Server.

The Managed Instance Deployment Process/Experience
Deploying an Azure SQL Managed Instance (in preview mode) is currently an exercise in patience. I began by following the instructions in detailed in this Microsoft Docs article:

Create an Azure SQL Database Managed Instance in the Azure portal

The steps can be summarized as follow:

✔ Whitelist your subscription
✔ Configure a virtual network (VNet)
✔ Create new route table and a route
✔ Apply the route table to the Managed Instance subnet
✔ Create a Managed Instance
✔ Create a new subnet in the VNet for a virtual machine
✔ Create a virtual machine in the new subnet in the VNet
✔ Connect to virtual machine
✔ Install SSMS and connect to the Managed Instance

If I had to do this all over again, I would first wait until acceptance into the preview program has been confirmed, before going through the above steps. It's also worth noting that the deployment of the Managed Instance, in my particular case, took a bit over 27 hours. In the process, I ran into the following issues:

► At first, I did not receive a notification of acceptance (or rejection) within the program. This was easily resolved by opening an Azure subscription support case.

► When I initially provisioned my vNet, I created a single subnet spanning the full range of IPs afforded by the address space. Not being a network engineer, I am sure that this is something a seasoned professional wouldn't have done. It is recommended that subnets be smaller partitions within the address space. In any case, I initially assigned the Managed Instance to the only subnet I had and this caused the deployment to fail.

As it turns out, a Managed Instance requires its own subnet. Since my Azure environment is a sandbox, I had no problem in removing ALL resources and recreating them with the recommendations in the article. At the same time, I created specific resource groups for VMs, network resources, and storage resources, and furthermore set up a subnet for my VMs, different from the Managed Instance subnet -- in short, much, much cleaner! This is the result:

Resource Groups


► Once I got this all going, the deployment process began. Somewhere pass the 27 hour mark I decided to check the provisioning status and found this error message, "Cannot process request. Not enough resources to process request. Please retry you (sic) request later.".

It took a few email exchanges with the SQL Cloud Lifter Ops team -- yes, there's such thing -- and I was up and running and ready to connect to my instance. For the record, I had over 3 teams helping me with this.

When it's all said and done, you will end up with the following resources:

1. A vNet
2. A subnet for the Managed Instance, and for good measure a subnet for the VM
3. A VM
4. A route table associated to the Managed Instance subnet.
5. A storage account that stores the deployment diagnostics for the Managed Instance.

In the next installment, I will go through the deployment steps for Microsoft Dynamics GP. There is a lot to cover, so I will give you time to digest the content of this first installment.

Until next post,

Mariano Gomez, MVP