Freigeben über

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

  • Artikel

 

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 'https://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!

Comments

  • Anonymous
    January 01, 2003
    Hi Kevin,
    Apologies for the late reply, I was out of the office.
    In this blog post, the "dedicated" SQL Server groups are dynamically created as virtual machines managed by Hyper-V in WAP, but you could replace the VM Role deployment logic by targeting a pre-provisioned set of physical/virtual machines running elsewhere, or leverage PowerCLI to provision the SQL Servers on VMware before registering them in the SQL Server provider. The key is just that WAP should be able to access these SQL Server for server registration and database deployments.
    I hope this clarifies, let me know.
    Thanks
  • Anonymous
    January 01, 2003
    Hi Kevin
    The example from this blog post is based on a script doing all the steps you are looking for : the user asks for a dedicated "SQL Server group", and the SQL Server is provisioned, added to a group, added to a custom plan, and the user's subscription to the plan is created.
    If you already have a set of SQL Server instances pre-provisioned, you could do the rest in multiple ways. Here are two examples:
    1/ Pre-provision SQL Server groups per domains and the associated plans. Then, you could use Service Management Automation (SMA) hooks to react to a new user or another subscription, and trigger the subscription to the right plan, based on the user domain (assuming you can derive it from the user's login, or look it up elsewhere with the SMA Runbook).
    2/ Trigger the whole logic through a SMA hook (for a new user or another subscription), except that the SQL Server does not need to be provisioned.
    As of today, it is not possible however to filter specific plans for users based on their domain memberships or else. Plans can either be public (available to all users), public with a password (you could have one by domain, if there are not too many to keep things manageable), or private (need to be assigned by scripting or an administrator)
    Hope this helps, feel free to contact me offline as needed. Thanks!
  • Anonymous
    January 01, 2003
    great article, thank you for sharing.
  • Anonymous
    April 30, 2014
    You're always showing us great stuff! Thanks again!
  • Anonymous
    April 30, 2014
    You're always showing us great stuff! This is very useful exhibition of how to integrate and follow-through on multi-tenant approach. Thanks.
  • Anonymous
    August 15, 2014
    Hi Bruno,

    I read the above with interest and am wondering if you can clarify something for me. I am trying to design a multi-tenant database as a service solution without making use of Service Centre as a decision by others has been made to go with an alternative toolset in the future. One of the design considerations is that different tenants should have their databases hosted on separate VMs and instances. In my mind this could work if we pre-build these instances and then using ADFS, have tenants only be able to choose groups available to them which in turn will point to the correct backend servers. Surely, considering what you have just done in the code above, this must be possible using just the SQL resource without having to leverage Hyper-V manager etc.? So each tenant has their own tenant site and are “directed” via groups onto their own servers (separate VM’s – prebuilt each in their own VLAN and AD domain hosted on a Hyper-V cluster) all managed by one admin site. Is this possible or am I going to have to have separate admin sites for each customer – in effect a cloud for each customer?

    Regards

    Kevin Rankin
  • Anonymous
    September 04, 2014
    Hi Bruno,

    I have now built a POC environment that uses ADFS so that each customer can now register on the tenant portal as an authenticated windows account from multiple domains (so we can now identify them for billing purposes). This works well. This is done, as you pointed out by first manually provision SQL instances in their own domain on shared infrastructure each in their own VLAN. I can then restrict each user to a "Group" in WAP which lands them on their own SQL instances in their respective domain. Unfortunately in order to do this I lose the ability for them to "self-provision" as an admin type person frist has to choose which group they should be in according their domain name by adding them to a plan associated with that group. Once they are in the right plan (and group) they can now create databases etc. Do you know of any way that I could tweak it to restrict them to certain plans automatically depending on what domain they belong to? This would allow us to be more flexible in our offerings. It would also prevent an admin accidentally inserting them into the wrong plan!

    Regards

    Kevin Rankin