July 2011 – MSSQLTREK

Installing SQL Server 2008/2008R2 Failover Cluster- Part1

This is the first part of the series “Installing SQL Server 2008/2008R2 Failover Cluster”.

Let me ask you something first! Have you ever been in the situation where you want to explore about your cluster, but your hands were tied because of the Live Production Server? Raise your Hands Up if “YES”. I can see lot of hands raised. Okay!Come back to your normal position before somebody sees you raising your hand, sitting in front of your computer ;-p.
Did you ever think that ” hey what happens if i do this on my cluster, what is that option, what is this option etc…” but again you couldn’t because of the real environment. I Agree we’ve POC(Proof Of Concepts) Servers for DBA’s team, but how much freedom do you really have to destroy the entire cluster?? Do you really think that you Boss will be Okay if you destroy the entire cluster in your LAB?? Most of the LAB environments I’ve seen will be a simple Stand Alone Server(s) where we can install and play with new SQL Server(s) and Service Packs and so on….But really they don’t fulfill our desire to explore and play with cluster and SAN!!

Don’t worry…in this Series I’m going to explain step by step installation right from creating your own Domain on a Domain Controller, Build your own SAN(Yeyyyyyyy! Exciting huh???), Build your Windows Servers and at last Installing SQL Server 2008 Failover Cluster 🙂

Before going any further, please read What a cluster can do?. So…by this time i assume you understood what a cluster can do from 20,000 miles distance!
Let me explain little bit further:

Failover cluster is a combination of multiple Servers(Physical Nodes) all connected to a shared storage(SAN-Storage Area Network).
If your Primary Node fails for some reason,the hot spare node(Active-Passive config) takes over the ownership of your application and all your operations continue to work seamlessly
Failover Clustering provides high availability for your entire SQL Server Instance(Failover occurs as a Single unit) unlike Log shipping or Database Mirroring where they offer only Database level Failover.
Your SQL Server Failover Cluster appears as a single computer to the clients connecting to you SQL Server.They don’t even know at what node SQL Server is running on.
SQL Server offers Single Instance Clusters and Multi-Instance Clusters.
Single Instance:Let us say, you’ve only one SQL Server Instance running at any given time on your cluster. It’ll be running either on your 1st node or 2nd node.(Active-Passive).
Multi Instance: Let’s say you’ve 2 nodes running 2 Instances or even 4 Instances of SQL Server, Or let’s say you’ve 3 nodes where you’ve 2 Instances of SQL Server(Active-Active-Passive), the third node serving as a standby node ready to take ownership in an event of any failure of Node1 or Node2.

Hopefully below screenshot makes it more clear how things work in Clustered environment.

Things to Know:

Failover Clustering is an Enterprise edition feature. So, You can create a FOC only on Windows Server Enterprise or above(Windows Server Data Center Edition).
SQL Server 2008 Enterprise on Windows Server Enterprise/Data center supports upto 16 Physical Nodes and 50 Instances of SQL Servers 🙂
You can do SQL Server Cluster installation with SQL Standard edition too(But remember Windows needs to be minimum on enterprise edition)…but it only support upto 2 physical Nodes and can be scaled upto 16 Instances of SQL Server!!(which is never a good solution to go upto 16 Instances on Just 2 Nodes)
SSIS/SSRS are not cluster aware by default.(You can imagine SSRS as a Web Service where you really don’t need to be as a fail over cluster service. Typically it goes with scaled out deployment on NLB Clustering).
There are few tricks where we can cluster SSIS, but it is not recommended by Microsoft to cluster your SSIS installation.

Initial Preparation notes/Prerequisites for creating your LAB on your Laptop:

Download and Install Vmware Workstation on your Laptop. You can really go crazy with Vmware ESX(i) hosts and Vsphere or Citrix XEN Server. It’s up to you if you really think that your Laptop can handle all that load. I prefer VMWare Workstation to keep it very simple.

