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

No comments: