slideshow 1

I am an independent data consultant. Here you'll find a collection of my thoughts, mostly on data architecture and SQL Server. The links above will take you to my blog, my open source projects, and presentations I have delivered that you may be interested in.

Data is one of your company's most valuable assets. I can help you make the most of it.

slideshow 3 slideshow 2

Fun With Transactions

Demo files can be found here

This is a short presentation on some of the confusing aspects of transaction handling in SQL Server.  We start with transaction naming:  

Transaction Names

Even on a case-insensitive database this will fail:  

But this will not fail:  

And even this will work because transaction names are totally ignored when issuing a COMMIT:

Autonomous Transactions

A sorely missed feature of SQL Server is native "autonomous transactions."  This is where you "pause" the current transaction and spin up a new transaction.  When the new transaction "completes" (commits or rolls back) then we continue the paused transaction.  Some use cases:

  • out-of-band logging/auditing
  • custom sequence generators

.NET and Java developers have the concept of .REQUIRES_NEW to "pause" an existing transaction and start a new, autonomous transaction.  Under the covers this is implemented as a new spid with potentially different transaction semantics.  I give some demos on how to use Linked Servers in SQL Server to generate your own autonomous transactions. 

Implicit Transactions

Implicit Transactions (IT) cause inordinate confusion.  At their worst they can cause what appears to be massive blocking events on your server.  Most developers and data access technologies do not use IT, the notable exception being the Java world (Spring JDBC, WebSphere, etc).  When SET IMPLICIT_TRANSACTIONS ON is set a transaction is started for you...if it is needed.  That "if" statement is what causes problems because the "if" assumptions are not properly documented by Microsoft.  If your driver forces you to use IT then ensure that whenever a connection is pulled from the pool that sp_reset_connection is being called.  This will reset the IT setting.  If this isn't done then you can "leak" connections where a developer might have turned OFF IT to get finer-grain control.  That's a big problem.  

I have lots of demos on the confusing aspects of implicit transactions as well as how to monitor your server for IT anomalies that may cause blocking or "abandoned spid" problems for you.  

Demo files can be found here



You have just read "Fun With Transactions" on If you found this useful please feel free to subscribe to the RSS feed.  


Presenting at PSSUG next week

On Tuesday, December 2, 2014 beginning at 5:30 I'll be doing a short presentation on "Transaction Trivia" (I'll probably come up with a better title later) at the monthly PSSUG meeting at Microsoft's Malvern office.

Service Broker Demystified - Services

This is another post in my Service Broker Demystified series.  Services and [DEFAULT] contracts can be a bit confusing.  Let's see if we can clear things up.  I already covered the basics of Services and Contracts here and here.  If you want to follow along with the examples for this post you can download the repro script here.  

A bit of trivia first...what is the difference between ServiceA and ServiceB in the screenshot below?  You may be looking at that and thinking, "how can two services point to the same queue?"  It's perfectly legal.  We'll cover some (terrible) use cases for that below.  For now I'm merely interested in understanding what [DEFAULT] is going to do to ServiceB to make it work differently from ServiceA.  

In previous posts we noted that the [DEFAULT] contract is not the default contract on a service.  But what does that mean regarding functionality?  

I can tell just by looking at these services that ServiceA is an initiator-only service and ServiceB might be an initiator or a target service.  [DEFAULT] tells me that.  


Let's take a step back.  An SSB Service can be thought of as a set of tasks that includes where to route and queue a message, any contracts that may be enforced, and whether or not an "activator" is fired when a message is enqueued.  The "[DEFAULT]" listing indicates ServiceB enforces the [DEFAULT] contract.  [DEFAULT] is not the same as DEFAULT.  The latter is a keyword in SQL Server that indicates a value that should be used when no value isspecified.  The former is the name of a contract in SSB that has very distinct characteristics.  Specifically it uses validation of NONE...meaning any value is allowed.  

Modeling Send-only Services

So, how do I know ServiceA is an initiator only? Initiator services are not bound to a contract. Why?  Initiator services are allowed to send to potentially many target services, each with its own set of possible contracts.  Let's prove that ServiceA is a send-only service and ServiceB can either send or receive.  You can download the repro script from this link.  

Here we send a message from ServiceA to ServiceB.  Note that the message reaches the destination properly and without error.  

Since QueueA is shared between two services it's a bit difficult to know that in fact the single message in QueueA is the success message vs a failed delivery message from ServiceA.  In this case we also interrogate sys.transmission_queue to prove everything worked.  

Let's see what happens if we attempt to send from ServiceB to ServiceA.  We know that this should fail because ServiceA is not bound to any contracts, not even [DEFAULT].  

