Part 2: Deploy MSSQL and MySQL as PaaS on Azure Stack

Previous Post in Series:  Part 1: Deploy Azure Stack Single Node POC

19/07/2017:  UPDATED for the Azure Stack Dev Kit

Welcome back folks. In the last guide we deployed a single node Azure Stack POC and uploaded a Server 2016 Image to the gallery. In part 2 we’re going to deploy both the MSSQL and MySQL resource providers. Microsoft have done a great job of making this a fairly straightforward endeavour so I don’t expect this to be too meaty a post

Here’s a high level list of what we’ll be going over:

• Deploy MSSQL Resource Provider
• Verify MSSQL Resource Provider Deployment
• Deploy MySQL Resource Provider
• Verify MySQL Resource Provider Deployment

MSSQL Resource Provider

Overview

Let’s start by discussing the MSSQL Resource Provider (RP). By deploying this, we’ll be able to offer our tenants SQL databases as a service. This RP can be connected to multiple SQL server instances at the backend to meet out tenants DB requirements without the need for them to spin up a VM running SQL server themselves…nice eh?

The resource provider is made up of three components, these are as follows:

Component	Detail
SQL RP adapter VM	This server hosts the RP process and a small database required for the RP state. It can also be used as a sample SQL hosting server.
The Resource Provider	The RP is responsible for processing any SQL provisioning requests and for exposing our database resources to tenants for consumption.
SQL Hosting Servers	These servers run SQL Server and are what provide database capacity out to our tenants.

That’s enough for the overview I think, let’s get on and deploy this thing.

Deploy MSSQL Resource Provider

One of the main reasons I included the Default image creation and upload step in the last guide is because it’s actually used as part of our MSSQL RP deployment, so let’s tick that box.

Log onto the POC host with your AzureStack\AzureStackAdmin credentials.

Download the MSSQL RP installer from HERE and run it to extract the files to C:\Temp\MSSQLRP\

We’re going to deploy the MSSQL RP using a PowerShell script provided by Microsoft.

Here’s a breakdown of what the script is doing:

• Installs the AzureRM.Bootstrapper module
• Installs the 2017-03-0-profile version of the AzureRM modules for Computer, Storage, Network, Key Vault etc.
• Installs Azure Stack specific PowerShell modules
• Downloads and extracts AzureStack Tools from GitHub
• Imports the Azure Stack connect Powershell module for the above downloaded tools and adds the AzureStack Environment
• Gets the Tenant ID for your AAD directory
• Creates and stores local admin credential object for the SQL VM you’re about to spin up
• Creates and stores credential object for AAD directory user
• Runs the DeploySQLProvider.ps1 script using the above provided information

Here’s a quick table showing the expected input for the script variables.

Variable	Details
$TenantID	The value for "myadtenant" should be your AAD Directory name, usually companyname.onmicrosoft.com.
$VMLocalAdminPass	Enter a password to be used as the local administrator password for the SQL VM you're about to deploy
$AdminPass	Enter the password the AAD user you've used during your Azure Stack deployment
$AdminCreds	This should be the AAD username you've used to deploy Azure Stack in the following format username@AADDirectory.com
$PfxPass	Enter a password to be used by the script when creating the required certificates

Open an elevated PowerShell ISE console and paste the code below into the editor:

NOTE:  As with this entire deployment, I’ll be using an AAD account, if you’re using ADFS, update the script as required.

NOTE:  When installing the AzureStack PowerShell module you may get the warning shown below, it’s never caused me an issue as of yet, so I’ve been ignoring it.

# Install the AzureRM.Bootstrapper module
Install-Module -Name AzureRm.BootStrapper -Force

# Installs and imports the API Version Profile required by Azure Stack into the current PowerShell session.
Use-AzureRmProfile -Profile 2017-03-09-profile
Install-Module -Name AzureStack -RequiredVersion 1.2.10 -Force

# Download the Azure Stack Tools from GitHub and set the environment
cd C:\
Invoke-Webrequest https://github.com/Azure/AzureStack-Tools/archive/master.zip -OutFile master.zip
Expand-Archive master.zip -DestinationPath . -Force

# This endpoint may be different for your installation
Import-Module C:\AzureStack-Tools-master\Connect\AzureStack.Connect.psm1
Add-AzureRmEnvironment -Name AzureStackAdmin -ArmEndpoint "https://adminmanagement.local.azurestack.external" 

# For AAD, use the following
$TenantID = Get-AzsDirectoryTenantID -AADTenantName "AADDirectoryName.onmicrosoft.com" -EnvironmentName AzureStackAdmin

