Ravi S.Maniam's Blog

SQL Server 2008 - Resource Governor - Part I

Hi Friends, I am back with one more blog post on an interesting feature in SQL Server 2008. Today I was discussing about run away situations with my colleagues and I found that only few people read about this excellent feature available in SQL Server 2008 so just thought why not to blog about it.

If you are using SQL Server everyday then you would be aware of the situations when you get into run-away situation during the peak load on the server. For example: there is a heavy transaction volume on the server and someone from the remote end fired an ad-hoc query to churn several tables, running to a few million rows and everything comes to a screeching halt. You would have always wondered if there was a way to manage this to avoid run-away situation. Even if there was any solution that sort you would have wondered can it be done on the fly with a minimal impact.

What is run-away ?

To explain it in a very simplified manner, every server hardware has a finite number of CPUs and amount of RAM installed on it. When you try to fire a query that is resource intensive and the hardware resources are not available to serve the request ..you get into a run-away situation due to which the service requested is delayed and other requests being handled by the server in parallel are also delayed.

So what is the solution ?

In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.

Resource Governor

Resource Governor is a new technology in SQL Server 2008 that enables you to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests. In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor.

It is feature that is designed to manage the resource limits in real time with minimal impact on workloads that are executing.

Issues addressed by Resource Governor

Resource Governor addresses 3 primary issues that are prevalent in the database environments.

1. Run-Away : A situation where one process eats up all the resources and another process starves.
2. Workload Priority : A situation where we need to prioritise one workload type over another viz. a current payment transaction over another query that is crunching millions of rows to take out some summarized information which is expected to run for a few minutes.
3. Unpredictable workload execution : A situation similar to this one where two data warehouse applications are a mix of OLTP and data warehouse applications. These applications are not isolated from each other and the resulting resource contention causes unpredictable workload execution.

All of the above scenarios require the ability to differentiate workloads in some way. Resource Governor provides:

• The ability to classify incoming connections and route their workloads to a specific group (Identifying a workload group based on an incoming connection).
• The ability to monitor resource usage for each workload in a group.
• The ability to pool resources and set pool-specific limits on CPU usage and memory allocation. This prevents or minimizes the probability of run-away queries.
• The ability to associate grouped workloads with a specific pool of resources.
• The ability to identify and set priorities for workloads. (Setting the priority or Low, Medium, High)

Scope of Resource Governor

There is a clear scope in which Resource Governor works and it is important to understand the scope of it.

• It is only manages the CPU Bandwidth and the RAM managed by the SQL Server 2008.
• Resource Governor itself will not optimise any slow running queries.
• No load balancing will be done between multiple SQL Server instances.
• It does not divide the server into isolated pieces.
• OLTP workloads. Resource Governor can manage OLTP workloads but these types of queries, which are typically very short in duration, are not always on the CPU long enough to apply bandwidth controls. This may skew in the statistics returned for CPU usage %.

How Resource Governor Works

The following three concepts are fundamental to understanding and using Resource Governor:

1. Resource pools. Two resource pools (internal and default) are created when SQL Server 2008 is installed. Resource Governor also supports user-defined resource pools.
2. Workload groups. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed. Resource Governor also supports user-defined workload groups.
3. Classification. There are internal rules that classify incoming requests and route them to a workload group. Resource Governor also supports a classifier user-defined function for implementing classification rules.

Explanation (Step by Step):

1. There is an incoming connection for a session (Session 1 of n).
2. The session is classified (Classification).
3. The session workload is routed to a workload group.(Workload Group 1)
4. The workload group uses the resource pool it is associated with, for example, Pool 1.
5. The resource pool provides and limits the resources required by the application, for example, Application 1

For more information on Resource Governor you may visit SQL Server 2008 Books Online.

I will write more about the Resource Governor very soon so keep a watch.

SQL Server 2008 - Auditing

Hi Friends, I am back again with one more blog post on SQL Server 2008 Auditing.

