Niels Berglund
SQL Down Under Show 5 - Guest: Niels Berglund - Published: 13 Aug 2005
SQL Server identity Niels Berglund discusses why Service Broker is so important, each of the objects that needs to be configured, the typical lifecycle of a message and the GUI administration tool he
Details About Our Guest
Niels Berglund is a member of Develop Mentor’s technical research, curriculum development, and teaching staff. He specializes in the .NET system software and in database areas, and is the co-author of Develop Mentor’s database courses. Niels speaks regularly at various industry conferences, he writes technical articles for the trade press, and is the co-author of the first book released for SQL Server 2005 which was “A First Look at SQL Server 2005 for Developers.” Before joining Develop Mentor, Niels worked as a consultant specializing in design and development of distributed applications mostly to the financial industry.
Show Notes And Links
Show Transcript
Greg Low: Introducing Show #5 with guest Niels Berglund.
Greg Low: Our guest this evening is Niels Berglund. Niels is a member of Develop Mentor’s technical research, curriculum development, and teaching staff. He specializes in the .NET system software and in database areas, and is the co-author of Develop Mentor’s database courses. Niels speaks regularly at various industry conferences, he writes technical articles for the trade press, and is the co-author of the first book released for SQL Server 2005 which was “A First Look at SQL Server 2005 for Developers.” Before joining Develop Mentor, Niels worked as a consultant specializing in design and development of distributed applications mostly to the financial industry. So, welcome Niels.
Niels Berglund: Thanks, Greg.
Greg Low: I first came across you in the Beta newsgroups in SQL Server 2005. Even though tonight we’ll be talking about the Service Broker specifically, I notice you also have a strong interest in the CLR integration areas as well.
Niels Berglund: Yes. I’m interested in the .NET development side and the Service Broker side of SQL Server 2005.
Greg Low: Service Broker, as well as CLR integration, is a whole new area for the product. I was in one their new industrial strength .NET classes, talking with Darren Neimke, an ASP.NET MVP, and we had a look at Service Broker. He was very surprised with its capabilities and interested in how it potentially overlaps with other products. How do you see Service Broker fitting into the product line? And maybe introduce it and what it does.
Niels Berglund: Service Broker is basically a messaging framework between SQL Server 2005 databases. Service Broker allows us to send messages from one database to another and these databases do not have to be located on the same machine or in the same instance. They can be totally disparate databases. It’s a messaging framework but its doing much more than that.
Greg Low: I gather the messages are XML based messages, so I’ve seen from the definitions of the messages?
Niels Berglund: Not necessarily. You can send whatever type of message you want to. Under the covers Service Broker sends it as binary globs, basically, but you can choose to send XML messages in XML format—we’re living in the age of XML anyways—or binary formats, or any format you’re interested in.
Greg Low: Why do you think there’s a need for a messaging framework like this in the database when you compare it to other parts of the product line like MSMQ or even BizTalk?
Niels Berglund: Good question. Why do we need Service Broker? A problem that Service Broker solves today is, say you have your MSMQ application and a SQL Server database and your workflow, your business process, requires you to do transactional messaging. For that to happen using MSMQ and SQL Server we will be doing two-phase commit transactions and from a performance point of view that is kind of heavy. With Service Broker everything is in the database so when we’re doing transactions we’re doing normal, local transactions which are much more lightweight than a distributed transaction.
Greg Low: How does the new system transactions name-space fit with that? Would that have reduced the problem a bit of using DTC?
Niels Berglund: No, it wouldn’t because with MSMQ we need to be sure we’re connecting back on both sides.
Greg Low: Because it’s not in the same object, it still has to be a distributed transaction.
Niels Berglund: Absolutely. You noticed I mentioned it’s in the database and that’s another benefit. Using MSMQ and a database in the background we have two points of backup, two points of restore, and two points of failure. Here everything is in the database. If something goes wrong, when SQL Server comes back up we’re rolling back to whatever state we were in when we went down. We have a much easier administration, so to speak, than we would with two different systems for this.
Greg Low: In terms of administration, do you think the fact that these things are living in database tables makes it easier to manage than MSMQ?
Niels Berglund: The jury is still out on that. Service Broker is not the easiest thing to manage in the world. Yes everything are just database objects and a queue is just a table. So in that way, for a database person it’s probably easier to come to grips with and understand what goes on under the covers. However, from an admin perspective there are things we could wish for.
Greg Low: I notice you’ve been involved with trying to build a GUI interface to help administer Service Broker because the support that’s directly in the product in management studio isn’t that strong to-date.
Niels Berglund: Correct. It’s not there at all, basically.
Greg Low: I was being nice.
Niels Berglund: The background to this is I was doing a lot of Service Broker stuff and I got fed up having to write the same old scripts. All of this is T-SQL based so you’re writing T-SQL statements to do whatever you’re doing in Service Broker, and I got fed up so I started a “small” project where I was going to write this little admin tool which allowed me to create the various Service Broker objects and send messages, etc. And that grew and grew and now we’re talking about an almost full blown admin and management tool for Service Broker.
Greg Low: You might want to mention a URL for people to find that.
Niels Berglund: Yes it’s at 'HTTP://'
Greg Low: Yes, I recall it was a zip file. I’ll dig up the link and put it together with materials from the show anyways. Given the fact that the admin tool assists you in managing it, let’s talk about the objects you need to manage. To set up Service Broker and get it running what do we need? The starting point is the message type?
Niels Berglund: Yes, the starting point is the message type. Service Broker introduces some new database objects and new T-SQL syntax. The smallest unit in Service Broker is the message type. This is nothing else but an entry in a database table telling us what the message we’re sending will look like. Is it an XML message? Binary? Do I send XML? Do I send anything else? Anything at all? Because I could conceivably just send a message header. And if I’m sending XML I can choose if it’s just well-formed XML, in other words just normal XML data type in SQL Server, or if the XML is validated against an XML schema, in which case the schema has to be stored in SQL Server.
Greg Low: I mentioned the schema collections to Kent Tegels the other week. We’re interested in the concept of a schema collection and the fact that it’s a collection, not just a schema, and I’m interested in why people would use more than one schema in a collection. He was suggesting it was mostly versioning. Do you think that’s the main reason?
Niels Berglund: Versioning is a very valid reason. Another reason for this is look at something like a WSDL document. How many schemas does that document contain? So a document could conceivably consist of several schemas that we’re validating against. Then when we’re doing the validation we’re validating against a target name-space.
Greg Low: The idea is when we create a message, we state the name of the message, and what sort of thing will be contained in the message and it can be well-formed XML, binary, or XML that satisfies some schema collection. I also notice that the naming used in the examples tend to look like URLs inside square brackets because they’re using them as SQL Server identifiers. So why do they use URL type names?
Niels Berglund: Yeah, when you’re looking at Service Broker objects in general you’ll see these stupidly long names (“stupidly” is a technical term, by the way) which look like URLs. That’s just for identification purposes, to keep something unique. Just a unique identifier.
Greg Low: It’s just a SQL Server identifier, isn’t it?
Niels Berglund: Yes. Now we can conceivably exchange messages between organizations, between business domains, we need to make sure we’re keeping our message-naming unique, therefore we’re using URL syntax.
Greg Low: I did wonder if there was ever an intention to have them exposed somehow via that URL—something a partner could look up or something like that?
Niels Berglund: For messages I am actually not sure. Talking about services, you can do a service look-up based upon the name of the service. So when you’re naming a service you can indicate there where the service lives.
Greg Low: Okay. Once we have the type of message defined, next thing is the contract?
Niels Berglund: Next thing is the contract. We can have hundreds and hundreds of different messages, which as I said are just entries in a table. We then use a contract to be a little more granular. We are creating a contract saying this contract is using these message types. When I eventually create the service, when we start sending message, I say we’re using a particular contract. By using the contract I then know immediately what message types I can get or not get.
Greg Low: Yes, I get that. So the idea of the contract is really just which messages can be sent by whom and when.
Niels Berglund: Correct. And it doesn’t really say when they can be sent. Yes the contract says this message has to be sent by the side that starts the chain of messages, or this message can only be sent by the receiving side. Other than that there is no real work flow indications here, in the contract.
Greg Low: The times you seem to use the contract, one is when you’re setting up the receiving end to say this is the sort of thing that’s going to come, and also when you’re starting a conversation to say this is the sort of contract I want to use for the conversation. Right?
Niels Berglund: Yes, that is right. You also use the contract when you set up the actual service.
Greg Low: I suppose the service is the next object, isn’t it? Or another one first?
Niels Berglund: The queue is probably better even though we have already talked a little about services. But let’s do the queue before services. When we have a message type and a contract, we now know what we’re going to send and what we can send. Eventually when we’re sending, the message ends up on a queue. A queue is like a table. Deep under the covers of SQL Server it is marked as an internal table. It’s a table with a specific syntax where this syntax disallows you to do a direct insert or direct update against the table. You have syntax to read off the messages from this table, from the queue. So the queue is really the storage place for the messages.
Greg Low: What are the parameters you set when you’re setting up the queue? Roughly.
Niels Berglund: If you’re doing it quick and dirty, you just set the queue name and if it’s started or stopped. So far we have only talked about Service Broker as a messaging framework, but Service Broker allows you to do more asynchronous programming. It allows you to emulate multi-threaded programming just because on the queue you have the ability to set up a queue reader name. Where a queue reader is a stored procedure that kicks in when a message arrives on the queue. So when a message arrives on the queue a stored procedure is initialized and starts handling the messages.
Greg Low: You mentioned started or not on the queue. Does started mean things get processed by the stored procs or does it mean that the messages don’t go into the queue at all?
Niels Berglund: It means that the messages don’t go into the queue at all. So I would say “create queue,” “queue name,” this is done from memory so don’t quote me.
Greg Low: Again, the queues tend to have those big URL names, or general purpose names?
Niels Berglund: Yeah, they could have. The queues not as much as the various message types and contracts. I guess we’ll come back to this activation stuff later on. So when we have a queue we can now create the endpoints of these messaging applications which are services. This is not that much different from message queue application, a little bit more granular, etc. In a message queue application you send a message to a queue. In Service Broker you send a message from a service and to a service. So we are introducing the notion of services where the relationship between a service and a queue is that we have one queue which can serve several services. So that’s sent to a service but the message eventually ends up on a queue.
Greg Low: How would you describe a service as to what its real function is?
Niels Berglund: The service defines the locality of your endpoint. And a service is the thing that also defines what message types you can send because when you’re creating a service you’re creating the service against a contract. That’s not 100% correct because we have something called a default contract, but basically you create a service against a contract over a queue. And when you are using Service Broker you are saying you want to send from a service to a service.
Greg Low: The other one that might get involved with this, the HTTP endpoints?
Niels Berglund: The HTTP endpoints have nothing to do whatsoever with Service Broker. What you probably mean is Broker endpoints.
Greg Low: Sorry, yes, absolutely. Broker endpoints.
Niels Berglund: Endpoints, schema, all of these are such extremely overloaded words…
Greg Low: Yes. It’s the “create endpoint” and we can do that for Service Broker.
Niels Berglund: The endpoint for Service Broker is to allow you to communicate out of the instance you are in. In other words, if I want to send a message from my machine in England over to you in Australia you would need to create a Service Broker endpoint on your side, and I would need to have an endpoint on my side. And these endpoints basically tell SQL Server that we can speak out of this instance, that we can communicate out of instance.
Greg Low: Okay, so we’ve talked about the message, the contract, endpoints, services, and queues. We’ll take a short break and then walk through the typical life of a message and how they actually move around. Welcome back. If you could, Niels, please discuss the life cycle of a message from where they start to how they get processed.
Niels Berglund: Sure. So we’ve talked about how we created the various objects that we need to have in order to send a message. Now that we’ve created these types, contracts, and services, we now want to talk between two databases on the same machine, to keep it simple initially. Let’s point out here that the message types and contracts need to be duplicated on the various points that are partaking in my message exchange. So if I have created a Greg type as a message type and a Greg contract as a contract, all the participating parts need to have those guys in their databases. Having taken care of that, to start the message flow what we’re doing is beginning a conversation of a particular type. Right now in the logic of Service Broker that type is a dialogue and we can cover that later if we so desire, but we’re starting a dialogue conversation.
Greg Low: Quick question. How do you see people sharing around the definitions of messages? Will it just be a T-SQL script?
Niels Berglund: T-SQL scripts, or with my tool you can actually copy it and redeploy just by point and click, drag and drop. But T-SQL scripts, basically, yes.
Greg Low: You were starting to say about conversations?
Niels Berglund: We’re starting a conversation from a service to a service on a particular contract. So this indicates to Service Broker that we now want to have a communication between these two services. The only thing that really has happened at this stage is there’s an entry in a “conversations_endpoints” table in the instance that started the conversation. Nothing else, just an entry in a table. When we have started this “begin conversation,” it gives us back the conversation handle and by using this conversation handle we now have the ability to send a message. So, begin dialogue conversation, from service to service, write contract, etc., gives me back my conversation handle.
Greg Low: That’s a GUID, isn’t it?
Niels Berglund: Exactly, a unique identifier. Having this conversation handle I can now say, send on conversation this GUID, what message type I’m sending, and the actual message. So we have now talked a bit about new T-SQL syntax. So Begin Conversation is new and Send is new. These are new key words in SQL Server.
Greg Low: Yep. I notice with Send as well they always precede it with a semicolon. I gather that’s some sort of parsing problem?
Niels Berglund: Yes that is a parsing because Send is not a reserved keyword. I bet that there are quite a few databases with a table called Send. So this semicolon just means that, yes, this is just the first line of the batch. Having said that, we should be prepared to start using semicolons more and more because in future releases they will use semicolons for much more stuff. When we have call to send at this stage Service Broker will try to find its other endpoints. It will use certain heuristics to do that which we can cover a little later on. When we have sent the message, Service Broker internally finds the endpoint, it inserts some data in the conversation endpoints table as I said, and hopefully now sends the message to that other endpoint where it will end up on the queue for that particular service. When the message is there we then have the ability to do a select on the queue which in MSMQ terms is really doing a peek against the queue. In other words we are not doing anything with the message. If we want to read off the message from the queue then they call receive, another new SQL Server statement. And we say receive from this queue. And that’s about it.
Greg Low: With the receive I notice people normally only use top one and only process a message at a time but again I presume if it’s possible to process multiples, its desirable?
Niels Berglund: That really is dependent upon your business application, but SQL Server is good at doing set based processing, so we should probably do a receive into a table variable or something and then handle the messages. But as I said, it really depends upon your type of application. And when we’re doing a receive we are receiving everything from that particular conversation handle, so when I’m saying send on this conversation handle, my messages will then have that conversation handle and when I receive, I will receive all of those with that particular message handle.
Greg Low: I’ve also noticed that you can have multiple procs running against a queue and in that case it also makes sure only messages for a specific conversation are sent to a particular proc, or instance of a proc.
Niels Berglund: Not exactly. You can only have one of these activation procs, however, in the proc. You then know what service this message is sent to and you can then route this message onto some other proc that handles that particular service type, or whatever.
Greg Low: I think the thing I was thinking of is in the queue definition isn’t there something where you can set the maximum number of readers?
Niels Berglund: Oh! Sorry, right! Yes, you’re absolutely right. What happens here is that, if in my queue definition it says “create queue” and I use this proc as a process proc to process messages, when Service Broker then receives messages in on the queue, this proc is being spun up on a new thread and is handling the messages. If Service Broker then receives more and more messages and lands behind in processing of the messages, Service Broker will automatically spin up new threads with more instances of this particular proc. And the max queue reader is there to cap out so we’re not saturating the SQL Server thread pool, basically. Because we don’t want to bring SQL Server down because we’re running out of threads so that’s just a way of saying, okay, if we’ve spun up five threads then we have to wait around until one becomes available.
Greg Low: I was just pleased it was also clever enough to only send messages from a particular conversation to an individual thread to avoid race conditions and things.
Niels Berglund: Yes, absolutely, absolutely.
Greg Low: Great. Can you describe to us what support there is for administration in management studio, which as we said isn’t a lot, and then talk about what you’ve done in your admin tool that you’ve built?
Niels Berglund: Support for admin in management studio is basically nil. There is none. You can see in object explorer in management studio, you have notes for Service Broker objects so you can see what message types you have, what contracts, etc. but you do not have the ability to do a new contract or something like that. There are, in management studio, T-SQL templates for creating the objects but that’s all there is. There’s no hooking up between the templates and the objects in the object explorer to be able to more automatically create a new message type, contract, or whatever. That’s basically what I did in my admin tool where you drill down into the server, into the database, into that particular broker object you’re interested in. You right click on that node, choose new, and you get a forum based dialogue where you can basically research to create the object you’re interested in. From there you can also start a new conversation, send a message and do stuff like that. So you could have your whole lifetime of a Service Broker application and do that from just the admin tool.
Greg Low: So do you have the ability in there to manage what’s in what queues at the moment so that people can visualize where the messages are?
Niels Berglund: Yes, you can see the queues and you can see what messages are in the queues. You cannot read messages from the queues because this is only an admin tool after all. So I debated heavily with myself, should you be able to read and I said no, you shouldn’t, but we’ll see what happens.
Greg Low: That’s great. The other thing to find out is just what other things are coming up? Presentations? Articles?
Niels Berglund: Yes, so first of all watch the space with SSP admin, there may be new stuff coming in. One of the hardest problems at the moment with Service Broker is security. I’m talking with Microsoft at the moment to see if we can do something smart with SSP admin in order to help people set up security for Service Broker because this is a real hardship at the moment, so watch that space.
Greg Low: So people need, I presume, permission on the endpoint?
Niels Berglund: Yeah. You need basically to set up certificates on the various endpoints to be able to talk at all, and you also need certificates in order to handle the dialogue security in order to encrypt and be secure when you’re sending the messages.
Greg Low: We should mention also that when the procs run there are a variety of security context options you’ve got there as well.
Niels Berglund: Absolutely. You can say under which context, when you’re setting up with a proc, you’re defining under which security context your proc is running through the “execute as” statement, this new statement in SQL Server 2005.
Greg Low: I’ve noticed book-wise that Roger Wolter has got one coming out…Have you seen any of that as yet?
Niels Berglund: Roger is the grandfather of Service Broker. What Roger doesn’t know about Service Broker is not worth knowing. He is program manager at Microsoft for Service Broker and he wrote a book that was released for TechEd in the US. If you’re interested in Service Broker, even remotely interested in Service Broker, that is the book to have.
Greg Low: I’ve pointed a few people at it but it still seems to be in pre-release at the moment and not available yet.
Niels Berglund: That is the book to have. That is the book to have. So, really what I would like to say more about Service Broker, is what Service Broker allows you to do, we can emulate multi-threaded SQL Server programming all of a sudden. We can send off messages and another process kicks in and handles these messages for us. We can take a long running proc that is doing lots and lots of things, and we could take that and split that up into several sub-procs if you so wish, and then send off a message to a queue inside my proc and this message ends up on the queue, another process spins up, handles that message and is executing some of my other procs. It is a really, really powerful environment that we’ve got.
Greg Low: Are we going to get to see you speaking about this?
Niels Berglund: I’m at the moment doing a series of articles for SQL Magazine. Initially it is not about Service Broker but hopefully there will be a couple coming out about Service Broker as well. The next conference I’m at is DevWeek in London in 2006
Greg Low: You’re based in London, aren’t you?
Niels Berglund: I’m based in London, yes. Well, I’m based in the UK, but yeah. London is my base, put it that way. So DevWeek is the first conference I’ll be at next year, speaking about Service Broker.
Greg Low: Great. That’s good. Thank you very much for that and it has been most enlightening and hopefully we’ll talk to you again sometime soon.
Niels Berglund: Thank you, Greg.
Phone: 1300 SQL SQL (1300 775 775) l International +61 1300 775 775 l Fax: +61 3 8676-4913
Copyright 2017 by SQL Down Under | Terms Of Use | Privacy Statement