Setting up a SQL Server 2008 Cluster on Windows Server 2008

This week I was installing a new failover cluster at a client site.  Boy have there been a lot of changes regarding clustering between Windows Server 2003 and Windows Server 2008.  Microsoft has redefined clustering with Windows Server 2008, supposedly making it simpler and easier to implement, but I found it still quite an effort since a lot of the steps have changed.  I found this great article on the web that helped me to set everything up correctly so I thought I would share it here:

installing SQL Server on a Windows Server 2008 cluster

What features are supported by the various Editions of SQL Server 2008?

Have you ever wondered what edition of SQL Server you really needed for your specific project needs but you could never really remember where you can find the proper documentation to help you decide.  I was looking on the web today and it took me a while to find it myself so I thought I would share it with you all now.   Take a look at the following link and you can see for yourself. 

Features Supported by the Editions of SQL Server 2008

If you are interested in the costs of the various editions check out the following:

SQL Server 2008 Pricing

How to Diagnose Blocking issues in SQL Server 2008

Over the weekend I was running into a blocking issue with one of my clients.  I was using SQL Server 2008 and I was used to using the current activity pane in 2005 etc. to find the lead blocker. This is now gone in 2008 with the new Activity Monitor.  I started to use a lot of the old ways to diagnose using the profiler etc but this is a very tedious process.  I found this issue I was working on lastnight and it is resolved now but I did some searching on the web today and I found a really great article by Cherié Warren .  Cherie does a great job describing the ways to do it in SQL 2008 so I would advise you all to take a look when you get a chance.  She includes two really great scripts and I have included them below. 

To gather Lock stats run the following query:

SELECT  er.wait_time                      AS WaitMSQty
      , er.session_id                     AS CallingSpId
      , LEFT(nt_user_name, 30)            AS CallingUserName
      , LEFT(ces.program_name, 40)        AS CallingProgramName
      , er.blocking_session_id            AS BlockingSpId
      , DB_NAME(er.database_id)           AS DbName
      , CAST(csql.text AS varchar(255))   AS CallingSQL
      , clck.CallingResourceId
      , clck.CallingResourceType
      , clck.CallingRequestMode
      , CAST(bsql.text AS varchar(255))   AS BlockingSQL
      , blck.BlockingResourceType
      , blck.BlockingRequestMode
FROM    master.sys.dm_exec_requests er WITH (NOLOCK)
        JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
          ON er.session_id = ces.session_id
        CROSS APPLY fn_get_sql (er.sql_handle) csql
        JOIN (
-- Retrieve lock information for calling process, return only one record to
-- report information at the session level
              SELECT  cl.request_session_id                 AS CallingSpId
                    , MIN(cl.resource_associated_entity_id) AS CallingResourceId
                    , MIN(LEFT(cl.resource_type, 30))       AS CallingResourceType
                    , MIN(LEFT(cl.request_mode, 30))        AS CallingRequestMode
-- (i.e. schema, update, etc.)
              FROM    master.sys.dm_tran_locks cl WITH (nolock)
              WHERE   cl.request_status = 'WAIT' -- Status of the lock request = waiting
              GROUP BY cl.request_session_id
              ) AS clck
           ON er.session_id = clck.CallingSpid
         JOIN (
              -- Retrieve lock information for blocking process
              -- Only one record will be returned (one possibility, for instance,
              -- is for multiple row locks to occur)
              SELECT  bl.request_session_id            AS BlockingSpId
                    , bl.resource_associated_entity_id AS BlockingResourceId
                    , MIN(LEFT(bl.resource_type, 30))  AS BlockingResourceType
                    , MIN(LEFT(bl.request_mode, 30))   AS BlockingRequestMode
              FROM    master.sys.dm_tran_locks bl WITH (nolock)
              GROUP BY bl.request_session_id
                    , bl.resource_associated_entity_id
              ) AS blck
           ON er.blocking_session_id = blck.BlockingSpId
          AND clck.CallingResourceId = blck.BlockingResourceId
        JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
          ON er.blocking_session_id = ber.session_id
        CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE   ces.is_user_process = 1
        AND er.wait_time > 0

To review the objects run the following query:

DECLARE @SQL                           nvarchar(max)
      , @CallingResourceType           varchar(30)
      , @Objectname                    sysname
      , @DBName                        sysname
      , @resource_associated_entity_id int

-- TODO: Set the variables for the object you wish to look up

USE     ' + @DbName + N'
DECLARE @ObjectId int

SELECT  @ObjectId = CASE
                    WHEN @CallingResourceType = ''OBJECT''
                    THEN @resource_associated_entity_id
                    WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
                    THEN (SELECT  object_id
                          FROM    sys.partitions
                          WHERE   hobt_id = @resource_associated_entity_id)
                    WHEN @CallingResourceType = ''ALLOCATION_UNIT''
                    THEN (SELECT  CASE
                                     WHEN type IN (1, 3)
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions
                                           WHERE   hobt_id = allocation_unit_id)
                                     WHEN type = 2
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions
                                           WHERE   partition_id = allocation_unit_id)
                                     ELSE NULL
                          FROM    sys.allocation_units
                          WHERE   allocation_unit_id = @resource_associated_entity_id)
                    ELSE NULL

SELECT  @ObjectName = OBJECT_NAME(@ObjectId)'

EXEC    dbo.sp_executeSQL
      , N'@CallingResourceType varchar(30)
      , @resource_associated_entity_id int
      , @ObjectName sysname OUTPUT'
      , @resource_associated_entity_id = @resource_associated_entity_id
      , @CallingResourceType = @CallingResourceType
      , @ObjectName = @ObjectName OUTPUT

DBCC FREEPROCCACHE for a specific query plan handle

Did you know that with SQL Server 2008 you can now clear the query plan for a specific statement you are testing?  This is a wonderful addition for SQL Server 2008 as you do not have to drop everything in cache anymore.  You can just drop the query you are working on.  Look at the following code below from Books Online (BOL) and try it out yourself to see how it works:

The following example clears a query plan from the plan cache by specifying the query plan handle. To ensure the example query is in the plan cache, the query is first executed. The sys.dm_exec_cached_plans and sys.dm_exec_sql_text dynamic management views are queried to return the plan handle for the query. The plan handle value from the result set is then inserted into the DBCC FREEPROCACHE statement to remove only that plan from the plan cache. 

USE AdventureWorks;
SELECT * FROM Person.Address;
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';

Here is the result set.

plan_handle                                         text

--------------------------------------------------  -----------------------------

0x060006001ECA270EC0215D05000000000000000000000000  SELECT  * FROM Person.Address;

(1 row(s) affected)

-- Remove the specific plan from the cache.
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

TSQL2SDAY Diagramming Relationships in SQL Server

For this month’s T-SQL Tuesday, we had a guest host Rob Farley  and we were asked to write a blog post about relationships:

As always, even given the event’s name the posts are not limited to T-SQL! Any component of, or software product related to SQL Server, is fair game. MDX, SSIS, LINQ to SQL, Entity Data Model, NHibernate, and any other software product that deals with SQL Server data can be featured in your post. Be creative!


As before, any blogger can–and should–feel free to participate in this event. In order to make the event slightly more international, I’m changing the time range from PST-based to UTC-based. So the rules are:

  • Your post must go live between 00:00:00 UTC and 23:59:00 UTC on Tuesday, February 9, 2010
  • Your post must link back to this one, and it’s recommended that you clearly identify the post as a T-SQL Tuesday post
  • You are responsible for ensuring that a trackback or comment appears here so that I can find the posts

My Submission

For my post in this month’s TSQL2SDAY submission I chose to discuss SQL Server Diagrams.  This month’s focus was on Relationships so I figured the Diagramming in SQL Server would be a great topic to discuss.  A lot of people are aware of the capability to add relationships through foreign keys etc but I always found it much more tedious then it can be if we just used the Digraming features in SQL Server.  This makes things much more easier to apply relationships and to see how all the tables are relating as a whole.  It is also a free Entity Relationship modeling tool.  Let’s discuss the basics. 

1) First if you never used the diagrams before it will quickly ask you to install some needed files.  These will create some system tables that are used to hold the diagram information.  They used to be called Davinci Tables but I am not sure what they are called any more. 

2) Next we just right click on Diagrams and Select Create New Diagram.

3)From here we add the tables we are interested in setting up relationships for and then hit ok.

For my example we have two simple tables one part table and one order table.  Review them below:

Next we click on the ID in the Part Table and hold down on the mouse and drag the ID field to the Order table.  This will bring up the following screen shot where we select the Primary Key table and the foreign Key table and the field we are using for the relationship like below:

Next we specify The cascading Update or Deletes if we want that to occur and select whether we want the relationship to be checked on creation.

 Finally we can see the completed relationship and we can save the diagram.


This is a much easier way of adding relationships to our databases using a GUI tool that makes it a snap.  Try it out and see how much easier it is.

Where can I get the SQL Server 2008 sample databases?

Today I was reading over a really great article online.  One of the examples as you could guess was based on one of the SQL Server sample databases.  I really wanted to play around with it a bit but wouldn’t you know it?  I didn’t have the sample dbs installed anywhere.  I typically do not install the sample databases anymore as I always think they just take up space and I will never really use them.  Isn’t that always the case?  Well I went searching on the internet and I found them here on Codeplex and I figured I’d share the link with everyone.  You can download the SQL Server sample databases here.