SQL Server with Mr. Denny


July 10, 2008  11:00 AM

My Experience with putting a CRL Procedure into SQL

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

A little while ago I came upon a situation where I needed to put a CRL procedure into SQL Server 2005.  Now I’ve been using SQL Server 2005 since the day it went RTM, and this is the first occasion that I’ve needed to put a CLR procedure into SQL.  Could I have done what I needed to without a CRL procedure?  Yeah probably, but the .NET code was written, and had been working.

My code is written in C# as that’s what the .NET guys here use.  There is one method within the C# class called HashPassword.  It takes a text string and makes a SHA1 Hash of it which we then store.  We had to make a couple of changes to the C# code to make it work within SQL Server.

The original code looked like this.

using System;
using System.Collections.Generic;
using System.IO;
using System.Security.Cryptography;
using System.Text;
 

namespace Rapid.Database.Security{
public class User
     {
     public static void HashPassword(String password, out String hash)
          {
          SHA1 sha1 = new SHA1CryptoServiceProvider();hash = 
          BitConverter.ToString(sha1.ComputeHash(UnicodeEncoding.Default.GetBytes(password.ToLower()))).Replace("-", "");

          }
     }
}

The changed C# code looks like this.

using System;
using System.Collections.Generic;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using Microsoft.SqlServer.Server;

namespace Rapid.Database.Security

{
public class User
     {

     [SqlProcedure(Name=“HashPassword”)]
     public static void HashPassword(String password, out String hash)
          {
          SHA1 sha1 = new SHA1CryptoServiceProvider();hash = 
          BitConverter.ToString(sha1.ComputeHash(UnicodeEncoding.Default.GetBytes(password.ToLower()))).Replace("-", "");

          }
     }
}

Once these changes are made and the DLL recompiled we can attach the DLL to the SQL Server database as an assembly.  This is done with the CREATE ASSEMBLY command.  I have to use the EXTERNAL_ACCESS flag instead of the SAFE flag because my dll requires the use of the System.IO assembly which can’t be run as SAFE under SQL Server 2005.

CREATE ASSEMBLY [Rapid.Database.Security]AUTHORIZATION [dbo]
FROM ‘D:\Rapid.Database.Security.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

We can now create our procedure which needs to have one input and one output parameter to match the number of parameters within the C# procedure.  This is done with the CREATE PROCEDURE command just like creating any normal procedure.

CREATE PROCEDURE [dbo].[HashPassword]
@Password [nvarchar](50),
@PasswordHash [nvarchar](50) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Rapid.Database.Security].[Rapid.Database.Security.User].[HashPassword]
GO

The three part name of the C# method are:

  1. Assembly name which SQL Server knows about (basically the name of the DLL without the file extension).
  2. The full name to the class, in our case the namespace then the class.  Our namespace is Rapid.Database.Security with the name of the class after that.
  3. The name of the method within the C# class.

Don’t forget to enable CLR within SQL Server using the sp_configure before trying to use the CLR procedure.

 At this point I am able to run the procedure and pass in text and get back the hash.

declare @PasswordHash varchar(50)
exec HashPassword @password=‘test’, @passwordHash=@PasswordHash OUTPUT
SELECT @PasswordHash

The value of this is: A94A8FE5CCB19BA61C4C0873D391E987982FBBD3

I hope that you find this useful. It took me and a .NET developer a couple of hours to get all this working correctly and loaded into the SQL Server correctly. I know that I’ll be referring back to this if I ever need to attach a CLR method into SQL again.

Do keep in mind that when we were doing this, we created this as a separate DLL for the SQL Server as to get the original DLL into the SQL Server, SQL wanted us to load up a bunch of other assemblies that some of the other methods required.

Denny

July 10, 2008  11:00 AM

SQL 2008 RC0 Intellisense doesn’t work against SQL 2005

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

I’ve been working with SQL 2008 RC0 for a couple of weeks now, and something I’ve noticed that really sucks.  The Intellisense doesn’t work against a SQL 2005 server any more.  I’ve been using the SQL 2008 UI exclusively for several months now (since the November CTP at least) and I’ve gotten quite used to the intellisense.

