Need distributed service broker sample

I'm working with the April CTP of SQL Server and I'm trying to create a proof of concept using service broker. I'm struggling with the "abc's" of it. If anyone has or can point me to a distributed "Hello, World" for service broker between SQL Server 2005 and SQL Express instances it would save me some time and trouble.

thx!

Christopher Yager wrote:

I'm working with the April CTP of SQL Server and I'm trying to create a proof of concept using service broker. I'm struggling with the "abc's" of it. If anyone has or can point me to a distributed "Hello, World" for service broker between SQL Server 2005 and SQL Express instances it would save me some time and trouble.

I have uploaded a zip file containing two SQL projects with scripts for sending messages between two SQL Server 2005 instances. You probably have to create your own certificates, but at least the code is there how to catalogue them etc. You can get the file from here.
Niels

|||Thanks - I've downloaded the code and I'm about to jump back in. (I've spent a few days with failure and I'm ready for some success!)|||

I don't think this is Service Broker related but I can't create a master key in the master db of "instance 1" (using the sample code example). I get the error:

Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.

when executing the statement:

create master key encryption by password = 'Hello1234'

I'm logged in to the computer as domain administrator and using windows security to connect to SQL Server. I was having the same trouble before posting to this forum in the first place. I don't know what I did but I was able to create a key before. I've since deleted, re-created, and deleted. Now I can't seem to create it again.

any thoughts?

thx.

|||I found the error of my ways. I changed the logon account for the SQL Server service. That invalidated the Service Master Key apparently. After re-generating the Service Master Key (using the FORCE option) I was able to successfully create a master key.

Oh - the things we learn!|||OK - I've run through the sample. I am much more enlightened but still frustrated. I have everything set up - certificates created - routes set up, bindings created - but messages are not moving from instance1 to instance2. Messages are staying in the sys.transmission_queue on instance1.

I appreciate the assistance thus far.

My configuration is as follows:

VPC1 - running Win2K3(Active Directory), SQL2005 April CTP, VS2005, Team Foundation
VPC2 - running XP(domain member), SQL2005 Express April CTP, VS2005 Team Suite

Both VPC's running on host Win2K3 system. Networking and sharing between the systems is working fine. I'm able to connect from SQL MGMT Studio to the SQL Express instance without trouble.|||OK - sorry for not answering straight away; I'm out of the office this week, and my internet connection is not the best.
A couple of things:
1. In sys,transmission_queue, you should have a column saying why the message is not sent (I don't have a SSB machine up and running at the moment, so I don't remember the name of the column). See what the reason is.
2. Are endpoints enabled on both macines?
3. Is the database on Instance2 SSB enabled?
4. have you created a route on Instance2 for the service on Instance1?
5. Run SQL profiler on Instance2 and see if there are anything happening on that machine when you send a message from Instance1.
Niels
|||Thanks for all your help.

I saw a message in the event log about connect permission on the endpoint.

I granted connect on the service broker endpoint on the initiator (and target although that one was already done) to the remcert user and the messages just started flowing!

Great technology and very timely for my organization.

Thanks again.

For anyone else struggling - here are a few other tips:
The syntax has changed for the certificate related objects -DECRYPTION_PASSWORD change to DECRYPTION BY PASSWORD;
ENCRYPTION_PASSWORD change to ENCRYPTION BY PASSWORD;
PRIVATE_KEY change to PRIVATE KEY (no underscore);
there are a few others but I don't remember where I found the list I used.

If you change the login account for the SQL Server service, you'll need to regenerate the Service Master Key. All other keys and certs are based on this so be aware before changing the login for any of your services.|||You can actually save the service master key before changing the SQL Server service login account, using BACKUP SERVICE MASTER KEY..., and then restore it under the new login account, using RESTORE SERVICE MASTER KEY...|||Ni Niels,

the link u gave for the zip file download does not work anymore. if u can provide me the zip file with code that would definitely help me getting started with service broker.

Thanks in advance.|||

Kapil Aggarwal wrote:

the link u gave for the zip file download does not work anymore. if u can provide me the zip file with code that would definitely help me getting started with service broker.

Try this link, those scripts "should" have correct syntax etc. For updates etc, check my blog.
Niels|||If you do setup the broker endpoint successfully, it is time to take the BrokerChallenge:

http://blogs.msdn.com/rushidesai/archive/2005/06/15/429649.aspx

Thank you for participating,
Rushi|||Niels,

The two link
http://staff.develop.om/nielsb/code/routing2.zip
http://staff.develop.com/nielsb/code/routing-aprilctp.zip

still do not work (a connection with the server could not be established). I do not know if the server is down, or is the document no longer for pulic domain?

Appreciate your reply