As we all know that SQL Server 2008 is coming up with *Cool* enhancements that will help the End Users, DBAs, Developers in improving their productivity. Today I am going to blog about a feature which would be liked by people who maintain the compliance with the Security Standards.

Before the advent of SQL Server 2008, auditing was done using SQL Server Traces and Profiler. Now Auditing is an integral object in SQL Server 2008.

So what is Auditing in SQL Server..

Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. Based on the information accumulated we would be able to track the changes to the database, access to the database etc. An audit is the combination of several elements into a single package for a specific group of server actions or database actions. The components of SQL Server Audit combine to produce an output that is called an audit, just as a report definition combined with graphics and data elements produces a report. SQL Server Audit uses Extended Events to help create an audit.

While we are working with SQL Server 2008 auditing we need to keep four things in mind:

1. SQL Server Audit
2. Server Audit Specification (Events to capture on the Server Instance Level)
3. Database Audit Specification (Events to capture on a specific database)
4. Target (Where would be the events be logged)

Note: I will be using the definitions In Books Online to explain you these 4 objects because I think they are perfect and very easy to understand.

SQL Server Audit

The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance. When you define an audit, you specify the location for the output of the results. This is the audit destination. The audit is created in a disabled state, and does not automatically audit any actions. After the audit is enabled, the audit destination receives data from the audit.

Server Audit Specification

The Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope. The server audit specification collects many server-level action groups raised by the Extended Events feature. You can include audit action groups in a server audit specification. Audit action groups are predefined groups of actions, which are the atomic events exposed by the Database Engine. These actions are sent to the audit, which records them in the target.

Server-level audit action groups are described in the topic SQL Server Audit Action Groups and Actions.

Database Audit Specification

The Database Audit Specification object also belongs to a SQL Server Audit. You can create one database audit specification per SQL Server database per audit. The database audit specification collects database-level audit actions raised by the Extended Events feature. You can add either audit action groups or audit events to a database audit specification. Audit events are the atomic actions that can be audited by the SQL Server engine. Audit action groups are predefined groups of actions. Both are at the SQL Server database scope. These actions are sent to the audit, which records them in the target. Database-level audit action groups and audit actions are described in the topic SQL Server Audit Action Groups and Actions.

Target

The results of an audit are sent to a target, which can be a file, the Windows Security event log, or the Windows Application event log. (Writing to the Security log is not available on Windows XP.) Logs must be reviewed and archived periodically to make sure that the target has sufficient space to write additional records. Writing to the Windows Security log requires the SQL Server service account to be added to the Generate security audits policy. By default, the Local System, Local Service, and Network Service are part of this policy. This setting can be configured by using the security policy snap-in (secpol.msc). Additionally, the Audit object access security policy must be enabled for both Success and Failure. This setting can be configured by using the security policy snap-in (secpol.msc). In Windows Vista or Windows Server 2008, you can set the more granular application generated policy from the command line by using the audit policy program (AuditPol.exe). For more information about the steps to enable writing to the Windows Security log,see How to: Write Server Audit Events to the Security Log. For more information about the Auditpol.exe program, see Knowledge Base article 921469, How to use Group Policy to configure detailed security auditing. The Windows event logs are global to the Windows operating system. For more information about the Windows event logs, see Event Viewer Overview. If you need more precise permissions on the audit, use the binary file target. For more information about the audit records written to the target, see SQL Server Audit Records.

NOTE: Any authenticated user can read and write to the Windows Application event log. The Application event log requires lower permissions than the Windows Security event log and is less secure than the Windows Security event log.

Step By Step Walk Through

Now when you have understood Auditing Objects in SQL Server 2008, let me walk you through the process of creating an Audit in SQL Server 2008. I bet that it will be a good fun learning this feature in spite of the seriousness of the role of Auditing. In SQL Server 2008, the product team has made sure that the features are simple to use for the end users in spite of the underlying complexity. So let's start and explore.

Note: You can click on the images below to maximize.

