Monthly Archives: November 2013

What is C2 Audit tracing in SQL Server

In this article I will discuss the C2 audit trace option in SQL Server.C2 is security-auditing level defined by the US Government’s Trusted Computer Security Evaluation Criteria (TCSEC) program. Microsoft added the C2 audit mode option to address government requirements that certain contractors document database activity and possible security policy violations. So what does this audit trace do and how can we enable\disable the audit trace.

This audit captures the following information on the SQL server.
1) End User Activity (All SQL commands, logins and logouts)
2) Security Events (Grant/Revoke/Deny, login/user/role add/remove/configure)
3) Utility Events (Backup/Restore/ Bulk Insert/BCP/DBCC commands)
4) Server Events (Shutdown, Pause, Start)

Let us now see how we can enable this audit trace
Step 1: Run the below code

EXEC sys.sp_configure N'c2 audit mode', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Step 2: Restart the SQL Server

Once you have restarted the SQL Server, you will find a new trace has kicked off. If you run sys.traces on the server you will find an audit trace that is running
c2If you browse to the location of the trace and open the trace file and view the properties of the trace then you will see as below
c2_1So how can we stop this trace. If we try the traditional way of stopping a trace then it will result in an error. Snapshot below.
C2_3Follow the below steps to stop this trace and remove its definition from the server
Step 1: Run the below code

EXEC sys.sp_configure N'c2 audit mode', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

Step 2: Restart the SQL server

Few important points that we have to keep in mind before implementing this audit trace
1) This is resource consuming . Implement with caution on a busy OLTP system.
2) The max size of the trace file is set to 200 Mb. You cannot change this.
3) Once the size is breached, a new trace file generated.
4) If the directory in which logs are being saved runs out of space, SQL Server will shut itself down. If auditing is set to start automatically, you must either restart the instance with the -f flag, or free up additional disk space for the audit log.
5) You cannot filter out events from this trace property, it captures everything.

Advertisements

CONCAT() function in SQL Server 2012

In this article I will discuss the new function concat() in SQL Server 2012. This function performs a concatenation operation. We need to pass CONCAT() a number of string arguments and it will concatenate, or join them together and return an output string.
The basic syntax is as follows:
CONCAT ( string_value1, string_value2 [, string_valueN ] )

Let us now understand with an example how we can use this function and how it is different from the concatenation operator. Let us create a new table and insert some values in the table and then use a query that will use this function

CREATE TABLE MyConcatTable
(
FirstName varchar(20) NOT NULL,
MiddleName varchar(20) NULL,
LastName varchar(20) NOT NULL
)
INSERT INTO MyConcatTable
VALUES
('Sachin', 'Ramesh', 'Tendulkar'),
('Diego', 'Armando', 'Maradona')
,('Diego', Null, 'Maradona')
SELECT CONCAT(FirstName + ' ', MiddleName + ' ', LastName) AS
CustomerName
FROM MyConcatTable

The output of the above as shown below
concat1The concat function joins the columns and returns a single string. Now the big question is how is it different from the below

SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS CustomerName
FROM MyConcatTable

Let us run the above code and see the outcome
concat2The 3rd value in the table has a null in the middle name column. Normal concatenation would not handle that thus resulting in a null output but the concat() would automatically remove the null and join the next corresponding string value.

I hope this short article was helpful in understanding the new function in SQL Server 2012 called concat().

Life is a SEQUENCE of events – Part 2

In my previous article on Sequence we went through how we can create and implement Sequence.In this short article I will demonstrate how we can achieve the following:

1) How to get the current value of the Sequence
2) How to get the next Sequence Value
3) Restarting the Sequence Value
4) Dropping a Sequence object

1) current value of the Sequence
To find the current value of a Sequence we need to query the system view sys.sequences.

USE AdventureWorks2012
go
SELECT Current_Value FROM sys.sequences
WHERE name= 'MySequence1' -- specify the name of the sequence
GO

2) get the next Sequence Value:

use Adventureworks
go
SELECT (NEXT VALUE FOR MySequence1)
AS NextSequenceValue

3) Restarting the Sequence Value:

USE [AdventureWorks2012]
GO
ALTER SEQUENCE [dbo].[MySequence1]
RESTART  WITH 100001
INCREMENT BY 10
NO MINVALUE
MAXVALUE 200000

4) Dropping a Sequence object:

use Adventureworks2012
go
DROP SEQUENCE MySequence1;
go

How to restore a table from a Full backup using Quest Litespeed without restoring the entire database

In this article I will demonstrate how we can restore a table from a SQL Server database backup file which has been taken by Quest Litespeed tool. This feature called Object level recovery in the tool is extremely helpful as you need not restore the entire database and yet retrieve data from the backup. Follow the below mentioned steps and understand how this can be accomplished. I will be using a trial copy of Quest Litespeed along with SQL server 2012 and the database we would use is Adventureworks2012. However you can use SQL server 2008 also.

Step 1: Create a database and take a full backup of the database
Let us create a database for this purpose

CREATE DATABASE MyDB
GO
USE mydb
GO
CREATE TABLE Mytable1
( id INT,FNAME NVARCHAR(50) )
GO
INSERT INTO Mytable1 VALUES (1,'Sanchayan')
GO
INSERT INTO Mytable1 VALUES (2,'Abhishek')
go
CREATE TABLE Mytable2
( id INT,LNAME NVARCHAR(50) )
GO
INSERT INTO Mytable2 VALUES (1,'Pandit')
GO
INSERT INTO Mytable2 VALUES (2,'Agrawal')
go

Now take a Full Backup of the database.

exec master.dbo.xp_backup_database
@database = N'Mydb',
@compressionlevel = 3,
@filename = N'D:\MSSQL\SQLBackup\Mydb.bak',
@init = 0,
@OLRMAP = 1--This is important as this marks the backup for object level recovery
GO

Step 2: Now run the following code to find the Tables available in the backup

Use Master
go
exec xp_objectrecovery_viewcontents
@FileName = N'D:\MSSQL\SQLBackup\mydb.bak',
@FileNumber = 1,
@Type = 'All',
@DisableLogProcessing = 1

olr1
Step 3: Now select the table that you wish to restore. In this demo we would restore MyTable2.
We would need the below predefined:
a) The database where we would restore the table.
b) A temp directory location which should be large enough for the table to be restored initially.

Use master
go
exec xp_objectrecovery
@FileName = N'D:\MSSQL\SQLBackup\Mydb.bak',
@FileNumber = 1,
@ObjectName = N'dbo.Mytable2',
@DestinationTable = N'[dbo].[Mytable2]',
@DestinationDatabase = N'MyTempDB',
@DestinationServer = N'GN-A-MOOKBO-7L\sql2012',
@TempDirectory = N'D:\MSSQL\SQLBackup',
@DisableLogProcessing = 1

The output of the above code would be as below
olr2To verify if the data you can query the table that we restored on mytempdb database
olr3I hope this article was helpful in understanding how we can use ‘Object level recovery’ to fetch data from a full backup file that has been taken using Quest Litespeed for SQL Server tool.

Technical reference: http://www.quest.com/litespeed-for-sql-server/

Life is a SEQUENCE of events

In this article I will discuss this new feature in SQL Server 2012 called Sequence and how we can use this feature.

Sequence in SQL Server 2012 is a database object and can be considered as an alternative to Identity property which we set at a table level. The primary difference between the two is that sequence is scoped to the entire database while Identity is scoped to a specific column in a specific table.

If we expand the programmability node under a specific database in SQL Server 2012 then the last object that we would find is Sequences.
seq1The primary purpose of sequence is that it can used between different tables within the database and help maintain a synced copy of seed values between multiple tables that are referencing each other as it is not limited to a single table.
Another objective where Sequence can be very handy is performance gain because using identity means that SQL has to fetch the next value from the disk whereas sequences would be residing in the memory in case it is used with the cache option which we will see later.

Let us now understand with an example how we can create and implement sequence.

Creating a Sequence:
Step 1: Right click on the Sequence object under the Programmability node
Step 2: Select ‘New Sequence’ and you will see the below dialog box
seq2Let us go through and understand each field in this dialog box before proceeding

a) Sequence name: This is where you give a name of the sequence. By default it picks up the date and time of the sequence creation.
b) Sequence schema: The schema to which you would assign the sequence.
c) Data type: The data type of the sequence. By default it is bigint.
d) Start Value: The starting value of the sequence
e) Increment By: The value by which you want to increment the series.
f) Minimum value: The least value of the series. This comes into play when you cycle the series.
g) Maximum value : The upper bound of the series post which the series will either cycle or stop.
h) Cycle : This property specifies whether the series should restart from the minimum value or throw an error when its minimum or maximum value is exceeded. The default cycle value for new sequence objects is NO CYCLE.
i) Cache: This increases performance by minimizing the number of disk IO that are required to generate sequence numbers. If a cache is set to 100 is chosen, SQL Server does not keep 100 individual values cached. It only caches the current value and the number of values left in the cache.

Step 3: Lets now fill up the dialog box and create our first sequence.
seq3The TSQL for the same is given below

USE [AdventureWorks2012]
GO
CREATE SEQUENCE [dbo].[MySequence1]
AS [int]
START WITH 1000
INCREMENT BY 1
MAXVALUE 100000
CACHE
GO

When you click on ok button or you run the code, you will find a new sequence created under the Sequences node.
seq4

Now let us implement this object and understand how it works:
Step 1: Create a new table

USE [AdventureWorks2012]
GO
CREATE TABLE mytable
( ID INT PRIMARY KEY,
Fname VARCHAR(30),
Lname VARCHAR(30),
)
go

Step 2: Now lets insert some values into the table

INSERT INTO mytable
(ID,FName,Lname)
VALUES
(NEXT VALUE FOR Mysequence1, 'Sanchayan','Pandit')
INSERT INTO mytable
(ID,FName,Lname)
VALUES
(NEXT VALUE FOR Mysequence1, 'Abhishek','Agrawal')

Lets have a look at the rows inserted
seq5As you observe now, the id field now has incremented by 1 starting with 1000.

So this is how we can implement Sequence in SQL Server 2012. I will deep dive into Sequence in my next article and we will understand how we can play around this new feature in SQL Server 2012.

 

%d bloggers like this: