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'.

ALTER TABLE [sales].[orders] ADD [tax] DECIMAL(2,2) NOT NULL DEFAULT ((17.5))
ALTER TABLE [sales].[orders] ADD [tax] DECIMAL(2,2) NOT NULL CONSTRAINT [DF_tax] DEFAULT ((17.5))
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
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
exec adventureworks.dbo.USP_ReceiveResponseAndCloseConversation @Queue = N'AdventureWorksSourceQueue' ,@Audit = 1 go
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
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
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
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
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 '
-- 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;
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
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
-- 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
-- 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]
-- 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]
-- 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]
USE MASTER; GO ALTER DATABASE databasename SET ENABLE_BROKER; GO USE databasename ; GO
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