Mr T-SQL

RAID Levels

2013-08-12T10:51:00.002+01:00

A Quick Summary of the most commonly used RAID levels and how you should use them with SQL Server;

RAID Level	Description	Parity	Mirrored	Stripe	Fault Tolerance	Use For
0	Stripes across multiple disks thus improving Read/Write performance. However as no Parity is used, if one disk fails you will lose the whole stripe set	N	N	Y	N	Data Files
1	Mirrored disks; maintains a exact copy on a separate physical disk. Write performance is affected as two writes have to be performed for every change.	N	Y	N	Y	Log Files
5	Raid 0 + Parity, this will stripe across multiple disk but in addition will maintain parity information , meaning that if you lose a single disk in the stripe set, it will be able to rebuild using the parity stored on the remaining disks	Y	N	Y	Y	Data Files

HOW TO: Moving TempDB files after a drive fault

2013-06-05T14:54:00.002+01:00

If you ever find yourself in a situation where the drive you store your tempdb files goes missing then you will find that your SQL instance will refuse to start.

No problem, I'll just change the location of my tempdb files, first i'll just start SQL... oh hang on a minute...?!!

Don't Panic there is a solution!

You can start SQL by using the following trace flag 3608 (Prevents SQL Server from automatically starting and recovering any database except the master database.)

Run this from the a CMD/Powershell window to start the SQL instance (if you are are not using default instances, you will need to change the MSSQLSERVER to reflect your named instance)

NET START MSSQLSERVER /f /T3608

Now use SQLCMD and run the following
ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='<Valid Path>')
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='<Vaild Path>')

Now stop the SQL Service

NET STOP MSSQLSERVER

Restart using the standard methods and your SQL server should be up and running again!

MERGE Statements - Just use them!

2013-05-08T12:54:00.001+01:00

I love MERGE statements I think they are great! However, it seems that most people don't use them, either they don't know about them or they can't get the grips with the syntax.

For those that don't know; MERGE statements provide us with the ability to perform UPSERTS (Update if the row exists, INSERT if it doesn't).

No more clearing down tables before inserting or using IF statments to handle UPSERTS , and once you get to hang of the syntax it makes your code more readable too;

The Basic Syntax

MERGE

<Target table you want to INSERT/UPDATE> AS TAR

USING

<Source of your data- this can be a table or a SELECT Query> AS SRC

TAR.<Column> = SRC.<Column> , .... -- columns to JOIN the Source and Target

WHEN MATCHED THEN

UPDATE SET TAR.<column> = SRC.<column>, ....

WHEN NOT MATCHED BY TARGET THEN

INSERT (<column List>) VALUES(SRC.<column>,...)

; -- A MERGE must be terminated with a semi-colon

You can also use OUTPUT to report what actions were performed by the MERGE statement.

So just start using them, they are a good thing!

SQL Startup Issue: An error occurred during decryption [SOLVED]

2013-02-18T11:19:00.001+00:00

The Issue
Have you been having trouble starting your SQL instance after a service account change (even though you used SQL Configuration Manager) and are you seeing these messages in the ERRORLOG;

Error: 15466, Severity: 16, State: 1.

An error occurred during decryption.

Starting up database 'model'.Server name is '**********'. This is an informational message only. No user action is required.

Error: 17190, Severity: 16, State: 1.

FallBack certificate initialization failed with error code: 1.

Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.

Error: 17182, Severity: 16, State: 1.

TDSSNIClient initialization failed with error 0x80092004, status code 0x80.

Error: 17182, Severity: 16, State: 1.

TDSSNIClient initialization failed with error 0x80092004, status code 0x1.

Error: 17826, Severity: 18, State: 3.

Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

Error: 17120, Severity: 16, State: 1.

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

The Fix

I found that the issue was down to a corrupt profile for the service account user. The fix was log on the server as the service account user then open regedit navigate to;

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders

You should notice that there isn't an "AppData" value.

What you need to do is create a new "Expandable String Value" call it AppData and insert the following as its value "%userprofile%\Application Data"

You should now be able to start SQL server, as it now has a place to create its self signed certificate.

Additional Fix for Powershell

You could quite easily do this using Powershell , however if you have a corrupt profile you might find that your powershell window will not open spitting out the following message;

The shell cannot be started. A failure occurred during initialization:
Object reference not set to an instance of an object.

This is also an easy fix, whilst you are in regedit simple create a key called "Environment" under HKEY_CURRENT_USER and as if by magic you can open Powershell again!

Automatically create logins after Database Restore

2013-02-06T12:43:00.002+00:00

This code comes in handy when restoring databases from LIVE to TEST systems. It will create any missing logins and map up SIDS for those that do exist.

DECLARE @Password VARCHAR(100),

@UserName VARCHAR(100) ,

@UserSID VARBINARY(85),

@SQLCmd VARCHAR(1000)

SET @Password = 'Chang3M3Lat3r'

CREATE TABLE #TempUser

(

UserName VARCHAR(100),

UserSID VARBINARY(85)

)

INSERT INTO #TempUsers exec sp_change_users_login 'Report'

WHILE (SELECT COUNT(*) FROM #TempUsers ) >0

BEGIN

SELECT TOP 1 @UserName=UserName,@UserSID= UserSID FROM #TempUsers

IF (SELECT COUNT(*) FROM master..syslogins WHERE Name=@UserName) >0

EXEC sp_change_users_login 'Auto_Fix',@UserName

ELSE IF (@UserName!='dbo')

BEGIN

SELECT @SQLCmd='CREATE LOGIN [' + @UserName +'] WITH PASSWORD = ''' + @Password + ''', SID = '+ master.dbo.fn_varbintohexstr(@Usersid)+ ' , DEFAULT_DATABASE = [' + db_name() + '], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF'

EXEC(@SQLCmd)

END

DELETE TOP (1) FROM #TempUsers

END

DROP TABLE #TempUsers

EXEC sp_changedbowner 'sa'

How to rename a SQL Instance

2013-01-31T12:07:00.002+00:00

Just run the following script (obviously changing the name stored in @NewServerName);

DECLARE @OldServerName VARCHAR(50),

@NewServerName VARCHAR(50)

SELECT @NewServerName='<New Server Name>'

SELECT @OldServerName=srvname FROM master.dbo.sysservers WHERE srvid=0

EXEC sp_dropserver @OldServerName

EXEC sp_addserver @NewServerName, local

Now restart the SQL Service and as if by magic you server has been renamed!

Off Topic: Raspberry Pi, my oh my!

2013-01-22T16:29:00.000+00:00

I received my Raspberry Pi a week ago and have started work on an internet radio project.

I have been making good progress on it, I have gutted an old Roberts Revival R250 FM Radio and managed to wire up the existing speaker to pi.

Before pictures are shown below for those interested;

I have configured the internet radio with MPD/MPC; which is working well and have been controlling it remotely via my phone using the MPDroid app.

To Do List;

Wire up original Tuner and Volume controls to the pi (will most likely use Arduino for this as these are better at handling Analogue signals)
Write some python code to control the radio using the GPIO or input from Arduino
May need to amplify the sound from the pi.
Read a few electronics books!
Practice my soldering!
Work out how to pack everything inside the small R250 carcass!

I will update this post with my progress.

SQL Change Data Capture - Automatically generate DML UNDO/REDO Scripts (Part 2)

2012-12-07T14:46:00.004+00:00

Sorry for the delay in posting the second part of this blog post (I totally forgot). Probably not the best way to display T-SQL code but a quick copy and paste into management studio should do the trick.

Below is my procedure, when executed against a database with CDC enabled it will generate REDO/UNDO DML scripts based on the parameters passed in. I have included two example calls here;

Example Calls

This will generate a script to rollback the last 120 minutes of Changes, across all CDC captured tables

EXEC dbo.up_GenerateCDCScript @Action='UNDO' , @rewind=120

This will generate a REDO script for all changes made in the last 120 minutes

EXEC dbo.up_GenerateCDCScript @Action='REDO' , @rewind=120

Procedure Definition

IF EXISTS(SELECT * FROM sysobjects WHERE id = object_ID('dbo.up_GenerateCDCScript') )

BEGIN

DROP PROCEDURE dbo.up_GenerateCDCScript

END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Paul Nutley

-- Version 1.0

-- Description: Generates an UNDO/REDO script based on data stored in CDC tables

-- =============================================

CREATE PROCEDURE dbo.up_GenerateCDCScript

@action VARCHAR(10) = 'REDO',

@datefrom datetime = null,

@dateto datetime=null,

@rewind int =1,

@dbname VARCHAR(100) = null

--===================================

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--=========

BEGIN TRY

--=========

-- start of procedure code

DECLARE @CDC_Table VARCHAR(500),

@CDC_ID INT,

@Table_ID INT,

@TableName VARCHAR(500),

@FullTableName VARCHAR(500),

@UpdateMask INT,

@IndexName VARCHAR(255),

@Operation INT,

@LSN binary(10),

@SeqVal binary(10),

@SQLCmd VARCHAR(MAX),

@LoopCount INT,

@MaxCount INT,

@ColCount INT=0

CREATE TABLE #CDC_TABLE_TRANSACTIONS

(

[RowID] INT IDENTITY(1,1),

[LSN] Binary(10),

[SequenceVal] Binary(10),

[Operation] INT ,

[UpdateMask] INT

)

CREATE TABLE #CDC_TABLE_COLUMNS

(

column_name VARCHAR(255),

column_id INT,

column_ordinal INT, -- new

column_type VARCHAR(100),

is_computed BIT,

is_pk BIT

)

CREATE TABLE #CDC_UNDO_COMMANDS

(

[LSN] binary(10),

[SeqVal] binary(10),

[TableName] VARCHAR(500),

[UndoCmd] VARCHAR(MAX)

)

SELECT

source_object_id AS [ID],

'cdc.'+ Capture_instance +'_CT' AS [TableName],

start_lsn,

Index_Name,

[OBJECT_ID] AS [CDC_ID]

INTO

#CDC_DEPLOY_ROLLBACK

FROM

cdc.change_tables

--WHERE

-- capture_instance LIKE '%_DEPLOY'

IF (@datefrom is null)

SELECT @datefrom=DATEADD(mi,-(@rewind),getdate()) -- get the from @rewind minutes passed in

IF (@dateto is null)

SELECT @dateto=getdate()

IF (@dbname is null)

SELECT @dbname=db_name()

WHILE (SELECT COUNT(*) FROM #CDC_DEPLOY_ROLLBACK) > 0

BEGIN

-- lets populate our varibles

SELECT TOP 1 @CDC_Table=[TableName], @CDC_ID=[CDC_ID], @Table_ID=[ID], @IndexName=Index_Name FROM #CDC_DEPLOY_ROLLBACK

SELECT @TableName=OBJECT_NAME(@Table_ID)

-- this is where you limt the window based on date from and date to values

--SET @SQLCmd='SELECT __$start_lsn AS [LSN], __$seqval AS [SequenceVal], __$operation AS [Operation], CONVERT(INT,__$update_mask) AS [UpdateMask] FROM ' + db_name() + '.' + @CDC_Table + ' WHERE __$operation != 3 ORDER BY __$start_lsn DESC'

SET @SQLCmd='SELECT __$start_lsn AS [LSN], __$seqval AS [SequenceVal], __$operation AS [Operation], CONVERT(INT,__$update_mask) AS [UpdateMask] FROM ' + db_name() + '.' + @CDC_Table + ' WHERE __$operation != 3 AND sys.fn_cdc_map_lsn_to_time(__$start_lsn) BETWEEN ''' + CONVERT(VARCHAR(100),@datefrom,121) + ''' AND ''' + CONVERT(VARCHAR(100),@dateto,121) + ''' ORDER BY __$start_lsn DESC'

INSERT INTO #CDC_TABLE_TRANSACTIONS EXEC (@SQLCmd)

-- Lets return the column Lists for the Table

INSERT INTO #CDC_TABLE_COLUMNS SELECT column_name,column_id,column_ordinal,column_type,is_computed,0 AS [is_pk] FROM cdc.captured_columns WHERE object_id=@CDC_ID --#-- new

-- Lets flag up any Primary key fields

UPDATE #CDC_TABLE_COLUMNS SET is_pk=1 WHERE column_name IN (SELECT I.column_name FROM cdc.[index_columns] I WHERE I.object_id=@CDC_ID )

WHILE (SELECT COUNT(*) FROM #CDC_TABLE_TRANSACTIONS) > 0

BEGIN

SELECT TOP (1) @LSN=[LSN], @SeqVal=[SequenceVal],@Operation=[Operation], @UpdateMask=[UpdateMask] FROM #CDC_TABLE_TRANSACTIONS

SELECT @FullTableName= @dbname + '.'+ SCHEMA_NAME(OBJECTPROPERTY(@Table_ID,'OwnerId')) + '.' + @TableName

IF (@Operation = 1) -- DELETE

BEGIN

IF (@action = 'UNDO')

BEGIN

-- we need to create an insert statement

SELECT @SQLCmd = 'INSERT INTO ' + @FullTableName + ' ('

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS

WHILE (@LoopCount < @MaxCount)

BEGIN

SELECT TOP (1) @LoopCount=column_id, @SQLCmd=@SQLCmd + column_name FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

IF (@LoopCount < @MaxCount)

SET @SQLCmd=@SQLCmd +','

END

SET @SQLCmd=@SQLCmd +') SELECT '

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS

WHILE (@LoopCount < @MaxCount)

BEGIN

SELECT TOP (1) @LoopCount=column_id, @SQLCmd=@SQLCmd + column_name FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

IF (@LoopCount < @MaxCount) --#-- new

SET @SQLCmd=@SQLCmd +','

END

SELECT @SQLCmd=@SQLCmd + ' FROM ' + db_name() + '.' + @CDC_Table + ' WHERE sys.fn_cdc_map_lsn_to_time(__$start_lsn) = ''' + CONVERT(VARCHAR(100),sys.fn_cdc_map_lsn_to_time(@LSN),121) + ''''

END

ELSE IF (@action = 'REDO')

BEGIN

-- we need to create a DELETE statement

SELECT @SQLCmd = 'DELETE FROM ' + @FullTableName + ' FROM ' + @dbname + '.' + @CDC_Table + ' C WHERE '

IF (SELECT COUNT(*) FROM #CDC_TABLE_COLUMNS WHERE [is_pk]=1) >0

BEGIN

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS WHERE [is_pk]=1 --# --new

WHILE (@LoopCount < @MaxCount)

BEGIN

SELECT TOP (1) @LoopCount=column_id, @SQLCmd=@SQLCmd + @TableName + '.'+ column_name + ' = C.' + column_name FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount AND [is_pk]=1 ORDER BY column_id --# --new

IF (@LoopCount < @MaxCount AND (SELECT TOP (1) [is_pk] FROM #CDC_TABLE_COLUMNS WHERE column_id = @LoopCount)=1) --#new

SET @SQLCmd=@SQLCmd + ' AND '

END

ELSE

BEGIN

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS

WHILE (@LoopCount < @MaxCount)

BEGIN

SELECT TOP (1) @LoopCount=column_id, @SQLCmd=@SQLCmd + @TableName + '.' + column_name + ' = C.' + column_name FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

IF (@LoopCount < @MaxCount)

SET @SQLCmd=@SQLCmd + ' AND '

END

SELECT @SQLCmd=@SQLCmd + ' AND sys.fn_cdc_map_lsn_to_time(C.__$start_lsn) = ''' + CONVERT(VARCHAR(100),sys.fn_cdc_map_lsn_to_time(@LSN),121) + ''' AND CONVERT(INT,C.__$seqval) = ' + CONVERT(VARCHAR(100),CONVERT(INT,@SeqVal)) -- #new

END

END -- END OF DELETE

ELSE IF (@Operation = 2) -- INSERT

BEGIN

IF (@action = 'UNDO')

BEGIN

-- we need to create a DELETE statement

SELECT @SQLCmd = 'DELETE FROM ' + @FullTableName + ' FROM ' + @dbname + '.' + @CDC_Table + ' C WHERE '

IF (SELECT COUNT(*) FROM #CDC_TABLE_COLUMNS WHERE [is_pk]=1) >0

BEGIN

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS WHERE [is_pk]=1 --# --new

WHILE (@LoopCount < @MaxCount)

BEGIN

IF (@LoopCount < @MaxCount AND (SELECT TOP (1) [is_pk] FROM #CDC_TABLE_COLUMNS WHERE column_id = @LoopCount)=1) --#new

SET @SQLCmd=@SQLCmd + ' AND '

END

ELSE

BEGIN

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS

WHILE (@LoopCount < @MaxCount)

BEGIN

SELECT TOP (1) @LoopCount=column_id, @SQLCmd=@SQLCmd + @TableName + '.' + column_name + ' = C.' + column_name FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

IF (@LoopCount < @MaxCount)

SET @SQLCmd=@SQLCmd + ' AND '

END

ELSE IF (@action ='REDO')

BEGIN

-- we need to create an INSERT Statement

SELECT @SQLCmd = 'INSERT INTO ' + @FullTableName + ' ('

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS

WHILE (@LoopCount < @MaxCount)

BEGIN

SELECT TOP (1) @LoopCount=column_id, @SQLCmd=@SQLCmd + column_name FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

IF (@LoopCount < @MaxCount)

SET @SQLCmd=@SQLCmd +','

END

SET @SQLCmd=@SQLCmd +') SELECT '

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS

WHILE (@LoopCount < @MaxCount)

BEGIN

SELECT TOP (1) @LoopCount=column_id, @SQLCmd=@SQLCmd + column_name FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

IF (@LoopCount < @MaxCount) --#-- new

SET @SQLCmd=@SQLCmd +','

END

SELECT @SQLCmd=@SQLCmd + ' FROM ' + db_name() + '.' + @CDC_Table + ' WHERE sys.fn_cdc_map_lsn_to_time(__$start_lsn) = ''' + CONVERT(VARCHAR(100),sys.fn_cdc_map_lsn_to_time(@LSN),121) + ''''

END

END -- END OF INSERT

ELSE IF (@Operation = 4) -- UPDATE

BEGIN

IF (@action = 'UNDO')

BEGIN

-- We need to create a reverse UPDATE statement, obtain previous values from operation 3 transactions

SELECT @SQLCmd = 'UPDATE ' + @FullTableName + ' SET '

-- lets check which columns have changed

SELECT @LoopCount=(MIN(column_id)-1), @ColCount=0, @MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS --# --new

WHILE (@LoopCount < @MaxCount)

BEGIN

-- if result is one then the column has been changed

IF (SELECT TOP (1) (@UpdateMask/POWER(2,(column_ordinal-1))) % 2 FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id ) =1

BEGIN

IF (@ColCount > 0) -- add a comma if this is not the first set value

SET @SQLCmd=@SQLCmd + ','

SELECT TOP (1) @LoopCount=column_id, @SQLCmd=@SQLCmd + @TableName + '.' + column_name + ' = P.' + column_name FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

SET @ColCount=@ColCount+1

END

ELSE

SELECT TOP (1) @LoopCount=column_id FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

END

SELECT @SQLCmd=@SQLCmd + ' FROM ' + db_name() + '.' + @CDC_Table + ' C INNER JOIN '+ db_name() + '.' + @CDC_Table + ' P ON C.__$start_lsn=P.__$start_lsn AND C.__$seqval=P.__$seqval WHERE '

-- lets check to see if this table has a PK

IF (SELECT COUNT(*) FROM #CDC_TABLE_COLUMNS WHERE [is_pk]=1) >0

BEGIN

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS WHERE [is_pk]=1 --# -- new

WHILE (@LoopCount < @MaxCount) --# --new

BEGIN

IF (@LoopCount < @MaxCount AND (SELECT TOP (1) [is_pk] FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id)=1) --# -- new

SET @SQLCmd=@SQLCmd + ' AND '

END

ELSE

BEGIN

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS --# -- new

WHILE (@LoopCount < @MaxCount)

BEGIN

SELECT TOP (1) @LoopCount=column_id, @SQLCmd=@SQLCmd + @TableName + '.' + column_name + ' = C.' + column_name FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

IF (@LoopCount < @MaxCount)

SET @SQLCmd=@SQLCmd + ' AND '

END

SELECT @SQLCmd=@SQLCmd + ' AND P.__$operation=3 AND C.__$operation=4 AND sys.fn_cdc_map_lsn_to_time(C.__$start_lsn) = ''' + CONVERT(VARCHAR(100),sys.fn_cdc_map_lsn_to_time(@LSN),121) + ''' AND CONVERT(INT,C.__$seqval) = ' + CONVERT(VARCHAR(100),CONVERT(INT,@SeqVal)) -- #new

END

ELSE IF (@action = 'REDO')

BEGIN

-- we need an update statement

-- We need to create a reverse UPDATE statement, obtain previous values from operation 3 transactions

SELECT @SQLCmd = 'UPDATE ' + @FullTableName + ' SET '

-- lets check which columns have changed

SELECT @LoopCount=(MIN(column_id)-1), @ColCount=0, @MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS --# --new

WHILE (@LoopCount < @MaxCount)

BEGIN

-- if result is one then the column has been changed

IF (SELECT TOP (1) (@UpdateMask/POWER(2,(column_ordinal-1))) % 2 FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id ) =1

BEGIN

IF (@ColCount > 0) -- add a comma if this is not the first set value

SET @SQLCmd=@SQLCmd + ','

SELECT TOP (1) @LoopCount=column_id, @SQLCmd=@SQLCmd + @TableName + '.' + column_name + ' = P.' + column_name FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

SET @ColCount=@ColCount+1

END

ELSE

SELECT TOP (1) @LoopCount=column_id FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

END

SELECT @SQLCmd=@SQLCmd + ' FROM ' + db_name() + '.' + @CDC_Table + ' C INNER JOIN '+ db_name() + '.' + @CDC_Table + ' P ON C.__$start_lsn=P.__$start_lsn AND C.__$seqval=P.__$seqval WHERE '

-- lets check to see if this table has a PK

IF (SELECT COUNT(*) FROM #CDC_TABLE_COLUMNS WHERE [is_pk]=1) >0

BEGIN

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS WHERE [is_pk]=1 --# -- new

WHILE (@LoopCount < @MaxCount) --# --new

BEGIN

IF (@LoopCount < @MaxCount AND (SELECT TOP (1) [is_pk] FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id)=1) --# -- new

SET @SQLCmd=@SQLCmd + ' AND '

END

ELSE

BEGIN

SELECT @LoopCount=(MIN(column_id)-1),@MaxCount=MAX(column_id) FROM #CDC_TABLE_COLUMNS --# -- new

WHILE (@LoopCount < @MaxCount)

BEGIN

SELECT TOP (1) @LoopCount=column_id, @SQLCmd=@SQLCmd + @TableName + '.' + column_name + ' = C.' + column_name FROM #CDC_TABLE_COLUMNS WHERE column_id > @LoopCount ORDER BY column_id --# -- new

IF (@LoopCount < @MaxCount)

SET @SQLCmd=@SQLCmd + ' AND '

END

SELECT @SQLCmd=@SQLCmd + ' AND P.__$operation=4 AND C.__$operation=3 AND sys.fn_cdc_map_lsn_to_time(C.__$start_lsn) = ''' + CONVERT(VARCHAR(100),sys.fn_cdc_map_lsn_to_time(@LSN),121) + ''' AND CONVERT(INT,C.__$seqval) = ' + CONVERT(VARCHAR(100),CONVERT(INT,@SeqVal)) -- #new

END

END -- END OF UPDATE

INSERT INTO #CDC_UNDO_COMMANDS SELECT @LSN,@SeqVal,@FullTableName, @SQLCmd

DELETE TOP (1) FROM #CDC_TABLE_TRANSACTIONS

END

-- Lets Cleanup

DELETE FROM #CDC_TABLE_COLUMNS

-- lets remove the table we have aready processed

DELETE TOP (1) FROM #CDC_DEPLOY_ROLLBACK

END

IF (@action = 'UNDO')

SELECT UndoCmd FROM #CDC_UNDO_COMMANDS ORDER BY LSN DESC ,SeqVal ASC

ELSE -- LETS REDO

SELECT UndoCmd AS 'RedoCmd' FROM #CDC_UNDO_COMMANDS ORDER BY LSN ASC ,SeqVal ASC

-- Lets Cleanup

DROP TABLE #CDC_DEPLOY_ROLLBACK

DROP TABLE #CDC_TABLE_TRANSACTIONS

DROP TABLE #CDC_TABLE_COLUMNS

DROP TABLE #CDC_UNDO_COMMANDS

-- ==========================================================================================

--========

END TRY

--=========

--========

BEGIN CATCH

--===========

DECLARE @errmsg NVARCHAR(4000)

SET @errmsg = LEFT('Error ' + Cast(ERROR_NUMBER() AS VARCHAR) + ' ' + ISNULL(ERROR_MESSAGE(),''),4000);

RAISERROR (@errmsg, 16, 1);

--========

END CATCH

--========

How can I tell if SQL CDC is working?

2012-11-20T10:37:00.000+00:00

A good DMV to query is sys.dm_cdc_log_scan_sessions . It will show you what is currently being processed by CDC.

SELECT
session_id
, start_time
, end_time
, duration
, scan_phase
, error_count
, start_lsn
, current_lsn
, end_lsn
, tran_count
, last_commit_lsn
, last_commit_time
, log_record_count
, schema_change_count
, command_count
, first_begin_cdc_lsn
, last_commit_cdc_lsn
, last_commit_cdc_time
, latency
, empty_scan_count
, failed_sessions_count
FROM
sys.dm_cdc_log_scan_sessions
WHERE
Session_ID = 0
OR Session_ID = (
SELECT MAX(Session_ID)
FROM sys.dm_cdc_log_scan_sessions
)

Note: Session Zero is always the aggregate, it summarises all the changes captured since the CDC log reader agent was started.

SQL: Return Deadlock information

2012-11-07T14:38:00.001+00:00

I thought I would post this here so I won't forget it!

I came across a great article on SQL Central (see link below) on how to return deadlock graphs after the event.

This can be a real life saver as chances are you won't have a trace running when one occurs .

SELECT
XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS DeadlockGraph
FROM
( SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE
XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

(http://www.sqlservercentral.com/articles/deadlock/65658/)

SQL: View missing Indexes using SQL Sever DMVs

2012-10-18T15:24:00.004+01:00

Handy query to detect missing indexes, uses the SQL DMVs;

SELECT TOP 20
S.text AS [SQL]
, P.cacheobjtype
, P.objtype
, DB_NAME(S.dbid)AS [DatabaseName]
, P.usecounts AS [Plan usage]
, Q.query_plan
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text(P.plan_handle) S
CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) Q
WHERE
CAST(Q.query_plan AS NVARCHAR(MAX))LIKE '%<MissingIndexes>%'
ORDER BY P.usecounts DESC

SQL DB Restore History

2012-10-18T11:25:00.000+01:00

You can use the command below to check out backup and restore history, very useful for audit purposes;

SELECT
destination_database_name AS DBRestored,
restore_date AS RestoreDate,
BS.database_name AS SourceDB,
physical_name AS SourceFile ,
backup_start_date AS BackupDate
FROM msdb.dbo.RestoreHistory RH
JOIN msdb.dbo.BackupSet BS
ON RH.backup_set_id = BS.backup_set_id
JOIN msdb.dbo.BackupFile BF
ON BF.backup_set_id = BS.backup_set_id
AND BF.File_type ='D'
ORDER BY RestoreDate DESC

The msdb.dbo.BackupSet table contains a whole host of useful information about the backups including:

Backup start and end times
Source Server
Backup size

SQL :Paging your Results

2012-09-24T17:09:00.000+01:00

I always find this useful for paging result sets from SQL server;

DECLARE @RowStart INT

,@RowEND INT

SET @RowStart=100

SET @RowEnd = 300

;WITH MyTableSubSet AS

(

SELECT *,

ROW_NUMBER() OVER (ORDER BY Name) AS RowNumber

FROM MyTable O

)

SELECT *

FROM MyTableSubSet

WHERE RowNumber BETWEEN @RowStart AND @RowEnd

Determine the size of data stored in a Column

2012-08-10T16:45:00.001+01:00

To return the actual size of data stored within a column is actually really simple.

All you need is a quick call to the DATALENGTH(<Column Name>) function. This will return the size of the given column in bytes

Example

SELECT DATALENGTH(MyColumn) AS SizeInBytes FROM My.Table

SQL Change Data Capture - Automatically generate DML UNDO/REDO Scripts (Part 1)

2011-11-17T16:53:00.000+00:00

What is CDC

With SQL Server 2008 Enterprise edition we can make use of the SQL feature known as “Change Data Capture” (CDC).

CDC will audit any DML changes made on specified tables. You can then process these Audit tables to identify changes. CDC works behind the scenes and is easy to setup.

How to we configure CDC

You can use the SQL statement below to enable CDC in a database and use the resulting output to add tables to audit.

USE <Database>

IF (SELECT is_cdc_enabled FROM sys.databases WHERE name=db_name())=0

exec sys.sp_cdc_enable_db

-- Run the output from the query below to add tables to CDC, apply to;

-- all tables or select specific tables based on your requirements

SELECT 'exec sys.sp_cdc_enable_table @source_schema = ''' + SCHEMA_NAME([Schema_id]) + ''', @source_name = '''+ name +''',@role_name = ''CDCRole'',@supports_net_changes = 0, @capture_instance='''+ name +'_DEPLOY''' AS [Command] FROM sys.tables WHERE SCHEMA_NAME([Schema_id]) != 'cdc' AND name != 'systranschemas' ORDER BY [name]

Unlike traditional auditing processes there are no triggers involved; the CDC process will read the transaction log and pick up the changes it’s interested in. All of the CDC objects are kept neatly inside its own schema, imaginatively called ‘cdc’.

CDC by default keeps 2 days worth of audit log history, you can increase this but it is not recommended as you don’t want these audit tables becoming too large. However there is nothing to stop us from archiving the historic logs to separate history tables, we can then store as much historical information as we like.

When you enable CDC it will create two SQL Agent jobs;

cdc.<DB name>_Capture - Starts the CDC process for the database, similar to a replication log reader process

cdc.<DB Name>_Cleanup – Deletes transactions older than 2 days from the Audit table

These jobs are scheduled to run automatically, so you won’t have to touch them. I have included them here for completeness.

Another thing worth mentioning is that CDC allows you to Audit the same table twice. It will create a new Audit table; the key benefit from our point of view is, if a requirement arises where a table needs to be audited, we can do so without affecting our UNDO/REDO process.

We have configured CDC now what do we do

Now CDC is configured and running you will notice a table in the cdc schema for each table you have enabled, it will be called <TableName>_DEPLOY_CT (“DEPLOY” is a unique identifier I use for my procedure)

The table will contain all the columns from the original table at the time of enabling CDC. It also contains a few additional columns;

__$start_lsn - The Starting Logical Sequence Number

__$end_lsn – The ending Logical Sequence Number

__$seqval – The Order in which the DML occurred within the start and end LSN

__$operation – The Action Performed

1 - Delete
2 – Insert
3 - Update (Before Value)
4 – Update (After Value)

__$update_mask – A bit mask to indicate which of the columns have been updated. You can use the following SQL to identify which column (1 means the column was updated);

SELECT (__$update_mask /POWER(2,( column_ordinal-1))) % 2

By processing these tables we can open up a whole host of possiblites for example; you can recreate or even undo transactions that have occurred.

The Stored Procedure

I have been working on automatic rollback processes for deployments, so I have done a lot of work in this field. I have managed to convert my previous work from a Powershell implementation into a T-SQL stored procedure (see Part 2 for source).

The procedure when called will automatically generate an UNDO or REDO script based on the parameters passed in. I will explain how to use it. There are a number of optional parameters you can pass to it;

@action – The type of script to generate , accepted values are ‘UNDO’ and ‘REDO’ (default - REDO)

@datefrom – Date time, date to start looking for transactions (default – Current date & time – 1 minute)

@dateto – Date time, date to stop looking for transactions (defult – Current Date & Time)

@rewind – Time in minutes to go back to look for transactions, this is used when @dateto is not specified. (default - 1 minute)

@dbname – database you wish to apply the transactions to (default – Current Database)

Example output from procedure;

EXEC dbo.up_GenerateCDCScript @rewind=2000 ,@action='UNDO'

Schema Change Consideration

When CDC is in use it will only detect changes to columns that existed when the CDC was enabled. If you need to make schema changes you will need to disable and enable CDC for that table after the schema has been updated.

However once you remove CDC on a table the Audit table will be dropped automatically, meaning you could lose some updates if a change is made whilst CDC is removed. To avoid this you could suspend writes and copy the current contents of the Audit log table into a temporary table or the historic data tables.

FIX: Changes made to publication are not being picked up

2011-10-17T16:41:00.002+01:00

The Problem

You add a new Column/Table to an already existing publication via T-SQL. The script works fine, but replication doesn't even acknowledge there has been a change.

This is a common issue that would have affected us at all some point.

The Fix

Most people might take the rather drastic step of re-initialising the whole publication just to push out the small change.

Rather then do that, Try running the following instead

EXEC sp_refreshsubscriptions @publication = '<Publication Name>'

This will update the publication and inform any subscribers that a change has occurred. You can then run the snapshot and it will generate the changes for the updated articles only.

Another alternative is to make the replication changes through the GUI which will automatically call the procedure mentioned above.

Replication Tip: Subscription Streams offer instant speed boost

2011-10-10T17:18:00.000+01:00

Subscription Streams allow the use of multiple distribution agents for your subscription. Rather than using the one process you can use up to 64 (I wouldn’t recommend using 64 processes, but I think 8 is a good number to start with)

Subscription Streams are mainly used where high latency links exist between your publisher and subscribers, but or course you can use them regardless.

What it does is split the transactions between the ‘X’ distribution processes, which means (at the expense of a little CPU overhead) your transactions reach the publishers faster as they will be run in parallel. The transactions are then applied in the order they were applied at the publisher (for obvious reasons).

Improvements of 400% have been reported in some cases, so it’s something you should seriously consider using for your publications.

How to I use Subscription Streams?

Open up the distribution agent job for the publication you wish to use Subscription Streams against. Modify the second step, which should be “Run Agent” and add the following option to the end of the list.

-SubscriptionStreams <number of processes>

Once you have saved the changes simply restart the job. If you have any problems or decide against Subscription Streams, just remove the option from the job and restart.

Columnstore indexes in Denali

2011-10-04T16:42:00.000+01:00

What is a Columnstore index?

Columnar or “Columnstore” indexes as they are called in SQL Server Denali, are basically non-clustered indexes which store the indexed data by column rather than row (traditional B-Tree index).

What are the benefits of using them?

“Columnstore” indexes can yield very high compression ratios, due to the fact that all the data stored at the index page level is grouped by column therefore very similar (data types and repeating values etc.) and tightly packed at the page level.

I/O is reduced as the required pages needed to scan are fewer, and each column can be accessed independently meaning you get your results faster.

Aggregations are faster due to the fact that the pages required to scan the table are reduced and the use of batch-mode processing.

How do I start using them?

The SQL syntax used to create a ColumnStore index will look very similar to you. It’s the same T-SQL you would use to create a non-clustered index with one exception, see if you can spot it (Hint: It's somewhere between “NONCLUSTERED” and “INDEX”).

CREATE NONCLUSTERED COLUMNSTORE INDEX <Index name> ON <Table name> (<Column name>, <Column Name 2>, …)

Hold you horses, read this first

One thing to bear in mind is that once you have created a “Columnstore” index against your table you will not be able to perform DML operations against it. If you wish to DELETE/UPDATE/INSERT data you must first disable/drop the index and rebuild it once you have made your changes.

You may think this is crazy, but “Columnstore” indexes are not designed for tradition OLTP systems. They should only be used in Data warehousing applications where modifications are normally made as part of a scheduled ETL process. When you start using “Columnstore” indexes you will need to update your ETL processes to ensure they perform these required pre and post steps.

Thinking of upgrading to SQL 2008 R2 or Denali soon?

2011-10-04T11:15:00.003+01:00

If you thinking of upgrading your SQL servers, you may be interesting in the following counter;

“SQL Server: Deprecated Features”

This Perfmon counter was first introduced in SQL 2008. What it does is flag any calls to deprecated features your database is currently using.

Database Transaction Acid Rules

2011-07-29T11:27:00.001+01:00

All Database transactions follow the ACID rule;

Atomicity – Committed changes made by a transaction are visible to users once all the SQL statements in the transaction are successful.

Consistency – Transactions (successful or not) must leave the database in a consistent state (i.e. if one statement fails the whole transaction will be rolled back).

Isolation – Transactions cannot affect other transactions. Database Locking strategies are used to enforce this.

Durability – Transactions are written to disk once they are committed, this ensures that changes persist even after crashes. Backups of the database and transaction logs gives us durability.

Replication - ReadBatchSize saves the day

2011-06-28T10:03:00.000+01:00

The Problem

Recently we had an issue with one of our larger transactional publications. In a nutshell this publication pushes out a 4 GB transaction each week to multiple subscribers. This data is loaded via a user controlled process, due to issues three sets of data needed to be loaded at the same time. The loads were kicked off as separate batches, they applied succesfully to the publisher database but replication out to the subscribers was screwed.

The problem was the log reader agent was timing out and all the while the transaction log had grown to ~1.3 TB and couldn't be cleared down due to the replication markers. It was a mess and was looking like we would have to reinitialise the subscriptions.

The Solution

I had a look and suggested changing ReadBatchSize in the agent profile for the log reader agent to 1 (the default is 500).

Setting this value to 1 will mean the log reader will only read one transaction at a time. We had three rathar large transactions to process and by using the default setting the log reader would have tried to process them in one go which caused the time outs.

SQL Mirror Automatic Failover: Reducing sensitivity

2011-06-24T15:10:00.000+01:00

We can tweak the auto failover sensitivity for SQL Mirroring by using the commands below;

ALTER DATABASE <Database> SET PARTNER TIMEOUT <Time out in Seconds>

The default timeout is 10 seconds, which can be bit too sensitive.

If we increase this to a few minutes we can reduce false failovers.

You can return the current timeout settings by using the following query

SELECT mirroring_connection_timeout FROM sys.database_mirroring

SQL Azure better than on-site SQL?

2011-06-20T16:54:00.001+01:00

So is SQL Azure really a viable alternative to on-site SQL installs in a word “No”. SQL Azure is still relatively new and I’m sure Microsoft will remove limitations with the service as it matures. I’m not saying that SQL Azure is a terrible way to go but based on my current requirements, it’s not a good fit.

There are some key limitations that really stop me from fully embracing SQL Azure;

No T-SQL Backups

The ability to be in control of when and where backups are taken is something a DBA expects and rightly so. With SQL Azure this ability is taken away from us. Microsoft backs up your databases, but you have no access to these files or control the time they run.

If you want to take your own backup you effectively copy your database to another SQL Azure server, which means you have a LIVE copy of your database and assumingly you have to pay for the privilege of having this extra database in the cloud, which can get costly, especially if you want to maintain several copies to allow you to have multiple restore points.

No T-SQL Restores

Restoring from your backup database will require Data Migration packages to be developed to restore the data and SQL objects, the complexity of said packages entirely depends on the type of restore you would like to perform. (I’m sure you will have some fun with FK constraints along the way). Of course you could just point your web clients to the backup database, but you will need to update all your connection strings to point to this database presumably, which could be a whole world of hurt if this isn’t managed correctly.

No Backups or Restores really makes getting your database out of Azure tricky, so it really ties you into the service.

Every table must have a Clustered Index

I know this is good design practice, but in reality there may be occurrences when a Clustered index does not exist or in the case of Third party databases you may have no control over the schema.

Have to open 1433 for outbound traffic

More a concern for the network guys, but most corporate networks configures their firewall to block the default ports.

SQL Authentication only

Understandable I suppose as Azure isn’t going to hook into Active directory.

Cost

Based on some of the pricing information, I’m not sure if it’s actually good value for money, when you compare with Amazon EC2 or other cloud providers.

No SQL Agent or SSIS

SQL Agent and SSIS are not part of Azure, so you will need an on-site SQL box to run your jobs and packages against your cloud database.

Replication, Mirroring and Log shipping

These features are not supported by SQL Azure.

Conclusion

If you really want SQL in the “Cloud”, the better option may be installing SQL on a highly resilient server running in the Cloud, and if you’re seriously considering SQL Azure (which has such a limited feature set) you may be better off installing SQL Express on that cloud server?

Chrome OS: is it any good?

2011-06-14T16:41:00.000+01:00

I have been lucky enough to be given an opportunity to test Google’s Chrome OS ahead of its August release date.

For those that don’t know what Chrome OS is, in a nutshell it’s a Linux based operating system tailored specifically for internet based usage. It is designed to be simple, quick and encourages its users to start living in the clouds.

Chrome OS is not be confused with Android, Google’s other Linux based offering;

Chrome OS = Netbook’s
Android = Smartphone’s and Tablets

The Hardware

I’d thought I would mention the hardware, even though this particular model will never be released and is technically a demo unit. The model in question is referred to as the CR-48 (which I think is the name of the robotic bounty hunter in Star Wars?). It has a nice matt black finish and kind of reminds me of an old school “Mac book”.

The keyboard is full size which makes typing very easy and the spaces between the keys helps eliminate novice typing errors. You will notice from the picture below that the function buttons (F1-F12) have been replaced with more Web/Chrome centric actions (Back, Forward, Refresh, Fullscreen mode, Switch Windows, Brightness and sound controls) and one welcome edition is the dedicated search button which replaces the rarely used Caps lock.

The screen is bright and clear as expected. The inbuilt speakers are fine also.

The track pad is essentially one giant button and takes some getting use to;

Left click – press down with one finger
Right click – press down simultaneously with two fingers
Mouse Movement - same as a regular track pad, just move your finger around to move the mouse
Scroll – Use two fingers and pull in the direction you wish to scroll
Drag and Drop – Left click on the object and with a second finger move the object

You can enable “tap-to-click” which makes things a lot easier, as I found pushing down the entire track pad quite difficult at times (might be an issue with the demo unit). I also whacked the mouse sensitivity up to its highest setting.

The device uses an SSD, so it’s silent when operating, so quiet that if you covered the screen you wouldn’t believe this thing was on.

Ports on the device include;

USB 2.0 ports
VGA Out

The OS

Man it’s quick to start up, from a full start-up to actually browsing the web; it is definitely sub 10 seconds. You only really need to shutdown when an OS update is applied.

When you shut the lid of the chrome book its goes into standby mode, when you open the lid it’s ready to use instantly and I mean instantly! Understandably I really like this feature, so no more waiting 10 minutes for your laptop to come to life.

If you are familiar with the Chrome browser then you are familiar with Chrome OS, it is very easy to use and there is a whole wealth of apps available in the “Chrome Store”, here are some of my top picks;

App launcher (I think this should be installed by default) - it allows you to launch your apps directly from the toolbar
Chrome to Phone – sends links from your browser to your android phone
Scratchpad (installed by default) – You can make notes and sync them automatically to your Google docs to access anywhere
Angry Birds – Kill some time and some green pigs
Yet Another Google Bookmarks Extension – Access your Google bookmarks from the toolbar

And of course the usual Google app suite (Gmail, Maps, Docs, and Calendar etc)

It would be nice for Chrome OS to support locally installed applications like “The GIMP” and Eclipse for development, but I suppose that’s goes against Google’s vision for the OS.

In Conclusion

After having the CR-48 for a week I think Chrome OS is great and I haven’t needed to power on my own laptop once. There are still a few bugs Google need to iron out, but with two months until the official release I’m sure they will nail it!

FIX: Powershell SQL Restore timeout

2011-05-31T12:54:00.000+01:00

The problem

Whilst restoring a rather large 40GB database using Powershell, I was greeted with the following message after a period of time;

Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server ‘XXXXXXX’. " At C:\temp\RestoreSQLDB.ps1:135 char:31

+ $MyRestore.SqlRestore <<<< ($MyConnection)

+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : DotNetMethodException

As I have used this script countless times and knew the backup source to be sound, it lead me to one conclusion – Time Out!

The fix

To get around this issue you need to set the parameter StatementTimeout to a higher value, the default is 600 (10 minute timeout).

StatementTimeout is part of ConnectionContext which is part of Microsoft.SqlServer.Management.Smo.Server (i.e your SQL connection object). The first line defines the connection and is shown here for completeness only; it’s the second line that is the key here. Just replace $MyConnection with your chosen variable name and set the value to your desired timeout setting.

# Lets create our connection to the box

$MyConnection = New-Object("Microsoft.SqlServer.Management.Smo.Server") $SQLServerName

$MyConnection.ConnectionContext.StatementTimeout = 1200 # set timeout to 20 minutes