Service Broker

Keep your databases off the Internet

Over on the SecurityFightClub.com site I wrote a post about why database servers shouldn’t be on the Internet.

Denny

Setting up SQL Server Service Broker for secure communication

Today a new article that I wrote was posted up on SearchSQLServer.com.

The article is about setting up SQL Service Broker to use Certificates to secure the traffic between the instances.

Denny

Here comes SoCal Code Camp

SoCal Code Camp is coming up in a couple of weeks now on June 27th and 28th.  I’m presenting 3 sessions solo, and I’m one of a few people doing an IT Pro Pannel to allow Developers to grill some IT Pros to see why we do things the way we do.

The Sessions are:

IT Pro Panel: Q&A for Developers
SQL Server Service Broker
SQL Service Broker Advanced Performance Tips and Tricks
Virtual SQL Servers Should I or Shouldn’t I?

I’ll be posting the slide decks and sample code shortly before Code Camp.

Denny

Outside Queue to SSB adapter

Something that I think that Microsoft should include with the SQL Service Broker is an adapter so that MSMQ messages (and other messaging systems as well) will flow automatically into the SQL Service Broker.  Since Microsoft hasn’t gotten around to writing one I’m going to start.

It shouldn’t be all that hard.  Setup a Windows Service which reads from a predefined MSMQ and have it take the message and send it to a SQL Service Broker queue.

Then setup a Windows application that allows you to setup the config file with the source you want to read from and the SQL Service Broker objects you want to send to.

Since I have little to know experience reading from other queues I’m putting a feeler our there for some assistance on this project.  Since I don’t know C#, the project will be written in VB.NET using Visual Studio 2008 on the .NET Framework v3.5.

I’ll be starting with MSMQ, and they other queuing systems as needed.

I’ve setup a project site on CodePlex.  There’s not much up there at the moment, just a basic framework of the project.  (Yes I know I now have two unfinished projects running, but this one will hopefully have others working on it as well.)

If you are interested in helping me out please let me know here, or on twitter, or in the discussion forum on CodePlex.

Thanks,

Denny

Slide Decks and Sample code from Quest Day with the Experts

The slide decks and sample code that Brent and I presented with have been uploaded.  The recordings have to be edited down, as Live Meeting uses a single large recording, and no one wants to sit through a 5 hour recording, so it’s being broken down into smaller chunks.

The slide decks are hosted via Slide Share and the sample code from my presentation is up on the SQLServerPedia.com servers.

Denny

January 2009 Code Camp Presentations

With the SoCal Code Camp this weekend, I’ve managed to get my slide decks put together early enough to get them posted well in advance this time. Continued »

Very useful little query to monitor the health of service broker

While dealing with a SQL Server Service Broker issue I needed a quick easy way to see how backlogged the service broker was.  So I came up with this little query which is surprisingly effective.

select far_service, state_desc, count(*) messages
from sys.conversation_endpoints
group by state_desc, far_service
ORDER BY far_service, state_desc

Hopefuly you find this helpful.

Denny

Improving SQL Service Broker Performance

A while back I was talking to a Microsoft Support Engineer and he had mentioned that in a high load Service Broker environment such as ours there can be some impressive performance improvement can be achieved by reusing the service broker sessions.

The cost of creating and closing a new conversation for every message is about 4x, while the performance increase when receiving messages is about 10x.  Remus Rusanu talks more about the numbers and shows a solution for reusing conversations on his blog posting Reusing Conversations.

I liked Remus’s solution, but an issue that I had with it was that I didn’t want to have a different conversation for each spid.  If I used this method I would end up having hundreds of conversations open and I’d need a job to close them.  Within our application any number of events can trigger a service broker message to be sent off.  We usually have a few hundred threads logging in and out of the database at any one time.

This required that I take Remus’s solution and make it more flexible before moving it into our environment.  The solution that I’ve come up with supports a single conversation for each process within our application.  And at random intervals those conversations are closed, and new ones are started. Continued »

Orange County SQL Server User Group has started up

Orange County now has its own SQL Server User Group.  It is being run as a part of the Orange County .NET Users Group.  The first speaker was my friend Lynn Langit last month on August 14th.  I’ve been asked to be the next speaker on October 16th.  The meeting starts at 6pm and runs until 9pm.  The meeting is being held at New Horizons in Anaheim at 1900 S. State College Blvd near the corner of Katella Ave.

I’ll be talking about Clustering SQL Server.  It is going to be similar to the session about Clustering SQL Server which I’ve given at the prior SoCal Code Camps. If you would like a preview of the slide deck it is available for download.  If I change the slide deck any I’ll update the version available for download.

If you are not able to make it to the session feel free to post your questions below and I’ll answer them as quickly as I can.

Denny

Service Broker may not remove messages after processing

I’ve seen an issue with Service Broker that others may be seeing.  Messages are sent into the service broker, and are processed as normal, and they are removed from the message queue.  However you still see the conversation in the sys.conversation_endpoints DMV in a CONVERSING state instead of a CLOSED state.  There are no records in the sys.transmission_queue which is the very strange part.  There are also no errors when checking with SQL Server Profiler.

 Apparently this is a known issue which they are working on.  The strange thing is that when it happens on my system, it only happens on a single queue in my database.

Currently the only workaround is to do an END CONVERSATION WITH CLEANUP on the conversations.  I’ve written this script which clears out the conversations.  I’ve made it so that it only removes the messages which are for the problem conversation which don’t currently exist in the queue (this queue is not auto processed, there is a service which queries the queue every 30 seconds so there can be a backlog of valid messages in the queue which I don’t want to delete).

declare @i int
set @i = 1
while @i <> 10000
begin
  declare @conversation_handle uniqueidentifier
  declare cur CURSOR for
  SELECT TOP (1000) conversation_handle
  FROM sys.conversation_endpoints
  WHERE NOT EXISTS (SELECT *
  FROM [tcp://AWT/Sonar/Q_ObjectDelete] a
  WHERE a.conversation_handle = sys.conversation_endpoints.conversation_handle)
  AND sys.conversation_endpoints.far_service = ‘tcp://AWT/Sonar/Svc_ObjectDelete’
  AND sys.conversation_endpoints.state <> ‘CD’
  open cur
  fetch next from cur into @conversation_handle
  while @@fetch_status = 0
  begin
  end conversation @conversation_handle with cleanup
  fetch next from cur into @conversation_handle
  end
  close cur
  deallocate curset @i = @i + 1
endI run this every hour to clean up the bogus records in the sys.conversation_endpoints DMV.

Without cleaning up the sys.conversation_endpoints DMV the tempdb will slowly start to fill up and throw out of space messages while sp_spaceused shows that the tempdb is empty in the same that id did in the other post I did a while back.

Denny