SQL Server 2008/20008R2

Reviewing SQL Server Error Log – A Better approach!

How many of you have a habit of reviewing Error Log(s) of the SQL Server(s) which you support on a daily basis(At least on regular basis, if not daily)? If you are not doing that, go ahead and start making it as a practice from “Right now”. You can thank me later for this suggestion 😀

If you’re already doing that,then you are awesome…! But, let me ask this. How many times you get annoyed reviewing your error log(s), especially if you are looking for a specific error. Well, you can apply filter or you can simply query your error log from T-SQL. But what if you are only looking for all the severe Errors, total error count, Errors by frequency all at one place? Yes, you can write your own query and use SSRS for generating a fancy report. But Wait……What if I say SSMS already has this cool report inbuilt for us?

In this blog post I will show you a better way to decipher your current Error Log for Errors by using that hidden report.
It’s called “Number Of Errors” Report and you can get to this report by right clicking on “Management” Node -> Reports -> Standard Reports->Number of Errors in your SSMS as shown below.

How it looks? See below…

As you can see, at a glance I know I’ve got 6 Severe Errors(Sev 18 or above) and 5 Moderate errors in my current error log. See below screenshots for how neat the info is once I expand those nodes.

Severe Errors:

You can see I’ve got the same 824 Error repeated 6 times. ( Well, I tried attaching the same corrupted ldf file 6 times for this demo)

Moderate Errors:

All errors by Frequency:

Is in’t nice? Personally, I liked this report…and thought of sharing what I’ve discovered with you folks 😀
Please ignore if you are already aware of this nifty report, if not…well, you know it now!

The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group.

Let me share with you guys, an Error Message what I’ve encountered yesterday. Well, this is not a very detailed post but I hope this will help what to check for when you encounter this Error/Warning Message.

Okay, I was validating a brand new SQL Server Clustered Instance(two node) and I tried to failover the Instance to the other node. But when I try to initiate a failover, I got this Message saying “the operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group“.

What’s the Issue: The 2nd Node was not added into this Clustered Instance.(Add Node to Cluster piece was missed by whoever Installed this SQL Server Cluster)

How to Confirm?

Go to the 2nd node and look for SQL Server Binaries for respective Instance. Also you should be seeing SQL Server Instance Being Installed but in Offline Status(assuming this is your Secondary Node at this particular point of time) from your SQL Server Config Manager Oooor you can simply Open Failover Cluster Manager and check for Possible Owners(Not preferred Owners) for this Instance.

Once he added the missing node to the SQL Cluster, everything was back in business as expected.

I know it’s a Quick and a Dirty Post, But I hope this helps!

How to Add a New Disk/Drive to SQL Server Failover Cluster?

Hey Folks! I know…..It’s been a long time since I wrote something technically related to SQL Server(well, am playing with my new DSLR Camera 😀 a lot now a days in leisure times) and so I’m here back with a very interesting and confusing topic for many DBA’s. Recently I had a discussion with one of my buddies on issues she had with a newly added drive to Windows Server 2008R2 Failover cluster and I was able(at least I think I was :D) to explain over our phone conversation! Later thought about coming up with a write up which might help even others in a similar situation. So, In this blog post let’s see how to add a new drive to your existing SQL Server Failover Cluster(Just an FYI : Win Server 2008/SQL 2008 in my case).

Before going any further, I’ll be wearing 3 hats in this Blogpost(a SAN Admin hat, Windows Admin hat and obviously a SQL DBA hat). Let’s start with wearing a SQL DBA hat!

Below are the screenshots of my current SQL Server Failover Cluster which I’m going to add a new Drive.

As you can see I’ve 6 SAN Drives dedicated to this cluster. Let’s see how to add a new drive with a name aaaaahhhh……say “SQLBacks2” to our SQL Server. First thing is your SAN Admin should create/present a new Drive for you to be able to add to our cluster.

Okay, let me wear my SAN hat. Now am a SAN Admin and I’m going to create a new SAN Drive for my Windows/SQL team.

Creating a SAN Drive:

Note: If you didn’t followed my earlier Clustering Series, I use Starwind for all my SAN Stuff. Just refer to my previous posts in clustering series to understand this tool more in depth.

step1: Add a new Target.

Step 2:

Step3:

Step 4: placing and sizing the Drive( I chose just 1 GB for this example)

Step 5: Making it a ISCSI aware disk(Mandatory for Clusters)

Okay..now Am done with SAN guy role and now I’m wearing a Windows Admin Hat to initialize the new disk which my SAN Admin just created for me from my Nodes.

Wearing a Windows Admin hat:

Went to ISCSI Initiator and all I have to do is Initiate this new drive as shown below. you can see our new drive as Inactive as of now in the below screenshot.

Once you click ok, you will be seeing this drive as connected in your ISCSI Initiator, but still this is not available for Windows. Now we’ve to go to Server manager->Storage and Bring it Online->Initialize->Create new Simple Volume(Format the Drive) so that you can see it physically in your My Computer. (See below Screenshot, once I did created the drive, I selected K$ as my volume)

Once, this drive is logged on both the Nodes basically now we’ve to add this drive to our Windows Cluster using Fail Over Cluster manager(am on Node1) as shown below.

It will search for all the disks which are suitable for clustering for a while…After few seconds, You’ll see below.

Once added, you can see this Drive has been added to our Cluster as Available Storage(Note: At this Point SQL Server Service is not yet ready using this Drive).

Now…Right Click on your SQL Server and select “Add Storage” as shown below.

So…Are we done yet? Nopee…….Here comes the most Important Part and the piece which I’ve seen people missing a lot while adding a new Drive to existing SQL Server Clusters. So what are we missing here? The Answer is SQL Server Dependency. Didn’t quite understood what I’m saying? Well, see the below Screenshot where Drive 7(new drive which we just added) is floating in air with no relation to SQL Server Service.

How to verify that the Drive has not yet added successfully 100% to our SQL Server Instance from SSMS? It’s very simple, Just try to access this Drive from your SSMS. In this example I’m just trying to take a backup of one of my Databases and looking for $K to place the backup, which I can’t see(But I can see it from My Computer) unless I add the dependency Manually as you can see below.

So…What to do now?? Just make an “AND” Dependency for your New Drive. But how??

Wearing SQL Admin hat now! Well, this could be even a Windows Admin depending on your company!

Navigate to your SQL Server and Right Click and select properties and go to dependencies tab as shown below.

As you can see, there is no Drive 7(DriveK) as dependency. Now you have to add the new drive as “AND” dependent as shown below and you are all set to go 🙂

Now see below Dependency report, where Drive 7 is no longer floating in the air 😀

and of course a happy SSMS as you would expect to locate the Drive for your Instance!

Now you are all set guys…Just try to move the Service to other node(s) and double check that everything is working as expected from all the nodes. Hope this helps! Cheers…

SQL Server 2008R2 SP2(CTP) got released

Microsoft released SQL Server2008R2 SP2(CTP) yesterday(13th May 2012).

You can download and test if if you are willing to from http://www.microsoft.com/en-us/download/details.aspx?id=29848

For list of Bugs that got fixed with this release, visit http://support.microsoft.com/kb/2630455

Cheers!

DB Mirroring from SQL 2008SP2 to 2008 RTM or from SQL 2005 to SQL 2008?

Have you ever thought about this? Can we Mirror a Database from SQL Server 2008SP2(Principal) to SQL Server 2008(Mirror) RTM? Well, how about Mirroring from SQL Server 2005 to SQL Server 2008?? Let me show you the prior case.

Scenario 1:

Principal DB Engine Version: SQL Server 2008 SP2

Mirror DB Engine Version: SQL Server 2008 RTM

DB Name: DB_Mirror

Answer: Yes, we can! You can see below screenshot.

You should not see any Issues with Failover/Switching DB Roles from Principal to Mirror and Viceversa.

Scenario 2:

Principal DB Engine Version: SQL Server 2005 SP3

Mirror DB Engine Version: SQL Server 2008SP2/2008R2.

I don’t have a SQL Server 2005 Instance to show you how it works, but Yes, we can! Very Important point to consider in this case is, Once you failover(Switch the Mirroring Roles) the Database from SQL 2005 to 2008, you can’t failback! This is One-Way approach.

Note: You can’t setup Mirroring from 2008(as Principal) to 2005(as Mirror). Well, DB Version can’t be downgraded. In other words, we can’t restore a DB to lower Versions!!

Hope this is Informative and something which you can think about as an option for upgrading/Migrating Large Databases with very less acceptable downtime! ( Make sure, you understand all the caveats before trying this in your Prod Environments).