# For ADFS, replace the previous line with
# $tenantID = Get-AzsDirectoryTenantID -ADFS -EnvironmentName AzureStackAdmin

$VMLocalAdminPass = ConvertTo-SecureString "Password Here" -AsPlainText -Force
$VMLocalAdminCreds = New-Object System.Management.Automation.PSCredential ("sqlrpadmin", $vmLocalAdminPass)

$AdminPass = ConvertTo-SecureString "Password Here" -AsPlainText -Force
$AdminCreds = New-Object System.Management.Automation.PSCredential ("AADUser@Company.com", $AdminPass)

# Default password for created SSL certificates
$PfxPass = ConvertTo-SecureString "Password Here" -AsPlainText -Force

# Change directory to the folder where you extracted the installation files
# and adjust the endpoints
C:\Temp\MSSQLRP\DeploySQLProvider.ps1 -DirectoryTenantID $tenantID -AzCredential $AdminCreds -VMLocalCredential $vmLocalAdminCreds -ResourceGroupName "SqlRPRG" -VmName "SqlVM" -ArmEndpoint "https://adminmanagement.local.azurestack.external" -TenantArmEndpoint "https://management.local.azurestack.external" -DefaultSSLCertificatePassword $PfxPass

The deployment will take a while (90 minutes or so for me), so go make yourself a coffee.

Once complete, it should look something like this:

Let’s move on and verify that our MSSQL RP did in fact deploy successfully.

Verify MSSQL Resource Provider Deployment

So now that we’ve deployed the MSSQL RP, let’s make sure that it actually works.

Log onto the Azure Stack Admin Portal as the Service Administrator (the AAD account you used to deploy it)

Navigate to “Resource Groups”, “SqlRPRG”, “Overview” and check that it says “Succeeded” under “Deployments”

Now we want to check that the registration of the SQL Resource Provider succeeded.

Navigate to “Resource Providers” and confirm there is an RP named “SQLAdapter”

Assuming all that checks out as expected, let’s connect a SQL hosting server to the RP so we can farm out SQL capacity to our tenants.

Still in “Resource Providers”, click the “SQLAdapter” we spoke about above.

Now click “Hosting Servers” and “+Add”

As part of the deployment, a SQL VM was spun up inventively named…“SqlVM” , enter that under “SQL Server Name”

Enter “sa” for the “Username”

Enter the password you set for the local admin account when deploying the MSSQL RP

Enter 10GB (for example) for the “Size of Hosting Server in GB”

Create a new resource group or use a sensible existing one

Now click on “SKUs” and select “Create new SKU”

Populate the SKU details as makes sense for your use case and click “OK”

Now click “Create” and you should quickly receive a popup advising you of a successful deployment.

This is where I’d usually create a database to test that everything is working from a tenant perspective too but I plan to roll that into a later guide I’m writing which will go over the creation of Offers, Plan, Quotas, MSSQL and MySQL DB creation and Web App deployment in detail.

NOTE:  On ASDK, even though my deployment reported it completed successfully, I had issues when adding the hosting server. I logged onto the SQL VM it created (SqlVM) and found that it hadn’t actually installed SQL at all…no wonder it was having a problem. I manually installed SQL on the VM and tried adding it as a hosting server again, this worked without issue. This has only happened to me once in about 15 installs though, so hopefully you’ll be fine.

That’s it for the MSSQL deployment, let’s move on to MySQL, it’s a very similar process so it shouldn’t require too much additional explaining

Deploy MySQL Resource Provider

Overview

Nothing new here really, the main difference being that deploying the MySQL RP will allow us to offer tenants the ability to spin up MySQL DBs as a service without the need for them to deploy VMs and install MySQL on it.

As I’ve already said, it’s a very similar process to the MSSQL RP deployment so let’s get right to it.

Deployment

Log onto the POC host with your AzureStack\AzureStackAdmin credentials.

Download the MySQL RP installer from HERE and run it to extract the files to C:\Temp\MySQLRP\

Being that we’ve already run through the MSSQL RP deployment, we could shave a little off the MySQL deployment script. However, on the off chance, you’re only here for the MySQL portion, I’ll go through it all again

Here’s a breakdown of what the script is doing:

• Installs the AzureRM.Bootstrapper module
• Installs the 2017-03-0-profile version of the AzureRM modules for Computer, Storage, Network, Key Vault etc.
• Installs Azure Stack specific PowerShell modules
• Downloads and extracts AzureStack Tools from GitHub
• Imports the Azure Stack connect Powershell module for the above downloaded tools and adds the AzureStack Environment
• Gets the Tenant ID for your AAD directory
• Creates and stores local admin credential object for the MySQL VM you’re about to spin up
• Creates and stores credential object for AAD directory user
• Runs the DeploySQLProvider.ps1 script using the above provided information

