Working with the Windows Azure Pack SQL Server Resource Provider : Dedicating a part of the SQL Server fabric to a specific tenant


Windows Azure Pack (WAP) includes a SQL Server resource provider that allows tenants to deploy and manage databases on a shared SQL Server fabric.

Out of the box, and in the spirit of a Platform as a Service approach, tenants do not control on which backend server the database is created. They are just guaranteed that only the user they choose is created and has access to this database, providing security isolation. They eventually know the target server by looking at the connection string provided back, but WAP handles the placement and quota allocation as needed, based on the plans the tenant has signed up for.


Some enterprises and service providers have been enquiring about the ability to dedicate a SQL Server from the “shared fabric”, to a specific tenant (because - especially with service providers - this is a service their own customers were willing to pay for).

If these organizations were already planning to leverage the Infrastructure as a Service (IaaS) features also supported by WAP, this is a first possible answer : A tenant can also be provided the ability to deploy “gallery items” (virtual machines possibly deploying SQL Server at the same time).

This blog post is going to describe another method, for those enterprises and service providers who are looking for an approach that would fit and stay within the realm of the SQL Server resource provider.

The sample scripts used to achieve this scenario are also being made available as a download, further down in this post.

Dedicating a SQL Server in the shared fabric : The goal

When a tenant goes to create a database in WAP, this is the screen he/she sees:


The “Edition” field corresponds to a “SQL Server group” in Windows Azure Pack, which is a logical grouping of the SQL Servers available in the fabric. Organizations can choose to classify the groups as they see fit : Production, Development, Business Unit XYZ, etc. Our tenant currently sees the “Production” group because he has a subscription to a plan which provides the SQL Server “service” (ability to create databases), through a quota from specific group(s) of servers (could be one or several).

Through the approach described in this blog post, we will get to the situation illustrated by the screenshot below. Here, our tenant, as a result of previously requesting a dedicated part of the SQL Server fabric, now has its own SQL Server group to leverage when creating a database. The new group is surfaced through an additional subscription to a custom dedicated plan for that SQL Server group:



How do we get there? Well, that’s what we are going to cover now Smile

Summary of the approach

Here is the overall flow of what will be happening when a tenant wants a dedicated part of the SQL Server fabric:

  1. A Runbook – started by an admin process, or via a custom tenant interface – deploys an instance of a VM Role gallery item including SQL Server.  In our sample scenario, this VM role will be provisioned under a subscription which is used by the service admin for delivering Database as a Service. Note : As you probably guessed, the time needed to provision the VM Role instance takes most of the time in the automated process. This could probably also be customized/enhanced, for example by tapping into a pool of pre-provisioned physical servers and provision the next one.
  2. Once the VM Role instance is provisioned, the Runbook also registers the corresponding new SQL Server provided with the SQL Server resource provider in WAP
  3. It also adds the server to a new SQL Server group for this tenant
  4. It creates a private plan with the SQL Server “service”, leveraging this new SQL Server group
  5. Finally, it creates a subscription for the tenant to that pan
  6. At this stage, when the tenant goes to create a new database, the “edition” field should now show the new “dedicated” group in the drop down box (unless the user has filtered data to specific subscriptions)

This scenario is the one provided as part of the downloadable script, and further described in the rest of this blog post (through screenshots and script extracts). It is provided as a PowerShell script, so you can easily paste it in a Service Management Automation (SMA) Runbook or another automation engine, as needed. See the section below called “How to trigger the script”

And to be fully comprehensive Smile, the actual downloadable scenario actually covers more than that : When running it multiple times, it handles scale out and conflict detection

  • For example, if you try to run the same script for the same tenant, it will consider that the tenant needs more “dedicated” capacity, and will scale out the tenant-associated VM Role in the shared description, to deploy an additional SQL Server VM instance. The new VM instance is then also registered and added to the tenant-specific SQL Server group in WAP. The dedicated plan/subscription automatically benefits from the expanded SQL Server group capacity.
  • Likewise, if you try to run the script while an initial or scale out deployment is still running, the script will exist after detecting the pending operation that is still in flight.

Great, where can I get it and how do I use it?

First, you’ll need to grab the script here


