Configure SQL Resource Governor in Windows Azure Pack

  • Article

 

Applies To: Windows Azure Pack

Resource Governor enables you to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests. Such limits can prevent a single tenant from consuming all the server resources – a situation often called the noisy neighbor syndrome. Thus, Resource Governor can help ensure that other tenants on the same server due not suffer. You can use the Resource Governor feature of SQL Server 2014 to enforce performance isolation on your tenant databases in Windows Azure Pack. Limits can be applied to:

  • CPU

  • Memory

  • IOPs

For more information on SQL Server Resource Governor, see Resource Governor on MSDN.

Windows Azure Pack support for Resource Governor is only available on hosting servers using SQL Server 2014 (Enterprise Edition) or later. This feature is available with Windows Azure Pack Update Rollup 5.

To configure Resource Governor, follow the steps below.

Create a group for Resource Governor servers

All hosting servers using Resource Governor must be in the same SQL Server Group. This ensures that Windows Azure Pack databases are on servers that have the capability to limit tenant resource utilization.

Resource Governor is compatible with standalone or AlwaysOn server groups.

  1. Sign in to the management portal for administrators, click New, click SQL Server, then click Create a group.

  2. For Group type, select Standalone servers or High availability (Always on enabled). For more information on SQL AlwaysOn availability groups, see https://technet.microsoft.com/library/dn457765.aspx.

  3. Type a name for the group.

  4. Provide the Network file share if using AlwaysOn.

  5. Select Enable Resource Governance.

  6. Click Create a group.

Add hosting servers to Windows Azure Pack

After you create the group, you must add SQL hosting servers to the group to create the fabric.

After you complete the steps below, Windows Azure Pack installs the following two items into the master database of each hosting server registered to a group that supports resource governance:

  • The SqlServerRP.SqlRPWorkloadLookup lookup table. This defines the relationship between tenant databases and their associated workload groups.

  • The SqlServerRP.fnSqlRPDatabaseClassifier SQL function. This classifies incoming connections based on the incoming database name. This function relies on the SqlServerRP.SqlRPWorkloadLookup lookup table.

Do not alter either of these items. They enable Windows Azure Packto control SQL Server Resource Governor.

  1. Sign in to the management portal for administrators, click New, click SQL Server, then click Connect to.

  2. In the Connect a hosting server window, in the SQL Server group dropdown, select a group that is resource governor enabled.

  3. Provide the information for the SQL Server name, Username, and Password boxes. The server must use SQL Server 2014 (Enterprise Edition) or later.

  4. In the Size of hosting server in GB box, enter the size of the hosting server.

  5. Click the next arrow.

  6. Type the Number of CPU cores, Installed memory, and Number of IOPs per volume.

  7. Type the Maximum number of resource pools to be created on the new server. To maintain performance, no more than 18 pools can be created on a server.

  8. Click the check mark to complete the wizard.

Create resource pool templates

To use Resource Governor, you must create Resource Pool Templates that define the parameters to use when creating Resource Pools in SQL Server.

For more information on Resource Governor, see Resource Governor Workload Group and Resource Governor Resource.

  1. Sign in to the management portal for administrators, click New, click SQL Server, then click Create a resource pool template.

  2. Type a name for the template.

  3. Type values for Minimum CPU cores, Maximum CPU cores (soft cap), and Hard cap CPU cores. Resource pools created from this template always have access to the minimum value you choose here. They also have access to CPU up to the maximum you set. See the links above for more details on how SQL Server uses these values.

    These values are converted to appropriate percentages for use in the SQL Server CPU Percentage setting.

  4. Type values for Minimum memory (MB), Maximum memory (MB), Minimum IOPS per volume, and Maximum IOPS per volume. Like CPU, these are the values that determine the resources available to the resource pool.

  5. Choose a value for Maximum subscriptions per pool. If this value is set to 1, one new resource pool is created for each new subscription. If this value is greater than 1, multiple subscriptions share the pool and a new pool is created only after the maximum subscriptions value is reached.

  6. Click the next arrow.

  7. Choose a value for Maximum memory per request (MB).

  8. Choose values for Maximum CPU Time in Seconds per Request, Memory Grant Timeout in Seconds per Query, Maximum Simultaneous Requests, and Maximum Degree of Parallelism. If you do not provide these values, the SQL Server default or unlimited values will be used. See Resource Governor Workload Groupfor more information on these values.

  9. Click the check mark to complete the wizard.

Associate resource pool templates in a plan

In order to use Resource Governor with your offering, you must create a plan.

Resource Governance is only available at the plan level. It is not available in add-ons.

  1. Sign in to the management portal for administrators, click New, click Plan, then click Create plan.

  2. Type a name for the plan and then click the next arrow.

  3. Under Select services for a Hosting Plan, select SQL Servers as one of the services in the plan.

  4. Click the next arrow and then click the check mark.

  5. After the plan is created, in the Services list of the plan’s dashboard, click SQL Servers. Then configure the quotas for the SQL servers in the plan.

  6. Click Add SQL Server group to the Plan.

  7. This pops up a wizard. Select the resource governor enabled Group you created and a Resource Pool Template from the dropdowns in the wizard.

  8. You can give this group a friendly name for your tenant in Edition and then select the quotas you want to apply for number of allowed databases, size, and so on.

  9. Click Save.

  10. If you want tenants to be able to subscribe themselves, make the plan public by clicking on Change access in the command bar.

You are now done configuring Resource Governor. When tenants subscribe to the plan you created, they will be able to create databases. These databases will be created within resource pools in the SQL Server based on the templates you created.