A SQL Server 2008 R2 change for Service Broker

There's another change to the SQL Server database engine in SQL Server 2008 R2. This concerns poison-message handling behavior in Service Broker applications. Service broker's messaging is always transactional. A RECEIVE SQL statement is transactional and can be combined with other database operations as part of a transaction. For example, you can code a RECEIVE […]

Broker External Activator (and more) in latest SQL Server 2008 feature pack

I'm not usually one to post product announcements, but I had to point out that the Service Broker External Activator shipped with the latest (Oct 2008) version of SQL Server 2008 feature pack. It's available here. Doing a quick "visual diff" with the August 2008 feature pack, I also notice some new PowerShell extensions, SQLXML […]

Service Broker in SQL Server 2008 – When sent message and receive message priority are different

More about Service Broker priority in SQL Server 2008. The books online states, when setting a initiator/target priority as local service name/remote service name that the priority affects: 1. Sends from the initiator queue 2. Receives from the initiator queue 3. Getting the next conversation group from the initiator queue. And specifies the mirror image […]

Service Broker in SQL Server 2008 – Priority By Contract

Conversation priority is a new feature with SQL Server 2008. In a previous blog post, I talked about how to simply set one up. But you'd usually not want to set up a priority for all messages. So, lets set up a simple service and then define a "Premier Customer" priority. Before trying this you […]

Service Broker Priorities in SQL Server 2008 CTP5 – An overview

SQL Server 2008 adds the concept of priority for conversations. It's setup using special DDL statements, priority cannot be specified on the CREATE DIALOG CONVERSATION or SEND/RECEIVE DML statements. The DDL statements are CREATE/ALTER/DROP BROKER PRIORITY. To specify a priority, you associate a BROKER PRIORITY object with combinations of the qualifiers LOCAL_SERIVCE_NAME/REMOTE_SERVICE_NAME/CONTRACT and the priority is […]

A supported Service Broker external activator?

Had to write about another thing that "caught my ear" at TechEd during a chalktalk by Rick Negrin about Service Broker usage patterns. Service Broker supports "internal activation", that is, associate a stored procedure that gets invoked when a queue has messages to process as well as "external activiation". When using external activation, an event […]

The Internals and Troubleshooting whitepaper is available

The companion whitepaper to my "Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005" whitepaper (see yesterday's post) is available. This whitepaper is called "Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005", with as much about internals as I could cram in 50 pages. Again, I don't have […]

The first of my scaleout whitepapers is available

I've been working on some whitepapers on scaleout technologies in SQL Server 2005. The first whitepaper is now available; I don't have the main link, but here is the direct link to the doc file on the Microsoft download site. The whitepaper is about the implementation steps when using scaleout technologies like Service Broker, Scalable Shared […]

Service Broker: When is DEFAULT not the default?

When I first heard about it, I didn't think the DEFAULT contract in Service Broker had the potential to cause confusion. Goes to show what I know. The problem comes up when you assume "no specification" means "use DEFAULT". Not always… Service Broker contracts indicate which message types can be sent from the conversation initiator, […]

Musings from the Immersion Event

Just got back from the SQLskills Immersion event in Chicago. There were a couple of bonus sessions, with Kimberly doing a high-availability talk for Microsoft customers and our students one evening, and Joe Celko making an appearance on Wedsnesday. Joe spoke on elements of SQL style, and I was able to acquire a signed copy […]

What’s that stuff good for, anyway?

There's an interesting article that caught my attention in this month's (July 2006) MSDN magazine by John Mollman about building the MSDN aggregation system. The system uses SQL Server 2005 Service Broker for reliable messaging, activation procedural written in T-SQL, SQLCLR for interactions with the world outside the database and the XML data type and […]

Technet Webcast Series for the ITPro – Part 6

I just finished my second talk in the “designing and managing scalable systems with SQL Server 2005” webcast series. I got to talk about one of my favorite features in SQL Server 2005, the Service Broker. Some DBA may still think that Service Broker is a developer-only feature, but after you've seen what you can […]

Service broker and database master keys

Service Broker security is the subject of confusion even among people who think they know how it works. Some of the confusion occurs because security was tightened up in the last few CTPs. I've read in two different places that Service Broker conversations always need to have a master key in the database(s) where the […]

SQL Server 200x wish list – part 3, Service Broker

This one is about SQL Server Service Broker. It's not necessarily a cool or sexy feature, but may be the most revolutionary feature in there. The problem with Service Broker is that most people don't get it. They think YAQS (yet another queuing system). It's more than that. It's already used for 3 features inside […]

Service Broker’s new poison message handling

In this last entry on Service Broker enhancements I inadvertantly referred to the new poison message handling as poison conversation handling. Well, maybe it wasn't so inadvertant. So what's the difference between Service Broker's poison message handling and traditional poison message handling? A poison message is a fact of life in transactional messaging. When a […]

New Service Broker features in the Feb CTP

I've been doing some experimenting with the new SQL Server Service Broker features in Feb CTP. You can read about them in the CTPNotes.doc file; I won't repeat the information here. The features are: 1. Improved Endpoint Security – authentication option NONE is not longer supported 2. DEFAULT message type and contract 3. Poison conversation […]

First release of Service Broker Explorer

My cohort, Dan Sullivan, has released the Service Broker Explorer on his Service Broker Developer's Spot website. It a graphic user interface for Service Broker that has some “topology map” features and configuration features and some management features for Service Broker objects. According to Dan: “It lets you drill into Sevice Broker and add and […]

Service Broker object names are case sensitive

One of my students last week noticed that using a Service Broker object name (like a CONTRACT, SERVICE, and MESSAGE TYPE name) with the wrong case caused an error message. That's because Service Broker object names are case sensitive by deisgn. Because these identifiers can go over the wire, and you can't predict the collation […]

.NET-based library for Service Broker

I was browsing through some of the SQL Server 2005 code samples today (the ones that come with the product) and came across one that was a really nice idea. It's a library to encapsulate SQL Server Service Broker T-SQL calls in an object model, called ServiceBrokerInterface. Some of the other Service Broker samples are […]