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:

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
FROM sys.dm_db_index_physical_stats(
) x
WHERE x.avg_fragmentation_in_percent > 50

Syntax 2 where we are having no issues:

use master
FROM sys.dm_db_index_physical_stats(
) x
WHERE x.avg_fragmentation_in_percent > 50
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.
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

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….!

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.

I’m really impressed with this and suggest you folks; do Consider testing this trace flag on your Servers!

Custom Server Roles in SQL Server Denali :)

Custom Server Roles! Yes, this is a brand new enhancement Microsoft has provided in SQL Denali.
Upto SQL Server 2008R2, We only had flexibility for creating Custom Database Roles. But the Server roles were fixed, which comes along with your installation.

So, Let’s peak on how to create a custom Server Role and assign Server Scoped Permissions in Denali!!
Pretty Simple Using GUI:
Scenario: Let’s assume your team hired a Level2 DBA who needs supervision for few weeks/months. You don’t want to grant him sysadmin privileges where he can do what ever he wants. You really don’t want a Jr DBA connecting to your SQL Server and stop/Start/Restart SQL Services. So let’s assume your plan is just to limit his permissions to Create/Alter any Database, Alter Trace – for Running Traces, Alter Logins/Connections/Linked Servers. What can be done with less pain is Just create a Server role with desired permissions and add him/her as its member.( you can use your own imagination for Server Scope Permissions, for example…if you want him/her to control DB Mirroring, consider granting perms on Mirroring ENDPOINTS and so on….). You can Just add all your newly joined DBA’s to this Role which is Just a matter if Single Button click 🙂
In my Example, I’m creating a Server Role called as “Lev2DBA” and choosing Server as a SECURABLE and granting permissions as per my requirement.

Snap 2:

Now, go to Members Tab and Add that Level2 DBA(login “test” in my case) to this Role as shown below.

Note: You can also directly add this to any existing Fixed Server Role directly from “Memberships” tab as shown below.

If you’ve  observed thoroughly,by this time you might’ve noticed that in the above Screenshot there is no “SYSADMIN” role. I believe MSFT is not allowing us to add these members to SYSADMIN role. ( Not sure, why they are allowing ServerAdmin in that case!!!)

It’s also pretty Simple from T-SQL: You can follow the below Syntax for achieving the same.

USE [master]

That’s it Guys! Very Very Simple huh?….
Happy Testing Denali!

Contained Databases in DENALI

Contained Database : A Brand new interesting feature introduced by Microsoft in SQL Server Denali(2011).
let’s get straight to the point. Simple and straightforward use of contained Database(s) – Let’s say you’ve multiple applications sharing the same SQL Instance and you’ve multiple Jobs,Linked Servers, Logins for respective Databases. consider the scenario, where you are trying to migrate the database, the entire pain comes when dealing with respective Logins, Jobs,Server Objects. 
With the introduction of “CONTAINED DATABASE”, you can assume that you are isolating your application(database) from rest of the applications(Databases) on this SQL Instance. So…They are independent of Instance itself in terms of Instance level dependencies!
Are they really independent in every aspect?? No….definitely not! let’s say your SQL Instance is DOWN, then of course these Database(s) will also be Down ;-p. So basically we are going to introduce some level of isolation from rest of the Databases and some level of freedom(independence) from actual SQL Server itself!

Terminology :
I’ve taken this terminology(definitions) from MSDN Library. The link is provided below FYI.
Application boundary:The boundary between the application model and the instance. Application model functions are within the application boundary, and instance functions are outside the application boundary.
Contained:A user entity that lives entirely within the application boundary.
Uncontained:A user entity that crosses the application boundary.
Non-contained database:A database with containment set to NONE.
Fully contained database:A fully contained database does not allow any objects or functions that cross the application boundary. Fully contained databases are not currently available.
Partially contained database: A partially contained database is a contained database that allows features that cross the application boundary.
Contained user:There are two types of users for contained databases.

  • Contained database user with password (SQL Auth)
       Contained database users with passwords are authenticated by the database.
  • Windows principals (Windows Auth)
       Authorized Windows users and members of authorized Windows groups can connect directly to the database and do not need logins in the master database.

Note: Users based on logins in the master database can be granted access to a contained database, but that would create a dependency on the SQL Server instance, so creating users based on logins is not recommended.

So what exactly are we gaining from this new feature?? It stores the below mentioned thingswhich can make a DBA life much more painless…:)

It stores all the Cool info like Tables,Procs,Functions,Constraints,Views,Schemas..etc as any other regular Database. Besides this, It also stores Instance level objects like Logins, Application Agent Jobs, Error Msgs, Linked Servers etc. Hurrrray….we can escape from dealing with Orphaned Users, SID Mappings blabla…:) Tadaaaaaaaaaaaaa!!

Let’s start a Quick DEMO:

Step 1: By default “Contained Database Authentication” property is set to ‘0’-False as shown below.

We’ve to set this Option to TRUE from GUI or use the below code from T-SQL:

/*Enabled Advanced options*/
sp_configure ‘show advanced’, 1;
/*Enabled Database Containment */
sp_configure ‘contained database authentication’, 1;
Step 2: Now we can create a Contained Database as shown in below Screenshot.
Change the Containment type to “PARTIAL” from “NONE”. Default for any Database is NONE.

From T-SQL:

CREATE DATABASE [Contained_tst] CONTAINMENT = PARTIAL ON  PRIMARY ( NAME = N’Contained_tst’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\Contained_tst.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N’Contained_tst_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\Contained_tst_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)GO

Step3: Now we’ve created a contained Database. Let’s play a bit with creating a test user and connecting via the new test user.

From TSQL:

USE [Contained_tst]GOCREATE USER [contained_usr] WITH PASSWORD=N’test’, DEFAULT_SCHEMA=[dbo]GO

Now let’s try connecting to the SQL Instance using the “contained_usr”.

Note: it might fail if you try to connect to default Master database because this particular login doesn’t exist at the Server level in the Master Database.Once connected through SSMS, It looks like the below Screenshot.

I’m not seeing any other Databases, Agent, Server objects…Literally I’m not able to see anything beyond this particular database. waiting for testing this Brand new thing in real environment!One More GOTCHA :“ALTER DATABASE” Syntax will fail for Contained Databases. We’ve to use “ALTER DATABASE CURRENT“!!Conclusion: IMHO this is a great new feature which we all(at least I was) were waiting since ages! Even though it is well publicized as a Contained Database is instance or server independent, has no external dependencies and has self contained mechanism of authenticating users, I would say no! It’s actually not Server independent. A contained Database can’t escape anything which impacts the Physical Server(Instance SHUT DOWN, SERVER REBOOTS etc).Hope this helps to give a Kick Start for exploring this new added feature of DENALI.