In this article, you will see how to install and configure SQL Server resource provider in the Azure Stack Development Kit environment. In the  next articles, I will cover the installation and configuration for MySQL and App Service resource providers.

Introduction

Azure Stack can offer Platform as a Service (PaaS) services to users. With the initial release of Azure Stack, Microsoft offers four PaaS-based services:

• Microsoft SQL Server and MySQL databases
• App Service for websites
• Key Vault for securely storing secrets such as passwords and certificates
• Azure Functions for serverless compute

SQL Server resource provider is not part of the core installation of Azure Stack. It’s optional. As an Azure Stack Cloud Architect or Cloud Operator, you can decide whether you want to offer SQL Server as a service to your users. The underlying infrastructure for SQL resource provider, in this case Database Software, is installed on a dedicated IaaS-based virtual machine in the Azure Stack environment.

Before you start installing any resource provider, you should perform the installation only when the Azure Stack environment is in a healthy state. The following high-level steps show you how to check the Health Status in Azure Stack:

1. Sign-in to the Azure Stack Administration portal as an Azure Stack Cloud Operator at https://adminportal.local.azurestack.external.
2. Navigate to Region management.
3. Select the Region you are installing the resource provider into. In this example, since we are using Azure Stack Development Kit environment, it will be the “local” Region.
4. In the Settings blade, review the System Health Tests results.

After you have determined the environment is healthy, you can proceed with the installation.

Installing SQL Server Resource Provider

To install the SQL resource provider, you must obtain the latest copy of the installation media and any additional scripts provided from Microsoft. You can obtain the required files from the following location:

Download the SQL resource provider binaries file.

As of this writing, the Azure Stack SQL Server resource provider version is 1.1.10.0 and the size is 38.4 MB.

With the Azure Stack Development Kit, the installation must be performed on the Hyper-V host.

Now take the following steps:

1. You must have a Windows Server 2016 Core image. If you already registered your development kit with Azure, then you can syndicate with Azure marketplace and download Windows Server 2016 Core Image. Check How to Add Windows Server 2016 image to Azure Stack. 
2. You can also use Windows Server 2016 with Desktop Experience (full GUI), but I prefer to use Server Core to save some space. If you want to go with full GUI, then you need to change line 56 in the DeploySqlProvider.ps1 script to “$Core = $false” after extracting AzureStack.Sql.1.1.10.0.exe in Step 3.
3. Extract the content of AzureStack.Sql.1.1.10.0.exe to a temporary location on the Hyper-V host.
4. The extracted contents will look like this:
5. Open a new elevated (administrative) PowerShell console and change to the directory where you extracted the files.
6. Run the DeploySqlProvider.ps1 script and specify each required parameter in the command line.
7. To facilitate the deployment process in Step 6, you can run the following PowerShell commands to automate the deployment. But before running the script, you need to change and update the 7 variables below with the account information and passwords as needed. Once you are prompted to enter admin credentials, you need to specify the login credentials for the administrator used for the deployment for this environment:
   • $TempDir
   • $ERCS01IP
   • $ServiceAdmin
   • $AdminPass
   • $vmLocalAdminPass
   • $CloudAdminPass
   • $PfxPass

