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

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:

VariableDescription
$TempDirThis should be the directory you extract the MySQL RP installation files to
$AADGlobalAdminThis should be the username of a Global Administrator in your Azure subscription. Format: username@mydirectory.onmicrosoft.com
$AADGlobalAdminPWThis should be the password for the above user
$VMLocalAdminPassThis should be a password for the local administrator account on the SQL VM being deployed
$ASDKAdminPassThis should be the password for the AzureStack\AzureStackAdmin user
$PfxPassThis should be a password for exporting your certificates as .pfx - Choose whatever suits you 🙂
$ERCS01IPThis 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…

clip_image001

 

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

clip_image002

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

clip_image003

I kept the default instance name of “MSSQLSERVER”

clip_image004

I kept the defaults for “Service Accounts”

clip_image005

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

clip_image006

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

clip_image007

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

clip_image008

clip_image009

  • Populate the blade as required using the table below as a guide
FieldExpected Value
SQL Server NameIt should be in your clipboard
UsernameShould be "sa"
PasswordYou set this during the deployment of SQL
Size of Hosting Server in GBEnter anything you want here within reason
Always on Availability GroupLeave this unchecked
SubscriptionLeave the default value
Resource GroupEither create a new one or use an existing if you prefer
LocationLeave the default value
SKUCreate as per instructions below

clip_image010

clip_image011

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”.

clip_image012

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

clip_image013

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”

clip_image014

  • Populate the blade as required using the table below as a guide
FieldExpected Value
Database NameGive your database a name
CollationLeave at the default unless there is a reason to change it
Max Size in MBEnter a size for your database in MB
SubscriptionLeave the default value
Resource GroupCreate a new one or deploy your new database into an existing one
LocationLeave the default value
SKUSelect from the available SKUs created by the Service Provider
LoginCreate login credentials to your database and click "OK"
  • Create a login for your SQL database and click “OK”

clip_image015

  • Click “Create” to provision your new database

clip_image016

Once complete, you should receive a notification confirming it:

clip_image017

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

clip_image018

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

clip_image019

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

2 Replies to “Part 2: Deploy MSSQL Resource Provider into ASDK”

  1. i was unable to connect tot he SQLVM (SQLVM1180) via remote desktop to install SQL.

    It looks like WinRM, Remote Powershell, and remote Deskstop are not enabled on the VM.

    were you able to just connect to the machine? I am on the 10202017 build of Azure Stack, running the 10252017 version of the SQL RPs.

    1. Hi Matthew,

      I connected to the VM from Hyper-V manager. To correct to it from the ASDK portal, you’ll likely have to modify the relevant Network Security Group yo allow it. I can’t remember off the top of my head if I had to enable Remote Desktop on the VM though.

      Best regards,
      David

Leave a Reply

Your email address will not be published. Required fields are marked *