Compressing Hyper-V disk images (.vhd files)

How to compress Hyper-V disk images (.vhd files) >

1) Go to Server Manager.

2) Shutdown the Virtual Machine in Hyper-V Manager.

3) Select the VM and click Settings in the right hand pane.


4) Click the hard drive and select 'Edit'.


5) Select 'Compact' (default action) and click 'finish'.

Name Table Constraints

When creating constraints, NAME THEM !

For example ;

ALTER TABLE [sales].[orders]
ADD [tax] DECIMAL(2,2) NOT NULL DEFAULT ((17.5))

Gives a randomly named constraint >

DF__Orders__Tax__42E0813E

Not a great problem in itself, it's not like you'll need to look at the name of the constraint is it?

WRONG!
Comparison tools (in my case SQL Compare) will compare otherwise identical tables and report differences.

It is far safer therefore, to explicitly name the constraint -

ALTER TABLE [sales].[orders]
ADD [tax] DECIMAL(2,2) NOT NULL CONSTRAINT [DF_tax] DEFAULT ((17.5))

Further Reading -
http://www.sqlteam.com/article/default-constraint-names

12 Tips for Optimising SQL Server 2005 Queries

1. Turn on the execution plan, and statistics

2. Use Clustered Indexes

3. Use Indexed Views

4. Use Covering Indexes

5. Keep your clustered index small.

6. Avoid cursors

7. Archive old data

8. Partition your data correctly

9. Remove user-defined inline scalar functions

10. Use APPLY

11. Use computed columns

12. Use the correct transaction isolation level


A well written article to jog the memory when writing TSQL >

http://tonesdotnetblog.wordpress.com/2008/05/26/twelve-tips-for-optimising-sql-server-2005-queries/

SQL Beautifier

Online SQL code beautifier -

http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

Brilliant for tidying and standardising your code.

Is similar to the SQL Prettifier I found last year.

Sending Service Broker Message on Trigger

Sample Trigger to send Service Broker message on data change

CREATE TRIGGER [Person].[TRG_Person_Contact]
ON [Person].[Contact]
FOR DELETE,INSERT,UPDATE
AS
BEGIN

SET NOCOUNT ON

DECLARE @ACT CHAR(6)
DECLARE @DEL BIT
DECLARE @INS BIT 
DECLARE @XMLDOC XML
DECLARE @XMLTEXT NVARCHAR(MAX)

DECLARE @ContactID [int]
DECLARE @NameStyle [bit]
DECLARE @Title [nvarchar] (8)
DECLARE @FirstName [nvarchar] (50)
DECLARE @MiddleName [nvarchar] (50)
DECLARE @LastName [nvarchar] (50)
DECLARE @Suffix [nvarchar] (10)
DECLARE @EmailAddress [nvarchar] (50)
DECLARE @EmailPromotion [int]
DECLARE @Phone [nvarchar] (25)
DECLARE @PasswordHash [varchar] (128)
DECLARE @PasswordSalt [varchar] (10)
DECLARE @AdditionalContactInfo [xml]
DECLARE @rowguid [uniqueidentifier]
DECLARE @ModifiedDate [datetime]

SET @DEL = 0
SET @INS = 0

IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1 

IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'

IF @INS = 0 AND @DEL = 0 RETURN

-- This cursor for inserted or updated records

IF @ACT = 'INSERT' OR @ACT = 'UPDATE'
BEGIN
DECLARE curINSERTED CURSOR LOCAL FAST_FORWARD
FOR
SELECT
[INSERTED].[ContactID]
,[INSERTED].[NameStyle]
,[INSERTED].[Title]
,[INSERTED].[FirstName]
,[INSERTED].[MiddleName]
,[INSERTED].[LastName]
,[INSERTED].[Suffix]
,[INSERTED].[EmailAddress]
,[INSERTED].[EmailPromotion]
,[INSERTED].[Phone]
,[INSERTED].[PasswordHash]
,[INSERTED].[PasswordSalt]
,[INSERTED].[AdditionalContactInfo]
,[INSERTED].[rowguid]
,[INSERTED].[ModifiedDate]
FROM
[INSERTED]

OPEN curINSERTED
FETCH NEXT FROM curINSERTED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

WHILE @@FETCH_STATUS = 0
BEGIN

SET @XMLDOC = (
SELECT
@ACT AS ZZ_ROWACTION
,[Person.Contact].[ContactID]
,[Person.Contact].[NameStyle]
,[Person.Contact].[Title]
,[Person.Contact].[FirstName]
,[Person.Contact].[MiddleName]
,[Person.Contact].[LastName]
,[Person.Contact].[Suffix]
,[Person.Contact].[EmailAddress]
,[Person.Contact].[EmailPromotion]
,[Person.Contact].[Phone]
,[Person.Contact].[PasswordHash]
,[Person.Contact].[PasswordSalt]
,[Person.Contact].[AdditionalContactInfo]
,[Person.Contact].[rowguid]
,[Person.Contact].[ModifiedDate]
FROM 
(
SELECT
@ContactID AS [ContactID] 
,@NameStyle AS [NameStyle] 
,@Title AS [Title] 
,@FirstName AS [FirstName] 
,@MiddleName AS [MiddleName] 
,@LastName AS [LastName] 
,@Suffix AS [Suffix] 
,@EmailAddress AS [EmailAddress] 
,@EmailPromotion AS [EmailPromotion] 
,@Phone AS [Phone] 
,@PasswordHash AS [PasswordHash] 
,@PasswordSalt AS [PasswordSalt] 
,@AdditionalContactInfo AS [AdditionalContactInfo] 
,@rowguid AS [rowguid] 
,@ModifiedDate AS [ModifiedDate] 
) AS [Person.Contact]
FOR XML AUTO, TYPE
)

SET @XMLTEXT = CONVERT(NVARCHAR(MAX),@XMLDOC)

IF @XMLTEXT IS NOT NULL
BEGIN 
--send the message
exec USP_SendBrokerMessage
@SourceService = 'AdventureWorksSourceQueueSourceService',
@TargetService = N'AdventureWorksTargetQueueTargetService',
@MessageContract = 'http://www.servicebroker.com/Contract',
@MessageType = 'http://www.servicebroker.com/SendType',
@MessageContent = @XMLTEXT;
END

FETCH NEXT FROM curINSERTED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

END

CLOSE curINSERTED
DEALLOCATE curINSERTED
END

-- This cursor for deleted records

IF @ACT = 'DELETE'
BEGIN
DECLARE curDELETED CURSOR LOCAL FAST_FORWARD
FOR
SELECT
[DELETED].[ContactID]
,[DELETED].[NameStyle]
,[DELETED].[Title]
,[DELETED].[FirstName]
,[DELETED].[MiddleName]
,[DELETED].[LastName]
,[DELETED].[Suffix]
,[DELETED].[EmailAddress]
,[DELETED].[EmailPromotion]
,[DELETED].[Phone]
,[DELETED].[PasswordHash]
,[DELETED].[PasswordSalt]
,[DELETED].[AdditionalContactInfo]
,[DELETED].[rowguid]
,[DELETED].[ModifiedDate]
FROM
[DELETED]

OPEN curDELETED
FETCH NEXT FROM curDELETED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

WHILE @@FETCH_STATUS = 0
BEGIN