Step 1:

Click open SQL Server 2008 Management Studio and log into it.

Step 2:

Explore the Security node in Object Explorer and select the Audit node.

Step 3:

Right click on the Audit node and select the option "New Audit" from the menu.

Step 4:

Now SQL Server 2008 will open up a dialog box "Create Audit" with a few fields. It is important to understand the significance of each of these fields. The details of these fields are provided below.

Audit name
The name of the audit. This is generated automatically when you create a new audit but is editable.

Queue delay (in milliseconds)
Specifies the amount of time in milliseconds that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous delivery. The default minimum value is 1000 (1 second). The maximum is 2,147,483,647 (2,147,483.647 seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds).

Shut down server on audit failure
Forces a server shut down when the server instance writing to the target cannot write data to the audit target. The login issuing this must have the SHUTDOWN permission. If the logon does not have this permission, this function will fail and an error message will be raised.

As a best practice, this should only be used in cases where an audit failure could compromise the security or integrity of the system.

Audit destination
Specifies the target for auditing data. The available options are a binary file, the Windows Application log, or the Windows Security log. SQL Server cannot write to the Windows Security log without configuring additional settings in Windows. For more information, see How to: Write Server Audit Events to the Security Log.

Note: Writing to the Security log is not available on Windows XP.

File path
Specifies the location of the folder where audit data is written when the Audit destination is a file. Clicking the button beside this field opens the Browse For Folder dialog to specify a file path or create a folder where the audit file is written.

Maximum rollover files
Specifies the maximum number of audit files to retain in the file system. When the setting of MAX_ROLLOVER_FILES=UNLIMITED, there is no limit imposed on the number of rollover files that will be created. The default value is UNLIMITED. The maximum number of files that can be specified is 2,147,483,647.

Maximum file size (MB)
Specifies the maximum size, in megabytes (MB), for an audit file. The minimum size that you can specify is 1024 KB and the maximum is 2,147,483,647 terabytes (TB). You can also specify UNLIMITED, which does not place a limit on the size of the file. Specifying a value lower than 1024 KB will raise the error MSG_MAXSIZE_TOO_SMALL. The default setting is UNLIMITED.

Reserve disk space
Specifies that space is pre-allocated on the disk equal to the specified maximum file size. This setting can only be used if MAXSIZE is not equal to UNLIMITED. The default setting is OFF.

Now after filling up appropriate values in the dialog box, press OK to create an Audit.

Step 5:

Now once the Audit is created, it could be found under the Security>>Audit node. Now to enable the Audit, just right click on the Server Audit that we have just created and from the menu select "Enable Audit".

You would see a dialog box with the success message, if the operation succeeds.

Step 6:

You can right click on the Audit just created and select the option "View Audit Logs". This opens up a dialog box that contains the audit logs.

You can also script the created policy. Right click on the Audit, select "Script Audit As" >> "Create To" >> (File/ Clipboard / Agent Job).

As we know that Policy Based Management is now an integral part of the SQL Server 2008 so if you would like to create a policy for this Audit then it is very easy to do that. You have to right click on the Audit just created, select the option "Facets" from the menu.

It brings up a dialog box that provides you with the status of the Audit. On the lower right corner of this dialog box you would find a button "Export Current State as Policy".

When you click on the button "Export Current State as Policy", it brings up another dialog box that shows the Policy Name and the condition name which is editable. You can select the appropriate option below these fields to apply this policy on the local server or to save it as a Policy file. If you select the "local server" option, you would see that a Policy and a Condition has been created with the name you have specified in the dialog box.

Step 7:

Now we will create the Server Audit Specification.

Before continuing further I would strongly recommend you to visit the TechNet Article : Server Audit Action Groups and Actions.

Once you have understood the Server Audit Action Groups, explore the node Security >> Audits >> Server Audit Specifications in the Object Explorer in the SQL Server 2008. Right click on the Server Audit Specifications and select "New Server Audit Specification".

When you select this option a dialog box appears in which you would specify Server Audit Specification Name and "Server Audit" that you have created in the Step 6. Thereafter you would specify the Audit Action Groups in the grid below. Once you have selected all the required Audit Action Groups, you would press OK and you would see an Audit Specification created for the server. In this Step I have selected the Audit Action Group "Backup Restore Group" and this event is raised whenever a backup or restore command is issued.

Once it is created, you would see that the Audit Specification is in the disabled state. You have to right click on the Audit Specification that you have just created and select "Enable Server Audit Specification" from the menu.

Just after that, you would see a dialog box saying, "The operation was successful" unless you have done something funny to make it fail. :-)

You may right click on the Server Audit Specification and select Facets from the menu. This brings up a dialog box with a button on the right bottom saying "Export Current State as Policy". Click on that button to create a policy as we have done above.

Step 8:

Now let's try backing up a database on the server. I have used AdventureWorksLT database for the demonstration. Then we will see the logs to assure that our Audit is working.

Explore the nodes in the Object Explorer, Databases >> AdventureWorksLT. Right click on the AdventureWorksLT database and select Tasks >> Backup.

This brings up the following dialog box to create a backup of the database. Please fill in appropriate values in the fields of this dialog box and press the button OK. For more information or help on taking backup please visit Books on Line.

You would see the backup successfully completes.

Now right click on the Server Audit we created in the Object Explorer and select the option View Audit Logs from the menu.

This will bring up the dialog box that will show the details of the backup event. In the picture below you would see the back up event that happened on the AdventureWorksLT database. You can scroll to the right to find the details in the dialog box.

Step 9:

Now we would create a Database Audit Specification. I would use the AdventureWorksLT database for the demonstration. You may visit the TechNet Article : Server Audit Action Groups and scroll down to "Database-Level Audit Action Groups" and "Database-Level Audit Actions" sections in this article to find more information.

The scenario is we want to Audit every select, insert, update, delete operation done on the SalesLT.Product table by anyone.

Explore the Database >> AdventureWorksLT >> Security >> Database Audit Specifications nodes in the Object Explorer. Now right click on the "Database Audit Specifications" node and select "New Database Audit Specification" from the menu.

This will open up a new dialog box with the Title - "Create Database Audit Specification".

Now we need to Give the Database Audit Specification a name and then select the Server Audit name from the drop down list.

Within the grid in the "Audit Action Type" choose "SELECT" from the drop down list, in the "Object Class" select "OBJECT". Click on the button beside Object Name text box, which opens up a dialog box. Select SalesLT.Product table which will bring "SalesLT" in "Object" and "Product" in "Object Name". Now click the button beside the text box under the section "Principal Name", select "public" by browsing the objects in the dialog box that appears once you click the button.

Repeat the operation for the Audit Action Type INSERT, UPDATE, DELETE. Then finally press OK.

This will create a Database Audit Specification for you which is not enabled. Right click on this Database Audit Specification and select "Enable Database Audit Specification" from the menu.

Once it the operation completes.. a dialog box appears confirming the success of the operation.

You can create a policy out of this Database Audit Specification by right clicking on the Database Audit Specification and selecting the option "Facets" and following the procedure in the similar way how we have done previously.

Step 10: (Last Step)

Now we would fire the query "Select * from SalesLT.Product" against the AdventureWorksLT database.

After that you may explore the Security Node in the Object Explorer. <SQL Instance Name> >> Security >> Audits and then right click on the server audit that is already created and select "View Audit Logs" from the menu.

Now the dialog box appears, it has the audit log that says that you have selected the SalesLT.Product table in the AdventureWorksLT Database. You may scroll to the right in the dialog box to find detailed information.

Wow! You have learned to work with this new new feature in just 10 steps.

Wasn't that very easy.

Thanks guys, to take a look at this post, I hope you enjoyed the article. Your comments and feedbacks are valuable so drop me an email. I would be happy to hear back from you, it gives me an opportunity to improve and gives me pleasure to bring new articles.

