Service Broker

Join me at the San Gabrial Valley .NET User Group Meeting on November 18th

For those not at PDC or the Underground party on November 18th, 2009 …

Join me at the San Gabrial Valley .NET User Group as I give a presentation all about SQL Service Broker.  The pizza starts at 6pm and the presentation starts at 6:30.  The groups site says that there is no fee to attend as the event is being sponsored.  I don’t see any RSVP info on the site, so show up ready to learn.

In addition to what the UG will be giving away, Quest is sending me over some goodies to give away (don’t ask me what, I’ll find out when the box arrives).  Thank you Quest.

I’ll see everyone there.

Denny

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