Databases Reference
In-Depth Information
The resource pool associated with the report application will be configured such that it has at
least 25 percent of CPU and memory resources available at the time of resource contention.
For both the applications, we will create a separate, dedicated login account and user, in the
AdventureWorks2012
database, for each application that should be used to connect to SQL
Server by respective application.
A separate username will be helpful in distinguishing the source of request in the classifier
function. Based on username of the current request, the classifier function will route the
request to the appropriate workload group, and the resource pool associated with that
particular workload group will be used to execute the request.
The following are the prerequisites for this recipe:
F
An instance of SQL Server 2012 Developer or Enterprise Evaluation edition.
F
An SQL Server login account with administrative rights.
F
A sample
AdventureWorks2012
database on the SQL Server instance. For more
details on how to install the
AdventureWorks2012
database, please refer to the
Preface
of this topic.
How to do it...
To configure Resource Governor with SQL Server Management Studio, perform the
following steps:
1.
Open SQL Server Management Studio and connect to an instance of SQL Server
containing the
AdventureWorks2012
database.
Make sure that the login account you are using to connect to SQL
Server is an administrative account, so that it can create new
login accounts and Resource Governor objects without any hassle!
However, this is not mandatory, as any account with
ALTER
LOGIN
and
CONTROL
SERVER
will work. But for the sake of simplicity, an
administrative account is recommended.
2.
First, in order to set up our scenario, we need to create two login accounts and their
corresponding users, one of which is supposed be used by our web application and
the other by the report application. To create these logins and users, execute the
following T-SQL script in a query window:
USE [master]
GO
--Creating new login AW_WebAppUser in SQL Server
--and its associated user in AdventureWorks2012
CREATE LOGIN [AW_WebAppUser] WITH PASSWORD=N'AW_WebAppUser123',
Search WWH ::
Custom Search