Saturday, May 24, 2008

The SqlConnectionStringBuilder Class

One of the more handy BCL (Base Class Library) features that I ran across years ago, but don't think has gotten much attention is the SqlConnectionStringBuilder class that resides in the System.Data.SqlClient namespace.  This is such a handy class, as it allows you to forget about the proper syntax of the connection string, and just let you deal with the parts that seem necessary.  No asking yourself "is it Trusted Security or Integrated Security???".  Well, maybe you have an easier time with it than I do, but, still a handy class.

It's also super handy if you've got your connection string as a string, and you need parts of it.  No need to write your own parsing.  Just load it up into the SqlConnectionStringBuilder class and get the parts you need from the properties of the class.

Below is a simple sample, there's obviously much more to it as far as connection string options, but I'll let you explore those on your own.

SqlConnectionStringBuilder builtFromScratch = new SqlConnectionStringBuilder();
builtFromScratch.DataSource = "(local)";
builtFromScratch.InitialCatalog = "MyDatabase";
builtFromScratch.IntegratedSecurity = true;
 
Console.WriteLine(builtFromScratch.ToString());
 
SqlConnectionStringBuilder builtFromString
    = new SqlConnectionStringBuilder(builtFromScratch.ToString());
Console.WriteLine("Server: {0}", builtFromString.DataSource);
Console.WriteLine("Database: {0}", builtFromString.InitialCatalog);
Console.WriteLine("Windows Authentication: {0}", builtFromString.IntegratedSecurity);

Tuesday, May 20, 2008

Three Things You Should Do When Using SQL Service Broker

There's three things that I've learned (the hard way) recently while working with the SQL Server Broker.  Not doing the first two of these things will really bite you.   Really bite you.  I mean, like, SQL Server will punch you in the neck, steal your wife and eat your babies, bite you...

The symptoms of not doing these things are:

  • Saturated CPU on the SQL Server
  • Out of control tempdb growth
  • Calls from angry customers

Back in January, I put together a post that used a "roll your own" approach to cache invalidation, and in that example I used the SQL Server Service Broker in order to notify the application that there was data that changed.  The sample issues and solutions  that I'm using below are from that post, so refer there to get the whole sample (I've updated that post to make sure I don't get anyone punched in the neck).

SQL Broker is designed to be the new "middleware", and is primarily setup to send messages between SQL Server Brokers.  All messages are sent in the context of a conversation.  The concept of conversation ensures that messages are sent in order, only received once, and are delivered reliably.

All of these symptoms above have to do with not knowing what the hell I'm doing using conversations properly, which leads me to believe there's quite a bit of overhead having to do with the management of conversations.

So, have a look below.  Definitely do things #1 and #2, and see if you can pull off #3.

1.  Always End the Conversation

This one seems to be pretty often overlooked.  In my example from back in January, I was using a one sided service, meaning I was receiving messages on the same service that was sending them.  When using the SQL Broker as middleware, in most cases you will be sending messages on one service, and receiving messages on another (two-sided).

In both cases, you absolutely must end the conversation.  Failing to do so, leaves the conversation out there, along with some space in the tempdb, and some portion of your CPU.  As more and more conversations are created, the worse and worse your problems get.

If you're experiencing this behavior, execute the following query to clear all conversations.

ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE myDB SET NEW_BROKER
ALTER DATABASE myDB SET MULTI_USER

After you get your SQL Server back, you'll want to update the code that receives the messages off the queue, to end the conversation after it has received the message.  From the sample in January, I'd change it from:

command.CommandText = "WAITFOR (RECEIVE CAST(message_body AS XML) FROM PersonChangeMessages);";

To:

command.CommandText = @"DECLARE @conversationHandle uniqueidentifier;
                        DECLARE @message AS XML;
                        WAITFOR (RECEIVE @conversationHandle = [conversation_handle],
                            @message = CAST(message_body AS XML) FROM PersonChangeMessages);
                            END CONVERSATION @conversationHandle;
                            SELECT @message;";

2.  Always Set the Lifetime on the Conversation

The next thing you'll always want to do, is provide a duration in which your conversation is valid for.  Conversations without a specified lifetime will last int.MaxValue in seconds.  So, quite some time.

Before we had the following:

BEGIN DIALOG @DialogHandle
FROM SERVICE [PersonChangeNotifications]
TO SERVICE 'PersonChangeNotifications'
ON CONTRACT [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [http://schemas.microsoft.com/SQL/Notifications/QueryNotification] (@Message);

And we'll want to change that to:

BEGIN DIALOG @DialogHandle
FROM SERVICE [PersonChangeNotifications]
TO SERVICE 'PersonChangeNotifications'
ON CONTRACT [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
WITH ENCRYPTION = OFF, LIFETIME = 30;
SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [http://schemas.microsoft.com/SQL/Notifications/QueryNotification] (@Message);

3.  Reuse Conversations and Conversation Groups

This last item is a bit more difficult to accomplish (thus I won't be providing a sample in this post), but it will insulate you from any conversations issues having a huge impact on your application's performance.  If there aren't that many conversations out there, you'll limit the overhead.  All conversations have a handle, or id, as we saw above.  If you persist this id, in the database, or in memory, you'll be able to reuse the conversation over and over, and reuse all the overhead created with the conversation.

Sunday, May 18, 2008

Are Experience and Education Mutually Exclusive - The Result

I got a great response from my post the other day on whether or not formal education is necessary if you have experience. I appreciate all of you who left comments or contacted me directly.

Almost all of you said that experience was more valuable than education, once someone has already gotten their foot in the door.

But, something funny happened a couple days after that post. I got schooled by a 20 year old teaching assistant. Needless to say, it was a bit humbling, and I drove one thing home for me:

I need to stay in school and finish my education.

Thursday, May 08, 2008

Are Experience and Education Mutually Exclusive?

I'm back.

Thanks to those who gave me a nudge. Promise I'll put some more technical posts up (MVC seems to be highly requested.)

So, most of you know, I got one of those non-traditional starts in the job market, like quite a few developers did in the 90's. I came from a totally different industry, without a 4 year college degree. But I had a knack for computers, a great friend and a small company took a chance, and here I am.

About 3 years ago, the small company was purchased by a much larger company, and introduced a tuition reimbursement program. So, I decided to enroll in a Bachelor's of Science program.

The reason I did so, was that I thought that because of my lack of formal education, I was missing so much lower level, "to the metal" concepts. I'd never written a Binary Tree, or a Quick Sort or a compiler. I'd never determined the order of an operation over a set. It was never an issue of improving my resume, it was always an issue of wanting to learn all the things that most of the people around me had learned.

So, for the last 3 years I've been chipping away at my degree. It's been an okay experience so far. I've found that a lot of what I thought I'd been missing, I'd already had. I've also found that what I thought would be mostly new, turned out to be mostly inefficient tedium.

Recently, as I was complaining (again), about how I felt like I was wasting my time, a noob young buck at work (who happens to be a recent grad), suggested that it would be a better use of my time to spend blogging and learning new things on my own than working on my undergraduate degree.

Ironically, my response to him was that it would be best if I finished it out, because it would look good on my resume. I also started thinking about how graduating would give me a good reason to buy a keg and have a party. Neither of these two reasons were very true to my original intent.

So, my (really long winded) question to you is: Do you feel like an experienced developer needs a degree to be marketable and to get a foot in the door, or is experience (including an active blog, twittering, speaking, networking) more valuable?