...and here we can clearly see that QueueA now contains the Error as well as the Success! message from the last experiment.  Note that sys.transmission_queue logged the error as well...  

...if we expand the XML error message we see exactly why ServiceA cannot be a target...

So you create a send-only service by not binding anycontracts to the service declaration.  

Modeling Receive-only services

We proved that it is possible to model "send-only" services.  Having services with the ability to only initiate a dialog means that we have another check to ensure no one writes code that is accidentally starting dialogs to services that will never process the messages.  Can we create receive-only services to ensure that developers don't accidentally send from a service where that was never the intention?

Let's try (you can follow along with the repro script).  

Here we created a ReceiverSvc bound to ReceiveOnlyContract.  The intent was to force ReceiverSvc to NOT be able to initiate any dialogs.  Don't get caught up in how I did this because the fact is, nothing will prevent a server from initiating a dialog if someone attempts to do that and has the necessary authorization.  Your odds also improve if you do NOT specify a contract on the dialog or a message type on the SEND statement:  

Note that this does work:  

There is no possible way to ensure that your receive-only services do not accidentally start dialogs due to programmer error.  You simply need to "handle" these cases in your activator procedure on your receive-only services.  

Don't create God Objects in your SSB designs

I've seen some SSB implementations where there is one "master service" that performs lots of little tasks.  I think this is bad design.  Essentially you differentiate what each of these little tasks does by using different Contracts and Message Types.

In this example (follow along with the repro script) there is one big 'ol EnterpriseSvc that services one big GenericQ.  This is an example of the "God Object" anti-pattern.  There are lots of contracts and message types that will handle doing the heavy lifting, but all embedded in one giant "class".  Assuming GenericQ was an activated queue you would process your queue by dequeueing messages in the activator and then determining the business logic based on the Message Type (which I omitted in the screenshot).  This pattern has scalability issues baked-in.  If a given contract/message type begins to overwhelm the service there is no way to segregate that service on a different instance or processing schedule.  

I went down a tangent a bit there.  The point of this post is that EnterpriseSvc, as declared, can be an Initiator or a Target.  When possible, I like to model my services such that they are either Initiators or Targets, but not both.  Then I don't need to worry about a codepath accidentally sending a message to a service that should be only an Initiator.  I view Initiator services as nothing more than pieces of code that want to invoke a workflow (generally from a stored proc).  But I don't want to accidentally have an Initiator receive a message.  Unfortunately there is no way to do this.  


