1. Create a SQL Server login for creating low-priority compressed backups.
   To create a login
   
   • How to: Create a SQL Server Login
     
   • CREATE LOGIN (Transact-SQL)
     
2. Optionally, grant VIEW SERVER STATE to this login.
   
   • GRANT System Object Permissions (Transact-SQL)
     
   For more information, see Permissions of Fixed Server Roles (Database Engine).
   
3. Create a SQL Server user for this login.
   To create a user
   
   • How to: Create a Database User
     
   • CREATE USER (Transact-SQL)
     
4. To enable sessions of this login and user to back up a given database, add the user to the db_backupoperator database role of that database. Do this for each database that this user will back up. Optionally, add the user to other fixed database roles.
   To add a user to a fixed database role
   
   • sp_addrolemember (Transact-SQL)
     
   For more information, see Permissions of Fixed Database Roles (Database Engine).
   

The following example is relevant only if you choose to create a new SQL Server login and user for low-priority backups. Alternatively, you can use an existing login and user, if an appropriate one exists.

Important:
The following example uses a sample login and user name, domain_name\MAX_CPU. Replace these with the names of the SQL Server login and user that you plan to use when creating your low-priority compressed backups.

This example creates a login for the domain_name\MAX_CPU Windows account and then grants VIEW SERVER STATE permission to the login. This permission enables you to verify the Resource Governor classification of sessions of the login. The example then creates a user for domain_name\MAX_CPU and adds it to the db_backupoperator fixed database role for the AdventureWorks sample database. This user name will be used by the Resource Governor classifier function.

-- Create a SQL Server login for low-priority operations
USE master;
CREATE LOGIN [domain_name\MAX_CPU] FROM WINDOWS;
GRANT VIEW SERVER STATE TO [domain_name\MAX_CPU];
GO
-- Create a SQL Server user in AdventureWorks for this login
USE AdventureWorks;
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';
GO

[Top]

1. Issue a CREATE RESOURCE POOL statement to create a resource pool. The example for this procedure uses the following syntax:
   CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value );
   Value is an integer from 1 to 100 that indicates the percentage of maximum average CPU bandwidth. The appropriate value depends on your environment. For the purpose of illustration, the example in this topic uses 20% percent (MAX_CPU_PERCENT = 20.)
   
2. Issue a CREATE WORKLOAD GROUP statement to create a workload group for low-priority operations whose CPU usage you want to govern. The example for this procedure uses the following syntax:
   CREATE WORKLOAD GROUP group_name USING pool_name;
   
3. Issue a CREATE FUNCTION statement to create a classifier function that maps the workload group created in the preceding step to the user of the low-priority login. The example for this procedure uses the following syntax:
   CREATE FUNCTION [schema_name.]function_name() RETURNS sysname
   WITH SCHEMABINDING
   AS
   BEGIN
   DECLARE @workload_group_name AS sysname
   IF (SUSER_NAME() = 'user_of_low_priority_login')
   SET @workload_group_name = 'workload_group_name'
   RETURN @workload_group_name
   END
   For information about the components of this CREATE FUNCTION statement, see:
   
   • DECLARE @local_variable (Transact-SQL)
     
   • SUSER_SNAME (Transact-SQL)
     

     Important:
     SUSER_NAME is just one of several system functions that can be used in a classifier function. For more information, see Considerations for Writing a Classifier Function.

   • SET @local_variable (Transact-SQL).
     
4. Issue an ALTER RESOURCE GOVERNOR statement to register the classifier function with Resource Governor. The example for this procedure uses the following syntax:
   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema_name.function_name);
   
5. Issue a second ALTER RESOURCE GOVERNOR statement to apply the changes to the Resource Governor in-memory configuration, as follows:
   
   ALTER RESOURCE GOVERNOR RECONFIGURE;

The following example performs the following steps within a single transaction:

1. Creates the pMAX_CPU_PERCENT_20 resource pool.
   
2. Creates the gMAX_CPU_PERCENT_20 workload group.
   
3. Creates the rgclassifier_MAX_CPU() classifier function, which uses the user name created in the preceding example.
   
4. Registers the classifier function with Resource Governor.
   

After committing the transaction, the example applies the configuration changes requested in the ALTER WORKLOAD GROUP or ALTER RESOURCE POOL statements.

Important:
The following example uses the user name of the sample SQL Server user created in "Example A: Setting Up a Login and User (Transact-SQL)," domain_name\MAX_CPU. Replace this with the name of the user of the login that you plan to use for creating low-priority compressed backups.

-- Configure Resource Governor.
BEGIN TRAN
USE master;
-- Create a resource pool that sets the MAX_CPU_PERCENT to 20%. 
CREATE RESOURCE POOL pMAX_CPU_PERCENT_20
   WITH
      (MAX_CPU_PERCENT = 20);
GO
-- Create a workload group to use this pool. 
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_20
USING pMAX_CPU_PERCENT_20;
GO
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'Default' group.
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @workload_group_name AS sysname
      IF (SUSER_NAME() = 'domain_name\MAX_CPU')
          SET @workload_group_name = 'gMAX_CPU_PERCENT_20'
    RETURN @workload_group_name
END;
GO

-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);
COMMIT TRAN;
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

[Top]

The following BACKUP example creates a compressed full backup of the AdventureWorks database in a newly formatted backup file, Z:\SQLServerBackups\AdvWorksData.bak.

--Run backup statement in the gBackup session.
BACKUP DATABASE AdventureWorks TO DISK='Z:\SQLServerBackups\AdvWorksData.bak' 
WITH 
   FORMAT, 
   MEDIADESCRIPTION='AdventureWorks Compressed Data Backups'
   DESCRIPTION='First database backup on AdventureWorks Compressed Data Backups media set'
   COMPRESSION;
GO

[Top]