While jumping around the net looking to see who else was talking about this (and several people are) I found this Connect bug about it.  The notes from Eric @ Microsoft are that if the vote count shows that enough people want this fixed they’ll see what they can do.   The comments in the Connect bug indicate that pretty much everyone wants it back, even if it’s not perfect.

The only other person I really see complaining about the lack of downlevel support is Aaron Bertrand.

Be sure to vote on the connect bug and leave a comment here or there so that the developers get the idea that we want it back.

Denny


July 7, 2008  11:00 AM

Back To Basics: The RECEIVE command (and END CONVERSATION)

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

The RECEIVE command is the last of the Service Broker items to cover in the Back To Basics series.  The RECEIVE command is what is used to actually get the message out of the queue so that you can process the data within the message_body column.  The RECEIVE command works just like the SELECT statement for the most part.  It has a FROM clause and a WHERE clause (which most people don’t use, but it’s there if you need it).  After you receive the message you will usually want to run the END CONVERSATION command.  This will tell the remote machine that you have receive the message and that no more messages should be sent on this conversation.  I usually only send a single message per conversation, so I don’t put in any logic to see if this is the last message in the conversation.

The basic syntax is very straight forward.

DECLARE @handle UNIQUEIDENTIFIER
DECLARE @message_body XML
RECEIVE TOP (1) @handle=conversation_handle, @message_body=cast(message_body as XML)
FROM [QueueName]
END CONVERSATION @handleIt is required that you cast the message body to XML, as it's transmitted as binary data.  (If you are sending a single value or a binary blob then change the cast as needed.)  You don't have to write a second command to delete the message from the queue after you receive it.  The RECEIVE command handles the SELECT and DELETE in a single command.If you haven't been able to make to to one of my sessions, here is the sample source code which I use for the service broker session.  This ZIP file contains not only the sample code, but also the slide deck from the presentation which covers much of which I've covered in these blogs.  If you have any questions about the sample code please feel free to post those questions here so that everyone can see the answers.

I may take a little break from the Back To Basics posts for a little bit, but I haven’t really decided yet, so if you see more popping up on the site, I guess that means that I’ve decided to keep working the series for a bit.  If I do take a break, I will start back up shortly. 

I promise.

Denny


July 3, 2008  4:59 PM

My Back To Basics session was way more popular than I was expecting

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

At the last SoCal Code Camp I did several new sessions.  One of which was my Back To Basics session.  When I was setting up the session I figured that a few people would show, mostly because the wouldn’t have anything else to do for the first session.

What I got instead was a massive turnout.  There were even people standing at the back of the room (and the room held about 40 people).  As to the questions, they were all excellent, and there were so many of them.  Because there were so many questions we ended up only getting about half way through the material.

Because of this I wanted to announce that next time code camp happens (Woody tells me that there will be one in October this year up at USC) I’m going to schedule it as a two part session.  That way we can be sure to get through all the material that I want to cover, and have plenty of time for questions this time.  As soon as Woody opens up the site for scheduling I’ll be sure to get the both halves of the session posted so that we can get a good judge of what size room is needed.

Check back here and on the SoCal Code Camp website for additional scheduling details.

Denny


July 3, 2008  5:39 AM

Installing SQL Server on Windows Server 2008

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

I ran across an excellent post from Shawn Hernan of the SQL Server Development team (the guys that write SQL Server).  Shawn has put out an excellent blog post on what needs to be done to get SQL Server2005 or SQL Server 2008 installed on Windows Server 2008.

 You can find the post here.

Denny


June 30, 2008  11:00 AM

New Article: Secure SQL Server from SQL injection attacks

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

An article that I wrote about securing your SQL Server from SQL Injection attacks was just published.  It covers information from both a DBAs point of view and a .NET developers point of view.

Denny


June 28, 2008  8:57 PM

SoCal Code Camp Slide Decks and Sample Code

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

As promised to the folks at the Code Camp here are the slide decks and sample code from this weekends code camp.

Back To Basics: Getting Back To The Basics of SQL Server
SQL Server 2008 Resource Governor
What Do All These Undocumented Stored Procedures Do
Storage for the DBA
Virtual SQL Servers Should I or Shouldn’t I
SQL Server Clustering 101

