Procédure : utiliser le gouverneur de ressources pour limiter l'utilisation de l'UC par compression de sauvegarde (Transact-SQL)

1. Créez une connexion SQL Server pour créer des sauvegardes compressées de priorité basse. 

   Pour créer une connexion

   • Procédure : créer un compte de connexion SQL Server

   • CREATE LOGIN (Transact-SQL)

2. Accordez éventuellement à cette connexion l'autorisation VIEW SERVER STATE.

   • GRANT – octroi d'autorisations d'objet système (Transact-SQL)

   Pour plus d'informations, consultez Autorisations des rôles serveur fixes (moteur de base de données).

3. Créez un utilisateur SQL Server pour cette connexion.

   Pour créer un utilisateur

   • Procédure : créer un utilisateur de base de données

   • CREATE USER (Transact-SQL)

4. Pour permettre aux sessions de cette connexion et cet utilisateur de sauvegarder une base de données spécifique, ajoutez l'utilisateur au rôle de base de données db_backupoperator de cette base de données. Répétez l'opération pour chaque base de données que cet utilisateur doit sauvegarder. Ajoutez éventuellement l'utilisateur à d'autres rôles de base de données fixes.

   Pour ajouter un utilisateur de base de données à un rôle de base de données fixe

   • sp_addrolemember (Transact-SQL)

   Pour plus d'informations, consultez Autorisations des rôles de base de données fixes (moteur de base de données).

L'exemple ci-dessous est pertinent uniquement si vous choisissez de créer une connexion et un utilisateur SQL Server pour des sauvegardes de priorité basse. Vous avez également la possibilité d'utiliser une connexion et un utilisateur existants, le cas échéant.

Cet exemple crée une connexion pour le compte Windows domain_name\MAX_CPU, puis accorde l'autorisation VIEW SERVER STATE à la connexion. Cette autorisation vous permet de vérifier la classification du gouverneur de ressources pour les sessions de la connexion. L'exemple crée ensuite un utilisateur pour domain_name\MAX_CPU et l'ajoute au rôle de base de données fixe db_backupoperator pour l'exemple de base de données AdventureWorks. Ce nom d'utilisateur sera utilisé par la fonction classifieur du gouverneur de ressources.

-- 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

[Haut de la page]

1. Émettez une instruction CREATE RESOURCE POOL pour créer un pool de ressources. L'exemple de cette procédure utilise la syntaxe suivante :

   CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value );

   Value est un entier de 1 à 100 qui indique le pourcentage de bande passante processeur moyenne maximale. La valeur appropriée dépend de votre environnement. À des fins d'illustration, l'exemple de cette rubrique utilise 20 % (MAX_CPU_PERCENT = 20.)

2. Émettez une instruction CREATE WORKLOAD GROUP pour créer un groupe de charge de travail pour les opérations de priorité basse dont vous souhaitez régir l'utilisation de l'UC. L'exemple de cette procédure utilise la syntaxe suivante :

   CREATE WORKLOAD GROUP group_name USING pool_name;

3. Émettez une instruction CREATE FUNCTION pour créer une fonction classifieur qui mappe le groupe de charge de travail créé à l'étape précédente à l'utilisateur de la connexion de priorité basse. L'exemple de cette procédure utilise la syntaxe suivante :

   CREATE FUNCTION [schema_name.]function_name() sysname RETURNS

   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

   Pour plus d'informations sur les composants de cette instruction CREATE FUNCTION, consultez :

   • DECLARE @local\_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     Important

     SUSER_NAME est simplement l'une des fonctions système qui peuvent être utilisées dans une fonction classifieur. Pour plus d'informations, consultez Considérations sur l'écriture d'une fonction classifieur.

   • SET @local\_variable (Transact-SQL).

4. Émettez une instruction ALTER RESOURCE GOVERNOR pour inscrire la fonction classifieur auprès du gouverneur de ressources. L'exemple de cette procédure utilise la syntaxe suivante :

   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema_name.function_name);

5. Émettez une deuxième instruction ALTER RESOURCE GOVERNOR pour appliquer les modifications à la configuration en mémoire du gouverneur de ressources, comme suit :

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

L'exemple ci-dessous effectue les étapes qui suivent dans une transaction unique.

1. Il crée le pool de ressources pMAX_CPU_PERCENT_20.

2. Il crée le groupe de charge de travail gMAX_CPU_PERCENT_20.

3. Il crée la fonction classifieur rgclassifier_MAX_CPU(), qui utilise le nom d'utilisateur créé à l'exemple précédent.

4. Il inscrit la fonction classifieur auprès du gouverneur de ressources.

Après avoir validé la transaction, l'exemple applique les modifications de configuration demandées dans les instructions ALTER WORKLOAD GROUP ou ALTER RESOURCE POOL.

-- 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

[Haut de la page]

L'exemple BACKUP suivant crée une sauvegarde complète compressée de la base de données AdventureWorks dans un fichier de sauvegarde récemment formaté, 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

[Haut de la page]