In the last series, I created on this site, I showed you how to create Gallery Items for extensions and SimpleVM deployment. In the meantime, I have been looking into what it would take to create a more complex Gallery Item for one of my Azure Stack TAP customers. One that actually deploys and configures something while facilitating user driven customization.

The reason this customer wanted to investigate their options in this area is that they currently have invested heavily in Azure Pack VM Roles which facilitates user driven customization of pre-defined roles like SQL Server, HPC nodes, etc.

I’m happy to say that all the efforts I had to put in reverse engineering and searching through all kind of GitHub repositories and packages on my Azure Stack TP2 node finally paid off. I now have a basic concept on how to do this and I would like to share it with the world!

Welcome to the first blog post (of 2) on this subject. Covering my research as a step by step guide for building an SQL Server 2016 solution exposed as Gallery Item running on Windows Server 2016 Core.

• Part 1 – Prerequisites (this post)
• Part 2 – SQL Gallery item

Breakdown

To create the SQL Gallery solution, we need to break down the deployment process first and then package it up as one Gallery Item with all prerequisites in place.

• Server 2016 Core image
• SQL 2016 install media on publicly accessible blob storage
• DSC configuration archive on publicly accessible blob storage

Once all this is in place, we can start creating the gallery item itself.

OS image

Just like in public Azure, we want to keep things as clean as possible in Azure Stack. Therefore, a generalized (patched) Server 2016 Core OS image is used. This images are not flavored in any sense, which means it will not contain any settings pre-baked. Pre-baking settings results in a lack of transparency and maintainability of these settings and can possibly be the cause of conflict with installation routines not kept in mind. Because this image can be used for multiple purposes, I won’t include any files or packages to help with the SQL installation. In the case of this scenario, we will use a sysprepped / updated image of Windows Server 2016 Standard Core which we will generate directly from the media. If your curious about the latest CU for Windows Server 2016, take a look here.

Note that a vhd is generated and it’s for a Generation1 VM as vhdx and Generation 2 VMs are not supported (yet) in Azure Stack (please upvote here and here!). Also, this script must be run as an Administrator!

The result should look something like this:

Now we have the image we need to make the image available to the Compute Resource Provider (CRP). First, we need to make the image available on a CRP reachable URI. In this case, we will create a storage account and upload the image to it while making it publicly available (once the CRP has added the image to the repository, the storage account could be removed but leave it in place to follow along with the rest of this blog). I’m assuming all prerequisites are already met (AzureRm PowerShell installed and connected with Azure Stack using default provider subscription).

Now we have the URI for our image (on the clipboard), there are 2 “supported” ways of adding it to the CRP repository:

• Via Portal UI
• Via API (using REST, PowerShell cmdlets, etc)

In this case, we will import the image to the CRP using the Portal UI.

The Portal UI used for adding the images can be accessed via Region Management.

Open the local region blade, and select the Compute Resource Provider. Here you can see the current amount of VM Images for this region.

When you select the VM Images tile, another blade will open which shows you the details of the VM Images currently available. It also allows you to add or remove images.

As you can see, adding an image can only be done when the vhd is available on a URI.

Fill out the form as follows and hit create:

Field	Value
Publisher	Microsoft
Offer	WindowsServer
OS Type	Windows
SKU	2016-Standard-Core
Version	1.0.0
OS Disk Blob URI	https://tenantartifacts.blob.azurestack.local/image/Server2016CoreEval.vhd
Data Disk Blob URIs

The CRP is now downloading the image and making it available.

Hit refresh until the status changes.

Once the status equals Succeeded, you can validate if the OS Image is deployable through PowerShell.

The image is not surfaced in the portal as there is no Gallery Item created for it.

If you want to know how to create a simple VM gallery item to deploy this Server 2016 Core image, please look at my previous blog post here.

SQL installation source files

One of the challenges with deploying to (Hybrid) Cloud environments is where to get your installation source files from. In public Azure, most source files are downloaded during deployment from their respective http links. SQL Server Gallery Items in Azure have their own OS images containing the SQL source files (I don’t like this approach as now you need multiple Server images which basically are the same with the exception of the installation source files).

In my opinion, it would be best to have a decent uniform package delivery mechanism in place like chocolatey (a nuget extension) which is http(s) based. For now, however, I’m uploading the SQL 2016 ISO file to a storage account and making it publicly accessible.

I downloaded the evaluation ISO of SQL 2016 with SP1 from here: https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

Now I have the ISO available, it’s time to make it available on the storage account.

The resulting publicly available Uri would be: https://tenantartifacts.blob.azurestack.local/iso/SQLServer2016SP1-FullSlipstream-x64-ENU.iso

DSC Configuration

In Windows VM configuration on Azure is typically handled by Desired State Configuration (DSC). DSC, like ARM, is idempotent which makes it a good fit. A DSC VM Extension is available out of the box with TP2 (although an older version then currently available) to handle DSC configurations which we will leverage. The resulting artefact will be put on a storage account.

What the DSC configuration needs to do:

• Download the ISO from the storage account
• Based on parameter input, install requested SQL server features (SQLEngine and / or IS)
• Configure default or named instance
• Based on parameter input, configure remote tcp port for SQL

For this blog post, we will keep configurable options limited. A mandatory SQL Data disk and Log disk will be created and the user will only be able to define the size per individual disk.

The configuration script:

Now we have the configuration in place, we need to make it available as a published DSC Configuration Archive. To do this, we will leverage the Publish-AzureRmVMDscConfiguration cmdlet and target the storage account we have been using already. This cmdlet generates an archive with ps1.zip extension and is able to publish it to the storage account directly or have the result saved to disk instead. On the system you run the cmdlet, all modules imported into the configuration script using the Import-DscResource keyword, need to be in place already with the correct version number. You can download the resources from the PowerShell gallery with exception of the xDownloadISO resource module which comes from the SQL Resource Provider. To make things easy for you, I’ve uploaded the script and the modules to a GitHub repository to go with this blog series.

Let’s look at the result of calling the cmdlet with saving the result on disk in the first place so we can examine the content.

As you can see from the verbose output, the DSC resource modules are packaged together with the configuration script.  The VM DSC extension will extract the archive and install these resource modules.

We can now publish the resulting archive ourselves or run the cmdlet again but this time with the parameters to send the archive to a storage account instead of local disk.

The archive is now available on the uri: https://tenantartifacts.blob.azurestack.local/dsc/SQLConfiguration.ps1.zip

All we need to do now is make this container publicly available (Remember the end goal is a Gallery Item available to anyone using the Portal. Don’t store secret or company other specifics within the configuration!).

Quick validation

If you already have a deployed VM available, use the following snippet to quickly test the foundational blocks we have put into place. (For Server 2012 VM, make sure .net 4.6.2 is already installed before trying).

Now all prerequisites are in place, we can start creating the Gallery Item which will be the subject of the next blog post. Stay tuned!