Sergey Maskalik

Sergey Maskalik's blog

Focusing on improving tiny bit every day

Restsharp is a nice little library that abstracts calling webservices in .NET. It also provides ability to add authenticators to web requests which add required oauth signatures and tokens. At first I didn’t see the 2 Legged Oauth authenticator, but after almost going through the pain of making my own I’ve discovered that it does support 2 legged oauth. Here is how to create an authenticated 2 legged Oauth request.

    var client = new RestClient(FactualApiUrl);
    client.Authenticator = OAuth1Authenticator.ForProtectedResource(oAuthKey, oAuthSecret, string.Empty, string.Empty);

Hopefully it will save you some time next time.

P.S. The latest .NET 4 version 103.1 in NuGet for some reason is missing the authenticators, so you can get a previous version or get latest from GitHub and compile and you shold have no problems.

What can Service Broker do for you.

You might not have heard of the Service Broker before, I know I haven’t up until a month ago and after learning what it can do, I think all .NET developers should at least be familiar with it. It comes free with SQL Server and no additional installation is required. Some of the highlights for me are asynchronous triggers, reliability, offloading of long running batch jobs or activating external applications that might call for example Web Services. And some other great but rarely used features like Sql Server notifying the application layer when the data has changed which can be combined with a caching layer to make a very efficient and fast application. In fact list goes on.

Asynchronous triggers

First of all triggers in the SQL database are synchronous and they do an implicit lock on the database tables when an operation on the table is happening. I’m not a big fan of triggers myself, but there are time when you have no choice but use them. For example if you have no control when table gets update from application layer and you need to have some sort of processing when record is modified, deleted or added. So Service Broker solves this problem by creating a message queue for items that need to be processed instead of processing them right away. That in result creates asynchronous triggers because messages are sent to be processed when resources are available.

Reliable messaging

Another great benefit of Service Broker is that it provides a reliable mechanism of a messaging queue. Message only gets de-queued when a processing application processes a message and commits a transaction and message can be taken off the queue. If your SQL Server shuts down all your messages stay in the queue as it works just like any other sql table. If processing fails, the message never leaves the queue and will get retried shortly.

External activation

External activation might sound alien right now, but it’s not hard to understand. Basically when message arrives at the service broker queue you will need to provide an activation (or what is going to process that message). There are two types of activation internal or external. Internal activation is a stored procedure or a stored procedure calling a SQLCLR, it’s basically everything that happens inside of SQL server instance. External activation is when SQL notifies an external process (completely outside of sql) that some change is waiting to be processed. What external activation allows is to offload tasks that don’t belong inside of SQL server like calling Web Services or some kind of long running batch processes that communicate with other systems. In addition external activation does not require deployment of .NET assemblies inside of SQL Server and does not require SQLCLR.


There are a lot of other features like a reliable replication between SQL Servers, scalable distributed messaging queues and many others. Many of which will most likely be handles by a SQL DBA. What got me started on Service Broker is figuring out how to asynchornously call Web Services from a database trigger and I think it does an awesome job of doing that. Also while reading the Pro Service Broker book I found out that it can do manage code notifications that can for example invalidate cache in your application layer.

No more theory, let’s implement asynchornous triggers calling web services!

Service Broker is a part of the Microsoft SQL Server and doesn’t need to be installed separately, just enabled. like this:

SELECT name, is_broker_enabled FROM sys.databases

After we need to setup SQL Service Broker with needed objects.

Message types, these define types of messages being sent and received, we’ll use well formed xml, and for type you want a unique name a url is used by convention:



Contract defines how messages are being sent:


Now we will need two queues to hold our target messages (messages which will be processed by an activation program) and response messages for initiator which we will get as a response from activation program and processed by initiator).

CREATE QUEUE InitiatorQueue


Here I need to point out that in order for service broker to be reliable it uses a concept of dialogs basically once conversation dialog is open both sides will need to close conversations on their ends. It also happens that messages only get’s de-queued when the conversation end is received from another party. It will make more sense later.

Next, we will need to create a services, which is basically a service that routes messages, you will need to specify a contract defined earlier, like this:

CREATE SERVICE InitiatorService
ON QUEUE InitiatorQueue

ON QUEUE TargetQueue

Since we will be using external application activation we need to disable internal activation on the target queue. To enable external activation Service Broker send a message to the external activation queue when a message arrives at the target queue. This allows for centralizing all external queue notification. And as my co-worker Vincent pointed out this external queue can be used across databases, so there will be one central external notification queue for for all databases.

Let’s create that notification queue

CREATE QUEUE ExternalActivatorQueue

Then we will also need a service for that queue, as you see it uses a generic contract provided by microsoft for event notifications:

CREATE SERVICE ExternalActivatorService
ON QUEUE ExternalActivatorQueue

Finally we subscribe to the internal QUEUE_ACTIVATION event on our “TargetQueue”, so that we will receive notification in our ExternalActivationQueue when the TargetQueue gets messages.

CREATE EVENT NOTIFICATION EventNotificationTargetQueue
    ON QUEUE TargetQueue
    TO SERVICE 'ExternalActivatorService', 'current database';

Here comes the time to create our SQL trigger which instead of doing a synchronous operation will quickly send a message to the queue for later instead of processing it. This is the concept of asynchronous triggers at work, we are firing and forgetting instead of waiting for response right away. The most important part is the SEND ON CONVERSATION @ch MESSAGE TYPE; this is where we send the message to the queue. Creating a sample order table and a trigger which will send a message when a new order is inserted:


-- Trigger will add a message into a ImportQueue
        DECLARE @messageBody NVARCHAR(MAX);

            FROM SERVICE [InitiatorService]
            TO SERVICE 'TargetService'
            ON CONTRACT []
            WITH ENCRYPTION = OFF;

        -- Construct the request message
        SET @messageBody = (SELECT ID, Amount FROM [Order] FOR XML AUTO, ELEMENTS);

        -- Send the message to the TargetService
        MESSAGE TYPE [] (@messageBody);

So now when we add an order the trigger will send a message which will see it in our TargetQueue and ExternalActivationQueue. Target queue will wait for someone to process and ExternalActivationQueue will wait until external activation program will read that message.

Results from queues

What do I mean by external activation program… Well that’s the program that listens to the Activation Queue and basically reads messages and starts predefined application for that queue. And when application starts it knows that it has to read it’s designated queue and process messages in it. Luckily Microsoft already create that external activation service application and it is available here, scroll down and you will see Microsoft® SQL Server® Service Broker External Activator for SQL Server® 2008 R2.

After you install the service, you will need to read documentation and edit the config file. It’s pretty trivial to do, you will need to specify your database name where activation queue is, your targetqueue name and which executable file to run when a message comes to the targetqueue. If you are having problems take a look at the error log, that helped me to get it up and running. Once you have configuration done, start the service and it will run if there are no configuration issues. Also keep in mind that service is only designed to work in integrated security mode.

But before you start the service we need to create an app that will process our messages and notify the queue that we have successfully processes so it can end the conversation and de-queue the message. Here are the highlights of that app:

It’s a console application with a main while (true) loop that uses ADO.NET to send Service Broker Command to RECEIVE message on the specified queue. It receives a message, but still keeps it on the queue until commit is called.

broker.tran = broker.cnn.BeginTransaction();
broker.Receive("TargetQueue", out msgType, out msg, out serviceInstance, out dialogHandle);

if (msg == null)

switch (msgType)
    case "":
        //right here we call a web service or whatever processing you do
        //also use T-SQL to parse @msg from the queue to get data
        broker.Send(dialogHandle, "<Response><OrderId>1</OrderId><Status>Processed</Status></Response>");
    case "":


Basically the transaction only gets committed when we process message and that deletes a message of the queue hence the reliability of the message processing. Also remember it is important to end conversations on both ends.

When reply is sent from an external .NET application we need to process it inside the SQL Server. For this we will use an internally activated service which will call a stored procedure which will simply take that message, parse xml and insert it into a table.

CREATE PROCEDURE ProcessResponseMessages
    DECLARE @messagetypename NVARCHAR(256);
    DECLARE	@messagebody XML;
    DECLARE @responsemessage XML;

    WHILE (1=1)

        WAITFOR (
            RECEIVE TOP(1)
                @ch = conversation_handle,
                @messagetypename = message_type_name,
                @messagebody = CAST(message_body AS XML)
        ), TIMEOUT 1000

        IF (@@ROWCOUNT = 0)

        IF (@messagetypename = '')
            INSERT INTO ProcessedOrders (ID, SentTime) VALUES
                @messagebody.value('/Response/OrderId[1]', 'INT'),

        IF (@messagetypename = '')
            -- End the conversation
            END CONVERSATION @ch;


Finally we alter our InitiatorQueue to turn on internal activation with provided stored procedure.

ALTER QUEUE InitiatorQueue
    PROCEDURE_NAME = ProcessResponseMessages,
    STATUS = ON,

Final words