SET @XMLDOC = (
SELECT
@ACT AS ZZ_ROWACTION
,[Person.Contact].[ContactID]
,[Person.Contact].[NameStyle]
,[Person.Contact].[Title]
,[Person.Contact].[FirstName]
,[Person.Contact].[MiddleName]
,[Person.Contact].[LastName]
,[Person.Contact].[Suffix]
,[Person.Contact].[EmailAddress]
,[Person.Contact].[EmailPromotion]
,[Person.Contact].[Phone]
,[Person.Contact].[PasswordHash]
,[Person.Contact].[PasswordSalt]
,[Person.Contact].[AdditionalContactInfo]
,[Person.Contact].[rowguid]
,[Person.Contact].[ModifiedDate]
FROM 
(
SELECT
@ContactID AS [ContactID] 
,@NameStyle AS [NameStyle] 
,@Title AS [Title] 
,@FirstName AS [FirstName] 
,@MiddleName AS [MiddleName] 
,@LastName AS [LastName] 
,@Suffix AS [Suffix] 
,@EmailAddress AS [EmailAddress] 
,@EmailPromotion AS [EmailPromotion] 
,@Phone AS [Phone] 
,@PasswordHash AS [PasswordHash] 
,@PasswordSalt AS [PasswordSalt] 
,@AdditionalContactInfo AS [AdditionalContactInfo] 
,@rowguid AS [rowguid] 
,@ModifiedDate AS [ModifiedDate] 
) AS [Person.Contact]
FOR XML AUTO, TYPE
)

SET @XMLTEXT = CONVERT(NVARCHAR(MAX),@XMLDOC)

IF @XMLTEXT IS NOT NULL
BEGIN 
--send the message
exec USP_SendBrokerMessage
@SourceService = 'AdventureWorksSourceQueueSourceService',
@TargetService = N'AdventureWorksTargetQueueTargetService',
@MessageContract = 'http://www.servicebroker.com/Contract',
@MessageType = 'http://www.servicebroker.com/SendType',
@MessageContent = @XMLTEXT;
END

FETCH NEXT FROM curDELETED INTO
@ContactID
,@NameStyle
,@Title
,@FirstName
,@MiddleName
,@LastName
,@Suffix
,@EmailAddress
,@EmailPromotion
,@Phone
,@PasswordHash
,@PasswordSalt
,@AdditionalContactInfo
,@rowguid
,@ModifiedDate

END

CLOSE curDELETED
DEALLOCATE curDELETED
END

END
go

USP_ReceiveResponseAndCloseConversation

Procedure to tidy receiving & responding to a service broker message.
Requires TBL_ServiceBrokerAudit for optional message auditing. -

CREATE PROCEDURE [dbo].[USP_ReceiveAndRespondBrokerMessage]
@Queue NVARCHAR(200),
@MessageType NVARCHAR(200),
@ResponseType NVARCHAR(200),
@ResponseMessage NVARCHAR(4000),
@Audit BIT

AS

BEGIN

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg XML;
DECLARE @RecvReqMsgName sysname;
DECLARE @SqlString NVARCHAR(MAX);
DECLARE @Parameters NVARCHAR(MAX);

SET NOCOUNT ON

BEGIN TRANSACTION;
-- Receive Message

SET @Parameters = ' @RecvReqDlgHandle UNIQUEIDENTIFIER OUTPUT,
       @RecvReqMsg XML OUTPUT,
       @RecvReqMsgName NVARCHAR(MAX) OUTPUT'

SET @SqlString = ' WAITFOR
    ( RECEIVE TOP(1)
     @RecvReqDlgHandle = conversation_handle,
     @RecvReqMsg = message_body,
     @RecvReqMsgName = message_type_name
      FROM [' + @Queue + ']
    ), TIMEOUT 1000;'

EXEC sp_executesql
@SqlString,
@Parameters,
@RecvReqDlgHandle = @RecvReqDlgHandle OUTPUT,
@RecvReqMsg = @RecvReqMsg OUTPUT,
@RecvReqMsgName = @RecvReqMsgName OUTPUT

IF @Audit = 1
BEGIN
INSERT INTO [dbo].[TBL_ServiceBrokerAudit]
     ([Queue]
     ,[MessageHandle]
     ,[MessageName]
     ,[MessageContent]
     ,[AuditSource])
  VALUES
     (@Queue
     ,@RecvReqDlgHandle
     ,@RecvReqMsgName
     ,@RecvReqMsg
     ,'USP_ReceiveAndRespondBrokerMessage')
END
-- Display received message
SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsg IS NOT NULL
BEGIN
-- If received message is of the correct type, send response
IF @RecvReqMsgName = @MessageType

BEGIN
  SET @SqlString = ' SEND ON CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + '''
        MESSAGE TYPE
       [' + @ResponseType + '] (''' + @ResponseMessage + ''');
      END CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + ''';'

  EXEC sp_executesql @SqlString
  -- Uncomment next line to debug
  --SELECT @ResponseMessage AS SentReplyMsg;

END
ELSE
 SELECT 'Not Expected Message Type'
END
ELSE
SELECT 'No Message to Receive'
COMMIT TRANSACTION;

END
GO

Usage :

exec adventureworks.dbo.USP_ReceiveResponseAndCloseConversation
  @Queue = N'AdventureWorksSourceQueue'
 ,@Audit = 1
go

USP_ReceiveAndRespondBrokerMessage

Procedure to tidy service broker message response and conversation closure.
Requires TBL_ServiceBrokerAudit for optional message auditing.

CREATE PROCEDURE [dbo].[USP_ReceiveResponseAndCloseConversation]
@Queue NVARCHAR(200)
,@Audit BIT
AS

BEGIN

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg XML;
DECLARE @RecvReqMsgName sysname;
DECLARE @SqlString NVARCHAR(MAX);
DECLARE @Parameters NVARCHAR(MAX);

SET NOCOUNT ON

BEGIN TRANSACTION;
-- Receive Response

SET @Parameters = ' @RecvReqDlgHandle UNIQUEIDENTIFIER OUTPUT,
@RecvReqMsg XML OUTPUT,
@RecvReqMsgName NVARCHAR(MAX) OUTPUT'

SET @SqlString = ' WAITFOR
( RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM [' + @Queue + ']
), TIMEOUT 1000;'



EXEC sp_executesql
@SqlString,
@Parameters,
@RecvReqDlgHandle = @RecvReqDlgHandle OUTPUT,
@RecvReqMsg = @RecvReqMsg OUTPUT,
@RecvReqMsgName = @RecvReqMsgName OUTPUT

IF @Audit = 1
BEGIN
INSERT INTO [dbo].[TBL_ServiceBrokerAudit]
([Queue]
,[MessageHandle]
,[MessageName]
,[MessageContent]
,[AuditSource])
VALUES
(@Queue
,@RecvReqDlgHandle
,@RecvReqMsgName
,@RecvReqMsg
,'USP_ReceiveResponseAndCloseConversation')
END

-- Uncomment next line to debug
-- SELECT @RecvReqMsg AS ReceivedRequestMsg;

SET @SqlString = ' END CONVERSATION ''' + CAST(@RecvReqDlgHandle AS NVARCHAR(MAX)) + ''';'
EXEC sp_executesql @SqlString


COMMIT TRANSACTION;
END
GO

Usage :

exec USP_ReceiveAndRespondBrokerMessage
@Queue = N'AdventureWorksTargetQueue'
,@MessageType = N'http://www.servicebroker.com/SendType'
,@ResponseType = N'http://www.servicebroker.com/ResponseType'
,@ResponseMessage = N'Reply Service Broker Message from AdventureWorksTarget'
,@Audit = 1
go

TBL_ServiceBrokerAudit

Audit table for Service Broker Procedures (USP_ReceiveAndRespondBrokerMessage & USP_ReceiveResponseAndCloseConversation) -

CREATE TABLE [dbo].[TBL_ServiceBrokerAudit](
 [ArbitaryID] [int] IDENTITY(1,1) NOT NULL,
 [MessageReceived] [datetime] NOT NULL CONSTRAINT [DF_TBL_ServiceBrokerAudit_MessageReceived]  DEFAULT (getdate()),
 [Queue] [nvarchar](100) NULL,
 [MessageHandle] [nvarchar](100) NULL,
 [MessageName] [nvarchar](100) NULL,
 [MessageContent] [xml] NULL,
 [AuditSource] [varchar](50) NULL
) ON [PRIMARY]

GO

USP_SendBrokerMessage

Procedure to tidy sending a service broker message -

CREATE PROCEDURE [dbo].[USP_SendBrokerMessage] 
 @SourceService NVARCHAR(200),
 @TargetService NVARCHAR(200),
 @MessageContract NVARCHAR(200),
 @MessageType NVARCHAR(200),
 @MessageContent NVARCHAR(4000)

AS

BEGIN

-- Sending a Service Broker Message
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(4000);

SET NOCOUNT ON

BEGIN TRANSACTION;

 BEGIN DIALOG @InitDlgHandle
   FROM SERVICE @SourceService
   TO SERVICE @TargetService 
   ON CONTRACT @MessageContract
   WITH ENCRYPTION = OFF;

 SELECT @RequestMsg = @MessageContent;

 SEND ON CONVERSATION @InitDlgHandle
   MESSAGE TYPE @MessageType
    (@RequestMsg);

 --Uncomment next line to debug >
 --SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;

END
GO

Usage :

exec USP_SendBrokerMessage
@SourceService = 'AdventureWorksSourceQueueSourceService',
@TargetService = N'AdventureWorksTargetQueueTargetService',
@MessageContract = 'http://www.servicebroker.com/Contract',
@MessageType = 'http://www.servicebroker.com/SendType',
@MessageContent =  N'Initial Service Broker Message from AdventureWorks'

Service Broker Example #1 - Configuration, Send, Receive & Respond

Service Broker Example #1 - Configuration, Send, Receive & Respond

This example uses AdventureWorks and AdventureWorksTarget (an empty AdventureWorks db).

1 Initial Setup of databases -

-- Allow databases access to external object 

USE Master
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
ALTER DATABASE AdventureWorksTarget SET TRUSTWORTHY ON

-- Enable Databases for Service Broker

ALTER DATABASE AdventureWorks SET NEW_BROKER
ALTER DATABASE AdventureWorksTarget SET NEW_BROKER

-- Needed these as had downloaded Adventureworks and user that created it was not present.
-- Good practice anyway...
ALTER AUTHORIZATION ON DATABASE::AdventureWorks TO sa;
ALTER AUTHORIZATION ON DATABASE::AdventureWorksTarget TO sa;

2 Configuration of Target DB (do before source!) -

USE AdventureWorksTarget
GO
-- Set Key for Service Broker Communication
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PASSWORD2008'

-- Configure Message Types on target
CREATE MESSAGE TYPE [http://www.servicebroker.com/SendType]
    VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [http://www.servicebroker.com/ResponseType]
    VALIDATION = WELL_FORMED_XML;
GO

-- Configure Contract
-- Contract defines a message broker conversation
CREATE CONTRACT [http://www.servicebroker.com/Contract]
   ([http://www.servicebroker.com/SendType]
   SENT BY INITIATOR,
    [http://www.servicebroker.com/ResponseType]
   SENT BY TARGET
   );
GO

-- Configure Queue
CREATE QUEUE [AdventureWorksTargetQueue];

-- Turn Queue on
ALTER QUEUE [AdventureWorksTargetQueue]
WITH STATUS = ON

-- Configure Service
CREATE SERVICE [AdventureWorksTargetQueueTargetService]
    ON QUEUE [AdventureWorksTargetQueue]
    ([http://www.servicebroker.com/Contract]);
GO

3 Configuration of the Source DB -

USE AdventureWorks
GO

-- Set Key for Service Broker Communication
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PASSWORD2008'

-- Configure Message Types on source
CREATE MESSAGE TYPE [http://www.servicebroker.com/SendType]
    VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [http://www.servicebroker.com/ResponseType]
    VALIDATION = WELL_FORMED_XML;
GO

-- Configure Contract
-- Contract defines a message broker conversation
CREATE CONTRACT [http://www.servicebroker.com/Contract]
   ([http://www.servicebroker.com/SendType]
   SENT BY INITIATOR,
    [http://www.servicebroker.com/ResponseType]
   SENT BY TARGET
   );
GO

-- Configure Queue
CREATE QUEUE [AdventureWorksSourceQueue];

-- Turn Queue on
ALTER QUEUE [AdventureWorksSourceQueue]
WITH STATUS = ON

-- Configure Service
CREATE SERVICE [AdventureWorksSourceQueueSourceService]
   ON QUEUE [AdventureWorksSourceQueue];
GO

4 Examining the contents of the message queues.
Run this, no records will be returned just yet though -

-- Monitoring Queue contents
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]

-- Where is my message? (Look at transmission_status for error information)
select transmission_status,* from AdventureWorks.sys.transmission_queue
select transmission_status,* from AdventureWorksTarget.sys.transmission_queue

5 Sending a Message -

-- Send the Message
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

 BEGIN DIALOG @InitDlgHandle
   FROM SERVICE [AdventureWorksSourceQueueSourceService]
   TO SERVICE N'AdventureWorksTargetQueueTargetService' 
   ON CONTRACT [http://www.servicebroker.com/Contract]
   WITH ENCRYPTION = OFF;

 SELECT @RequestMsg =
    N'Initial Service Broker Message from AdventureWorks';

 SEND ON CONVERSATION @InitDlgHandle
   MESSAGE TYPE [http://www.servicebroker.com/SendType]
    (@RequestMsg);

 SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO

-- Examine the queues to see the message just transmitted in the target one >
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]

6 Read & Respond to the message -

-- Read the message on the target server and respond to it
USE AdventureWorksTarget

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

 WAITFOR
 ( RECEIVE TOP(1)
  @RecvReqDlgHandle = conversation_handle,
  @RecvReqMsg = message_body,
  @RecvReqMsgName = message_type_name
   FROM [AdventureWorksTargetQueue]
 ), TIMEOUT 1000;

 SELECT @RecvReqMsg AS ReceivedRequestMsg;

 IF @RecvReqMsgName =
    N'http://www.servicebroker.com/SendType'
 BEGIN
   DECLARE @ReplyMsg NVARCHAR(100);
   SELECT @ReplyMsg =
   N'Reply Service Broker Message from AdventureWorksTarget';
  
   SEND ON CONVERSATION @RecvReqDlgHandle
     MESSAGE TYPE
    [http://www.servicebroker.com/ResponseType] (@ReplyMsg);

   END CONVERSATION @RecvReqDlgHandle;
 END

 SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;
GO

-- Examine the queues to see the response message back on the source queue, 
-- along with an EndDialog message for it. >
-- It is no longer in the target queue.
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]

7 Send message response & close the conversation -

-- read the response on the sender & close conversation.
use AdventureWorks
go

DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
@RecvReplyDlgHandle = conversation_handle,
@RecvReplyMsg = message_body
FROM [AdventureWorksSourceQueue]
), TIMEOUT 1000;

END CONVERSATION @RecvReplyDlgHandle;

-- Display recieved request.
SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;
GO

-- Examine the queues once more.
-- Having verified receipt of the message. the Source queue is empty again.
select * from AdventureWorks..[AdventureWorksSourceQueue]
select * from AdventureWorksTarget..[AdventureWorksTargetQueue]

SQL 101 : Service Broker

Quick Facts about Service Broker -

Service Broker is a sql server message queuing system introduced in sql 2005 which provides guaranteed delivery.

Messages can be based in 1 sql instance or go between instances via tcp/ip.

XML is preferred message content.

Service Broker Objects -

• message type - specifies validation of the xml message
• contract - defines that a message direction of travel for message types
• queue - store of messages
• service - ties contracts & queues together, a wrapper for conversations.

MSDN Service Broker Forums
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=91&SiteID=1
Service Broker vs MSMQ
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2489857&SiteID=1

Enabling Service Broker -

USE MASTER;
GO

ALTER DATABASE databasename SET ENABLE_BROKER;
GO

USE databasename ;
GO

Correlating Performance Monitor & SQL Profiler

I originally covered this a few months ago on a 'High Availability SQL' course, but prompted by this month's technet, I thought I'd give it another go.
(Here I'm using SQL 2005 on Windows 2003)

1) Start Performance Monitor > (start > run > type 'perfmon' [enter])

2) Create a counter log as shown below >


3) Add Counters for whatever you want to monitor. >

I've chosen Memory, SQL Wait Statistics, PhysicalDisk and Processor.

Start the Counter log

4) Prepare Tracing >

Start SQL Profiler (management studio > tools > sql server profiler)
Start a new trace (file > new trace)
On the 'General' tab, specify the Trace name and select 'Save to file'.


On the 'Events Selection' tab, select the sql events to record in the trace.
Column Filters can be used to restrict the data recorded.
In the example here I have added 'DatabaseName' as a column and have filtered to only record data from one db. >


5) Run the trace >

Click 'Run' to start the trace.
The trace appears in real time, capturing SQL Activity as shown here >


NB : In another window, i ran some pre-prepared queries as a test for this demo. Some SELECTs, random CROSS JOINS (to return large recordsets) and a CURSOR for good measure :)

Stop the profiler and then the counter log when finished.

6) Set up the data comparison >

Open the profiler trace (file > open > trace file > (select file saved in last step).
Import the trace data (file > import performance data > (select .blg file saved from perfmon).

The 'Performance Counters Limit Dialog' selection window is then displayed.
Use this to restict the comparison to objects of counters eg 'Page Faults/Sec' within Memory.
(Select OK when finished) >


7) The Results >

Profiler now combines the standard trace window at the top (sql statements recorded etc) with the performance monitor graph at the bottom. Basicly, it correlates the two sets of data by timestamp to assist with locating performance issues.

By clicking on a statement in the trace window, the highlighted bar moves to the point of the timeline so we can see processor, memory etc at that point in time.
Similarly, by clicking the timeline, the sql command executing at the time is highlighted.

Move Database

USE [master]
GO   
 
-- Take the database offline 
 
ALTER DATABASE [myDatabase] SET OFFLINE;
GO
 
 
-- Move all files to new path
 
-- Then run statements below
 

ALTER DATABASE [myDatabase] 
MODIFY FILE (NAME = N'myDataPrimary', FILENAME = N'D:\Data\mydatabase.mdf')
GO

ALTER DATABASE [myDatabase] 
MODIFY FILE ( NAME = N'myDataSecondary', FILENAME = N'D:\Data\mydatabase2.mdf')
GO

ALTER DATABASE [myDatabase] 
MODIFY FILE ( NAME = N'myLog', FILENAME = N'E:\Logs\mydatabase.ldf')
GO

 
-- Then Put database online again...
 
ALTER DATABASE [myDatabase] SET ONLINE;
GO

XP SP3 'Features'

1. Vista's product activation model (yawn...)
2. Cryptographic Algorithms module in the kernal (am sure some developers will love this)
3. Network Access Protection (NAP), as seen in Vista / Windows 2008 - is only of use to companies who chose to implement it.
4. Black Hole Router detection - OK, its there, but I cannot find any information on what XP will do when it finds one!
   
   

There is a small (but noticable) increase in speed when firing up my regular applications, post sp3 install.

I'm not saying it will occur for everyone, but i'm sure it is so for my first upgrade client anyway...

more over @ http://lifehacker.com/385295/field-guide-to-windows-xp-sp3

XP SP3 now available...

...well, it has finally reached us in the UK, via (an optional) windows update!

Here is why not to become a beta test monkey for MS....

I'm sure that SP3 RC1 (previously available from msdn downloads) probably catered for multiple xp versions and dealt with multiple issues i'll never understand.
The fact remains though, that there is one hell of a difference in filesize between the downloads. I hope its down to the fact that my machines are up-to-date with patches and update is only downloading what i dont already have!

Dec 2007
XP SP3 RC1
329MB

Mar 2008
XP SP3 RC2
315MB

May 2008
XP SP3 Final (via windowsupdate.com)
66.9MB

:)