Microsoft has made a new version of the SQL resource provider (RP) for Azure Stack TP2 available. It can be found here in the documentation: https://docs.microsoft.com/en-us/azure/azure-stack/azure-stack-sql-resource-provider-deploy. This RP is an add-on for Azure Stack and allows you to offer SQL as PaaS.
This is a great SQL self-service scenario for Hybrid Cloud. The aforementioned link contains documentation on how to deploy the SQL RP. There are some “Gotchas” with the RP and some other information that is important when deploying and configuring this RP.
I am going to walk through my deployment and configuring experience covering the “Gotchas” and other important information in this blog post. This post will be broken out into the following sections:
- RP Configuration
- Offer/Plan Setup
- Tenant provisioning of SQL PaaS
Ok. Now let’s dive into it.
– DEPLOYMENT –
Before you begin go to the document link and review the RP documentation. You can download the RP on this page from the Download the SQL Server RP installer executable file link.
Once the RP is downloaded extract the files and scripts by running AzureStack.Sql.22.214.171.124.exe. You should have the following:
Now from an elevated PowerShell window run DeploySQLProvider.ps1.
NOTE: Important this should not be run from PowerShell ISE. It fails when it is run from ISE and you may end up with a partial deployment that requires cleanup.
NOTE: Also you can specify a local location for the SQL 2014 SP1 Enterprise Evaluation ISO if you have it downloaded already. To do this run the script with a parameter of -DependencyFilesLocalPath. If not specified the ISO will be downloaded during deployment. I prefer to let the script download it as a part of the deployment.
This script will do the following:
The script will prompt you to input local admin account info. Note that the password you input here will also be used for the SQL SA account.
The script will then prompt you for your Azure Active Directory tenant name. This is YOURDOMAIN.onmicrosoft.com.
You will then be prompted for an Azure Active Directory account. This should be the account you deployed Azure Stack TP2 with. This will be used to access Azure Stack and create stuff such as the resource provider, resource group and other resources needed by the RP.
You need to enter a resource group name. You can leave the default if you want.
You will then be prompted for the SQL server VM Name. Ignore the title of the pop-up here.
The script will then run through all of its steps. Here is what the script does as detailed in the official documentation:
- If necessary, download a compatible version of Azure PowerShell.
- Create a wildcard certificate to secure communication between the resource provider and Azure Resource Manager.
- Download an evaluation build of SQL Server SP1 from the internet or from a local file share.
- Upload the certificate and all other artifacts to a storage account on your Azure Stack.
- Publish gallery package so that you can deploy SQL database through the gallery.
- Deploy a VM using the default Windows Server 2012 R2 image that comes with Azure Stack.
- Register a local DNS record that maps to your resource provider VM.
- Register your resource provider with the local Azure Resource Manager.
- Connect the resource provider to the SQL server instance on the RP VM
As the script runs you will see it run through each of the steps with detail and status. Be patient. I have had this take anywhere from 30 minutes to 45 minutes. Good time to go take a break.
Once the script is done it will show that the installation is successful as shown in the following screenshot.
NOTE: You could run the deployment script with the required parameters to avoid the prompts. For example:
DeploySQLProvider.ps1 -AadTenantDirectoryName “YOURDOMAIN.onmicrosoft.com” -AzCredential “user@YOURDOMAIN.onmicrosoft.com” -LocalCredential “username”
If for some reason the RP deployment fails you will need to view the logs to troubleshoot. Logging will be found in: LOCATIONOFYOURDOWNLOADEDRP\SQL PaaS RP\Logs in the following format DeploySQLProvider.ps1_20161205-171516.txt as shown in the following screenshot.
– RP CONFIGURATION –
After the SQL is deployed you can see it in the Azure Stack portal at https://portal.azurestack.local. The first thing you will want to look for is the resource group. Mine is named Microsoft-SQL-RP. Default is Microsoft-SQL-RP1. The RG is shown in the following screenshot.
Next you will want to ensure the SQL Resource Provider is listed in Resource Providers as shown in the following screenshot.
Go ahead and click on the resource provider to display its details. The first thing you will notice is that we don’t have any SQL Hosting Servers. This is normal and does not mean the script forgot to deploy the SQL server.
Click on SQL Hosting Servers. Now click on + Add.
Input the same name that you put in when prompted for ...