Manage Tenant Database Workloads with Resource Governor in Azure Pack

As of the Azure Pack Update Rollup 5 release, WAP administrators can now use the functionality of SQL Server 2014 Resource Governor in WAP to manage tenant workloads on the same SQL Server.

You can read more about UR5 and how to download it here.

What is Resource Governor?

Resource Governor is a feature that was introduced in SQL Server 2008 and further enhanced in the SQL Server 2014 release (WAP only supports Resource Governor in SQL Server 2014). Resource Governor allows you to manage the resource consumption (across CPU, Memory, and IOPS) of incoming requests which lends itself well to multi-tenant scenarios like those in WAP. You can read more about Resource Governor here.

Why should I use it?

Without Resource Governor, your tenants can find themselves fighting the “noisy neighbor syndrome” where they cannot predict the performance of their own databases since other databases on the same server may be monopolizing server resources at any time. Resource Governor solves this issue by allowing the administrator to define policies and limits on the server so that tenants can receive predictable service.

Keep reading for a step-by-step guide on how to use this feature in WAP to offer multiple levels of predictable service.

Using Resource Governor to Provide Multiple SLAs to Tenants

I will assume some basic familiarity with the SQL Resource Provider in WAP, so if you want to read more, please check the TechNet documentation.

Scenario

The steps I am about to walk you through can apply to a few different scenarios:

Scenario A:

I am the database administrator at a large organization and I want to make sure that a critical workload is consistently getting the resources it needs to run and is unaffected by other less critical workloads that get periodically run.

Scenario B:

I am a service provider offering databases to my customers and I want to sell varying levels of database performance at varying prices to my tenants.

These and more scenarios can now be accommodated using Azure Pack Update Rollup 5 with SQL Server 2014 Enterprise Edition. The steps below can be generalized and values changed to accommodate both scenarios above.

STEP 1: Create a Resource Group Enabled SQL Group

If you are familiar with the WAP SQL Resource Provider, this step is not new to you. (If it is, there are details here) The only added step is to make sure you check the box that let’s WAP know that this is a group for resource governor enabled servers (Note: this feature works with AlwaysOn or Standalone servers).

clip_image002

Using PowerShell?

PS C:\> Add-MgmtSvcSqlServerGroup -AdminUri "https://Computer01:30004" -Token $Token -GroupName "SQL Group 01" –ResourceGovernorEnabled true

STEP 2: Add a SQL 2014 Enterprise Edition Server

SQL Server instances (whether virtual or actual) are the fabric of your DBaaS offering in WAP. To use resource governor, you must connect to a version that is SQL 2014 Enterprise Edition or later. As usual, WAP will ask for the credentials to connect to the server and for the size of the server, but additionally there are more fields that are asked of now: number of CPU Cores, amount of Memory, number of IOPS per volume, and maximum number of resource pools allowed.

clip_image005

If you are not sure how to determine these values, this SQL Server documentation may be a good place to start. As far as maximum number of resource pools, this is where you can choose to limit the number of resource pools created one server before WAP begins creating the pools on a new server. WAP automatically caps this value to 18 pools as a recommended maximum before performance starts to falter on the server. WAP will never create a resource pool on a server that cannot support the minimum performance guaranteed by that pool, so you do not have to consider this when setting the maximum pools per server.

Once the server is added, you can view the Server Dashboard to monitor and manage capacity of the server across all dimensions to make decisions about when to add new servers, change server groups etc:

clip_image007

Using PowerShell?

PS C:\> $Creds = Get-Credential

PS C:\> Add-MgmtSvcSqlHostingServer -AdminUri "https://Computer01:30004" -Token $Token –Name "SQLServer01.Contoso.com" -TotalSpaceMB 2048 -ServerGroupId "g5sho0" -User $Creds –NumberOfCpuCores 1 –TotalMemoryGB 2 –SupportedIopsPerVolume 100 –MaximumResourcePools 18

Note: As of Update Rollup 5, WAP now supports editing server values (only increases in value) at the PowerShell level. This can be accomplished using Set-MgmtSvcSqlHostingServer