In this post we covered the differences between send and receive services.  Send-only (initiator) services have no contract bound to them.  Send-and-receive services have at least one contract bound to them which can minimally be [DEFAULT].  The key takeaway is that an iniitating service does not require a contract to be specified, which makes it only able to receive messages in response to a conversation that was initiated from it.  There is no way (that I've ever found) to model true, receive-only services.  

You have just read "Service Broker Demystified - Services" on If you found this useful please feel free to subscribe to the RSS feed.  

SSIS and data contracts

Have you ever tried to use a stored proc with a temp table in an SSIS package?  Did it work?  SSIS is severely hampered by the fact that this doesn't work seamlessly.  In this post I'll demo the problem and I'll give you the best fix for it.  The objective is to run a stored proc from SSIS and optionally send the output to a text file. 


  • Create a very simple stored proc.  You can download the repro script here.  Our stored proc simply builds a temp table, inserts a few rows and then reads those rows as a result set to the caller.  No parameters...very simple.  
  • Create a new SSIS package.  
  • Add a Data Flow Task 
  • Double-click it.  
  • Add an "OLE DB Source" to the designer
  • Connect to your instance/db where you have dbo.SampleDataContract installed
  • Change the "Data access mode" to SQL Command and enter the call to our stored proc
  • Click "Preview..."
  • You should immediately generate an error 
  • The error text is:  

Exception from HRESULT:0xC020204A
Error code: 0x80004005
Description: "Invalid object name ''.".
Unable to retrieve column information from the data source. Make sure your target table in the database is available.  

This error occurs because you decided to use a temp table (#temptable...the kind in tempdb) somewhere in the text of your procedure.  That's not allowed.  SSIS (and SSRS and a number of ORM tools, etc) attempt to read the metadata about your procedure to determine what the output will look like (number of columns, names, and data types).  The error is telling you that the metadata could not be found for the temp table that you are attempting to use.  

BTW, this will not happen if you usse @TableVariables.  However, there are performance ramifications of using table variables in some versions of SQL Server.  

Data Contracts to the Rescue

I've written about Data Contracts for Stored Procedures before.  Quick the Oracle world you CANNOT return a result set from a stored procedure.  Instead you create a package to do this.  In the package "header" you declare the "data contract" (inputs and outputs, including ordinal position, name, and datatype).  Then, in the package body you write the actual code and that code must adhere to the declared data contract.  In the example, emp_actions is the package and it implements 2 procedures, a cursor (which is nothing like a SQL Server cursor, it is more like an ADO cursor in that it is a "CURrent Set Of Records" a result set) and the output format of the cursor, which is known as a record type.  Note that the package body actually outputs the data when someone queries for the desc_salary result set.  

When I first started working with Oracle I thought this syntax was EXTREMELY cumbersome.  Too much typing of redundant information (a CURSOR is declared over a TYPE and then the actual query has to be bound to the CURSOR and to the TYPE.  Too much can go wrong here.  

I thought this was cumbersome until I started working with SQL Server in the Nineties.  SQL Server then only partially declared the full data contract.  Stored procs declared the inputs but not the outputs.  In later releases M$ graciously gave us functions and table-valued parameters which FULLY implement data contracts.  But they never fixed procedures to fully support data contracts.  Why do we need data contracts for stored procs?  See this connect item for a list of reasons...but having a true data contract solves the SSIS problem I noted above.  In this post a blogger lists exactly what he would want to see in a fully implemented data contract for stored procedures.  That's a great list.  

While having full-blown data contracts would be nice, you don't have to wait for M$ to roll your own data contracts and solve some of these issues.  Here's how I do it.  At the top of your stored procedure add a little code block that can NEVER execute that defines your data contract.  When these "smart tools" like SSIS, SSRS, and most ORMs try to read your metadata they see that you have a SELECT statement and determine that is the "shape" of the result set.  Even though the SELECT code block is in a IF block that will never be executed (note the IF 1 = 0).  Just doing that little bit of work is enough to fool EVERY "smart tool" that looks for result set metadata.  

Mocking up a data contract really fixes this?

Yes.  Let's demo.  Go back to SSIS to your OLE DB Source component.  Click the "Preview..." button again.  Last time we got an error.  This time we get our result set as expected.  


Creating your own mocked-up data contract is an easy way to overcome tools that attempt to read the metadata of your procedures and fail because they contain references to a temp table.  If your SSIS packages require the IsSorted flag you can even put an ORDER BY clause on your "data contract" and your output will automatically have the IsSorted flag set.  


You have just read "SSIS and data contracts" on If you found this useful please feel free to subscribe to the RSS feed.  

Service Broker Demystified - Why do we need Services and Queues?

People claim that they don't want to use Service Broker because it is too complicated. I started a blog series called Service Broker Demystified because SSB really isn't that difficult if you understand some basic concepts.

Today we are going to cover why we need both Services and Queues.  

Let's start with some definitions.  A Service is used to route messages to the correct queue and enforce any contracts for a message.  Remember that contracts are optional anyway so the only purpose of a service is to route the message to the correct queue.  A Queue is where messages are stored and then processed based on rules you create.  The queue is where all of the magic and important stuff occurs.  99% of what you code in Service Broker will be functionality involved with queue message processing.  

In most SSB designs (probably 95% that I've worked with) there is a one-to-one correlation between services and queues.  Why do we need to have both?  Is there any benefit to having both?  Service-to-queue correlation is just one more of those things that adds to the confusion around SSB.  

Remember that services route messages to queues.  So, in some advanced designs you may want to change which queue processes your messages based on some event.  You can therefore change the queue associated with your service and then the queue-processing logic can be different on the new queue.  You can do all of this without changing your application code, which is coded to a specific SERVICE, never to a specific QUEUE.  So this could potentially help in "versioning" your SSB design but there are far better ways to do that.  

This "new" queue can be a different queue in the same db or on an entirely new SQL Server instance on the other side of the world.  Hopefully you see that separating Qs from Services gives your design a bit of resiliency and failover.  The CREATE ROUTE command helps you here too.  You can map a SERVICE to a new SSB instance somewhere else.  But that is a complicated design that most noobs needn't worry about.  

I can't think of any good reasons to model "multiple Queues per Service" and I've never seen one in action.  "Versioning" would be one use case, but not a very good one.  

The other possibility is to model "multiple Services per Queue".  I have seen people model their designs this way, with disastrous results.  In this model you have many services, each with possibly multiple contracts, that all dump their messages to a single "Enterprise" Q.  The payload of all messages in the Q is similar with a "message type" discriminator embedded in the XML.  The activator proc for this Q shreds the XML and looks at the "message type" to determine the processing logic and the originating service.  Basically the activator proc is a gigantic switch statement ("if then" block).  People that model their SSB implementations like this believe that it is convenient to keep all of their activator logic in one procedure.  The disaster strikes when one service begins to overload all processing and we can't throttle it using MAX_QUEUE_READERS.  If each service had its own Q then we could throttle any service hogs by throttling that Q's MAX_QUEUE_READERS entry.  


Services are meant merely as an aid to route messages and enforce contracts.  The bulk of your logic will be concerned with Q processing, the queue being where the message is persisted while it is waiting to be asynchronously processed.  I have never seen a good design that uses "multiple services per queue" or "multiple queues per service" that couldn't be modeled more effectively in another manner.  Just remember that Service and Queue objects pretty much refer to the same thing...a mechanism to route and persist data until it can be processed.  


You have just read "Service Broker Demystified - Why do we need Services and Queues?" on If you found this useful please feel free to subscribe to the RSS feed.  

Service Broker Demystified - Why is there no ALTER CONTRACT statement?

People tell me all the time that they don't want to use Service Broker because it is too confusing.  I started a blog series called Service Broker Demystified because SB really isn't that tough if you don't get lost in the weeds.  Today we are going to cover how you alter a Service Broker (SB) contract. 

Remember from the last post that a SB contract merely lists which services are allowed to use which message types.  Message types and contracts are both totally optional and your SB implementation will run fine without them.  When I'm first learning a new technology I try to avoid overloading my brain with optional features to limit potential confusion.  

A contract can specify multiple message types and who may use them (valid values are ANY, INITIATOR, and TARGET).  Let's start with a little demo.  If you want to follow along the full source code is available here.  Let's say that we've been tasked with designing an asynchronous inventory management system with SB.  Here are some message types that we may decide to create.  Hopefully the names are sufficient to understand the intent of the system.  

And here is the associated contract that handles are inventory workflow (very rudimentary):


Initially the workflow's intention was to have either the inventory requestor or the inventory system be able to cancel an inventory request.  But let's say you decide later that only the sender can cancel an inventory request...the inventory system can never cancel a request, rather it can only delay it. In 
this case you must drop and recreate the can't alter it.  This command clearly will fail:

Instead we must DROP and reCREATE the CONTRACT.  Something like this:  

Note that we have changed the contract such that the INITIATOR is the only party that can post messages of type CancelInventoryItemRequest.  

Why is there no ALTER CONTRACT?

In the real world you don't alter a legal contract.  You generally make a new version with a new set of signatures.  Certainly there are exceptions but contracts aren't meant to be broken.  It's no different in the Service Broker world.  CONTRACTs are not meant to be broken.  Contracts are immutable.  However, situations such as my inventory example are commonplace.  When requirements, and hence CONTRACTs, must change BEFORE the system is released into the wild it is certainly reasonable to simply DROP/CREATE the contract like the above screenshot.  

However, this is frowned upon once other systems begin to send messages to your service using that contract.  Altering the contract could break other systems that are relying on its functionality.  Since there may be a lot of moving parts and different systems with different release schedules you can't guarantee that everyone will be able to alter their functionality due to the change in your contract.  Instead you should consider versioning your contracts and supporting multiple versions of a contract for a certain amount of time.  A simple naming convention like CancelInventoryItemRequest_v1 is usually sufficient.  

So if we should version our CONTRACTs, why not our SERVICEs...or...why is there an ALTER SERVICE command?

Versioning your SERVICEs is probably a best practice as well, but the fact is SQL Server does allow us an ALTER SERVICE command.  Why?  

  • It may be necessary to temporarily alter a service to point to a different queue with different activation semantics.  
  • you can always ADD CONTRACT to an existing SERVICE...meaning that your service can adjust to support new features without service interruption.  
  • at a later date you can also ALTER SERVICE...DROP CONTRACT to deprecate the old functionality.  


So based on how Microsoft architected services, contracts, and message types you can see that serious thought was given to versioning the critical objects to help you ensure no service disruptions when there are changes to your design.  If you understand why MS has given you these options you start to understand better how Service Broker really works.  


You have just read "Service Broker Demystified - Why is there no ALTER CONTRACT statement?" on If you found this useful please feel free to subscribe to the RSS feed.  

Service Broker Demystified - Contracts and Message Types

People tell me all the time that they don't want to use Service Broker because it is too confusing.  There are too many moving parts.  What's the difference between a service and a queue?  How do I monitor Service Broker?  Where's the GUI?  I started a blog series called Service Broker Demystified because SB really isn't that tough if you don't get lost in the weeds.  

I was helping a customer with a new Service Broker implementation of my tickling pattern and I briefly said, "We'll worry about the Message Types and Contracts later...they really aren't required."  The customer immediately stopped me and wanted to know why.  

Many SB experts will disagree with me and state that contracts and message types are not optional...but they are.  You don't need either for any Service Broker implementation.  However, their use will help you avoid problems later.  I consider contracts and message types to be the "foreign keys" and "check constraints" of the Service Broker world.  You don't need FK and check constraints...but when you goof up your code you'll be glad you had them. 

What is a Message Type?

When you send a message to a service by default it will have no message type.  It's up to you, therefore, to "do the needful" with a given message.  Maybe you have a tickler where the message is merely asking the target service to "wake up" and do something.  In this case no message type is needed.  However, for clarity I would still create a message type and call it Tickler with VALIDATION = EMPTY...meaning I'm expecting it to have no payload.  

Perhaps your service is simple enough that you only ever expect one type of message with a little bit of XML to traverse from service to service.  You are fine with not validating your are a trusting soul.  Again, no message type is really needed.  But if you believe in "sanitizing your sql inputs" then you really do want to validate your XML.  In that case, you need a message type that is either WELL_FORMED_XML or, even better in most cases, VALID_XML with SCHEMA COLLECTION.  

So, a message type is a lot like various constraints on a table.  

Type of "constraint" Message Type Equivalent Notes
no constraint on a NVARCHAR(MAX) column NONE With NONE you can do whatever you want with the Message Type payload.  Just like you can do just about anything with an NVARCHAR(MAX) column
CHECK and DEFAULT on a bit column EMPTY EMPTY means the message payload must not contain anything.  This is kinda/sorta like a NOT NULL specification on a bit column with a DEFAULT of 1 and CHECK CONSTRAINT of 1.  The column will be automatically set to the only possible value of 1.  EMPTY is used to flag that an event occurred.  The target service will have defined workflows that will respond to that event.  
a datatype declaration on a column WELL_FORMED_XML When you declare a datatype on a column you are specifying that only certain valid data will be allowed.  In this case only text that can be converted to the xml datatype will be accepted.  
CHECK constraint to enforce a mask on a SSN col VALID_XML WITH SCHEMA COLLECTION This states that the XML must be in a given format, much like a SSN is in the format "123-45-6789".  

A CHECK constraint limits the allowable values in a column to a defined domain.  A Message Type does the same thing for your XML message payloads.  As with a CHECK constraint, a message type is not needed, but you'll find you have better quality data, and less support issues, when you have them properly implemented.  

Whenever I design a new SB implementation I don't concern myself with the message types in the beginning.  I want to get a prototype up and running quickly.  After I have some stability I then add in these constraints.  

What is a Contract?

A contract is a binding agreement between a message type and who can utilize it.  If you don't have message types then you don't have contracts.  Period.  So again, I save contract implementation for a point in the project when I have a stable design.  

Service Broker conversations are always dialogs (that's why the syntax is BEGIN DIALOG CONVERSATION).  A dialogue in the English language, according to google, is a conversation between two or more people.  In SQL Server it is a little more strict...a dialog is EXACTLY two Initiator and a Target.  You can model monologs in Service Broker just like in other messaging systems, but it requires some extra effort and I'll cover that in a future blog post.  (If you can't wait for that post...for a monolog in SB you really need to have an activator on the initiator side that is ENDing CONVERSATION).  Again, we'll cover this in a future post since it is confusing and most noobs model their SB implementations as a series of monolog workflows, not dialogs (just my opinion).  

We got off subject...a contract merely says who can SEND the given message type.  Let's say you have SB services called EmployeeSvc and AccountingSvc.  Let's say you have a message type of ExpenseReportSubmission.  The goal is to allow the EmployeeSvc to submit an expense report to the AccountingSvc.  Let's model this as simply as possible with NO contracts or message types.  You can follow along by downloading the repro script.  

Here is the most basic setup...there are NO contracts or message types defined.  The [DEFAULT] contract is specified and the "contract clause" is necessary whenever you want to create a Service capable of receiving messages.  See my last post for some clarification on that bit of confusion.   If we don't at least provide the [DEFAULT] contract we'll get this error in the sending queue:  

  <Description>Target service 'AccountingSvc' does not support contract 'DEFAULT'.</Description>
Note on Line 35 that our message payload is empty.  That's fine because the [DEFAULT] contract on the receiving service is set to 

VALIDATION=NONE.  Hence we get no error and the message is properly enqueued in the AccountingQ with a NULL message_body.  
Of course this design is certainly wouldn't want to allow NULL messages in an expense submission workflow...but you can when you don't properly declare your "constraints".  You can send just about any message and the AccountingSvc will accept it without validating it.  Here is an expense submission using simple text and note that it too does not fail.  

At this point it would be the responsibility of whatever program or activator is processing the AccountingQ to perform any message validation and send any errors back to the sending service.  But it's probably better design to implement those constraints as message types and contracts.  We do that by creating a MESSAGE TYPE that specifies that we will only accept WLL_FORMED_XML.  We can of course be even more precise, but this should be sufficient to illustrate the point.  We then create a contract that specifies that our new message type can only be sent by the initiator.  Finally we bind the contract to the existing service.  

Let's run a little test.  This time we are going to send some XML (Line 81) and ensure that our message made it to the target service.  Note that the previous two messages, prior to us adding the "constraints" are still available to be processed.  

Let's see what happens when we attempt to send some garbage now that we have proper "constraints"

What?  That actually did succeed!  Why?  Because we did not specify the contract clause of the BEGIN DIALOG statement.  Look around Line 97.  The ON CONTRACT statement is missing. Note that we did include that on Line 78 in the previous screenshot above.  

So, you are probably asking yourself, even with a proper constraint at the target service we can still send garbage?  What's the point of contracts and message types?  With a database constraint the constraint is enforced regardless of whether you specifically ask it to be enforced (yes, I know you can disable a constraint and force it to go untrusted).  

The problem is easily explainable.  Although we bound a contract to our service we did not "unbind" the DEFAULT it is still valid.  We can query the SSB metadata to prove this:
So, we need to specifically DROP the [DEFAULT] contract.  Let's do that and retest.  
  <Description>Target service 'AccountingSvc' does not support contract 'DEFAULT'.</Description>


So now the error is being thrown correctly and we can no longer use the [DEFAULT] contract.  

Here's one place where the "constraint" analogy for contracts and message types breaks down.  Note that the message did not ERROR for the sender, rather it was simply put in sender's Q as an error, as well as being noted as an error in sys.transmission_queue.  This is another case where SSB confuses noobs.  As long as your BEGIN DIALOG syntax is correct you can always send a message without the sender seeing the error.  Instead, the error causes the message to be retained in the sender's queue and sys.transmission_queue.  
We covered a lot of ground today around Contracts and Message Types.  Both can be confusing until you remember that they are optional Service Broker objects.  Everything will work just fine without them, but will work better with them.  


You have just read "Service Broker Demystified - Contracts and Message Types" on If you found this useful please feel free to subscribe to the RSS feed.  

Service Broker Demystified - [DEFAULT] is not the DEFAULT

People tell me all the time that they don't want to use Service Broker because it is too confusing.  I started a blog series called Service Broker Demystified to dispel these misconceptions.  Today we are going to cover the [DEFAULT] objects, which are not really default objects...sometimes. 

Quick review...a contract binds a message type (defined as the name of a message and how it is validated) to who may send it during a dialog.  A service is defined as a set of tasks associated to a queue with a defined contract.  Looking at the syntax of each SSB object might make it easier to comprehend.  

Message types and contracts are always optional SSB objects...we'll cover why that is in the next post.  But you should also note in the syntax above that the word DEFAULT is placed in square brackets.  That is because DEFAULT not a keyword in SSB, it is the actual name of objects that are, by default (...couldn't resist), shipped by Microsoft.  

I ran the follow queries on a database without SSB enabled.  Here is the repro code if you want to try for yourself:

So, the syntax for CREATE MESSAGE TYPE and CONTRACT indicates that there are message types and contracts named DEFAULT...and we confirmed that by querying the SSB metadata.  

But what is the DEFAULT...what does it mean?

  • The DEFAULT message type, as you can see in the screenshot above means there is no validation done.  By the way, this actually *is* the default behavior for a message type that did not specify the optional VALIDATION clause (see the screenshot above).  
  • The DEFAULT contract uses the DEFAULT message type (meaning there is no validation done).  However, in this usage the [DEFAULT] object is not the default and MUST BE SPECIFIED to actually be used.  If you don't believe me just look at the syntax again. Optional syntax is in square brackets and the message_type_name clause is NOT in square brackets.  

Let me repeat, the fact that these objects are named DEFAULT is a misnomer...they are not actually the defaults.  This leads to confusion and errors for the uninitiated.  Let's create the simplest possible SSB solution to illustrate this fact...a simple employee expense submission.  Here's the code to follow along:  

Here we avoid all of this DEFAULT confusion by simply not using contracts and message types...they are optional anyway (see next post).  We create two services and queues...the EmployeeSvc will enqueue a message to the AccountingSvc.  DEFAULT is nowhere to be found in the code.  On Line 33 we start the code to send a message.  Note on Line 38 that we turn off encryption to avoid that headache (see the last post).  Note that the message has no payload (Line 40).  

This seems to me that it should work.  But it doesn't.  

The actual transmission error is "Service Broker received an error message on this conversation. Service Broker will not transmit the message; it will be held until the application ends the conversation."  The message is "stuck" in the sending Q (EmployeeQ).  The error embedded in the XML is "Target service 'AccountingSvc' does not support contract 'DEFAULT'.".  

So, there you have it...we did not specify the [DEFAULT] contract, therefore we cannot use the [DEFAULT] contract and it generates an error.  If you haven't fallen asleep yet you are probably thinking back to the syntax for the CREATE SERVICE command (screenshot to the right).  The "contract clause" is indeed listed as optional...and we were able to create the AccountingSvc without what is going on?  

When you create a service and do not specify the "contract clause" then the resultant service is a "send only" service.  It cannot receive messages.  This is because a sending service (or any service really) may potentially send to many different services each with its own contracts and message types.  So we can't really restrict a sending service at all.  All validation occurs on the receiving service.  So when we created our two services we really created two sending services.  SQL Server assumes we knew what we were doing.  

So, the next question you probably have is how do we get our SSB design fixed and working as desired.  All we really need to do is, minimally, declare that the [DEFAULT] contract should be used for the AccountingSvc, thus making it a target, with the [DEFAULT] validation, which is validation NONE.  Let's see if that works.  You can download the code to try it yourself:    

It does work.  So merely adding ([DEFAULT]) was enough to allow the Svc to receive messages.  

Of course this isn't the most ideal setup since [DEFAULT] implies that there is NO VALIDATION.  This means we can send just about anything and AccountingSvc will accept it.  

The first message is the "no payload" message.  The second is some meaningless text.  


So the takeaway is that the [DEFAULT] contract is not really the default contract for a service.  You must still declare it.  If you don't then you created a send-only service.  In the next post we'll cover why contracts and message types are optional, which builds on the concepts from this post.  


You have just read "Service Broker Demystified - [DEFAULT] is not the DEFAULT" on If you found this useful please feel free to subscribe to the RSS feed.  

Service Broker Demystified - SET ENABLE_BROKER vs NEW_BROKER

Do you think Service Broker is confusing?  If so, I started a blog series called Service Broker Demystified because SSB really isn't that difficult.  Today we are going to cover what happens when you restore a database that contains Service Broker objects.  When you run ALTER DATABASE SET NEW_BROKER, in other words enable SB for the first time...SQL Server creates a service_broker_guid for your database as well as a flag that indicates Service Broker is enabled.  You can also configure your SQL Server to have all new databases automatically have SSB enabled.  

In the screenshot you'll see that I have a number of databases that have SSB GUIDs, but SSB is not enabled on any of them.  

First, let's cover what the GUID is for.  There is no requirement that the service_broker_guid be unique on a given instance.  This is easy to prove.  I have a repro script that you can download and try for yourself.  Or you can just follow along with the screenshots.  

First I create a database called SBTestOriginal and enable SSB.  Then I verify that SSB is enabled and determine the GUID.  We take a backup and restore it on the same instance as a copy with a new name of SBTestNew.  

The process of restoring the database did NOT change the GUID...but it did disable SSB.  So, we'll need to fix it if we want to use our services and queues in that database.  

Let's assume your SSB infrastructure runs totally intra-database, which is the case for most people.  After restoring a db you probably just want to run ENABLE_BROKER.  But you can't...

The reason is that the "assigned GUID" for the copied db is the same as an existing GUID on the instance.  So, the correct process is to SET NEW_BROKER.  

Note that now SSB is "reset" and we have a brand new GUID.  

But this raises another question...if I am forced to run SET NEW_BROKER in a restored db, what happens to "in-flight" messages in existing queues?  Are they destroyed or do they restart themselves automatically once SSB is again enabled?  Before we run a test to see the behavior let's think about what should happen.  When you ENABLE_BROKER then everything should just pick up where it left off.  But NEW_BROKER implies that everything should be cleaned up.  This makes sense from a safety perspective too.  If you restore your prod db to a dev system you want your in-flight messages to be destroyed so you don't accidentally continue processing them on a dev instance.  Let's see if I'm right:  

We are going to take the existing example and merely create a test service and queue and then enqueue a message before we take the database backup and restore it. 

Note that our message was successfully enqueued and is waiting for processing.  Now we backup and restore the db as a COPY to the same instance and we SET NEW_BROKER.  And then we run some code to see if our message is still awaiting processing in the new db:  

The contents of the queue are destroyed when we SET NEW_BROKER, as expected.  Nothing in the queue and nothing "stuck" in sys.transmission_queue.  Note also that are queue is ready for processing.  The message is gone.  It isn't logged anywhere, it is just destroyed.  

In general I think this behavior is the desired behavior in most use cases.  But this behavior is important to understand.  Depending on your messaging infrastructure and design you may need to apply some "compensating" logic on your queues before you simply SET NEW_BROKER.  For instance, it is common to design workflows in SSB that entail many messages going back-and-forth between services.  In those cases you may have transactions in a goofy state when you re-enable SSB.  You need to think about this and work with your DBAs to determine what the correct process should be when you restore databases with SSB enabled.  

This doesn't necessarily mean manual intervention by a DBA.  I'm a strong advocate of automation and I've always used a "SSB enable" procedure that I tell my DBAs to run WHENEVER they restore a db.  A script like this should be responsible for

  1. if needed, handling compensating logic BEFORE SET NEW_BROKER is run.  Once it is run any old messages are gone forever.  
  2. smartly determining if ENABLE_BROKER can be run, or if NEW_BROKER is required.  
  3. the db must be in SINGLE_USER mode (or equivalent) when these commands are run.  
  4. you may also need to set TRUSTWORTHY flags and deal with encrypted database master keys, depending on your requirements.  

You can download the script I use to enable Service Broker on a restored db.  It doesn't handle every scenario above, but it gives you a framework to plug in values specific for your needs.  

Whether you are a DBA or a SSB designer you need to be aware of what happens when you restore your database.  It can be a bit tricky and you don't want to lose important message data.  

You have just read "Service Broker Demystified - SET ENABLE_BROKER vs NEW_BROKER" on If you found this useful please feel free to subscribe to the RSS feed.  

Service Broker Demystified - Encryption Weirdness

Do you find Service Broker confusing?  I started a blog series called Service Broker Demystified because SB really isn't that tough if you don't get lost in the weeds.  Today we are going to cover the first thing that trips up noobs...issues with encryption.  

Service Broker requires communications to be encrypted by default.  This makes perfect sense to even the newest noob.  When transmitting data over-the-wire it should always be encrypted.  Of course if I'm communicating with other services I should encrypt those communications.  However, most noobs model their first SB designs as simple intra-database/intra-instance asynchronous workflows.  For example, most people first learn of SB when they want to model an asychronous trigger to do some near real-time ETL processing.  In this case no data is communicating over-the-wire so why should encryption be needed?

Before answering that question, let's see the problem in action.  You can download the repro script to play along at home.:

We first create a database and SET NEW_BROKER.  We then build two services and two queues, one with be the Initiator and the other the Target.

If you are experienced with SB you can probably just look at code and, knowing the setup, realize that it will generate an error.  But noobs will look at this and think that this looks like valid code...and it is, but it will still error.  We can query the queue and see the message failed and then look in sys.transmission_queue and see the error:

"The session keys for this conversation could not be created or accessed. The database master key is required for this operation."

So, in the case of a simple intra-database/intra-instance SB design, you still need to setup encryption.  

The fix is to run code similar to the following:  

FROM sys.databases
WHERE name = DB_NAME()
is_master_key_encrypted_by_server = 0)
PASSWORD = '8uY75%jj5k5#%#'

This creates an encrypted database master key.  If you run the demo again you should see the message properly enqueued.  

A Workaround

There is a workaround that's a bit easier.  You can download the repro script here.  

All I did was add WITH ENCRYPTION = OFF.  

There may be other workarounds but this is the workaround I use when I don't have the permissions available to create encrypted database master keys and I know my SB design will ALWAYS be intra-db/intra-instance.  

In the case where no data is communicating over-the-wire, why is encryption required?

This starts to get REALLY confusing.  Here are some rules:  

  • the default ENCRYPTION option is ON which I think is wise...better to be safe than sorry.  The side-effect is you have to deal with the encrypted database master key errors.  
  • If the two services are intra-db then communications are NEVER encrypted, regardless of the default setting.  Why?  Because the "communication" occurs as an IPC (in-memory) and no database traffic traverses the wire.  So why is encryption needed in this scenario?...see the previous bullet point...that's the default!!!
  • If ENCRYPTION is OFF but you are communicating to a remote service and the proper certificates are configured, encryption will be used anyway.  So using ENCRYPTION = OFF simply avoids the error message when the proper certificates are not available.  Very wise.  

Hopefully encryption is a little less baffling now.  

You have just read "Service Broker Demystified - Encryption Weirdness" on If you found this useful please feel free to subscribe to the RSS feed.  


Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer