Service Broker

I spent my recreation time this weekend on the softball field and doing the Service Broker challenge. I left the softball field for the second time in as many weeks on the losing side, and with scraped up legs. It’s time to buy long pants – I’m becoming old and fragile, I am.

The Service Broker experience involved a lot less pain, although Geoff is now taunting me. May your blog be filled with an overabundance of spam, Geoff!

The amazing part about Service Broker is not how I dropped an XML document into a local queue, nor how the document was whisked to a remote machine over an encrypted TCP connection authenticated by digital certificate, nor that the remote machine validated the document against an XML schema before activating a stored procedure and sending back a response that I pulled out of a local queue, but that all this was done using only Transact SQL.

What was that thud? Another DBA keeling over?

My one hang up in the challenge was that I did not understand the different protocol layers in Service Broker, but Rushi got me on track:

The adjacent layer protocol that connects two SQL Server instances and the dialog endpoint layer protocol that connects two services are distinct protocols that stack one on top of the other. Think IP and TCP. Hence the certificates used at the adjacent layer for authenticating instances to each other are different from the certificates used at the dialog layer for authentication services to each other.

Two services that wish to engage in a conversation may not be connected directly to each other, but via multiple hops using routes. Each pair of adjacent hops (i.e. SQL Server instances) use their own authentication/encryption which is hidden from the dialog endpoints.

SQL 2005 – a crazy new world.