Archive for the ‘SQL Server’ category

Service Broker – Some thoughts post hoc

December 1st, 2009

sql service broker

This post has been sitting in my drafts for a while. After my day today i felt that it was best that i put my thoughts about service broker down.
In a project that i worked on about 2 years ago Service Broker was used as part of the system to perform data validation.  If success was measured by the amount of time that a system has been in production without major issues then i can say that it was successful.

But if a system was to be judged by how much of a pain it is to maintain then l must say that its was not a glowing success. Below is a list of things that i found as good or bad about it.

 

Bad things

  1. Limited information on the internet
  2. Testing is extremely hard (compromises everywhere)
  3. Troubleshooting is a pain
  4. It could stop working and you may never know, (if using a fire and forget mechanism)
  5. Poison messages disable queues (would have been nicer if it moved the message to a error queue)

Good things

  1. Its Asynchronous
  2. Its Asynchronous
  3. Its Asynchronous

Yes this list is one sided but true. The question that is important though is

Would i use Service Broker again?

The answer to that question is strangely YES. Only in very specific circumstances where system is ok with not having some data loss because of queue being disabled

What would I use instead?

of course. It provides an answer to all the negatives of service broker and is still asynchronous.

No comments »

Posted in .net, Architecture, NServiceBus, SQL Server

Tags:

Service Broker C# Wrapper Interface and Transactions

August 16th, 2008

Service Broker has a really good C# wrapper interface that comes part of the samples on Codeplex. If you need to work with service broker objects in the managed world then I would recommend using the interface from the .

Most samples I have seen demonstrate communicating within a single database, and hence use the SqlTransaction Class The code below is an example of sending a message to a SSB Service  in a SqlTransaction. 

   1: private void SendMsgToSSB()
   2: {
   3:     SqlConnection conn = null;
   4:     SqlTransaction tran = null;
   5:     Conversation dialog = null;
   6:     Service client = null;
   7:     string connString = "Persist Security Info = False; Integrated Security = True; Initial Catalog = MyServiceBrokerDB; Data Source = .; Connect Timeout = 30;"
   8:     try
   9:     {
  10:         conn = new SqlConnection(connString);
  11:         conn.Open();
  12:  
  13:         // Begin a transaction
  14:         tran = conn.BeginTransaction();
  15:  
  16:         // Create a service object
  17:         client = new Service("MyCoolService", conn, tran);
  18:         client.FetchSize = 1;
  19:  
  20:         // Begin a dialog with the MyCoolService
  21:         dialog = client.BeginDialog(
  22:             "MyCoolServiceTarget", null, "DEFAULT",
  23:             TimeSpan.FromMinutes(1), false, conn, tran);
  24:  
  25:         // Create request message
  26:         string outgoingBody = "my really cool msg that broker understands";
  27:         Message request = new Message("DEFAULT",
  28:                                       new MemoryStream(Encoding.UTF8.GetBytes(outgoingBody)));
  29:  
  30:         // Send the message to the service
  31:         dialog.Send(request, conn, tran);
  32:  
  33:         dialog.End(conn, tran);
  34:         tran.Commit(); 
  35:     }
  36:     catch (ServiceException svcEx)
  37:     {//deal with this pesky service exceptions
  38:     }
  39:     finally
  40:     {//clean up here close connections etc.
  41:     }
  42: }

But in scenarios where the transaction needs to be elevated from a lightweight transaction to one that needs the aid of the DTC the SqlTransaction object is not going to cut it for you.

With a simple change (in bold and italicized) you can then support enlisting in a Distributed Transaction.

   1: public void SendMsgToSSB()
   2: {
   3:     SqlConnection conn = null;
   4:     Conversation dialog = null;
   5:     Service client = null;
   6:     string ConnectionString = "Persist Security Info = False; Integrated Security = True; Initial Catalog = MyServiceBrokerDB; Data Source = .; Connect Timeout = 30;";
   7:     try
   8:     {
   9:         conn = new SqlConnection(ConnectionString);
  10:         if (conn.State != ConnectionState.Open)
  11:         {
  12:             conn.Open();
  13:         }
  14:         conn.EnlistTransaction(Transaction.Current);
  15:  
  16:         // Create a service object
  17:         client = new Service("MyCoolService", conn, null);
  18:         client.FetchSize = 1;
  19:  
  20:         // Begin a dialog with the MyCoolService
  21:         dialog = client.BeginDialog(
  22:             "MyCoolServiceTarget", null, "DEFAULT",
  23:             TimeSpan.FromMinutes(1), false, conn, null);
  24:  
  25:         // Create request message
  26:         string outgoingBody = "my really cool msg that broker understands";
  27:         Message request = new Message("DEFAULT",
  28:                                       new MemoryStream(Encoding.UTF8.GetBytes(outgoingBody)));
  29:  
  30:         dialog.Send(request, conn, null);
  31:         dialog.End(conn, null);
  32:     }
  33:     catch (ServiceException ex)
  34:     { //deal with the exception
  35:     }
  36:     finally
  37:     { //clean up
  38:     }
  39: }

 

