A Lap Around the SQL Server 2005 Service Broker – Part I

The Service Broker is a highly scalable message queuing system available directly within the SQL Server 2005 architecture.  It essentially provides a messaging system for asynchronous processing of, well, anything really, however, it’s grossly underutilized in the developer community as a whole.  The Service Broker solves some common issues with queued systems for you:  issues of durability, backups, synchronization, and singularity.  I won’t go too much into the detail of how the messaging system works under the hood, the difference between monologs and dialogs, etc., because I want to focus mostly on how you, the developer, can get up and running with the Service Broker as quickly as possible so that you can take advantage of it in your applications.  Do the necessary legwork ahead of time to find out if the Service Broker provides a solution to your specific problem.  In other words, don’t over-engineer your application to solve a problem you don’t have and don’t expect to have for the foreseeable future.  For the purpose of delving deeper into the inner workings of the Service Broker I recommend the readings below:

0.  MSDN Article:  Typical Uses of Service Broker
1.  TechNet Article:  Intro to Service Broker
2.  MSDN Article:  More Service Broker Info

The code samples below will help create messaging communication within a single database.  The Service Broker allows for routing communication between multiple databases, physical servers, or instances, however, that is beyond the scope of this writing.  Let’s get started by assuming a scenario in which you are writing an application that is commonly disconnected from the Internet and from a central SQL database that stores that applications data.  For example, you’re writing an application for an extremely large team of field biologist recording population counts for over 500 species of insects around the world in extremely remote locations.  These field biologist have a lap top with an application installed in which you record your data locally.  As connectivity permits, the application automatically connects to the central SQL database to synchronize the data of not only your data, but the data of every other biologist in the field.  Your charge, as the developer, is to make this application possible using the Service Broker.

Enabling The SQL Server Service Broker

As is the case with the majority of the other SQL Server features, the Service Broker is disabled by default.  Let’s first enable the service broker on your testing server.  We’ll also create a master key on your test database that will be used as a session key for the Service Broker conversations.  Open up SQL Server Management Studio to a testing database of your choice and run the following statements in a new query window:

ALTER DATABASE MyDatabase SET ENABLE_BROKER;

IF NOT EXISTS(SELECT * FROM SYS.SYMMETRIC_KEYS T0 WHERE T0.[NAME] = ‘##MS_DatabaseMasterKey##’)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘123456′;

Message Types and Contracts

Message types and contracts work in much the same way as linguistics.  For the Service Broker to be an effective messaging system, we need to define what types of messages will be put into the queue, much like you and I decide on a language when we decide to communicate.  Let’s start with the message type.  Message types are made up of two key components:  name, and validation.  The name can really be anything, but you want to be sure to provide something unique.  It’s also important to remember that the names are case sensitive.  Right now, our purpose is simple:  we want to store and process messages on a queue within a single instance of SQL Server and a single database.  However, once you get more comfortable with these concepts, you may want to start using the Service Broker as a mechanism for communication between multiple instances, multiple servers, and multiple databases, so you need to ensure uniqueness in your naming conventions.  You can easily use a namespace or URI naming convention for this purpose.

You can choose whether or not you want your messages validated as they come into the message queue.  All messages are stored with a data type of VARBINARY(MAX), which allows you plenty of rope and 2GB of storage to hang yourself with, so be careful here.  For our application, we’re going to use the WELL_FORMED_XML validation type for our messages, which means that our queue will accept any well formed XML document.  That should fit our purpose nicely for both the sending and the receiving of messages to our insect population application.

Enough chatter, let’s create the two message types we need for sending and receiving messages into a queue using a simple namespace naming convention:

CREATE MESSAGE TYPE [MyCompany.InsectPop.SendCountData]
VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [MyCompany.InsectPop.RecieveCountData]
VALIDATION = WELL_FORMED_XML;

Create a Contract

You can think of contracts as the equivalent of an interface in OOP.  Contracts are really the heart of any well designed, Service Broker-enabled application because they define how your application can communicate with and derive information from tasks given to the Service Broker.  In the case of our insect population application, it could very easily take a while for a user to submit their count data to the database from a remote location with a poor quality Internet connection.  You need a way to provide feedback to the user as to the status of their submittal.  This is a common problem that needs to be solved in any asynchronous operation, and the Service Broker provides a nice clean way to accomplish this.  We’ll delve more deeply into just how this occurs in Part II of this post.

We already have our two send and receive message types defined:  InsectPop.SendCountData, and InsectPop.RecieveCountData.  Those will be included in the contract since they are part of the logical operation we are trying to achieve.  To monitor status, we also need to add a 3rd message type to the contract:  InsectPop.CountDataStatus.

CREATE MESSAGE TYPE [MyCompany.InsectPop.CountDataStatus]
VALIDATION = WELL_FORMED_XML;

We’ll also create our contract as follows:

CREATE CONTRACT [MyCompany.InsectPop.InsectCountContract]
(
[MyCompany.InsectPop.SendCountData] SENT BY INITIATOR,
[MyCompany.InsectPop.RecieveCountData] SENT BY TARGET,
[MyCompany.InsectPop.CountDataStatus] SENT BY ANY
);

Let’s paraphrase the code block above to better understand what is going on:  the SendCountData message can only be sent by the initiator, which is our application, and the RecieveCountData message can only be received by our target, the service, which we will soon create, and the CountDataStatus message can be sent by either the initiator or the target to check on the status of a queued request.

In Part II of this post we’ll go into more detail about setting up the queues and services that will make our Service Broker application do something useful.

Click here to go to Part II (Coming SOON!)

The Importance of Focus and Perspective

As software developers, we tend to look at the “big picture”, meaning, what the finished product should look like when we are “done” developing.  Our tendencies, in general, are to focus day-to-day, hour-to-hour, and minute-to-minute on how the piece of code we’re working to create fits into the larger landscape.  Although it’s important to have enough perspective on the big picture to know what exactly we are building and what purpose it should serve, if we liken it to building a working machine such as a car, an improperly focused development process is the equivalent of focusing on the whole car while fashioning one of the bolts that fastens the engine to the block.  Focusing on the car as a whole gives you very little perspective on the state of the car in terms of completeness and functionality; especially if multiple engineers are building it.  The typical result is that the bolt doesn’t serve it’s purpose entirely.  Focusing on the car instead of the bolt also has a tendency, at least speaking for myself, to throw you into a tailspin of panic in terms of the fact that “things aren’t getting done fast enough”.  What, effectively, are your options?

1.  Quickly build a bolt.  The car will be on the dealers lots, maybe even ahead of schedule.  It may even sell well at first.  However, with the motor rattling around under the hood, and with the trickle-down effect from this problem, do you think the customer who bought your car the first time will be tempted to buy the new model?  If, for it’s limited lifetime, allows you to glide over crowded streets and get to your destination much more quickly, then the answer would be:  maybe.

2.  Build a good bolt that fits the specs and satisfies the requirements.  Sell your car with pride.  Assuming your building a car to fill a marketable niche, reap the benefits of repeat customers to offset nearly any cost that might have been incurred from building a better bolt, within reason of course.

In terms of software development, option #1 is chosen far more often that option #2.  It’s important to remember that the simile of focus and perspective and the resulting level of quality applies not only to the end product, in our example, the car, but also to us individually; the engineer that built the bolt.  Each of us our effectively are our own enterprise and the results of our labors hang with us for long periods of time.

Getting back to our bolt engineering example, isn’t it more important to focus on, for instance, threading the bolt to create enough clamp force to hold the engine to the block?  Isn’t it more important to make the bolt from the right materials to satisfy weight and strength requirements so that the car doesn’t weigh too much and also so that the act of simply tightening the bolt doesn’t cause it to shear?  Isn’t it more important to have enough perspective on the project to be completely aware of the holes that the bolt will be inserted into so that the bolt is long enough, and so that it will not only fit in the whole, but so that the bolt is the right diameter to keep the engine from moving under stress?

As software developers, we have a distinct disadvantage over other types of engineers in that the parts that comprise our end product are in fact soft and pliable.  Please notice the work disadvantage.  We can change these parts relatively easily once they are made in contrast to an actual, physical bolt in terms of our example.  However, that doesn’t necessarily mean that the bolt can be of poor quality in version 1.0.  You will never fix it later.  Period.

Zune 2.3 Issues – Blank Screen, Non-responsive Zune

Today I plugged in my Zune 80 to my PC in order to update/charge it. When I did, I was prompted to download the latest firmware, which I did. Shortly after, the Zune device itself stopped working. I could hold the back of the device up to my ear and hear the drive spinning, even occasionally reading, but nothing showed up on the screen. Occasionally, when something did show up on the screen, the menu items were scrollable, but actually selecting the item didn’t do anything. In other words, selecting “Music” or “Radio” had no effect.

I also had an entry in my event log related to “ZuneDriver” from around the time when the firmware upgrade supposedly installed.  It read as follows:

The description for Event ID 15301 from source ZuneDriver cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

Zune MTPZ USB Driver
0×802a0006
0×1000

Here’s how I fixed the issue. I’m using a Windows Vista 64 bit machine, but this should work for any OS. Please keep in mind that this process will remove all the music from your Zune.

1. Disconnect your Zune from your PC, and uninstall the Zune software through Add/Remove programs, or Uninstall Software for you Vista users (Start -> control panel -> Uninstall a Program).
2. Download and install “UnZoone”. I checked this application with recent virus definitions for Norton AV 2008 and it came back with no spy ware or viruses. You can download this utility here.
3. Run the utility and follow the directions on screen. It’s nice and easy. It took about 1 or 2 minutes on my machine for the utility to “clean up the mess”.
4. Re-download and install the Zune 2.3 software here per the directions on the screen.
5. Before plugging the device into the USB port, reset it by pressing and holding the “left”, or “back” arrow plus the center directional button at once for several seconds. If the device is off, you should not be able to hear the device operating if your Zune has a hard drive. If it doesn’t, well, just do your best to make sure it’s off. This make take several tries.
6. The device will automatically “reboot” when properly reset. When it comes back on and you can see the menu where you select “Music”, or “Radio” etc., plug your device into the USB port on your computer.
7. You should see some messages about reinstalling drivers, etc. On my machine, this driver installation would cycle several times. It would just keep trying to install the drivers saying it was successful. If this happens to you as well, just use the “Eject USB Device” function of your operating system. Usually, there is an icon in the system tray that you can right click to eject a device.
8. After ejecting the device, unplug it completely from the USB port. Reset the device again using the same method mentioned above, and then plug it back in. The Zune software should be able to see it now.
9. Under “Device -> Settings” in the Zune software, I recommend erasing all the content and re-synching everything, but you may not have to depending on your situation.

Hope this helps.

-Greg

Streaming Music Through Your XBox 360

I had the absolute pleasure of setting up my XBox 360 to stream music from my Windows XP SP2 machine. To do so, I essentially just had to install the Microsoft Zune software, which was a really impressive experience, though slightly slow as it just had to run and do things while I sat and watched. You can also share through Media Player 11, but I really wanted to give the Zune software a try to see what it was like.

Microsoft has done their homework on the level of usability that needs to be present in order for a device like the Zune to be successful. I’ve been an iPod/iTunes users since well before Microsoft’s entry into the market, and I have to say, the install experience and usability of this software is fantastic. Granted, I don’t own a Zune device (yet) but their iTunes equivalent is impressive. But I digress…

The streaming setup was super easy for the XBox. After installing the Zune software, and allowing it to search for all of the music, pictures, and video on my machine (about 26 GB worth) in literally under 1 minute, and changing a single setting that allows music sharing on my home network, I was able to find my PC and all my music from the XBox and play all the music I ever purchased through iTunes as well as old CD rips. Even all of my iTunes playlists showed up, as well as Scott Hanselmans and others podcasts that I listen to regularly. Not only that, but all of my pictures and video were now available too. I watched some old movies of my niece and nephew, and my lovely dogs Alle and Coda on my TV in beautiful clarity that I hadn’t seen in a long time. My wife and I flipped through our honey moon pictures, and our wedding pictures which was very enjoyable as well. I was then able to set up a playlist on the XBox and listen to it while playing my favorite game right now, skate.

Nice job, Microsoft.

Overlapping Education – The .NET Way of Life

The Microsoft .NET Framework is changing incredibly fast; for the better from my perspective. The issue really isn’t with the changes that are being made, it’s the astounding pace. It’s so fast in fact that I often find myself learning about multiple versions of the same technology at the same time, which can be somewhat confusing. This is a necessary evil for developers and development teams that are in the process of creating new applications, and at the same time investing in their education through Microsoft certification exams. It’s my opinion that this is a common occurance in the development world. A development team would typically not decide to use WSE 3.0 technology to development web services for a project at the beginning of it’s development life cycle when WCF and the .NET Framework 3.0 has already been released, and the .NET Framework 3.5 is on the near horizon. This fact forces a rift between what the best tools are for a developer to use, and what knowledge you need to have to be certified by Microsoft on the .NET Framework as dictated by Microsoft. It’s a problem that I think can be approached and resolved to the betterment of both parties.

As an example, let’s look at the curriculum for exam number 70-529 .NET Framework 2.0 Distributed Application Development. The curriculum clearly states that you need to have detailed knowledge about implement web services using Web Services Enhancements 3.0. I’ll also note, as an aside, that this particular exam covers two different “versions” of the web service technology as they are implemented through .NET techonology. That fact demonstrates clearly that Microsoft is aware that there is a need for overlapping education in the .NET Framework. However, getting back to the topic at hand, how does WSE 3 compare with WCF? It does compare in some ways, that’s a given, but the basic foundation for creating the WCF equivelent of web services is entirely different…and arguably better and easier to implement in a lot of ways. Also, why are there not exams that cover version 3.0 of the .NET Framework? Some may say, “Well, the current version of the .NET Framework that is cited as being relevant for current exams is version 2.0. Duh.” That statement would be true except for the fact that the .NET Framework 3.0 runs on the 2.0 CLR.

Again, the issue really isn’t with what’s changing, and I’ll even amend my earlier statement that the issue is with the pace of the changes and say that its with the pace as well as the certification processes employed by Microsoft being unable to keep up with the tools being released in the market place. The question really becomes, how do we become certified on market relevant technologies built by Microsoft through Microsoft while at the same time remaining current on the tools that are in full release (not beta, not release candidates)? The answer, unfortunately, for now I feel is that it’s impossible. With the sheer amount of information that is necessary to learn for a single exam, normal, working professionals, spend a lot of time in preparation, which in and of itself is not an issue. However, by the time they are ready to take the exam, the technology has already changed and the exam is less relevant to the market than it was even sixth months prior, and the developer doesn’t have extensive knowledge of all the tools available to their disposal. In other words, the material is too broad to be as dynamic as the tools in the market place. Whereas, if there were smaller “chunks” to an overall larger end-result certification, the material could be changed often enough to be relevant to the tools on the market that developers are actively using to build their applications; especially new development.

The volume of information is necessary to be an effective developer, and I don’t advocate “lowering the bar”, so to speak. My point is that splitting the objectives up into smaller objectives would allow curriculum updates to flow in much more quickly, it would open up the publication markets to more profits through the release of more books for more tests, it would allow Microsoft to increase revenue from testing fees, and it would also allow developers to keep more market relevant tools in their belt.