March 14, 2019 at 4:55 pm
In the area where you setup the workloads, you can use APP_NAME, IS_MEMBER.
If you have 100 users connecting and want them to use one workload, rather than typing in every user account into this, how can I use the IS_MEMBER.
App name would work if users are coming from an application that has this coded in the application name in the connection string.
If users come directly into SQL Studio what can I use instead of coding every user.
In this example we are checking SUSER_NAME() andmaking the decision of Workgroup selection. We can use other functions suchas HOST_NAME(), APP_NAME(), IS_MEMBER() etc.
-----------------------------------------------
-- Step 3: Create UDF toRoute Workload Group
-----------------------------------------------
CREATE FUNCTION dbo.UDFClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroupAS SYSNAME
IF(SUSER_NAME() = 'domain\user1')
SET @WorkloadGroup= 'Reporting'
ELSE IF(SUSER_NAME() = 'domain\user2')
SET @WorkloadGroup= 'Reporting'
March 14, 2019 at 5:29 pm
TRACEY-320982 - Thursday, March 14, 2019 4:55 PMhttps://blog.sqlauthority.com/2012/06/04/sql-server-simple-example-to-configure-resource-governor-introduction-to-resource-governor/In the area where you setup the workloads, you can use APP_NAME, IS_MEMBER.
If you have 100 users connecting and want them to use one workload, rather than typing in every user account into this, how can I use the IS_MEMBER.
App name would work if users are coming from an application that has this coded in the application name in the connection string.
If users come directly into SQL Studio what can I use instead of coding every user.In this example we are checking SUSER_NAME() andmaking the decision of Workgroup selection. We can use other functions suchas HOST_NAME(), APP_NAME(), IS_MEMBER() etc.
-----------------------------------------------
-- Step 3: Create UDF toRoute Workload Group
-----------------------------------------------
CREATE FUNCTION dbo.UDFClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroupAS SYSNAME
IF(SUSER_NAME() = 'domain\user1')
SET @WorkloadGroup= 'Reporting'
ELSE IF(SUSER_NAME() = 'domain\user2')
SET @WorkloadGroup= 'Reporting'
If the users are in the same Windows groups, you could use that. IF (IS_member('YourDomain\YourWindowsGroup')) = 1
SET @WorkloadGroup ='Reporting'
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply