How to Set Up a SQL Server Stress Test Environment in 8 Steps: Lessons From the Field
http://www.sqlservercentral.com/articles/Miscellaneous/2634/
Stress Testing SQL Server
http://www.sqlservercentral.com/articles/Miscellaneous/2634/
Which is Faster: SAN or Directly-Attached Storage?
http://www.sqlteam.com/article/which-is-faster-san-or-directly-attached-storage
Friday, February 26, 2010
Description of the SQL Server Performance Analysis Utilities Read80Trace and OSTRESS
Description of the SQL Server Performance Analysis Utilities Read80Trace and OSTRESS
http://support.microsoft.com/kb/887057
Description of the Replay Markup Language (RML) Utilities for SQL Server
http://support.microsoft.com/kb/944837
PSSDIAG data collection utility
http://support.microsoft.com/kb/830232
http://support.microsoft.com/kb/887057
Description of the Replay Markup Language (RML) Utilities for SQL Server
http://support.microsoft.com/kb/944837
PSSDIAG data collection utility
http://support.microsoft.com/kb/830232
SAN And SQL Server
SAN Performance Tuning with SQLIO
http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO
data storage caculator
http://www.wmarow.com/strcalc/
SAN Design and Deployment
http://www.vmware.com/pdf/vi3_san_design_deploy.pdf
How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem
http://support.microsoft.com/kb/231619
http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO
data storage caculator
http://www.wmarow.com/strcalc/
SAN Design and Deployment
http://www.vmware.com/pdf/vi3_san_design_deploy.pdf
How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem
http://support.microsoft.com/kb/231619
Wednesday, February 24, 2010
DB Engine
select *
from sys.filegroups
select *
from sys.destination_data_spaces
select *
from sys.data_spaces
select *
from sys.master_files
select *
from sys.allocation_units
select *
from sys.dm_db_session_space_usage
select *
from sys.dm_db_file_space_usage
select *
from sys.databases
select *
from sys.dm_io_cluster_shared_drives
select *
from sys.dm_io_pending_io_requests
select *
from sys.database_files
select *
from sys.database_recovery_status
--- sql 2008 --
-- select *
-- from sys.change_tracking_databases
--select *
--from sys.dm_filestream_file_io_requests
--select *
--from sys.change_tracking_files
--select *
--from sys.dm_filestream_file_io_handles
from sys.filegroups
select *
from sys.destination_data_spaces
select *
from sys.data_spaces
select *
from sys.master_files
select *
from sys.allocation_units
select *
from sys.dm_db_session_space_usage
select *
from sys.dm_db_file_space_usage
select *
from sys.databases
select *
from sys.dm_io_cluster_shared_drives
select *
from sys.dm_io_pending_io_requests
select *
from sys.database_files
select *
from sys.database_recovery_status
--- sql 2008 --
-- select *
-- from sys.change_tracking_databases
--select *
--from sys.dm_filestream_file_io_requests
--select *
--from sys.change_tracking_files
--select *
--from sys.dm_filestream_file_io_handles
Tuesday, February 23, 2010
ExternalMailQueue
select is_broker_enabled from sys.databases where name='msdb'
alter database msdb set enable_broker
alter queue ExternalMailQueue with status = on
to Fix The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'The service queue "ExternalMailQueue" is currently disabled.'
Service Broker-TestDatabase
USE master
IF DB_ID('TestDb1') IS NOT NULL
DROP DATABASE TestDb1
CREATE DATABASE TestDb1
GO
-- enable service broker
ALTER DATABASE TestDb1 SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE TestDb1 SET TRUSTWORTHY ON
GO
USE TestDb1
GO
-- Drop existing service broker items
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataSender')
DROP SERVICE [//Audit/DataWriter]
IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'InitiatorAuditQueue')
DROP QUEUE InitiatorAuditQueue
IF EXISTS(SELECT * FROM sys.service_contracts WHERE NAME = '//Audit/Contract')
DROP SERVICE [//Audit/Contract]
IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
DROP MESSAGE TYPE [//Audit/Message]
GO
-- create a message that must be well formed
CREATE MESSAGE TYPE [//Audit/Message]
VALIDATION = WELL_FORMED_XML
-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
([//Audit/Message] SENT BY INITIATOR)
-- create the initiator queue
CREATE QUEUE dbo.InitiatorAuditQueue
-- create an initiator service that will send audit messages to target service
CREATE SERVICE [//Audit/DataSender]
AUTHORIZATION dbo
ON QUEUE dbo.InitiatorAuditQueue -- no contract means service can only be the initiator
GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
DROP TABLE dbo.AuditErrors
GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
ErrorProcedure NVARCHAR(126) NOT NULL,
ErrorLine INT NOT NULL,
ErrorNumber INT NOT NULL,
ErrorMessage NVARCHAR(4000) NOT NULL,
ErrorSeverity INT NOT NULL,
ErrorState INT NOT NULL,
AuditedData XML NOT NULL,
ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
GO
IF OBJECT_ID('dbo.usp_SendAuditData') IS NOT NULL
DROP PROCEDURE dbo.usp_SendAuditData
GO
-- stored procedure that sends the audit data to the be audited
CREATE PROCEDURE dbo.usp_SendAuditData
(
@AuditedData XML
)
AS
BEGIN
BEGIN TRY
DECLARE @dlgId UNIQUEIDENTIFIER, @dlgIdExists BIT
SELECT @dlgIdExists = 1
-- Check if our database already has a dialog id that was previously used
-- Why reusing conversation dialogs is a good this is explaind here
-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
-- very well
SELECT @dlgId = DialogId
FROM MasterAuditDatabase.dbo.AuditDialogs AD
WHERE AD.DbId = DB_ID()
IF @dlgId IS NULL
BEGIN
SELECT @dlgIdExists = 0
END
-- Begin the dialog, either with existing or new Id
BEGIN DIALOG @dlgId
FROM SERVICE [//Audit/DataSender]
TO SERVICE '//Audit/DataWriter',
-- this is a MasterAuditDatabase Service Broker Id (change it to yours)
'4F5FF2B8-73D2-4EFE-84D4-36904579F205'
ON CONTRACT [//Audit/Contract]
WITH ENCRYPTION = OFF;
-- add our db's dialog to AuditDialogs table if it doesn't exist yet
IF @dlgIdExists = 0
BEGIN
INSERT INTO MasterAuditDatabase.dbo.AuditDialogs(DbId, DialogId)
SELECT DB_ID(), @dlgId
END
--SELECT @AuditedData
-- Send our data to be audited
;SEND ON CONVERSATION @dlgId
MESSAGE TYPE [//Audit/Message] (@AuditedData)
END TRY
BEGIN CATCH
INSERT INTO AuditErrors (
ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage,
ErrorSeverity, ErrorState, AuditedData)
SELECT ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
ERROR_SEVERITY(), ERROR_STATE(), @AuditedData
END CATCH
END
GO
-- Create Sample Table
IF OBJECT_ID('Person') IS NOT NULL
DROP TABLE Person
GO
CREATE TABLE Person
(
ID INT PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
DateOfBirth SMALLDATETIME
)
-- Create Trigger that will audit data
GO
IF OBJECT_ID ('trgPersonAudit','TR') IS NOT NULL
DROP TRIGGER trgPersonAudit
GO
CREATE TRIGGER dbo.trgPersonAudit
ON Person
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @auditBody XML
DECLARE @DMLType CHAR(1)
-- after delete statement
IF NOT EXISTS (SELECT * FROM inserted)
BEGIN
SELECT @auditBody = (select * FROM deleted AS t FOR XML AUTO, ELEMENTS),
@DMLType = 'D'
END
-- after update or insert statement
ELSE
BEGIN
SELECT @auditBody = (select * FROM inserted AS t FOR XML AUTO, ELEMENTS)
-- after update statement
IF EXISTS (SELECT * FROM deleted)
SELECT @DMLType = 'U'
-- after insert statement
ELSE
SELECT @DMLType = 'I'
END
-- get table name dynamicaly but
-- for performance this should be changed to constant in every trigger like:
-- SELECT @tableName = 'Person'
DECLARE @tableName sysname
SELECT @tableName = tbl.name
FROM sys.tables tbl
JOIN sys.triggers trg ON tbl.[object_id] = trg.parent_id
WHERE trg.[object_id] = @@PROCID
SELECT @auditBody =
'
' + DB_NAME() + '
' + @tableName + '
' + SUSER_SNAME() + '
' + @DMLType + '
' + CAST(@auditBody AS NVARCHAR(MAX)) + '
-- Audit data asynchrounously
EXEC dbo.usp_SendAuditData @auditBody
GO
-- we want this trigger to fire last for each command so that if there are other triggers
-- that update the table they finish their job before auditing
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'delete'
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'insert'
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'update'
-- Run code for TestDb1 in each database you wish to enable audting in.
-- Run some samples to see if it works:
USE TestDb1
-- will be audited
INSERT INTO Person
SELECT 1, 'name 1', 'surname 1', '19761215' UNION ALL
SELECT 2, 'name 2', 'surname 2', '19780705' UNION ALL
SELECT 3, 'name 3', 'surname 3', '19660305' UNION ALL
SELECT 4, 'name 4', 'surname 4', '19660609' UNION ALL
SELECT 5, 'name 5', 'surname 5', '19990606' UNION ALL
SELECT 6, 'name 6', 'surname 6', '20010202' UNION ALL
SELECT 7, 'name 7', 'surname 7', '19440404' UNION ALL
SELECT 8, 'name 8', 'surname 8', '19630523' UNION ALL
SELECT 9, 'name 9', 'surname 9', '19860929'
-- will be audited
UPDATE Person
SET DateOfBirth = '19800217'
WHERE id = 5
-- won't be audited because of the rollback
BEGIN TRANSACTION
UPDATE Person
SET DateOfBirth = '19701215'
WHERE id < 6
ROLLBACK
GO
-- check if auditing succeeded
USE MasterAuditDatabase
GO
SELECT * FROM MasterAuditTable
ORDER BY ChangeDate DESC
SELECT * FROM AuditDialogs
IF DB_ID('TestDb1') IS NOT NULL
DROP DATABASE TestDb1
CREATE DATABASE TestDb1
GO
-- enable service broker
ALTER DATABASE TestDb1 SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE TestDb1 SET TRUSTWORTHY ON
GO
USE TestDb1
GO
-- Drop existing service broker items
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataSender')
DROP SERVICE [//Audit/DataWriter]
IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'InitiatorAuditQueue')
DROP QUEUE InitiatorAuditQueue
IF EXISTS(SELECT * FROM sys.service_contracts WHERE NAME = '//Audit/Contract')
DROP SERVICE [//Audit/Contract]
IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
DROP MESSAGE TYPE [//Audit/Message]
GO
-- create a message that must be well formed
CREATE MESSAGE TYPE [//Audit/Message]
VALIDATION = WELL_FORMED_XML
-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
([//Audit/Message] SENT BY INITIATOR)
-- create the initiator queue
CREATE QUEUE dbo.InitiatorAuditQueue
-- create an initiator service that will send audit messages to target service
CREATE SERVICE [//Audit/DataSender]
AUTHORIZATION dbo
ON QUEUE dbo.InitiatorAuditQueue -- no contract means service can only be the initiator
GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
DROP TABLE dbo.AuditErrors
GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
ErrorProcedure NVARCHAR(126) NOT NULL,
ErrorLine INT NOT NULL,
ErrorNumber INT NOT NULL,
ErrorMessage NVARCHAR(4000) NOT NULL,
ErrorSeverity INT NOT NULL,
ErrorState INT NOT NULL,
AuditedData XML NOT NULL,
ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
GO
IF OBJECT_ID('dbo.usp_SendAuditData') IS NOT NULL
DROP PROCEDURE dbo.usp_SendAuditData
GO
-- stored procedure that sends the audit data to the be audited
CREATE PROCEDURE dbo.usp_SendAuditData
(
@AuditedData XML
)
AS
BEGIN
BEGIN TRY
DECLARE @dlgId UNIQUEIDENTIFIER, @dlgIdExists BIT
SELECT @dlgIdExists = 1
-- Check if our database already has a dialog id that was previously used
-- Why reusing conversation dialogs is a good this is explaind here
-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
-- very well
SELECT @dlgId = DialogId
FROM MasterAuditDatabase.dbo.AuditDialogs AD
WHERE AD.DbId = DB_ID()
IF @dlgId IS NULL
BEGIN
SELECT @dlgIdExists = 0
END
-- Begin the dialog, either with existing or new Id
BEGIN DIALOG @dlgId
FROM SERVICE [//Audit/DataSender]
TO SERVICE '//Audit/DataWriter',
-- this is a MasterAuditDatabase Service Broker Id (change it to yours)
'4F5FF2B8-73D2-4EFE-84D4-36904579F205'
ON CONTRACT [//Audit/Contract]
WITH ENCRYPTION = OFF;
-- add our db's dialog to AuditDialogs table if it doesn't exist yet
IF @dlgIdExists = 0
BEGIN
INSERT INTO MasterAuditDatabase.dbo.AuditDialogs(DbId, DialogId)
SELECT DB_ID(), @dlgId
END
--SELECT @AuditedData
-- Send our data to be audited
;SEND ON CONVERSATION @dlgId
MESSAGE TYPE [//Audit/Message] (@AuditedData)
END TRY
BEGIN CATCH
INSERT INTO AuditErrors (
ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage,
ErrorSeverity, ErrorState, AuditedData)
SELECT ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
ERROR_SEVERITY(), ERROR_STATE(), @AuditedData
END CATCH
END
GO
-- Create Sample Table
IF OBJECT_ID('Person') IS NOT NULL
DROP TABLE Person
GO
CREATE TABLE Person
(
ID INT PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
DateOfBirth SMALLDATETIME
)
-- Create Trigger that will audit data
GO
IF OBJECT_ID ('trgPersonAudit','TR') IS NOT NULL
DROP TRIGGER trgPersonAudit
GO
CREATE TRIGGER dbo.trgPersonAudit
ON Person
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @auditBody XML
DECLARE @DMLType CHAR(1)
-- after delete statement
IF NOT EXISTS (SELECT * FROM inserted)
BEGIN
SELECT @auditBody = (select * FROM deleted AS t FOR XML AUTO, ELEMENTS),
@DMLType = 'D'
END
-- after update or insert statement
ELSE
BEGIN
SELECT @auditBody = (select * FROM inserted AS t FOR XML AUTO, ELEMENTS)
-- after update statement
IF EXISTS (SELECT * FROM deleted)
SELECT @DMLType = 'U'
-- after insert statement
ELSE
SELECT @DMLType = 'I'
END
-- get table name dynamicaly but
-- for performance this should be changed to constant in every trigger like:
-- SELECT @tableName = 'Person'
DECLARE @tableName sysname
SELECT @tableName = tbl.name
FROM sys.tables tbl
JOIN sys.triggers trg ON tbl.[object_id] = trg.parent_id
WHERE trg.[object_id] = @@PROCID
SELECT @auditBody =
'
-- Audit data asynchrounously
EXEC dbo.usp_SendAuditData @auditBody
GO
-- we want this trigger to fire last for each command so that if there are other triggers
-- that update the table they finish their job before auditing
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'delete'
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'insert'
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'update'
-- Run code for TestDb1 in each database you wish to enable audting in.
-- Run some samples to see if it works:
USE TestDb1
-- will be audited
INSERT INTO Person
SELECT 1, 'name 1', 'surname 1', '19761215' UNION ALL
SELECT 2, 'name 2', 'surname 2', '19780705' UNION ALL
SELECT 3, 'name 3', 'surname 3', '19660305' UNION ALL
SELECT 4, 'name 4', 'surname 4', '19660609' UNION ALL
SELECT 5, 'name 5', 'surname 5', '19990606' UNION ALL
SELECT 6, 'name 6', 'surname 6', '20010202' UNION ALL
SELECT 7, 'name 7', 'surname 7', '19440404' UNION ALL
SELECT 8, 'name 8', 'surname 8', '19630523' UNION ALL
SELECT 9, 'name 9', 'surname 9', '19860929'
-- will be audited
UPDATE Person
SET DateOfBirth = '19800217'
WHERE id = 5
-- won't be audited because of the rollback
BEGIN TRANSACTION
UPDATE Person
SET DateOfBirth = '19701215'
WHERE id < 6
ROLLBACK
GO
-- check if auditing succeeded
USE MasterAuditDatabase
GO
SELECT * FROM MasterAuditTable
ORDER BY ChangeDate DESC
SELECT * FROM AuditDialogs
Service Broker-MAster Audit Database
USE master
-- one audit database and table for all
IF DB_ID('MasterAuditDatabase') IS NOT NULL
DROP DATABASE MasterAuditDatabase
CREATE DATABASE MasterAuditDatabase
GO
-- enable service broker
ALTER DATABASE MasterAuditDatabase SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE MasterAuditDatabase SET TRUSTWORTHY ON
GO
USE MasterAuditDatabase
GO
-- get service broker guid for MasterAuditDatabase.
-- we must copy/paste this guid to the BEGIN DIALOG
-- in dbo.spSendAuditData stored procedure
-- it's E5E588AC-346C-445C-92C5-DADBDC5DC93C for my MasterAuditDatabase
SELECT service_broker_guid
FROM sys.databases
WHERE database_id = DB_ID()
GO
IF OBJECT_ID('dbo.MasterAuditTable') IS NOT NULL
DROP TABLE dbo.MasterAuditTable
GO
-- Master Audit Table
CREATE TABLE dbo.MasterAuditTable
(
Id BIGINT IDENTITY(1,1),
SourceDB sysname NOT NULL,
SourceTable sysname NOT NULL,
UserID NVARCHAR(500) NOT NULL,
-- D = Delete, I = Insert, U = Update
DMLType char(1) NOT NULL CHECK (DMLType IN ('D', 'U', 'I')),
ChangedData XML NOT NULL,
ChangeDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
GO
IF OBJECT_ID('dbo.AuditDialogs') IS NOT NULL
DROP TABLE dbo.AuditDialogs
GO
-- Table that will hold dialog id's for each database on the server
-- These dialogs will be reused. why this is a good thing is explained here:
-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
CREATE TABLE dbo.AuditDialogs
(
DbId INT NOT NULL,
DialogId UNIQUEIDENTIFIER NOT NULL
)
GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
DROP TABLE dbo.AuditErrors
GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
ErrorProcedure NVARCHAR(126) NOT NULL,
ErrorLine INT NOT NULL,
ErrorNumber INT NOT NULL,
ErrorMessage NVARCHAR(4000) NOT NULL,
ErrorSeverity INT NOT NULL,
ErrorState INT NOT NULL,
AuditedData XML NOT NULL,
ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
GO
IF OBJECT_ID('dbo.usp_WriteAuditData') IS NOT NULL
DROP PROCEDURE dbo.usp_WriteAuditData
GO
-- stored procedure that writes the audit data from the queue to the audit table
CREATE PROCEDURE dbo.usp_WriteAuditData
AS
BEGIN
DECLARE @msgBody XML
DECLARE @dlgId uniqueidentifier
WHILE(1=1)
BEGIN
BEGIN TRANSACTION
BEGIN TRY
-- insert messages into audit table one message at a time
;RECEIVE top(1)
@msgBody = message_body,
@dlgId = conversation_handle
FROM dbo.TargetAuditQueue
-- exit when the whole queue has been processed
IF @@ROWCOUNT = 0
BEGIN
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
BREAK;
END
DECLARE @SourceDB sysname, @SourceTable sysname,
@UserID NVARCHAR(500), @DMLType CHAR(1), @ChangedData XML
-- xml datatype and its capabilities rock
SELECT @SourceDB = T.c.query('/AuditMsg/SourceDb').value('.[1]', 'sysname'),
@SourceTable = T.c.query('/AuditMsg/SourceTable').value('.[1]', 'sysname'),
@UserID = T.c.query('/AuditMsg/UserId').value('.[1]', 'NVARCHAR(50)'),
@DMLType = T.c.query('/AuditMsg/DMLType').value('.[1]', 'CHAR(1)'),
@ChangedData = T.c.query('*')
FROM @msgBody.nodes('/AuditMsg/ChangedData') T(c)
INSERT INTO dbo.MasterAuditTable(SourceDB, SourceTable, UserID, DMLType, ChangedData)
SELECT @SourceDB, @SourceTable, @UserID, @DMLType, @ChangedData
-- No need to close the conversation because auditing never ends
-- you can end conversations if you want periodicaly with a scheduled job
-- END CONVERSATION @dlgId
IF @@TRANCOUNT > 0
BEGIN
COMMIT;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
-- insert error into the AuditErrors table
INSERT INTO AuditErrors (
ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage,
ErrorSeverity, ErrorState, AuditedData)
SELECT ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
ERROR_SEVERITY(), ERROR_STATE(), @msgBody
END CATCH;
END
END
GO
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataWriter')
DROP SERVICE [//Audit/DataWriter]
IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'TargetAuditQueue')
DROP QUEUE dbo.TargetAuditQueue
IF EXISTS(SELECT * FROM sys.service_contracts WHERE NAME = '//Audit/Contract')
DROP SERVICE [//Audit/Contract]
IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
DROP MESSAGE TYPE [//Audit/Message]
GO
-- create a message that must be well formed XML
CREATE MESSAGE TYPE [//Audit/Message]
VALIDATION = WELL_FORMED_XML
-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
([//Audit/Message] SENT BY INITIATOR)
-- create the queue to run the spWriteAuditData automaticaly when new messages arrive
-- execute it as dbo
CREATE QUEUE dbo.TargetAuditQueue
WITH STATUS=ON,
ACTIVATION (
PROCEDURE_NAME = usp_WriteAuditData, -- sproc to run when the queue receives a message
MAX_QUEUE_READERS = 50, -- max concurrently executing instances of sproc
EXECUTE AS 'dbo' );
-- create a target service that will accept inbound audit messages
-- set the owner to dbo
CREATE SERVICE [//Audit/DataWriter]
AUTHORIZATION dbo
ON QUEUE dbo.TargetAuditQueue ([//Audit/Contract])
-- one audit database and table for all
IF DB_ID('MasterAuditDatabase') IS NOT NULL
DROP DATABASE MasterAuditDatabase
CREATE DATABASE MasterAuditDatabase
GO
-- enable service broker
ALTER DATABASE MasterAuditDatabase SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE MasterAuditDatabase SET TRUSTWORTHY ON
GO
USE MasterAuditDatabase
GO
-- get service broker guid for MasterAuditDatabase.
-- we must copy/paste this guid to the BEGIN DIALOG
-- in dbo.spSendAuditData stored procedure
-- it's E5E588AC-346C-445C-92C5-DADBDC5DC93C for my MasterAuditDatabase
SELECT service_broker_guid
FROM sys.databases
WHERE database_id = DB_ID()
GO
IF OBJECT_ID('dbo.MasterAuditTable') IS NOT NULL
DROP TABLE dbo.MasterAuditTable
GO
-- Master Audit Table
CREATE TABLE dbo.MasterAuditTable
(
Id BIGINT IDENTITY(1,1),
SourceDB sysname NOT NULL,
SourceTable sysname NOT NULL,
UserID NVARCHAR(500) NOT NULL,
-- D = Delete, I = Insert, U = Update
DMLType char(1) NOT NULL CHECK (DMLType IN ('D', 'U', 'I')),
ChangedData XML NOT NULL,
ChangeDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
GO
IF OBJECT_ID('dbo.AuditDialogs') IS NOT NULL
DROP TABLE dbo.AuditDialogs
GO
-- Table that will hold dialog id's for each database on the server
-- These dialogs will be reused. why this is a good thing is explained here:
-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
CREATE TABLE dbo.AuditDialogs
(
DbId INT NOT NULL,
DialogId UNIQUEIDENTIFIER NOT NULL
)
GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
DROP TABLE dbo.AuditErrors
GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
ErrorProcedure NVARCHAR(126) NOT NULL,
ErrorLine INT NOT NULL,
ErrorNumber INT NOT NULL,
ErrorMessage NVARCHAR(4000) NOT NULL,
ErrorSeverity INT NOT NULL,
ErrorState INT NOT NULL,
AuditedData XML NOT NULL,
ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)
GO
IF OBJECT_ID('dbo.usp_WriteAuditData') IS NOT NULL
DROP PROCEDURE dbo.usp_WriteAuditData
GO
-- stored procedure that writes the audit data from the queue to the audit table
CREATE PROCEDURE dbo.usp_WriteAuditData
AS
BEGIN
DECLARE @msgBody XML
DECLARE @dlgId uniqueidentifier
WHILE(1=1)
BEGIN
BEGIN TRANSACTION
BEGIN TRY
-- insert messages into audit table one message at a time
;RECEIVE top(1)
@msgBody = message_body,
@dlgId = conversation_handle
FROM dbo.TargetAuditQueue
-- exit when the whole queue has been processed
IF @@ROWCOUNT = 0
BEGIN
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
BREAK;
END
DECLARE @SourceDB sysname, @SourceTable sysname,
@UserID NVARCHAR(500), @DMLType CHAR(1), @ChangedData XML
-- xml datatype and its capabilities rock
SELECT @SourceDB = T.c.query('/AuditMsg/SourceDb').value('.[1]', 'sysname'),
@SourceTable = T.c.query('/AuditMsg/SourceTable').value('.[1]', 'sysname'),
@UserID = T.c.query('/AuditMsg/UserId').value('.[1]', 'NVARCHAR(50)'),
@DMLType = T.c.query('/AuditMsg/DMLType').value('.[1]', 'CHAR(1)'),
@ChangedData = T.c.query('*')
FROM @msgBody.nodes('/AuditMsg/ChangedData') T(c)
INSERT INTO dbo.MasterAuditTable(SourceDB, SourceTable, UserID, DMLType, ChangedData)
SELECT @SourceDB, @SourceTable, @UserID, @DMLType, @ChangedData
-- No need to close the conversation because auditing never ends
-- you can end conversations if you want periodicaly with a scheduled job
-- END CONVERSATION @dlgId
IF @@TRANCOUNT > 0
BEGIN
COMMIT;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
-- insert error into the AuditErrors table
INSERT INTO AuditErrors (
ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage,
ErrorSeverity, ErrorState, AuditedData)
SELECT ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
ERROR_SEVERITY(), ERROR_STATE(), @msgBody
END CATCH;
END
END
GO
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataWriter')
DROP SERVICE [//Audit/DataWriter]
IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'TargetAuditQueue')
DROP QUEUE dbo.TargetAuditQueue
IF EXISTS(SELECT * FROM sys.service_contracts WHERE NAME = '//Audit/Contract')
DROP SERVICE [//Audit/Contract]
IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
DROP MESSAGE TYPE [//Audit/Message]
GO
-- create a message that must be well formed XML
CREATE MESSAGE TYPE [//Audit/Message]
VALIDATION = WELL_FORMED_XML
-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
([//Audit/Message] SENT BY INITIATOR)
-- create the queue to run the spWriteAuditData automaticaly when new messages arrive
-- execute it as dbo
CREATE QUEUE dbo.TargetAuditQueue
WITH STATUS=ON,
ACTIVATION (
PROCEDURE_NAME = usp_WriteAuditData, -- sproc to run when the queue receives a message
MAX_QUEUE_READERS = 50, -- max concurrently executing instances of sproc
EXECUTE AS 'dbo' );
-- create a target service that will accept inbound audit messages
-- set the owner to dbo
CREATE SERVICE [//Audit/DataWriter]
AUTHORIZATION dbo
ON QUEUE dbo.TargetAuditQueue ([//Audit/Contract])
Monday, February 22, 2010
SQL Server also has a few optional startup parameters that you can set to help troubleshoot your installation or make some minor performance optimizations.
· -c Shortens SQL Server's startup time by starting the instance independent of the Service Control Manager. Starting SQL Server independent of the Service Control Manager will keep if from running as a Windows service. I have not had the need to use this parameter so I can only guess that it could be useful for troubleshooting startup issues.
· -f This parameter starts an instance of SQL Server with the minimal configurations and enables the sp_configure allow updates option. This parameter is very useful if a configuration option is set that disables SQL Server. You can use this parameter to start SQL Server and change the inappropriate configuration setting back to the original.
· -g This parameter will specify the amount of virtual address space that SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. What does that mean? This memory allocation outside of the SQL Server memory pool is used for loading times such as extended stored procedures, dll files, OLE DB providers referenced by queries, and automation objects reference in Transact-SQL statements. The default size of this memory is 128 MB, which is usually fine for most SQL Server installations. Keep in mind that SQL Server installations under 2 GB (SQL Server 2000 Standard Edition) or 3 GB (Enterprise Edition) will only use 128 MB no matter what size you set with the -g parameter. You can however, use this parameter to optimize the memory on installations above the 2 GB and 3 GB cutoffs. Microsoft recommends that you do not use this parameter unless you see the following error in your error log. WARNING: Clearing procedure cache to free contiguous memory However, I have used this and seen others use this parameter for errors such as: OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of memory.
· -m This parameter may be one of the most used, especially if you are studying for the SQL Server tests. The -m parameter will start SQL Server in the single user mode and enable the sp_configure allow updates configuration option. Starting SQL Server in the single user mode does not issue a CHECKPOINT command. This parameter allows you work on your system databases if you encounter a problem with one of them. If you ever have to rebuild your master database you will be familiar with this parameter.
· -n This parameter turns off SQL Server logging its errors to the Windows application log. It is recommended that if you use this parameter you should also use the -e starup parameter, which you should be doing anyway. This is another parameter that I have not used and personally, I would not recommend turning off the Windows application logging.
· -s This parameter is used to start a named instance of SQL Server. Without the use of this parameter, SQL Server will try to start the default instance, which may or may not be present or the one you want to start.
· /Ttrace# Used to start a Trace Flag when you start SQL Server. This parameter is useful for general troubleshooting purposes.
· -x This parameter will disable the keeping of the CPU time and CACHE-HIT ratio statistics. While this parameter allows maximum performance, I do not use it as the benefits of the CACHE-HIT ratio outweigh the small performance gain this parameter buys you.
Most of the time, you will not have to use these parameters but they are nice to know for that rare instance that you have system database problems or when you need to start a Trace Flag.
Using the sp_MSforeachdb and sp_MSforeachtable stored procedures (SPs), you can simplify the code you need to process through all databases and/or all
Exec sp_MSforeachdb 'Select ''[?]'',* From ?..sysobjects where name like ''form%'''
Subscribe to:
Posts (Atom)