Scripting Resource Pools and Resource Groups

Unfortunately, SQL Server’s management studio doesn’t provide you with a simple way to script your resource pools and resource groups. If you want to do this you’ll first have to script all the groups, and then the pools one by one in the correct order. To recreate them you have to do the same but in a reverse order.
This SQL script does just that, it generates the scripts needed to recreate your resource groups and pools in the correct order. This is extremely useful in Always-on scenario’s as it provides you with a script that can be run on the primary node, and the result on any secondary node(s) creating a completely identical resource configuration for your resource governors.

The generated scripts can also be used as post scripts for application deployment or for documentation purposes. Please note that it does NOT script the configuration of your resource governor nor will it script your classification function. This is not within the scope of this script.
The difficulty for this script was to make the script generate the statements in the correct order, and to take into consideration the edition changes in the resource governor.

The script has two self-explaining parameters:

• @Drop = Generates the drop statements
• @Create = Generates the create statements

DECLARE @Drop bit=1
DECLARE @Create bit=1
DECLARE @CMD nvarchar(4000)
 
declare @version int
set @version = (SELECT convert (int,REPLACE (LEFT (CONVERT (varchar, SERVERPROPERTY ('ProductVersion')),2), '.', '')))
 
CREATE TABLE #Command (pk int identity(1,1) PRIMARY KEY,Command nvarchar(4000))
IF @Drop=1 
BEGIN
        IF NOT Exists (select * from tempdb.sys.objects where name like'#DROPS%' and type='U')
        BEGIN
                 CREATE TABLE #DROPS (Group_id int,Pool_id int,IsActive bit, Command nvarchar(4000))
        END
        ELSE BEGIN
                 Truncate table #DROPS
        END
        INSERT INTO #DROPS
        Select Group_id,Pool_id,1,
        'use [master]; DROP WORKLOAD GROUP  '+QUOTENAME(wg.name)+';' 
        FROM sys.resource_governor_workload_groups wg where group_id > 2
 
        INSERT INTO #DROPS
        Select 0,Pool_id,1,
        'use [master]; BEGIN TRY DROP RESOURCE POOL '+QUOTENAME(name)+' END TRY BEGIN CATCH END CATCH;'
        FROM sys.dm_resource_governor_resource_pools where Pool_id > 2
 
        INSERT INTO #Command Select Command From #Drops order by Pool_id asc,Group_id desc
END
 
IF @Create = 1 
BEGIN
        IF NOT Exists (select * from tempdb.sys.objects where name like'#CREATES%' and type='U')
        BEGIN
                 CREATE TABLE #CREATES (Group_id int,Pool_id int, Command nvarchar(4000))
        END
        SET @CMD='Select 0,Pool_id,''use [master]; BEGIN TRY CREATE RESOURCE POOL ''+QUOTENAME(name)+'' WITH (''+ 
                 ''min_cpu_percent=''+CAST(min_cpu_percent as varchar(3))+'', ''+
                 ''max_cpu_percent=''+CAST(max_cpu_percent as varchar(3))+'', ''+
                 ''min_memory_percent=''+CAST(min_memory_percent as varchar(3))+'', ''+
                 ''max_memory_percent=''+CAST(max_memory_percent as varchar(3))+'', ''+ '+
                 CASE WHEN @version > 10 THEN '''cap_cpu_percent=''+CAST(cap_cpu_percent as varchar(3))+'', ''+ 'ELSE '' END+
                 CASE WHEN @version > 11 THEN '''min_iops_per_volume=''+CAST(min_iops_per_volume as varchar(3))+'', ''+ 'ELSE '' END+
                 CASE WHEN @version > 11 THEN '''max_iops_per_volume=''+CAST(max_iops_per_volume as varchar(3))+'', ''+ 'ELSE '' END+
                 '''AFFINITY SCHEDULER = AUTO)''+'+
                 ''' END TRY BEGIN CATCH END CATCH ''' +
                 'FROM sys.dm_resource_governor_resource_pools where Pool_id > 2'
        
        PRINT @cmd
 
        INSERT INTO #CREATES
        exec sp_executesql @cmd
 
        INSERT INTO #CREATES
        Select wg.group_id,wg.pool_id,
        'use [master]; BEGIN TRY CREATE WORKLOAD GROUP '+QUOTENAME(wg.name)+' WITH (' +
        'group_max_requests='+CAST(group_max_requests as varchar(4))+', '+
        'importance='+importance+', '+
        'request_max_cpu_time_sec='+CAST(request_max_cpu_time_sec as varchar(5))+', '+
        'request_max_memory_grant_percent='+CAST(request_max_memory_grant_percent as varchar(5))+', '+
        'request_memory_grant_timeout_sec='+CAST(request_memory_grant_timeout_sec as varchar(5))+', '+
        'max_dop='+CAST(max_dop as char(2))+') USING '+QUOTENAME(rp.name)+' END TRY BEGIN CATCH END CATCH;'
        FROM sys.resource_governor_workload_groups wg 
        inner join  sys.dm_resource_governor_resource_pools  rp on rp.pool_id=wg.pool_id
        where wg.group_id > 2
 
        INSERT INTO #Command Select Command From #CREATES order by Pool_id desc,Group_id asc
END
GO
        Select * From #Command
GO
IF Exists (select * from tempdb.sys.objects where name like '#DROPS%' and type='U')
BEGIN
        DROP TABLE #DROPS
END
GO
IF Exists (select * from tempdb.sys.objects where name like '#CREATES%' and type='U')
BEGIN
        DROP TABLE #CREATES
END
GO
DROP TABLE #Command