STEP 3: Create Resource Pool Template(s)

The most important step in setting up your resource governed environment is creating the resource pool templates in WAP (for more information on resource pools, see here). This is where you will want to carefully plan out the various tiers of service you want to offer your tenants. Since my goal is to offer two different levels service, I am first going to create a lower tier template for a resource pool with access to a relatively small portion of the server across CPU, Memory, and IOPs:

clip_image010

Note: If you are comparing with the SQL Server Resource Pool documentation, you will notice that SQL Server actually takes percentage values for many of the above values. In WAP, we do not ask the administrator for percentages because it is important to create a consistent experience for tenants across a potentially non-homogeneous set of servers (20% CPU is very different on a 1 core machine and 4 core machine!) Therefore, we ask for concrete values for resource pool templates, and will convert these to the appropriate percentage when creating the resource pool on the SQL Server. 

Next, I create a HighTierServiceTemplate which is a cookie cutter for WAP to create Resource Pools that are larger and offer more resources.

clip_image012

I can also enforce different policies on the incoming requests of databases created in the pool via my resource pool template. There is a ton of flexibility in how you choose to create your resource pools and a lot of opportunity to offer different services to your tenants based on different items you can throttle on. I won’t spend too much time going through all the different values you can differentiate on, but they are explained in great detail in the SQL documentation found here:

clip_image014

Using PowerShell? (These commands will be available in UR6)

PS C:\> $Template = New-MgmtSvcSqlResourcePoolTemplate -AdminUri "https://Computer01:30004" -Token $Token TemplateName “LowTierService” MinCPUCores 0.1 MaxCPUCores 0.1 CapCPUCores 0.1 MinMemoryMB 64 MaxMemoryMB 64 MinIopsPerVolume 5MaxIopsPerVolume 10 MaxSubscriptionsPerPool 10WorkloadRequestMaxCpuTimeSec 200 WorkloadRequesMaxMemoryGrantMB 0WorkloadRequestMemoryGrantTimeoutSec 0 WorkloadGroupMaxRequests 0WorkloadMaxDegreeOfParallelism 0

PS C:\> Add-MgmtSvcSqlResourcePoolTemplate –AdminUri https://Computer01:30004 –Token $Token –SqlResourcePoolTemplate $Template

STEP 4: Create a Plan

Now that you have created your resource pool templates, you are finally able to turn this into a tenant offering! We will create Plans for each of the resource pool templates we created. To do this, you will create two Plans in WAP and include SQL Servers as one of the Services being offered in each Plan. When you go to configure SQL quotas in the Plan, you will be asked to add a Group to the Plan.

clip_image017

Click to add a group, and the wizard that pops up will ask you to set quotas for your offering:

clip_image019

If the Group selected in the first dropdown happens to be Resource Governor Enabled, then you will be prompted to choose a Resource Pool Template as well. The pairing of these two manifests itself to the Tenant as an “Edition” that they can choose when creating databases and this affects what type of resource pool the database gets placed in. Let’s take a look at the quotas page for the two Plans I created:

Basic Database Plan quotas:

clip_image021

High Tier Database Plan quotas:

clip_image023

What’s pretty cool here is that two Plans are created; each offering ten databases to the Tenant and each pointing to the same group of SQL Servers in the backend for allocation, but the two Plans are using two different Resource Pool Templates. So now we can offer the two Plans at two different rates or to different priority groups (depending on the scenario).

For the administrator, WAP has made your life super easy by taking care of the management of these different resource pools. You only have to manage one group of servers and WAP will take care of creating resource pools of various sizes and shapes as they are needed by tenants.

Using PowerShell?

PS C:\> $sqlserverRP = Get-MgmtSvcResourceProvider -AdminUri "https://Computer01:30004" -Token $Token -DisableCertificateValidation -Name sqlservers

PS C:\> $QuotaList = New-MgmtSvcQuotaList

PS C:\> $SqlQuota = Add-MgmtSvcListQuota -QuotaList $QuotaList -ServiceName sqlservers -ServiceInstanceId $sqlserverRP.InstanceId

