Archive for May, 2012

Service Broker Across 2 instances on the same machine

May 31, 2012

Not that much more difficult than across 2 databases on the same instance.

Ive attached 6 files in a single .zip file 2 instances.zip

Service Broker Across two databases on the same instance

May 29, 2012

This post modifies the project contained in Service Broker with In a Single database by splitting the conversation out to 2 databases within the same instance. The rest of the functionality remains the same.

service_broker_across_db.sql

Service Broker In A Single Database

May 29, 2012

This post demonstrates how Service Broker can work within a single database. The example here demonstrates a conversation initiator sending a message to the target, the target stores the message text in a table then the conversation is closed.

service_broker_single_database.sql

Service Broker: Completing a Conversation Between Instances

May 27, 2012

The Microsoft Tutorial located here gives instructions on how to create a simple service broker solution between two databases on separate instances. The assumption of the tutorial is that you are working on two seperate servers. The purpose of this post is to document the changes that were necersary in order to get this working for two instances on the same server.

My Set Up

The default instance holds the target database.

The named instance holds the conversation instigator database.

Lesson 1:  Creating the Target Database

service_broker_across_instances_a .sql

expiry date of certificate

The example certificate has an expiry date of Dec 31st 2010, this date is in the past and no longer valid. I have changed this date to Dec 31st 2010

certificate backup location

The example uses C:\storedcerts\$ampleSSBCerts\InstTargetCertificate.cer as the location of the certificate back up. I have used the root of the c drive, C:\InstTargetCertificate.cer’;

Lesson 2:  Creating the Initiator Database

service_broker_across_instances 2a.sql

Initiator Endpoint Listener Port

The example uses 4022, I use 4023. I have 2 instances on this server, the target is using 4022 so I need to come up with something else, I went with 4023.

Expiry date of certificate

As before I needed to make tweaks to the certificate.

The example certificate has an expiry date of Dec 31st 2010, this date is in the past and no longer valid. I have changed this date to Dec 31st 2010

Certificate backup location

The example uses C:\storedcerts\$ampleSSBCerts\InstInitiatorCertificate.cer as the location of the certificate back up. I have used the root of the c drive, C:\InstInitiatorCertificate.cer’;

The example uses C:\storedcerts\$ampleSSBCerts\InstTargetCertificate.cer as the location of the certificate back up. I have used the root of the c drive, C:\InstTargetCertificate.cer’;

InstTargetRoute Address

The example uses  TCP://MyTargetComputer:4022, change this to TCP://localhost:4022

Lesson 3: Completing the Target Conversation Objects

service_broker_across_instances 3a.sql

Certificate backup location

The example uses C:\storedcerts\$ampleSSBCerts\InstInitiatorCertificate.cer as the location of the certificate back up. I have used the root of the c drive, C:\InstInitiatorCertificate.cer’;

InstInitiatorRoute

The example uses TCP://MyInitiatorComputer:4022 I have changed this to TCP://localhost:4023

No changes were made in lessons 4, 5 and 6

service_broker_across_instances 4a.sql

service_broker_across_instances 5a.sql

service_broker_across_instances 6a.sql

Introduction

May 27, 2012

The purpose of this blog is to record any tricks/tips which I find related to SQL Server. Today it’s 27th May 2012 and Ive ploughing my way through the Service Broker tutorials found on the Microsoft site here Ive been having a little trouble getting the tutorial entitled Completing a Conversation between instances working.

My main problem was that the tutorial wasnt written for 2 instances on the same server, it was written for instances on seperate servers. Here I document the changes I had to make to get it up and running. I’m well aware this could very well be my first and last entry however we shall see.

Please note all sql attachment have the .doc extension due to limitation imposed by wordpress. Please note these are are just plain text files and can be opened in Management Studio or you preferred code editor.