Configure resource govener

-- Step 0: Here we are assuming that there are separate login accounts for Reporting server and OLTP server.

/*-----------------------------------------------
 Step 0: (Optional and for Demo Purpose)
 Create Two User Logins
 1) ReportUser, 2) PrimaryUser
 Use ReportUser login for Reports workload
 Use PrimaryUser login for OLTP workload
 -----------------------------------------------*/

-- Step 1: Creating Resource Pool

-- We are creating two resource pools. 1) Report Server and 2) Primary OLTP Server. We are giving only a few resources to the Report Server Pool as described in the scenario 1 the other server is mission critical and not the report server.
-----------------------------------------------
 -- Step 1: Create Resource Pool
 -----------------------------------------------
 -- Creating Resource Pool for Query Server
CREATE RESOURCE POOL QueryServerPool
WITH
( MIN_CPU_PERCENT=0,
MAX_CPU_PERCENT=30,
MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO
-- Creating Resource Pool for Primary Server
CREATE RESOURCE POOL PrimaryServerPool
WITH
( MIN_CPU_PERCENT=50,
MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50,
MAX_MEMORY_PERCENT=100)
GO

-- Step 2: Creating Workload Group

-- We are creating two workloads each mapping to each of the resource pool which we have just created.
-----------------------------------------------
 -- Step 2: Create Workload Group
 -----------------------------------------------
 -- Creating Workload Group for Report Server
CREATE WORKLOAD GROUP QueryServerGroup
 USING QueryServerPool ;
GO
-- Creating Workload Group for  Primary Server
CREATE WORKLOAD GROUP PrimaryServerGroup
 USING PrimaryServerPool ;
GO

-- Step 3: Creating user defined function which routes the workload to the appropriate workload group.

-- In this example we are checking SUSER_NAME() and making the decision of Workgroup selection. We can use other functions such as HOST_NAME(), APP_NAME(), IS_MEMBER() etc.
-----------------------------------------------
 -- Step 3: Create UDF to Route Workload Group
 -----------------------------------------------
CREATE FUNCTION dbo.UDFClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
     BEGIN
         DECLARE @WorkloadGroup AS SYSNAME
         IF(SUSER_NAME() = 'DOMAIN\etl_service')
            SET @WorkloadGroup = 'PrimaryServerGroup'
         ELSE IF (SUSER_NAME() in ('DOMAIN\dba1',
                                   'DOMAIN\dba2',
                                   'DOMAIN\dba3'))
            SET @WorkloadGroup = 'default'
         ELSE
            SET @WorkloadGroup = 'QueryServerGroup'
         RETURN @WorkloadGroup
    END
GO

-- Step 4: In this final step we enable the resource governor with the classifier function created in earlier step 3.
-----------------------------------------------
 -- Step 4: Enable Resource Governer
 -- with UDFClassifier
 -----------------------------------------------
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

-- Step 5: If you are following this demo and want to clean up your example, you should run following script. Running them will disable your resource governor as well delete all the objects created so far.
-----------------------------------------------
 -- Step 5: Clean Up
 -- Run only if you want to clean up everything
 -----------------------------------------------
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
 GO
DROP FUNCTION dbo.UDFClassifier
 GO
DROP WORKLOAD GROUP ReportServerGroup
 GO
DROP WORKLOAD GROUP PrimaryServerGroup
 GO
DROP RESOURCE POOL ReportServerPool
 GO
DROP RESOURCE POOL PrimaryServerPool
 GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO