SQL 2025 master class LAB 01 Service Broker

--step 1

USE master
GO

IF EXISTS ( SELECT  *
            FROM    sys.databases
            WHERE   name = 'SoftwareSmithServiceBroker' )
    BEGIN
            DROP DATABASE SoftwareSmithServiceBroker ;
    END
GO

CREATE DATABASE SoftwareSmithServiceBroker
GO

USE SoftwareSmithServiceBroker
GO

ALTER DATABASE SoftwareSmithServiceBroker SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE
go
ALTER DATABASE SoftwareSmithServiceBroker SET NEW_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE SoftwareSmithServiceBroker SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
GO

--step 2 

CREATE MESSAGE TYPE
[SoftwareSmithRequestMessage]
VALIDATION = WELL_FORMED_XML
GO

CREATE MESSAGE TYPE
[SoftwareSmithResponseMessage]
VALIDATION = WELL_FORMED_XML
GO

CREATE CONTRACT [SoftwareSmithContract]
(
[SoftwareSmithRequestMessage] SENT BY INITIATOR,
[SoftwareSmithResponseMessage] SENT BY TARGET
) 

GO

--step 3 

CREATE QUEUE InitiatorQueue
WITH STATUS = ON
GO

CREATE QUEUE TargetQueue
WITH STATUS = ON
GO

CREATE SERVICE InitiatorService
ON QUEUE InitiatorQueue 
(
[SoftwareSmithContract]
)
GO

CREATE SERVICE TargetService
ON QUEUE TargetQueue
(
[SoftwareSmithContract]
)
GO  

--step 4 

BEGIN TRANSACTION ;
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @msg NVARCHAR(MAX) ;

BEGIN DIALOG CONVERSATION @ch
      FROM SERVICE [InitiatorService]
      TO SERVICE 'TargetService'
      ON CONTRACT [SoftwareSmithContract]
      WITH ENCRYPTION = OFF ;

SET @msg = '<SoftwareSmithRequest>
               Software-smith
      </SoftwareSmithRequest>' ;

SEND ON CONVERSATION @ch 
   MESSAGE TYPE [SoftwareSmithRequestMessage] 
   (@msg) ;
COMMIT TRANSACTION
GO

--step 5

SELECT message_type_name, CAST(message_body AS XML) FROM TargetQueue
GO 

DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML
DECLARE @responsemessage XML

BEGIN TRANSACTION ;

RECEIVE TOP(1)
@ch = conversation_handle,
@messagetypename = message_type_name,
@messagebody = CAST(message_body AS XML)
FROM TargetQueue

PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX))
PRINT 'Message type: ' + @messagetypename
PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX))

IF ( @messagetypename = 'SoftwareSmithRequestMessage' ) 
    BEGIN
-- Construct the response message
        SET @responsemessage = '<SoftwareSmithResponse>Broker response messsage, '
            + @messagebody.value('/SoftwareSmithRequest[1]', 'NVARCHAR(MAX)')
            + '</SoftwareSmithResponse>' ;

-- Send the response message back to the initiating service
        SEND ON CONVERSATION @ch MESSAGE TYPE [SoftwareSmithResponseMessage] (@responsemessage) ;

-- End the conversation on the target's side
        END CONVERSATION @ch ;
    END
COMMIT TRANSACTION
GO 

--step 6

SELECT message_type_name , CAST(message_body AS XML) FROM InitiatorQueue
GO

DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML

BEGIN TRANSACTION ;
RECEIVE TOP (1)
@ch = conversation_handle,
@messagetypename = message_type_name,
@messagebody = CAST(message_body AS XML)
FROM InitiatorQueue

IF ( @messagetypename = 'SoftwareSmithResponseMessage' ) 
    BEGIN
        PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX))
        PRINT 'Message type: ' + @messagetypename
        PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX))
    END

IF ( @messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' ) 
    BEGIN
-- End the conversation on the initiator's side
        END CONVERSATION @ch ;
    END
COMMIT TRANSACTION 
GO
--step 7  run step 6 again

--with thanks and acknowledgement to https://www.sqlservercentral.com 

-- Housekeeping 
use master

IF EXISTS (SELECT * FROM sys.databases WHERE NAME = 'SoftwareSmithServiceBroker')

DROP DATABASE  SoftwareSmithServiceBroker;

GO

SQL 2025 master class LAB 02 Installation

 

Please install a SQL Server Database Instance with the following settings:

• Instance Name: Sever2025Lab

Authentication Mode: Windows

TempDB: In a separate folder c:\TempDB

Server Agent : Automatic

When reaching Ready to Install, note configuration file path.

Cancel and use Advanced in Installation Center to Install based on configuration file

 

Run as administrator

 

 

SQL 2025 master class LAB 03 localDB

  

  

 

 

locate files on hard disk

*ask trainer 

sqllocaldb stop MSSQLLocalDB

sqllocaldb delete MSSQLLocalDB