8. # Install the AzureRM.Bootstrapper module, set the profile, and install AzureRM and AzureStack modules
   Install-Module -Name AzureRm.BootStrapper -Force -Confirm:$false
   Use-AzureRmProfile -Profile 2017-03-09-profile
   Install-Module -Name AzureStack -RequiredVersion 1.2.11 -Force -Confirm:$false
   
   # Use the NetBIOS name for the Azure Stack domain. On ASDK, the default is AzureStack
   $Domain = "AzureStack"
   
   # Point to the directory where the SQL Server RP installation files were extracted
   $TempDir = 'D:\ASDK\Resource Providers'
   
   # IP address of the Azs-ERCS01 VM that's deployed as part of the ASDK
   $ERCS01IP = '192.168.200.225'
   
   # This should be the credentials for a Global Administrator on your Azure Subscription
   $ServiceAdmin = "admin@addirectory.onmicrosoft.com"
   $AdminPass = ConvertTo-SecureString "P@ssw0rd1" -AsPlainText -Force
   $AdminCreds = New-Object System.Management.Automation.PSCredential ($serviceAdmin, $AdminPass)
   
   # Set the credentials for the Resource Provider VM
   $vmLocalAdminPass = ConvertTo-SecureString "P@ssw0rd1" -AsPlainText -Force
   $vmLocalAdminCreds = New-Object System.Management.Automation.PSCredential ("sqlrpadmin", $vmLocalAdminPass)
   
   # and the cloudadmin credential required for Privileged Endpoint access
   $CloudAdminPass = ConvertTo-SecureString "P@ssw0rd1" -AsPlainText -Force
   $CloudAdminCreds = New-Object System.Management.Automation.PSCredential ("$domain\CloudAdmin", $CloudAdminPass)
   
   # change the following as appropriate
   $PfxPass = ConvertTo-SecureString "P@ssw0rd1" -AsPlainText -Force
   
   # Change directory to the folder where you extracted the installation files
   # and adjust the endpoints
   .$tempDir\DeploySQLProvider.ps1 -AzCredential $AdminCreds `
   -VMLocalCredential $vmLocalAdminCreds -CloudAdminCredential $cloudAdminCreds `
   -PrivilegedEndpoint $ERCS01IP -DefaultSSLCertificatePassword $PfxPass -DependencyFilesLocalPath $tempDir\cert

9. The installation process typically takes approximately 60 to 90 minutes to complete depending upon your hardware. You will receive a success message like the following:
10. To verify that the deployment is succeeded, sign in to the admin portal as the service administrator. Browse for Resource Groups >, click on the system.local.sqladapter resource group and verify that all 4 Deployments Succeeded.

After the installation is complete, the SQL Server resource provider is available for configuration through the Azure Stack Administrator Portal under the Resource Providers section. You must then use the Administrator Portal to configure the provider with the details of a hosting Microsoft SQL Server that will be used for capacity when provisioning SQL Server databases.

Configuring the SQL Server Resource Provider

The installation of SQL Server resource provider does not involve any configuration. Therefore, after you install SQL resource provider, it’s not immediately ready for users to consume them because no capacity has been allocated yet.

After installing the SQL resource provider, you must allocate capacity that will be used when provisioning databases in Azure Stack. To facilitate this, you must either create a new SQL Server instance, or attach an existing SQL Server to the SQL resource provider. Below are the high-level steps to perform this task:

1. Sign-in to the Azure Stack Administrator Portal at https://adminportal.local.azurestack.external/.
2. Navigate to More services > click on the SQL Hosting Servers.
3. Click +Add and then provide the following information:
   • SQL Server Name. The name of the SQL Server.
   • Username. The user name that will be used by the resource provider to access the SQL Server.
   • Password. The password for the user name specified above.
   • Size of Hosting Server in GB. The amount of database space to allocate.
   • Subscription. The Azure Stack Subscription it will be allocated with. Default Provider Subscription will be selected by default.
   • Resource Group. The Azure Stack Resource Group it will be allocated with.
   • Location. The Azure Stack location it will be associated with. For Azure Stack Development Kit, the location is set to local.
   • SKU. To enable different service offerings, you must associate the SQL Server with a SKU that includes information such as Name, Family, Edition and Tier. After completing the steps above, Azure Stack is associated with the SQL resource provider. You can start provisioning SQL Server Databases.

Adding Quota for the SQL Resource Provider

After you have installed SQL Server resource provider, you must configure it so that the resource provider knows which SQL Servers it can use and how much capacity they have available for user consumption. You can also create custom quotas for users to be assigned. However, there are four default quotas available post-installation that you can choose from:

These quotas are added to the plan you define in Azure Stack, which is in turn is attached to the offer to which users subscribe.

That’s it there you have it!

Until then, see you in the next article where we will install and configure MySQL resource provider.

Thanks for reading!

Cheers,
-Ch@rbel-