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:

image

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:

clip_image002

 

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

DownloadButton

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

image

 

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

image

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

clip_image006


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”

 

image

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

 

image

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.

 

image

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

 

image

The VM Role instances are shown as registered as SQL Servers, and assigned to the groups. In this case, SQLDedicated01.contoso.com and SQLDedicated02.contoso.com are part of the group Ded-Grp-testuser2@contoso.com. Actual naming conventions can be customized in the script.

 

image

A plan “Ded-SQL-testuser2@contoso.com” has been created for each tenant. We can see there is a subscription added to each plan.

 

clip_image010

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

 

clip_image012

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

 

clip_image014

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 = "https://wap01.contoso.com:30005"
#This is the URI for the Admin API
$AdminUri = "https://wap01.contoso.com:30004"
#This is the ADFS endpoint, when using ADFS
$AdfsAddress = "https://adfs01.contoso.com"
#This is the user who has access to WAP Admin APIs
$AdminUserID = "brunosa@contoso.com"
#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 = "testuser2@contoso.com"
#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!