Thanks for attending the Code Camp, and see you next time.

Denny


June 26, 2008  11:00 AM

Back To Basics: The SEND command (and the BEGIN DIALOG command)

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

The SEND command was introduced to SQL Server when SQL Server 2005 was released.  It’s kind of like the INSERT command except that it is used only with the SQL Server Service Broker.  With the service broker you put data into a queue instead of a table.  Insert of inserting a command into the queue, you send a message to the queue, kind of like sending an email to someone else.  The service broker uses this same kind of idea (not really, but email is something that everyone gets, so it’s a half way decent anology).

 When you use the SEND commad you also have to use the BEGIN DIALOG command to get everything ready.  So before you can send the message you have to start a dialog, which then starts a conversation, with the service which will be doing the heavy lifting.  This is done with the BEGIN DIALOG command as shown in the code sample below.

 After you have the dialog handle in the @dialog_handle variable you use this handle to send the actual message to the conversation which was started when you begin the dialog.  In our example below we are just going to send the contents of the sys.tables DMV to the the queue.

DECLARE @xml AS XML
DECLARE @dialog_handle AS uniqueidentifier
SET @xml = (SELECT * FROM sys.tables FOR XML AUTO)BEGIN DIALOG @dialog_handle
FROM SERVICE [tcp://codecamp/AW/sample_send_service]
TO SERVICE 'tcp://codecamp/AW/sample_receive_service'
ON CONTRACT [tcp://codecamp/AW/sample_contract];
SEND ON CONVERSATION @dialog_handle
 MESSAGE TYPE [tcp://codecamp/AW/sample_messagetype]
 (@xml);

Confused yet?  Shortly there will be a Back To Basics post about the RECEIVE command which is how you “read” the message.  At the end of that post I’ll include a link to some sample code which I’ve used a few times in my sessions which should help wrap this all up.

Denny


June 25, 2008  9:27 PM

Comic Book Cover Contest

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

I know that this is way outside the normal content for my blog, but oh well.

 A friend at work has entered a comic book cover contest.  Needless to say he’s very talented.  His picture is below.  The reason that I’m posting this is it’s a public voting contest, and he needs your help.  The winner of the contest will have their cover published as one of the covers of the comic when it’s released.  His name is Jamie Tyndall, you’ll find his artwork second from the bottom of the list.

Jamie's Cover

Thanks,

Denny


June 23, 2008  11:02 AM

How much performance are you loosing by not aligning your drives?

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

Myself and many other people have been saying for quite a while now that you need to align your disks before putting data on them.  I know have some information on how you can figure out just how much potential performance you are loosing by not aligning.

 Before we can begin to figure this out we need to know what the average work load for the disk is going to be.  In the SQL Server world this is easy.  SQL Server does everything in 8k pages within 64k extents.  Each time it needs to read from the disk it reads the 64k extent from the disk and each time it writes to the disk it writes the 64k extent.  So our data size is 64k.

We take this number and divide by 64.  So in our case 64/64 = 1.  1 as a percentage is 100%, so 100% of our data reads and writes are requiring two physical reads or writes.

If you are in the exchange world everything is done in 8k reads and writes.  So in this case 8/64 = 0.125 or 12.5% of the reads and writes are requiring two physical reads or writes.

Now for SQL Server just because we are doubling the number of operations doesn’t mean that by fixing this you will double your disk speed.  What it means is that if your disks are running at 100% utilization you can probably reduce your disk load by 50%.  But if your disk utilization is 30% your disk activity won’t be any faster as your disks are not running at capacity.  Will you see a performance improvement, yes.  Will it be as high as if your disk was at 100%, no.  Should you still fix the alignment problem?  Yes.

To fix the problem isn’t easy.  You have to remove all the data from the disk, and delete the partition, then recreate the partition using the DISKPAR.EXE (Windows 2000) or DISKPART.EXE (Windows 2003/2008) with the ALIGN=64 setting.  To remove the data from the disk you will either need to migrate to a new disk within the server, or backup the database, fix the alignment then restore the database.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: