Part 2: Deploy MSSQL Resource Provider into ASDK

Previous Post in Series: Part 1:  Azure Stack Deployment Guide (Updated)

Welcome back folks. So…with all the work we’ve done thus far, we’re now in a position to deploy the MSSQL RP. This will allow you to provide SQL databases as a service, so PaaS!

Here’s a list of what we’re going to do in this section of the guide:

• Download Required Files
• Deploy the MSSQL Resource Provider
• Install MSSQL Server on the SQL RP adapter VM
• Add the SQL RP adapter VM as a SQL Hosting Server
• Create a SQL database

Download Required Files

First things first, we’ll need to grab a hold of the SQL RP Binaries file. The code below will take care of that by doing the following:

• Download SQL RP binaries file
• Extract the contents of the zipped archive to C:\Temp\MSSQLRP

Launch an elevated PowerShell console, paste in the following code and execute it:

# Download SQL RP Binaries File
Invoke-WebRequest -Uri https://aka.ms/azurestacksqlrp -OutFile C:\Temp\MSSQLRP.exe

# Create Directory for files extracted from above .exe
New-Item -ItemType Directory -Path C:\Temp\MSSQLRP\

# Extract files from WinRAR SFX archive
cd C:\Temp
.\MSSQLRP.exe -dC:\Temp\MSSQLRP -s

Deploy the MSSQL Resource Provider

Before we can run the “DeploySQLProvider.ps1” script, we’ll have to make a quick modification to it. Earlier in the guide, we downloaded an image…this image is full GUI, the above script is set to look for a CORE image by default. Luckily though, this is a quick change to allow our deployment to continue on without issue.

• Launch an elevated PowerShell ISE console and open “C:\Temp\MSSQLRP\DeploySQLProvider.ps1”
• Search for the following line “$Core = $true” – it was line 56 in the script at the time of writing
• Change this line to: “$Core = $false“
• Now save and close the file

You’ll be running a PowerShell cmdlet in a minute, the table below shows what information each variable is expecting:

Variable	Description
$TempDir	This should be the directory you extract the MySQL RP installation files to
$AADGlobalAdmin	This should be the username of a Global Administrator in your Azure subscription. Format: username@mydirectory.onmicrosoft.com
$AADGlobalAdminPW	This should be the password for the above user
$VMLocalAdminPass	This should be a password for the local administrator account on the SQL VM being deployed
$ASDKAdminPass	This should be the password for the AzureStack\AzureStackAdmin user
$PfxPass	This should be a password for exporting your certificates as .pfx - Choose whatever suits you 🙂
$ERCS01IP	This should be the IP address of the Azs-ERCS01 VM that's deployed as part of the ASDK

• Launch an elevated PowerShell ISE console, paste in the following code, modify it as required and run it.

# This should be the directory where you extracted the SQL RP installation files to
$TempDir = 'C:\TEMP\MSSQLRP'

# This should be the IP address of the Azs-ERCS01 VM that's deployed as part of the ASDK
$ERCS01IP = 'IP address'

# This should be the credentials for a Global Administrator on your Azure subscription
$AADGlobalAdmin = 'username@mydirectory.onmicrosoft.com'
$AADGlobalAdminPW = ConvertTo-SecureString 'Password here' -AsPlainText -Force
$AADGlobalAdminCred = New-Object System.Management.Automation.PSCredential ($AADGlobalAdmin, $AADGlobalAdminPW)

# Set the credentials for the Resource Provider VM
$VMLocalAdminPass = ConvertTo-SecureString "Password here" -AsPlainText -Force
$VMLocalAdminCred = New-Object System.Management.Automation.PSCredential ("sqlrpadmin", $VMLocalAdminPass)

# Set the AzureStackAdmin credential required for Privileged Endpoint access
$ASDKAdminPass = ConvertTo-SecureString "Password here" -AsPlainText -Force
$ASDKAdminCred = New-Object System.Management.Automation.PSCredential ("AzureStack\AzureStackAdmin", $ASDKAdminPass)

# change the following as appropriate
$PfxPass = ConvertTo-SecureString "Password here" -AsPlainText -Force

# Change directory to the folder where you extracted the installation files
.$TempDir\DeploySQLProvider.ps1 `
    -AzCredential $AADGlobalAdminCred `
    -VMLocalCredential $VMLocalAdminCred `
    -CloudAdminCredential $ASDKAdminCred `
    -PrivilegedEndpoint $ERCS01IP `
    -DefaultSSLCertificatePassword $PfxPass `
    -DependencyFilesLocalPath $TempDir\cert

Success looks like this…

Install MSSQL Server on the SQL RP adapter VM

Now that we’ve got our SQL RP Adapter VM deployed we can crack on and install SQL on it. I’m installing it on the same VM as the SQL adapter to save space in the environment. Log onto the SQL VM that was deployed above (using the sqlrpadmin credentials). As for the SQL version, I’m going with SQL 2016 Developer with SP1 and downloading it from https://my.visualstudio.com

I won’t go through a SQL installation in detail, but here are a few notes:

I only install the following features:

• Database Engine Services
• Client Tools Connectivity

I kept the default instance name of “MSSQLSERVER”

I kept the defaults for “Service Accounts”

I chose “Mixed Mode” authentication and clicked “Add Current User” to add the “sqlrpadmin” user to the SQL Server administrators group

I also downloaded and installed SQL Management Studio in case I need to do any troubleshooting or admin tasks on the server. You can either click on the link within the SQL installer, or get it HERE

Add the SQL RP adapter VM as a SQL Hosting Server

So, we’ve got the SQL RP adapter installed and configured and SQL installed on a VM in the environment…time to add it as a hosting server.

The first thing you’ll need is the SQL VM name.

• Select “Virtual Machines” from the left pane
• Copy the VM name to your clipboard

• Click “More Services” and select “SQL Hosting Servers”
• Click “Add”

• Populate the blade as required using the table below as a guide

Field	Expected Value
SQL Server Name	It should be in your clipboard
Username	Should be "sa"
Password	You set this during the deployment of SQL
Size of Hosting Server in GB	Enter anything you want here within reason
Always on Availability Group	Leave this unchecked
Subscription	Leave the default value
Resource Group	Either create a new one or use an existing if you prefer
Location	Leave the default value
SKU	Create as per instructions below

Once you’ve populated all fields and created your SKU, click “Create”

NOW…It’s at this point I got a connection error. I was pretty sure I’d done everything correctly but was still faced with a sea of red. Shame on me though, I forgot to screenshot it.

Anyway, the short story is that I had actually done everything correctly but for some reason SQL decided to disable TCP/IP in its Network Configuration. I found this by loading up “SQL Server 2016 Configuration Manager” and browsing to “SQL Server Network Configuration”.

Here I changed the status of “TCP/IP” from “Disabled” to “Enabled”

I then tried my SQL Hosting Server addition again and it completed without issue…woohoo!

So, something to be aware of in case you run into the same problem.

Create a SQL Database

The whole point of deploying the MSSQL RP is to allow the provision of self-service databases to our tenant. Let’s quickly create one to confirm our deployment is working as expected.

NOTE:  I chose to ignore the advice from Microsoft advising that the SKUs can take up to an hour to become available in the portal…apparently that’s good information as I couldn’t create a database for 45 minutes after deploying the hosting server for this reason. So if you run into the same issue, just go for lunch or something and come back do this afterwards 🙂

• Click “+ New”, “Data + Storage” and select “SQL Database”

• Populate the blade as required using the table below as a guide

Field	Expected Value
Database Name	Give your database a name
Collation	Leave at the default unless there is a reason to change it
Max Size in MB	Enter a size for your database in MB
Subscription	Leave the default value
Resource Group	Create a new one or deploy your new database into an existing one
Location	Leave the default value
SKU	Select from the available SKUs created by the Service Provider
Login	Create login credentials to your database and click "OK"

• Create a login for your SQL database and click “OK”

• Click “Create” to provision your new database

Once complete, you should receive a notification confirming it:

If you log onto the SQL VM, you can see that the database has been created in the default instance as expected 🙂

If we look in the portal, we can also see connection string for our new database, notice the username and password.

…and that’s it for the MSSQL RP deployment, let’s move on and do the same for MySQL in Part 3.