Again this code has a small issue what about if there is no Transaction available then sending a message without a transaction could be a bad thing. With one more change its ready to support both scenarios.

   1: public void SendMsgToSSB()
   2:   {   SqlTransaction tran = null;
   3:       SqlConnection conn = null;
   4:       Conversation dialog = null;
   5:       Service client = null;
   6:       string ConnectionString = "Persist Security Info = False; Integrated Security = True; Initial Catalog = MyServiceBrokerDB; Data Source = .; Connect Timeout = 30;";
   7:       try
   8:       {
   9:           conn = new SqlConnection(ConnectionString);
  10:           if (conn.State != ConnectionState.Open)
  11:           {
  12:               conn.Open();
  13:           }
  14:           if (Transaction.Current != null)
  15:           {
  16:               conn.EnlistTransaction(Transaction.Current);
  17:           }
  18:           else
  19:           {
  20:               tran = conn.BeginTransaction();
  21:           }
  22:  
  23:           // Create a service object
  24:           client = new Service("MyCoolService", conn, null);
  25:           client.FetchSize = 1;
  26:  
  27:           // Begin a dialog with the MyCoolService
  28:           dialog = client.BeginDialog(
  29:               "MyCoolServiceTarget", null, "DEFAULT",
  30:               TimeSpan.FromMinutes(1), false, conn, null);
  31:  
  32:           // Create request message
  33:           string outgoingBody = "my really cool msg that broker understands";
  34:           Message request = new Message("DEFAULT",
  35:                                         new MemoryStream(Encoding.UTF8.GetBytes(outgoingBody)));
  36:  
  37:           dialog.Send(request, conn, null);
  38:           dialog.End(conn, null);
  39:           if (tran != null)
  40:           {
  41:               tran.Commit();
  42:           }
  43:       }
  44:       catch (ServiceException ex)
  45:       { //deal with the exception
  46:       }
  47:       finally
  48:       { //clean up 
  49:       }
  50:   }

 

I have also been making a few changes to the interface that I intend to publish shortly.

2 comments »

Posted in .net, Architecture, Development, SQL Server, c#

Tags:

Proper Case in SQL

July 18th, 2008

First up Proper Case is really .  And if you ever need to convert text to Title Case in SQL you can use Vyas Kondreddi’s PROPERCASE SQL script or David Wiesman’s PROPERCASE SQL script

My only change to their scripts would be to use NVARCHAR(MAX) or VARCHAR(MAX) as compared to their use of VARCHAR(8000), if your using SQL Server 2005 or above.

   1: CREATE FUNCTION [dbo].[fProperCase]
   2: (
   3:     @Value VARCHAR(8000), 
   4:     @Exceptions VARCHAR(8000),
   5:     @UCASEWordLength TINYINT
   6: ) 
   7: RETURNS VARCHAR(8000) 
   8: AS
   9: BEGIN
  10: ...
  11: END

 

So I would covert the above syntax when using SQL Server 2000 to the T-SQL below when using SQL Server 2005

   1: CREATE FUNCTION [dbo].[fProperCase]
   2: (
   3:     @Value VARCHAR(MAX), 
   4:     @Exceptions VARCHAR(MAX),
   5:     @UCASEWordLength TINYINT
   6: ) 
   7: RETURNS VARCHAR(MAX) 
   8: AS
   9: BEGIN
  10: ...
  11: END

No comments »

Posted in Development, SQL Server

Tags:

Using the CLR on SQL Server 2005 with 4GB Ram or More

July 14th, 2008

For most people using SQL Server CLR is not an issue however when you have more that 4GB or more of memory, the CLR does not just work.

Enter the –g startup switch, You can read more about the SQL Server 2005 startup options to get the fine detail. In order to make the required changes follow these steps

1.) Launch SQL Server Configuration Manger

image

2.) Under Sql Server 2005 Services choose the instance of SQL Server that you are targeting for the change and right click on it and select Properties.

3.) Move to the Advanced Tab and locate the Startup Parameters

image

4.) Add the –g option to the end by first closing the previous parameter with a ‘;’ then –g, not adding the ‘;’ to the end of the previous parameter will cause SQL server to fail on restart

image

5.) Click Apply /OK and restart the service. When the service restarts you should now have SQL CLR on your machine. A simple Hello World CLR stored proc should do tell you if your in business or not.

 

No comments »

Posted in .net, Development, SQL Server

Tags:

Deploying SMO client runtime

June 30th, 2008

How do you deploy SQL Server Management Objects (SMO) runtime?   Follow this 3 step process for success.

STEP 1:  

STEP 2:  

STEP 3:

The links above point to the X86 framework however at the time of writing the was the latest and had links to the various processor architectures that are supported by Microsoft

I guess the next logical step is to find out what the differences will be between SQL Server 2005 and SQL Server 2008 seeing as its release is imminent. That being said my total time invested in Microsoft’s latest database technology has been zero.

While I am deviating. Am I the only one not really concerned with SQL Server 2008’s launch?

Technorati Tags: ,,

1 comment »

Posted in Development, SQL Server

Tags: