SQL Server 2008 – All Action Auditing (AAA)

In SQL Server 2005, this was one of the area were there was significant enhancement from SQL Server 2000. DDL Trigger and default trace was introduced in SQL Server 2005 to provide administrator more controlled Audit without effecting the system performance. Still , the SQL Server community was not convinced with the auditing features of SQL Server 2005. For instance, DDL trigger was not able to track all the DDL Trigger events like sp_Rename. Running profiler for auditing was just can not be done because Profiler was not able to trace the only required data. So what was the solution provided in earlier versions to have a proper audit system? Nothing but go for third party tools.

In SQL Server 2008, Audit is a first class object in the server. You have complete set of DDL statement to manage Audit feature. Audit can also make the audited information entries into file , windows application log or windows security log. Security log entry benefit is that, even the administrator can not change the entries. Another good thing about this feature is , you have very granular level control. Ie. You can audit very specific action on an object for example, who is running select query on EmployeeSalary table , salary column. This was not possible in profiler.

Note : As on today, the information is that this feature will only be available in Enterprise Edition of SQL Server 2008.

There are two categories of actions for audits:
• Server-level. These actions include server operations, such as management changes and logon and logoff operations.
• Database-level. These actions encompass data manipulation languages (DML) and data definition language (DDL) operations.

1. How to configure Audit using Management Studio

Create Audit


Important

• File path : Only the folder has to be specified. The File filename has to be unique so, the system will provide the name with timestamp.
• Folder : The folder must already be existing
• You can mention the max rollover files and size.






2. Create Server Audit Specification (or Database Audit Specification as per your requirement)



Audit Action Type : There are many action type. Select the appropriate one.











3. Ensure that the Audit and Audit Specification is enabled
Select is_state_enabled,name From sys.server_file_audits
Select is_state_enabled,Name From sys.server_audit_specifications
Select is_state_enabled,Name From sys.database_audit_specifications





















The Audit file viewer report will looks like this.













4. Script to Create Audit Objects
GUI method is very simple and if somebody wants to do it by TSQL Script, its even simple. You can script the Audit objects the same way we do for any other objects. Just right click on the Audit and Server Audit specification which we have created and Script it. You can keep the script in Version control or so. Here is the script I have generated after configuring the audit process through GUI.

--Create Audit
/****** Object: Audit [Audit-20080612-194600] Script Date: 06/13/2008 09:43:25 ******/
CREATE SERVER AUDIT [Audit-20080612-194600]
TO FILE
( FILEPATH = N'D:\SQLAudit\'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 10
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'c314f405-ae9c-4f4b-947f-041070683c9d'
)
GO
--Create Server Specification
USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20080612-195650]
FOR SERVER AUDIT [Audit-20080612-194600]
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20080612-195650]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
GO

Important Datebase system objects Related to Auidt feature
fn_get_audit_file :Returns information from an audit file created by a server audit.
Eg.
Select Statement,* From fn_get_audit_file ('D:\SQLAudit\*',null,null)
--Will retrun all the file reports in the specified folder
sys.server_file_audits
sys.server_audit_specifications
sys.database_audit_specifications


Script to List all the Database Specification created from all the database
set nocount on
declare @My_Return int
declare @command varchar(1000)
set @command = 'use [?] Select *from sys.database_audit_specifications '
print @command
exec @My_Return = master.dbo.sp_MSforeachdb @command

Audit Trucnate Table Command
DDL Trigger in 2005 and 2008 still not able to track TRUNCATE Table Command. But Audit does that. SCHEMA_OBJECT_CHANGE_GROUP Database Specification will track your Truncate command too.

Summary
There are DDL statements to configure Audit like CREATE SERVER AUDIT AND CREATE SERVER AUDIT SPECIFICATION kind. But GUI method is pretty simple and straight forward. You also have these objects exposed through SMO. Because of the new events introduced to track the information, which is inside the engine unlike SQL Trace, the performance will be faster compare to SQL Trace. Any audit system will consume resource, but what All Action Audit (AAA) feature of SQL Server 2008 offers is, very granular level audit control with negligible performance hindrance.

Resource Governor in SQL Server 2008

Yet another fantastic feature or utility tool of SQL Server 2008 Resource Governor allows you to control the resource according to the requirements . This was motive behind this feature is providing predictable response to the user in any situation. In earlier versions, we had a single pool of resources like Memory, CPU, threads etc. You can not priorities the workload vs Resource pool in 2005 and earlier version. Generally, who accesses the system first and starts a process, it can consume the resources without any restrictions. Consider, some kind of BI runaway query is first hit system where the OLTP and OLAP Databases are in the same server. Now the OLTP process has to wait till the OLAP process releases the resource. This was a major concern in earlier versions. So what were the solution then, go for multiple instances and configure the Resource per instance or go for different machine altogether. Both method were having its own problem. By specifying the resource, if the system is not using that resource still will not released. If you go for another machine, you may have license issue and it’s not a cost effective method.

In SQL Server 2008, these problems are addressed by providing a tool called Resource Governor. You can differentiate the workload by Application Name, Login, Group, by database name etc. Once you have defined the workload, you can configure the resource which can consumed by workload. Probably, you want to give more resource for your OLTP application than the OLAP. You have that kind of flexibility and control here.
The following three concepts are fundamental to understanding and using Resource Governor:
• Resource pools. Two resource pools (internal and default) are created when SQL Server 2008 is installed. Resource Governor also supports user-defined resource pools.
• 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.
• 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.

I am exploring this tool in detail. I will be updating this blog entry soon with my hands-on with this wonderful tool

SQL Server 2008 - Sparse colum

One of the major enhancement in database engine of SQL Server 2008 is Sparse column. It improve data retrival and reduce the storage cost. It also can be used with Filtered Index to improve the performance.

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

How to Create sparse column.

Simple , just mention sparse keyword in table creation or alter statement.

CREATE TABLE TestSparseColumn
(Comments varchar(max) SPARSE null)

Refer this KB

Note : There are many limitations as far as the implementation is concerned. like the column has to be null, cannot be included in Clustered index, merge replication,compression etc. Please refer the site mentioned above or BOL.

Notification Service no more available in SQL Server 2008

Check this thread for more info. Joe Webb has explained in detail

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2006061&SiteID=1

SQL Server 2008 - Filtered Index

If you see the Create Index Syntax in SQL Server 2008 books online, you can see a new option called “ [ WHERE ]” which stands for Filtered Index feature.

A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Suppose you have a table which stored the history (not so relevant to OLTP application ) data also. The query on that table is mostly on the current data and you want to index only on current data for eg. DateofPurchace > ‘1-1-2007’ kind. Another example would be , you have a product catalog and you want to show only the active products to the customer.

Eg.
CREATE NONCLUSTERED INDEX NCI_OrderDetailsFilteredIndex
ON OrderDetail (OrderDetailID, DateofPurchase)
WHERE DateofPurchase >’1-1-2007’

Filtered Index Design Guidelines

Filtered Index Feature Support
In general, the Database Engine and tools provide the same support for filtered indexes that they provide for nonclustered full-table indexes, considering filtered indexes as a special type of nonclustered indexes. The following list provides notes about tools and features that fully support, do not support, or have restricted support for filtered indexes.
• ALTER INDEX supports filtered indexes. To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.
• The missing indexes feature does not suggest filtered indexes.
• The Database Engine Tuning Advisor considers filtered indexes when recommending index tuning advice.
• Online index operations support filtered indexes.
• Table hints support filtered indexes, but have some restrictions that do not apply to non-filtered indexes. These are explained in the following section.