Here’s a quick table showing the expected input for the script variables.

Variable	Details
$TenantID	The value for "myadtenant" should be your AAD Directory name, usually companyname.onmicrosoft.com.
$VMLocalAdminPass	Enter a password to be used as the local administrator password for the SQL VM you're about to deploy
$AdminPass	Enter the password the AAD user you've used during your Azure Stack deployment
$AdminCreds	This should be the AAD username you've used to deploy Azure Stack in the following format username@AADDirectory.com
$PfxPass	Enter a password to be used by the script when creating the required certificates

Open an elevated PowerShell ISE console and paste the code below into the editor:

# Install the AzureRM.Bootstrapper module
Install-Module -Name AzureRm.BootStrapper -Force

# Installs and imports the API Version Profile required by Azure Stack into the current PowerShell session.
Use-AzureRmProfile -Profile 2017-03-09-profile
Install-Module -Name AzureStack -RequiredVersion 1.2.10 -Force

# Download the Azure Stack Tools from GitHub and set the environment
cd c:\
# Uncomment if you haven't yet ran through the MSSQL PaaS deployment
# Invoke-Webrequest https://github.com/Azure/AzureStack-Tools/archive/master.zip -OutFile master.zip
# Expand-Archive master.zip -DestinationPath . -Force

# This endpoint may be different for your installation
Import-Module C:\AzureStack-Tools-master\Connect\AzureStack.Connect.psm1
Add-AzureRmEnvironment -Name AzureStackAdmin -ArmEndpoint "https://adminmanagement.local.azurestack.external" 

# For AAD, use the following
$TenantID = Get-AzsDirectoryTenantID -AADTenantName "AADDirectoryName.onmicrosoft.com" -EnvironmentName AzureStackAdmin

# For ADFS, replace the previous line with
# $TenantID = Get-AzsDirectoryTenantID -ADFS -EnvironmentName AzureStackAdmin

$VMLocalAdminPass = ConvertTo-SecureString "Password Here" -AsPlainText -Force
$VMLocalAdminCreds = New-Object System.Management.Automation.PSCredential ("mysqlrpadmin", $VMLocalAdminPass)

$AdminPass = ConvertTo-SecureString "Password Here" -AsPlainText -Force
$AdminCreds = New-Object System.Management.Automation.PSCredential ("admin@mydomain.onmicrosoft.com", $AdminPass)

# change this as appropriate
$PfxPass = ConvertTo-SecureString "Password Here" -AsPlainText -Force

# Change directory to the folder where you extracted the installation files
# and adjust the endpoints
C:\Temp\MySQLRP\DeployMySQLProvider.ps1 -DirectoryTenantID $TenantID -AzCredential $AdminCreds -VMLocalCredential $VMLocalAdminCreds -ResourceGroupName "MySqlRG" -VmName "MySQLRP" -ArmEndpoint "https://adminmanagement.local.azurestack.external" -TenantArmEndpoint "https://management.local.azurestack.external" -DefaultSSLCertificatePassword $PfxPass

If you’re not shaking yet, go make yourself another coffee while you wait. Once complete you should receive the following output:

Verify MySQL Resource Provider Deployment

As with the MSSQL provider, let’s go back into the portal and make sure everything is working as intended.

Log onto the Azure Stack Admin Portal as the Service Administrator (the AAD account you used to deploy it)

Navigate to “Resource Providers”, “MySQLAdapter”, “Hosting Servers “ and click “+Add”

Now populate all required settings and click “Create”

If you’ve used the code in this guide, populate using the following values:

• MySql Server Name = “MySQLRP”
• Username = “mysqlrpadmin”
• Password = Value set for $VMLocalAdminPass
• Resource Group = Either create a new one or use “MySqlRG”

NOTE:  Now that we’re on the ASDK, you’ll also have to add a SKU when adding a MySQL hosting server.  The process is identical to the SQL SKU added above.

NOTE:  On ASDK, even though my deployment reported it completed successfully, I had issues when adding the hosting server. I logged onto the MySQL VM it created (MySQLRP) and found that it hadn’t actually installed MySQL at all, only the connector…no wonder it was having a problem. I manually installed MySQL on the VM and tried adding it as a hosting server again, this worked without issue.

That’s us finished for this part of the guide, join me in part 3 when I’ll be deploying the App Service. See you then.