Then, you will need to edit the variables for your environment:

  • There is a section at the beginning of the main part of the script (at the end of the script, after all the functions – line 323)
  • There is a section in the “New-SQLDedicatedVM” function – line 91. In particular, this is where you may have different ways to provision the new SQL Server, depending on the VM Role gallery item you plan to use.

More specifically, the $AdminUserID variable corresponds to the user who has access to WAP Admin APIs, the $TenantUser is the user who is requesting dedicated SQL Server resources (something that will change with each tenant), and $SharedSubscriptionId is the Id for the “shared” subscription that will host the VM Roles for each tenant. You can find this in the WAP admin portal, or using the PowerShell APIs (Get-MgmtSvcSubscription cmdlet). Other variables are detailed in the script itself.

Finally, you may have to update the beginning of the main part of the script to change the way a Token is retrieved, if you do not use ADFS and/or if you use self-signed certificates. This is at line 342, and you can refer to the “First, let’s request a token” in this blog post for the different options depending on your configuration (there is also a downloadable script for the different authentication situations there).

The script in action

Output in the script window

The following screenshots show three runs of the scripts, for the same tenant

1st run – The script created the “dedicated” server, group, plan, subscription



2nd run – The script detected there was already a VM Role, so it scaled it out and added the new “dedicated” server to the existing SQL Server group


Trying to run the script again when it is still provisioning a VM, results in this:


Result from an admin perspective


In the admin shared subscription, a VM Role is created with SQL Server


In the admin shared subscription, a VM Role was created with SQL Server, for my tenant “TestUser2”



This would be the list of VM Roles in the shared subscription, if another tenant “TestUser” was also requesting dedicated SQL Server capacity in the shared fabric



Here are the VM instances for the VM Role for tenant “TestUser2” – in this case, there was an initial request, and then a scale out, leading to 2 servers. Any new instance provisioned in any tenant will follow the SQLDedicated## naming pattern.



The SQL Server groups were created for each tenant. The group for tenant “TestUser2” is highlighted in this screenshot



The VM Role instances are shown as registered as SQL Servers, and assigned to the groups. In this case, and are part of the group Actual naming conventions can be customized in the script.



A plan <“>” has been created for each tenant. We can see there is a subscription added to each plan.



As part of the plan, access to the “SQL Servers” service has been provided



The details/quota for the SQL Server service show that it is configured to use the custom group for that tenant.



Finally, this shows the subscription added for the right tenant, to their custom/dedicated SQL Server plan

Result from a tenant perspective

When refreshing, the tenant sees multiple subscriptions:

Depending on subscriptions they had before, a tenant might first notice the “subscriptions” filter added in the top right corner of the portal. This only appears when you have more than one subscription.


And the, when creating a database, they should now be able to select the “MyDedicatedSQLServerGroup” subscription, and the only “Edition” (SQL Server group) it surfaces, to create databases on their “own” dedicated SQL Servers.

How to trigger the script

Well, some standard options are possible : This could remain a script or a SMA Runbook, triggered via an external system (e.g. ITSM solution triggering the Runbook after approval) or launched manually by the admin. For more advanced scenarios, there is also the possibility for someone to surface this as a trigger in a custom resource provider, that would in turn be calling the SMA Runbook or script. This last option probably makes more sense as a service provider, where you own custom resource provider also surface other value add actions to tenants

Looking at the script

I’m not including the full script in this blog post, as you can download it and look at the content. However, I wanted to explain the overall structure and a few noteworthy items

Overall structure

The script has a main part (at the end) and several functions.

Sections with variables that need to be customized start at lines 91 and lines 323 (see the “Great, where can I get it and how do I use it? ” section earlier in this blog post)

Main process

Basically, the main process just checks if a Dedicated VM Role for that tenant already exists and:

  • If no, creates the VM Role for that tenant, SQL Server group, SQL Server registration, plan, subscription
  • If yes, scales out the VM Role for that tenant, adds it to the SQL Server group
  • If yes and there is a provisioning process pending, exits the script with a note. Note that this “exit” condition is only there to handle conflicts for requests from the same tenant. Nothing prevents multiple tenants to run the script at the same time. How many could do so depend on how you designed your WAP environment.

Extract of the main process

