Archive for August, 2012

Another awesome discussion for T-SQL Tuesday, and big thank you to Mike Fal. The question is what sort of tips and tricks you use with SQL Server. The first thing that came to mind for me was how I track the history of Windows cluster failovers. I am not a big fan of having to dive into error logs, events and emails to just find the history of my servers. Recently I ran into a situation where a Clustered SQL Server Install was performing better on one node of the cluster than the other node. I have a history of the performance, but I did not have a quick easy way to look at the history of what node was hosting when. The solution I came up with was to use my dbUtility database to track when a node was hosting. This way, I don’t have to go looking around error logs, or emails looking for any failover events. The solution is quite simple.

First I created a table to host the information that I wanted to track.

Simple enough? Below is a small version of the information that I track.



CREATE TABLE [CLU].[ClusterLogging](

[ClusterLoggingID] [int] IDENTITY(1,1) NOT NULL,

    [ClusterName] [sysname] NOT NULL,

    [PrimaryHostName] [sysname] NOT NULL,

    [ReadingDate] [datetime] DEFAULT getdate() NULL,




Once the table and the Schema are there, I created a stored procedure that will check and find out the host is that is currently running. This information can be pulled from the Server Property Function without much difficulty, once I have that information, I checked to see if this is a new host, if it is a new status and log the information if it is new. This is the procedure that I use.






DECLARE @CurrentStatusID INT


SET @PrimaryHostName = CAST(SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’) AS SYSNAME)

— Is this a new entry? If so insert seed.

If Not Exists (Select ClusterName, PrimaryHostName From CLU.ClusterLogging

            WHERE ClusterName like @ClusterName

            AND PrimaryHostName like @PrimaryHostName)


        Insert CLU.ClusterLogging (ClusterName, PrimaryHostName)

        VALUES (@ClusterName, @PrimaryHostName)


— What is the ID of the current Status

SET @CurrentStatusID = (Select Max(ClusterLoggingID) From CLU.ClusterLogging

            WHERE Clustername like @ClusterName

            AND PrimaryHostName like @PrimaryHostName)

— Does the current hostname match the most recent status?

If @PrimaryHostName not like (Select PrimaryHostName From CLU.ClusterLogging

            WHERE ClusterLoggingID = @CurrentStatusID)


        Insert CLU.ClusterLogging (ClusterName, PrimaryHostName)

        VALUES (@ClusterName, @PrimaryHostName)



All that is left is to schedule the execution of the stored procedure, I imagine it could be triggered as a startup procedure, but I just choose to run it on a schedule basis. I know that the times may be off a little depending on how often I run it however; it gets me in the ball park. Now I know that I can query the CLU.ClusterLogging table in my utility database and I will have a decent idea of what happened and when. Much more efficient than digging through large error logs that only go back so far.

Thanks KCMO

Posted: August 13, 2012 in Events, SQL Road Trip

On August 2nd myself and a couple of fellow SQL Guys jumped in my Hummer and took off to SQL Saturday 159 in Kansas City, Mo. or for those of you in the KC area… KCMO.

I wanted to give a huge Thank You to all the sponsors of not only this event but the companies that sponsors at any SQL Saturday event. I am not sure if we thank them enough, these events cannot be done without the help of these sponsors.

Aug. 3rd The Crawl

On Aug 3rd, we did the famous BBQ Crawl that took us to a number of really great BBQ joints. There is one in particular that really stands out, called Boulevards. I am not sure how to describe this place without being offensive. If I were to create an atmosphere from scratch where I would enjoy good food, great friends and just relax with a cold one. I think this would be it.

I can’t say that I have ever felt more at home meeting new friends and talking about all the cool things that were to come at the event the next day. One thing for sure, is that the awesome sessions they have at these SQL Saturday events is only the beginning to really getting everything you can out of these events. The network that can be built here is awesome, and the SQL Community doesn’t disappoint. So if you are learning SQL Server, or even if you are an expert at SQL Server, I cannot tell you how much I recommend going.

On Friday night we went to a local older theater for the speaker dinner, I ran into a good friend of mine where we talked the evening away. We had to call it an early night because 5 AM Saturday morning was doing nothing but getting closer.

Aug. 4th The Event

This is the second time I have been to a SQL Saturday in that KCMO and both of them have been at the same location. I must admit that this has got to be the best place that I have ever been to a SQL Event. The location itself is in an old casino that has been purchased by a local company. What makes this really neat is that most the things someone would need for entertainment of large groups of people are already on site. Some of the session rooms show this a little more than others; this picture is of a good friend of mine John Morehouse giving his session.

I did a session on “what is the best disaster recovery plan”. I was excited that it was well attended and I think we had a lot of good discussions. I was impressed that there were people of all skill levels that could add to the discussion with stories about what had happened to them in some of the past environments that they had been in. I am still working on some of the timing for this session and how I can present it where we can get a lot more interaction between the attendees. I think there is a lot of benefit in the way that the material is presented, and then taking the time to apply that material, to real case situations. But to do this I think the session would last at least a couple of hours, and would be more of a workshop type session than a lecture type session.

I have organized or been part of the planning for a few SQL Saturdays; I have been a speaker, and an attendee. But this is the first time I have gone as a sponsor as well. During the afternoon break Xtivia, was kind enough to purchase smoothies for the attendees at the event, I can tell you that I was really excited to have the opportunity to be part of event in this manner. It was a hot day in KCMO and I think that the smoothies did a great job of hitting the spot. We picked smoothies because with the Xtivia SQL Server health checks and the Remote DBA services our clients SQL Servers run smooth, so it was a natural jump and a fun to offer smoothies.

Without the help of the organizers and many other volunteers I am not sure we could have poured some many of these smoothies, so I wanted to make sure that I mentioned that we had a lot of help to serve so many, the second picture is only about half of the smoothies that we poured that afternoon. I can say I even went as far as trying my first smoothie, and well sure I am not a big fan of them because I can’t eat strawberries but I am not sure I would turn down the chance to have another Mango one if I come across one again. In the future I will be heading up to SQL Saturday #169 in Denver Colorado on September 22nd. and then I will be following it up by heading over to Lincoln Nebraska for SQL Saturday # 165 the first week of October.

I hope you take the opportunity to visit one of these events, or any of the SQL Saturday events in the future. I can assure you that a good time is had by everyone, and the learning opportunities are plenty. If you have learned something about SQL Server, and are willing to share it, consider speaking at a SQL Saturday event. There is a big need for those of us in the community to share what we have learned about SQL Server, and how it impacts our work each and every day. Keep an eye out for an event in your area by checking out or follow the hash tag #SQLSat on twitter. If you don’t have a SQL Saturday in your area, you might just think about hosting one. There are a lot of resources for you and a couple people can host a great event. Not all of the SQL Saturdays need to be in a big city, and not all of them need to have 50 or more people come to them to make them a great event, all that is needed is people who want to learn about SQL Server and network.

Most importantly I want to make sure I say thank you to all the attendees, as a speaker I can’t do what I love to do without the attendees and well, helping someone find the answer to an issue that they are having is primary reason that I like doing what I do.