Download Openfiler here or Starwind Software here to Build Software based SAN. In my demo I’m going to use Starwind Software. I prefer Starwind considering its simplicity and ease of implementation(especially for LAB purposes).

Download Windows Server enterprise eval edition from microsoft.

Download SQL Server 2008/2008R2 Enterprise eval Edition from microsoft.

General Guide Lines for Installing a cluster in your Production environment:

Minimum 2 windows servers(enterprise edition) besides your domain controller.
All the participating Hardware and drives should be certified by microsoft.
minimum two NIC are needed per server. Netbios should be disabled on the network cards associated with HeartBeat(Private network).
Shared Storage(SAN) is needed. Again PS, this should be microsoft certified.
Ensure that disks have same drive letter mappings on all the physical nodes participating in clustering.
Windows Server Failover Clustering Feature and Application role should be added on Each node manually participating in SQL clustering.

Let us see the actual Windows installation(s) and setting up domain controller in the Second part of this Series. See you in next part.

What is IPv6? Why should we care about it?

In my leisure time I was going through few of the recent advancements which world is moving towards in Information Technology. I found IPv6 Interesting of all. Like me, if you are curious about knowing what IPv6 is and how it offers advantages over IPv4, this short animated video will help you.
I guess we are not far away moving all our Servers in our Data Centers to IPv6 from IPv4.

Video from Explania folks:

Also, an interesting and very informative Pic from Focus.

Image Source: http://www.focus.com/images/view/49504/

Hope this gives you all a good Kick Start exploring about IPv6.

DMVs with Database Compatibility Level 80??…

Let me give a brief background on why i would like to discuss regarding this interesting and most often ignored topic of Dynamic Management Views(DMVs) and their relation with Database Compatibility Mode.

Note: When i say a DMV, i actually mean DMO(Dynamic Management Object) which Includes both DMV’s and DMF’s(Dynamic Management Functions). To avoid the confusion, people generalized and started calling both DMV’s and DMF’s with a single term called DMV and i love it:)

Why am i sharing this today?? Well, we(our Team) recently started a Small Internal Project something called as “Performance Tuning Initiative”. As you all know, a DMV can help a DBA than any other free tool(Sometimes a paid tool too) out there in market!! So…we started to Pull all the Cool information with proper benchmarks and started doing trend analysis and monitoring using few fabulous DMVs which are available to all of us.
Note: We’ve few Databases with Compatibility Mode 80 because of Vendor Restrictions even running on SQL Server 2005 SP4. So…as you imagined by this time, today in the morning myself and one of the DBA’s in my team had a discussion regarding how DMV’s behave if run against a database with Compatibility Level 80. Interestingly, he was in a misconception – “Most of the DMV’s are of no use if the DB Compatibility Level is 80” and i was arguing exactly the other way.- “Most(Almost every) of the DMV’s will have no issues even the Compatibility level is 80”

Quick Demo:
I’ve a Database “AdventureWorksDW” set to Compatibility Level 80 as you can see below.

Let’s run few typical DMV’s which we’ll be using on a regular basis against this database.

In the above Screenshot you can see it had no issues with the “AdventureWorksDW” Database.

Let’s see what happens if we run our sys.dm_db_index_physical_stats against this Database.

Ummm…it is screaming that there’s a syntax error. Actually it’s not. There’s a Simple trick to crack this thing.

Crack – Execute the DMV in context of any other database which has 90/100 compatibility mode. In my case I’m running on SQL Server 2008R2 and my master database has 100 Compatibility level.
Syntax 1 where we had issues:

USE AdventureWorksDW
GO
SELECT *
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
GO

Syntax 2 where we are having no issues:

use master
GO
SELECT *
FROM sys.dm_db_index_physical_stats(
DB_ID(‘AdventureWorksDW’)
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
GO
The results are as shown below in the screenshot.

If you observe the syntax carefully, I’ve just changed the database context of execution and I’m passing my Database which is in Compatibility level 80 to the DB_ID() Function.
What’s the Reason behind this??
Very Very Important……
Because the physical structure(Version) of the database is not going to change when you change compatibility level.In other words you are not downgrading a Database at it’s underlying physical architectural level by just reducing the compatibility level to an older version. You are changing only few Queries execution behaviors! That’s all basically what you are achieving here by reducing compatibility level to 80 in this case. Once the Database is on SQL Server 2005 by any means(Create from Scratch/Restored/Detached-attached), that’s it…the Database Version is set to 611/612.
Note:
SQL Server Denali Database Version – 700
SQL Server 2008R2 Database Version – 661
SQL Server 2008 Database Version – 655
SQL Server 2005 Database Version – 611/612
SQL Server 2000 Database Version – 539.

That’s the reason why you can’t restore a Database from SQL Server 2005(2008) onto SQL Server 2000(2005) even though you changed the compatibility level to 80(90). SQL Server won’t allow to downgrade any database version. No exceptions here!! You’ve to choose the difficult path of moving object by object and data using SSIS!!

Let’s do a Quick Demo what I’m talking about:
SQL Server Database stores the Database version information in it’s Boot Page( Page 9 of 1st File in the database). I’ve to turn on traceflag 3604 to dump the boot page to our results pane. For More Information on this please see my previous blog post http://sql-buzz.blogspot.com/2011/06/checkdb-in-errorlogs-on-startup.html

In the above screenshot, you can see the database has 80 Compatibility level but the DB version is 661(SQL Server 2008R2).

Hope this information is useful….!

What’s New in DENALI

I’m in the process of exploring all new cool features and enhancements of DENALI. Thought of sharing the MSDN Library URL which explains all the NEW features of Denali at one Single Place.
Please do follow the link http://msdn.microsoft.com/en-us/library/bb510411%28v=sql.110%29.aspx

Have Fun! Happy Testing Denali:)

Prevent logging all the Backup(Successful) Events in your Error Log

Have you ever been in a situation where you were trying to analyze a particular error details from your error log and you are annoyed by whole bunch of “Database Backed up/Log was Backed up bla bla…” messages?? Let’s say your Instance has 20+ databases and most of them are Log shipped(assume every 10 Mins log backups) to it’s DR Server (This is a very common Scenario). If you see anytime in your error log, you’ll end up with frustration digging into what exactly you are looking for. Because SQL Server has a Bad habit of logging in each and every detail(Success or Failure) of backup Operation(s) by Default. You’ve to define all kinds of crazy filters in your logs to exclude the junk and look what exactly you are looking for. Basically, You’ll be seeing Error logs something very similar to what you are seeing in the below Screenshot.

So…Is there anyway where you can get rid of all these Successful Backup events being logged from your SQL Server Error Log?? The Answer is YES 🙂
Microsoft has introduced a TraceFlag 3226 which you can enable globally, which eliminates all these Junk(In most of the Cases) getting logged into error log. Let’s do a Quick Demo.

Let’s recycle the Error Log, Just to make sure that we’ve a clean log – easy to Demo. It should come up as shown below.

Now, let’s start the actual testing.
I’m taking a Full Database Backup and an immediate Log Backup. Now let’s review our Error Log in the below Screenshot.

It logged Both the Full Backup and the Log Backup Operations.
Let’s turn ON TraceFlag 3226 now and let’s take couple of Backups and turn OFF Trace Flag and again take a backup and see what get’s logged in our Error Log for all the above operations. Just to convince you folks, I’m again Turning OFF Traceflag again and show you the Error Log all at one glance(See Below Screenshot)!!

If you carefully observe the Error Log at the left the tst5 and tst6 backup events are not logged and once we turned off the trace flag tst7 has been succesfully logged.

By this time you should be thinking what about the Failed Backup Events, We need them to be logged all the time in our Error Log. Absolutely “Yes“!!! All the Failed Backup Events are Successfully logged even with this Trace Flag Turned ON. You can see that in the below Screenshot.