321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 ipmo mgmtsvcadmin #Update these variables (and also in the 'New-SQLDedicatedVM' function) #This is the ID for the shared admin subscription $SharedSubscriptionID = "90aed2b8-a71d-444d-b804-a7b5c6d9805d" #This is the URI for the Tenant API $TenantUri = "" #This is the URI for the Admin API $AdminUri = "" #This is the ADFS endpoint, when using ADFS $AdfsAddress = "" #This is the user who has access to WAP Admin APIs $AdminUserID = "" #This is the password for the previous user, used to retrieve the token $AdminUserPassword = "Pass@word1" #This is the user who is requesting dedicated SQL Server resources (this will change with each tenant) $TenantUserID = "" #This password will be set as the 'SA' user password on newly deployed SQL Server instances, and used to register them with the SQL Server Resource Provider in WAP $NewSQLServerSAPassword = "Pass@word1" #Get token, define headers $Token = Get-AdfsToken -domain $AdminUserID.split("@")[1] -username $AdminUserID.split("@")[0] -password $AdminUserPassword -adfsAddress $AdfsAddress -clientRealm 'http://azureservices/AdminSite' $SharedUserID = (Get-MgmtSvcSubscription -AdminUri $AdminUri -Token $Token| Where-Object {$_.SubscriptionId -eq $SharedSubscriptionID}).AccountAdminLiveEmailId $SharedHeaders = @{Authorization = "Bearer $Token" "x-ms-principal-id" = $SharedUserID} $AdminHeaders = @{Authorization = "Bearer $Token" "x-ms-principal-id" = $AdminUserID} $CloudServiceName= $TenantUserID.Split("@")[0] + "-SQLDed-CS" $VMRoleName= $TenantUserID.Split("@")[0] + "-SQLDed-VM" $NewSQLGroupName = "Ded-Grp-" + $TenantUserID $NewDedicatedPlanDisplayName = "Ded-SQL-" + $TenantUserID New-CloudService -Name $CloudServiceName Switch (Get-VMRoleStatus){ "" { write-host -ForegroundColor green "["(date -format "HH:mm:ss")"] There is no SQL Server instance in the tenant-associated Cloud Service in the shared subscription. We'll go through the full processs..." $LatestDeployedComputerName = New-SQLDedicatedVM -SAPassword $NewSQLServerSAPassword While ((Get-VMRoleStatus) -eq "Provisioning") { write-host "["(date -format "HH:mm:ss")"] VM Role is in 'provisioning' state. Sleeping for 3 minutes..." Start-Sleep 180 } $LatestDeployedComputerName = Get-LatestDeployedComputerName -CloudServiceName $CloudServiceName -VMRoleName $VMRoleName write-host "["(date -format "HH:mm:ss")"] VM Role provisioning is finished, VM Name is $LatestDeployedComputerName" $NewSQLServerGroupId = New-SQLGroup -SQLGroupName $NewSQLGroupName Add-SQLServerToGroup -SQLComputerName $LatestDeployedComputerName -SQLGroupId $NewSQLServerGroupId -SQLGroupName $NewSQLGroupName -SAPassword $NewSQLServerSAPassword $NewDedicatedPlanId = New-SQLDedicatedPlanWithSQLServiceQuota -PlanName $NewDedicatedPlanDisplayName -SQLServiceGroupName $NewSQLGroupName Add-SubscriptionToDedicatedPlan -PlanId $NewDedicatedPlanId -SubscriptionFriendlyName 'MyDedicatedSQLServerGroup' write-host -ForegroundColor green "["(date -format "HH:mm:ss")"] Done!" } "Provisioned" { write-host -ForegroundColor green "["(date -format "HH:mm:ss")"] There is already a VM role instance provisioned for this tenant in the shared fabric, this is a scale out process..." Add-SQLDedicatedVM -NumberOfInstances ((Get-VMRoleInstanceCount) + 1) While ((Get-VMRoleStatus) -eq "Provisioning") { write-host "["(date -format "HH:mm:ss")"] VM Role is in 'provisioning' state. Sleeping for 3 minutes..." Start-Sleep 180 } $LatestDeployedComputerName = Get-LatestDeployedComputerName -CloudServiceName $CloudServiceName -VMRoleName $VMRoleName write-host "["(date -format "HH:mm:ss")"] VM Role provisioning is finished, Additional VM Name is $LatestDeployedComputerName, and new instance count is" (Get-VMRoleInstanceCount) $NewSQLServerGroupId = New-SQLGroup -SQLGroupName $NewSQLGroupName Add-SQLServerToGroup -SQLComputerName $LatestDeployedComputerName -SQLGroupId $NewSQLServerGroupId -SQLGroupName $NewSQLGroupName -SAPassword $NewSQLServerSAPassword write-host -ForegroundColor green "["(date -format "HH:mm:ss")"] Done!" } "Provisioning" #There is already a provisioning process happening, exiting for now { write-host -ForegroundColor green "["(date -format "HH:mm:ss")"] There is already a provisioning process happening for the same tenant, exiting to avoid conflicts, please try again later..." exit }


Role of each function

Note : For readability purposes, these functions are not pasted in the blog post, but they are definitely in the downloadable script Smile

  • Get-AdfsToken : This function retrieves an administrative token with the WAP admin API. If you do not use ADFS and/or use self-signed certificates, you will need to replace this function with another call. This is explained at section “First, let’s request a token” in this blog post (there is also a downloadable script for the different authentication situations there). To be more secure, this function could also be updated to use a PSCredential object rather than the user name and password variables from the script. In Service Management Automation (SMA), this could be an “asset” (variable) in the SMA environment.
  • New-CloudService : If it does not exist yet, this function creates a Cloud Service in the shared admin subscription, to host the future VM Role and its instances.
  • New-SQLDedicatedVM : This function creates a VM Role instance for the tenant, in the shared admin subscription. It exits after sending the creation request through the Tenant API. The actual script and JSON payload used may be customized if you use a different gallery item, but this is based on the samples provided in this blog post by Charles Joy.
  • Add-SQLDedicatedVM : This function scales out the VM Role instance for a tenant. It is pretty basic, as it just requires to send the new number of instances via the Tenant API.
  • New-SQLGroup : This function uses the Admin REST API to create a SQL Server, and returns the “Id” for the new group. If the group already exists, it just returns the existing “Id”.
  • Add-SQLServerToGroup : This function uses the Admin REST API to register a SQL Server into a specific group in WAP. Note that, this function and the previous one could actually use the SQL Server Resource Provider PowerShell APIs for more simplicity. Having them use the SQL Server Resource Provider REST API is just a way to show how to work with that API from a REST web service perspective, and how to send the right payload.
  • New-SQLDedicatedPlanWithSQLServiceQuota : This function creates a dedicated plan and adds the SQL Server service and group to the plan. It is using the Windows Azure Pack Admin PowerShell cmdlets. Note that the "OfferEditionId" is always set to the same value in this function. This is fine in the current process, since it needs to be unique within the same plan, and the plan is created with only one SQL Server group in our process. If you were to customize this script and need to have multiple SQL Server groups ("editions") in the same plan, you might want to update the script to generate a unique ID for the different SQL Server group quotas.
  • Add-SubscriptionToDedicatedPlan : This function creates the subscription for the tenant, to the specified dedicated plan. It also uses the Windows Azure Pack Admin PowerShell cmdlets.
  • Get-VMRoleStatus : This function checks the status of the VM Role associated to the tenant in the shared subscription. It returns the actual string for the current status : “Provisioning”, “Provisioned”,… It is used to wait for VM Role instances deployments to be finished before moving on to the rest of the script, and also to determine if the VM Role exists or not.
  • Get-VMRoleInstanceCount : This function retrieves the current number of VM instances in the VM Role associated to the tenant in the shared subscription. It is used for scale out, to compute the new number of instances to send via the JSON payload (new number = current number +1)
  • Get-LatestDeployedComputerName : This function queries the VM Role associated to the tenant in the shared subscription, to determine the name of the last VM created. Note that it is doing that in the context of that specific VM Role for that specific tenant. And since we are preventing multiple provisioning processes at the same time for the same tenant, it should be reliable in its current form.

In closing

I hope the content of this blog post will prove to be valuable to you, either to achieve this or a similar scenario, and/or to better understand the API and extensions capabilities of Windows Azure Pack. In all cases, keep the feedback coming!