PS C:\> Add-MgmtSvcQuotaSetting -Quota $SqlQouta -Key Editions -Value '[{"displayName":"Basic Databases", "groupName":"ResourceGovernorEnabled", "resourcePoolTemplateId":"1", "resourceCount":"10", "resourceSize":"100", "resourceSizeLimit":"100", "offerEditionId":"081313063701", "groupType":null}]'

Database Creation and Placement

As far as the WAP portal and APIs are concerned, the Tenant experience does not change at all. Tenants create databases in the same way they did before without being exposed to any of the details of resource governor and database placement. As before, when the Tenant creates a database they must select the Subscription and Edition they are creating the database in. WAP uses these values to understand which SQL Server Group to place database on and which type of resource pool the database should be in. Depending on various variables, WAP may create a new resource pool for the database or place it in an existing one.

As an administrator, you can use the WAP portal to manage and view tenant databases. Let’s say that two users have come to my WAP deployment: critical@contoso.com and maintenance@fabrikam.com. Each of the users subscribes to the Plan that they need (basic vs. high tier) and creates a database. Here’s what I see in my administrator view when I look at the databases created on my resource governor server:

clip_image026 

Now that I know the resource pools where the databases were created, I can dive deeper in my resource pools tab on the same page:

clip_image028

From this view, I see Contoso’s database is in a resource pool with a higher CPU, Memory, and IOPs allowance than Fabrikam’s pool. So now let’s see how this plays out on the SQL Server…

Resource Governor at Work

I am going to use Performance Monitor to monitor the CPU usage of the various resource pools on my SQL hosting server. (For step-by-step instructions on how to do this, you can check out this blog: Resource Governor at Work with Azure Pack) Below is a view from Performance Monitor over a timeframe of both customers accessing their databases. The blue line is the default resource pool (for non-tenant workloads), the red line is the resource pool for Contoso’s database, and the green line is the resource pool for Fabrikam’s.

clip_image030

In the above timeframe, before either customer even begins to use their database, the server is already being hit with a workload that is consuming all of its CPU (the default pool is at 100%). Without resource governor, if a tenant came in and tried to hit the server at this point, they would have a hard time vying for CPU resources on the server. At time A, Contoso starts to hit their database with a CPU intensive query. Here you see, that Contoso is hanging around 30% CPU usage of the 1 Core server, or 0.3 Cores. This is what was promised to Contoso via the HighTierServiceTemplate we created. We also see that this causes the default pool to be throttled and its CPU usage is dropped to around 0.7 Cores.

At time B, Fabrikam also starts to hit their database with a CPU-intense workload (it happens to be the same workload as Contoso). Looking carefully, we can see that Contoso’s usage of 0.3 Cores does not change even though Fabrikam starts to use 0.1 Cores of CPU. Instead, the default pool is throttled.

Both Contoso and Fabrikam were guaranteed their levels of CPU at 0.3 and 0.1 respectively. All while neither workload disrupted the either. This is the power of Resource Governor. It can save you lots of time by managing tenant workloads for you.

Interpreting Usage Data

The SQL Server usage data actually does not change at all with the addition of this feature. As it did before, SQL reports total allotted space and number of databases created so far by a tenant subscription and which edition they created the databases on:

    "EventId": 1,
    "ExternalRecordId": "1",
    "ResourceId": null,
    "StartTime": "2014-01-31T17:15:49",
    "EndTime": "2014-01-31T17:17:42",
    "ProviderName": "sqlservers",
    "ServiceType": "Basic Databases",
    "SubscriptionId": "a7319215-d5f8-483e-813c-44119bc4ca79",
    "Properties": null,
    "Resources": {
      "TotalAllottedSpace": "1024",
      "DatabaseCount": "1"
    }

If you want to have different billing or chargeback models based on usage of different resource pool templates, you can rely on the edition name (you specify this when creating the Plan) from usage data to provide this information.

Conclusion

Now, you have seen how Resource Governor can help balance competing loads within the same server and provide a predictable level of service to tenants. Hopefully this can act as a good template to get you started on implementation in your datacenter. Please comment and let me know your feedback and how you plan to use Resource Governor at your organization!