...building hybrid clouds that can support any device from anywhere
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.
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
Here is the overall flow of what will be happening when a tenant wants a dedicated part of the SQL Server fabric:
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 , the actual downloadable scenario actually covers more than that : When running it multiple times, it handles scale out and conflict detection
First, you’ll need to grab the script here
Then, you will need to edit the variables for your environment:
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 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:
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, SQLDedicated01.contoso.com and SQLDedicated02.contoso.com are part of the group Ded-Grpemail@example.com. Actual naming conventions can be customized in the script.
A plan “Ded-SQLfirstname.lastname@example.org” 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
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.
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
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
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)
Basically, the main process just checks if a Dedicated VM Role for that tenant already exists and:
Note : For readability purposes, these functions are not pasted in the blog post, but they are definitely in the downloadable script
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!
You're always showing us great stuff! Thanks again!
You're always showing us great stuff! This is very useful exhibition of how to integrate and follow-through on multi-tenant approach. Thanks.
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?
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.
great article, thank you for sharing.
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!
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!