It seems like a lot of configuration and setup, and it is. However what you do get is offloading of processing of your message outside of SQL Server which can call something like Web Services. You will also get reliability and scalability that service broker offers. And you don’t have to deploy assemblies into SQL and there is many other things you can do like paralel processeing, conversetion group locking and a ton of other things that you will need to read for yourself in Pro SQL Service Broker 2008 book. Good luck!

Software development has definitely seen an exponential growth in the past decade. And along with that came an immense demand for good developers. With only few industries that are hiring today it’s not uncommon to meet with a developer candidate at an interview who has a wonderful resume, but when you ask them to write a simple algorithm or even a nested for loop they really have a hard time. While I believe it’s the best job ever, it’s not for everyone. With this post I’d like to spell out what it means to be a good developer.

Good developer

But what do I mean by that illusive term “good developer”. Aren’t all programmers who know how to write code good? Well, absolutely not. I think an average computer science student with a descent understanding of concepts can write code but that does not make him a good developer.

Any fool can write code that a computer can understand. Good programmers write code that humans can understand. -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999

There is a lot of meaning in that simple phrase and a lot of learning that is required in order to make code human readable. I think first thing is a real understanding and practice of object oriented principles. I’m not talking about creating instances of the classes or calling methods on object but the object oriented design principles that were introduced to break down the complexity of the business domain. Concepts like single responsibly principle , open/closed principle, low coupling and high cohesion and etc. should be a second nature to a good developer. In addition knowing and practicing concepts like Test Driven Development (TDD) and Domain Driven Design (DDD) will help you transition from an average developer into a good developer. Another very important skill every good developer knows is how to take a spaghetti code and make it better, that concept is called refactoring and a big part of being developer is maintaining existing code which will most likely was written by an average developer. I think learning and becoming good at the concepts above alone would take a significant amount of time probably few years and a lot of reading of fundamental books. That brings us to next topic, continuous learning.


To be a good developer is to love learning. In fact continuous learning is what makes a developer better as time goes on. A lot of average developers don’t read books and don’t become better at their craft. I think it’s like that with many other professions. For example, I’m sure you don’t want to go to mediocre dentist because if he messes up it will cause an infection or some other problem down the line, so I’m sure you want to go the good dentist who is a master of his craft. Once a good developer has a grasp of design principles his learning doesn’t stop there, there is always going to be new frameworks new technologies other design principles, different programming language and he or she will need to take time to constantly learn them. So if you felt like college was too much learning then this career is not for you because it you will be constantly learning for the rest of your life.


Just like with anything else if you love what you do then you will get good at it. If it’s just another job then there is no way you are going to go home and read a book on the weekend, write a blog post, experiment with the new and shiny technology or build a project.

Your work is going to fill a large part of your life, and the only way to be truly satisfied is to do what you believe is great work. And the only way to do great work is to love what you do…

Your time is limited, so don’t waste it living someone else’s life. Don’t be trapped by dogma — which is living with the results of other people’s thinking. Don’t let the noise of others’ opinions drown out your own inner voice. And most important, have the courage to follow your heart and intuition. They somehow already know what you truly want to become. Everything else is secondary.” -Steve Jobs

Why waste your time and be mediocre at something when you can live a fulfilling life and be the best you can be.

Learn from others and pass your knowledge

I strongly believe that a good developer must have a twitter account, follow other developers and learn what others like you are working on. Once you have something good to share do so. I think building up your circle will get your more exposure and keep you up to the latest and greatest things that are happening. But also don’t let twitter destruct you and unfollow those that you think add noise to your stream. You can start by picking developers from my followers list.

Next on the list is a stackoverflow account. You will come across a problem that you cannot find an answer for sooner or later, and you can learn from others. As a bonus try answering other people’s questions.

Start writing a blog. I wish I started much earlier but it’s better late than never. When you are developing you always come up with some clever solutions to the problem or something that took you long time to figure out. If you take some time and share it, you can use it as your diary and when you forgot something, you can look it up from your own blog and other developers will appreciate it. I believe if you give something you will get it back in return.

Get a github account and follow great developers/project, learn from code they have written. It’s a wealth of knowledge for free.

Get a google reader, subscribe to developer blogs and read interesting articles once in a while.

Watch videos from conferences sometimes they have gems like a recent talk from Steven Sanderson on single page applications

Start your own project that uses latest exciting technology. As for me, right now I’m working on exciting single page application project that uses knockoutjs and ASP.NET Web API. I’m very excited about working with latest and learning new frameworks.


If you do all of the above while working your regular programming job you will no doubt become a good developer and be on the road to greatness! And remember it’s not the destination it’s the journey, and you gotta love the journey that’s all it matters :)

So do you still want to be a developer? Did I miss something, please let me know your thoughts.