I will bring in more articles soon so keep a ...

  Bye now !

Policy-Based Management : SQL Server 2008

NOTE: Declarative Management Framework has been renamed to Policy-Based Management so I just changed the Title of the article, the contents are unchanged.

Hi, I am writing this post after somewhat long time … I hope you all are continuing with your learning on SQL Server 2008. Today I have some time to blog so I am writing something.

So after thinking hard what to write about I thought why not to write about the Declarative Management Framework. A wonderful solution for the DBAs who keep themselves busy patching and closing the mouse holes what other folks have created by violating some of the best practices, may it be naming convention of the Tables or they have created the Stored Proc. in the wrong schema. So if you are wondering what is this “Declarative Management Framework” then, please stop wondering and download the latest SQL Server 2008 November 2007 available, install it, read this article and start practicing. I promise you will like it.

What is Declarative Management Framework

Declarative Management Framework is a policy-based system for managing one or more instances of SQL Server 2008 which means that now rather than turning on/off the physical knobs to do the effective database administration you would have logical knobs to do it which could even be exported to a file for the future use and could be applied to a bunch of SQL Servers in a Group.

DBAs don't have to worry, it is not any other Framework like .Net Framework where you need to reach the Visual Studio and write custom code, test it, deploy and manage it. It is very simple and all the policies could be authored well from the SQL Server 2008 Management Studio.

Components of Declarative Management Framework

Declarative Management Framework has three components:

1. Policy management
2. Explicit administration
3. Automated administration

Policy Management

Policy administrators create policies.

Explicit Administration

Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.

Automated Administration

Policy administrators can run policies on demand, or enable automated policy execution by using one of the following execution modes:

• Changes are attempted, prevent out-of-compliance. This uses DDL triggers to prevent policy violations.
• Changes are attempted, log out-of-compliance. This uses event notification to evaluate a policy when a relevant change occurs.
• On schedule, log out-of-compliance. This uses a SQL Server Agent job to periodically evaluate a policy.

When automated policies are not enabled, Declarative Management Framework will have no effect on system performance. If enabled the system will have only negligible effect on the performance depending on the number of policies applied on a particular object.

Declarative Management Framework - Key Terms and Concepts

Managed Target
Entities that are managed by Declarative Management Framework, such as an instance of the SQL Server Database Engine, a database, a table, or an index. All targets in a server instance form a target hierarchy. A target set is the set of targets that results from applying a set of target filters to the target hierarchy, for example, all the tables in the database owned by the HumanResources schema.

Facet
A set of logical properties that model the behavior or characteristics for certain types of managed targets. The number and characteristics of the properties are built into the facet and can be added or removed by only the maker of the facet. A target type can implement one or more management facets, and a management facet can be implemented by one or more target types.

Condition
A Boolean expression that specifies a set of allowed states of a Declarative Management Framework managed target with regard to a management facet.

Policy
A Declarative Management Framework condition and the expected behavior, for example, execution mode, target filters, and schedule. A policy can contain only one condition. Policies can be enabled or disabled.

Category
A user-defined category to help manage policies. Users can classify policies into different policy categories. A policy belongs to one and only one policy category. Database owners can subscribe a database to a set of policy categories. Only policies from its subscribed categories can govern a database. All databases implicitly subscribe to the default policy category.

Execution Mode
Specifies how a policy will be executed. On-demand execution modes are Check and Configure. Automated execution modes are:

1. Changes are attempted, prevent out-of-compliance
2. Changes are attempted, log out-of-compliance
3. On schedule, log out-of-compliance

Effective Policy
The effective policies of a target are those policies that govern this target. A policy is effective with regard to a target only if all the following conditions are satisfied:

• The policy is enabled.
• The target belongs to the target set of the policy.
• The target or one of the targets ancestors subscribes to the policy group that contains this policy.

Usage Scenarios : Examples

Declarative Management Framework would be helpful in resolving the issues presented in the following scenarios:

• A company policy prohibits enabling Database Mail or SQL Mail. A policy is created to check the server state of those two features. An administrator compares the server state to the policy. If the server state is out of compliance, the administrator chooses the Configure mode and the policy brings the server state into compliance.
• The AdventureWorks database has a naming convention that requires all stored procedures to start with the letters AW_. A policy is created to enforce this policy. An administrator tests this policy and receives a list of stored procedures that are out of compliance. If future stored procedures do not comply with this naming convention, the creation statements for the stored procedures fail.

A Quick Walk Through : Declarative Management Framework

We are going to create a quick policy that will make sure that the CLR integration is not enabled on this instance of SQL Server 2008. I am adding screen shots below, in case they are not clearly visible, click on them and they will be enlarged.

Step 1: We will reach the SQL Server 2008 Management Studio, log in and then expand the Management Node as demonstrated below.

Step 2: Now we will first create a condition so expand the node CONDITION and right click and in the menu select "New Condition". A new dialog box appears, in the "Name" field type in a name for this condition like "Condition_Disable_CLR" or any fancy name you like. In the Facet select "Server Configuration". Please see the screen shot below.

Step 3: In the Expression section reach the Field and select "@ClrIntegrationEnabled" from the Drop Down, Operator should be "=" and in the Value field select "False" from the Drop Down. Press OK and you have created a condition.

Step 4: Now it is the time to create a policy based on this condition. So right click on the "Policies" node in the Management Studio. Select "New Policy" from the menu. This brings up a new dialog box. In the "Name" field type in a name for the policy like "Policy_Disable_CLR". In the "Check Condition" select the condition that you have authored last in this case "Condition_Disable_CLR" which you would find under "Server Configurations". Select the "Execution Mode" as "On Demand" and Press OK.

Congratulations !!! you have created your first policy on the SQL Server 2008 Server.

Step 5: Now expand the "Policies" Node in the Management Studio and locate the Policy that you have created just now.

Step 6: Right click on the Policy and select "Test Policy" this will bring up the dialog box. If the CLR Integration is disabled on this Server instance then it will be notified else if the CLR was enabled anytime then it will be notified.

Policy Not Violated

Step 7: Now I will enable the CLR by executing the System Stored Procedure

sp_configure 'clr enabled', 1

Now we will execute "RECONFIGURE" to make the changes effective.

We will now retest the policy and see the result.

Policy Violated

So, I hope the DBAs will find it comfortable authoring policies for effective administration. If you like or dislike the article please post it .. it helps me to bring better articles every time. I will bring more articles on SQL Server 2008 Enhancements so keep a watch.

I use Windows Live Writer to write my blogs... what do you use ?

SQL Server 2008 - Change Data Capture - Part II

Hi ! It's been a long time since I last updated my blog. To continue with the Change Data Capture feature of SQL Server 2008, this is new post that helps you to know how you can write your own stored procedures / functions to get the required data in the fashion you want, using the CDC functions provided by Microsoft.

NOTE: If you haven't read the article "SQL Server 2008 - Change Data Capture - Part I" then I would recommend you to please visit the article first else you won't be able to connect well, what I am trying to express here.

The following function works in the similar fashion like cdc.fn_cdc_get_all_changes_InstanceHumanResourcesEmployee but this function will take the starting time and ending time i.e. the time range and the row filter option. The row filter option will either be 'all'  or 'all update old'.

cdc.fn_cdc_get_all_changes_InstanceHumanResourcesEmployee always matches the LSN number provided by you with the LSN present in the CDC table and if it is not found it will throw an error. The reason is it will check for the exact LSN Number so that duplicate records don't creep into the result set. The following function is more generic and will tell you about the changes that happened to a table within a time range. The logic is created in a fashion that if there are no records existing in the table matching the criteria specified by you then it will return an empty set.

This is just an example to demonstrate that how we could write our own SPs and Functions to get the data from the Change Tables.