16.2. Classifier function

When a user or application connects to a SQL Server instance, the logon process includes two major steps before the session is established: authentication, followed by the firing of any logon triggers. When Resource Governor is enabled, a third step is included for classifying the connection using the classifier function. The process is summarized in figure 16.2.

Figure 16.2. The logon process with Resource Governor enabled consists of authentication, logon triggers, and workload classification.
 

Given the integration of the classifier function within the logon process, the efficiency of the function becomes a crucial consideration. Poorly written functions have the potential for logins to time out if the execution time of the function exceeds the login timeout setting of the connecting application. Therefore, the classifier function should be tested, ideally under load, before production implementation.

As with workload groups and resource pools, the best classifier functions are those that are relatively simple in nature. Consider the example in listing 16.1.

Example 16.1. Example classifier function

-- Create a Classifier Function
CREATE FUNCTION fn_rg_classifier() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name AS SYSNAME
      IF (IS_SRVROLEMEMBER('sysadmin') = 1)
          SET @grp_name = 'rgDBAGroup'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'rgReportGroup'
    RETURN @grp_name
END
GO

The classifier function presented here is an example of a relatively simple function putting little overhead on the logon process. It uses the SUSR_NAME() and APP_NAME() functions to classify incoming connections based on the username or application name. The full list of supported functions for classification purposes is as follows:

• HOST_NAME()—Returns values such as MarketingPC4

• APP_NAME()—Returns values such as Microsoft SQL Server Management Studio - Query

• SUSER_NAME()andSUSER_SNAME()—Returns values such as BNESalesRColledge

• IS_SRVROLEMEMBER()—Used to evaluate server role membership, for example, if IS_SRVROLEMEMBER ('sysadmin') = 1

• IS_MEMBER()—Used to evaluate database role membership, for example, if IS_MEMBER ('db_owner') = 1

When designing a classifier function, you need to consider the following aspects of its effect on a Resource Governor implementation:

• A connection not explicitly classified will be assigned to the default workload group (and therefore the default resource pool). With this in mind, the function logic should address only connections that should be targeted for a nondefault workload group.

• Connections made using the dedicated administrator connection (DAC) are not subject to workload group classification. Using the DAC is one method of fixing a poorly written classifier function that's denying connections based on logon timeouts. For this reason, enabling the DAC is recommended when using Resource Governor.

• When Resource Governor is disabled, the classifier function is not executed as part of the logon process; all connections are automatically set to use the default workload group, whose settings are returned to their default values along with the values of the default resource pool.

Finally, you need to consider the security of the functions being used (or misused) for classification. One example of this, which also applies to logon triggers (covered in chapter 6), is spoofing the value of the supplied application name in an application's connection string. An example of this is shown in figure 16.3.

The Application Name attribute of the connection string is returned by the SQL Server APP_NAME() function. It follows that a user with access to the application's connection string and knowledge of the classification function's logic can potentially circumvent the intended result of the classification function (or logon trigger). Therefore, you must consider the security of the connection string details and the possible use of a more secure classification function.

Figure 16.3. A developer with access to the connection string can spoof the application name to circumvent logon triggers and workload classification.
 

Let's now focus on the purpose of the classifier function: to assign connections to a workload group.