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…
MSSQLTREK 
Excellent! blog. Thank you Sree
Very help full !! Blog Thanks
Great dear 🙂
glad to view this post i became u r student with this………:-)
Works great, thanks. Way better then msdn equalivelant
Hi,
I have a question about adding disks. Do you need to give any type of specific permissions to the disks under the security tab?
I have seen my disks all of a sudden loose the “self” group and I get a “recycle bing is corrupted” message when I log in tt he system. after failing over to the standby node.
thanks in advance.
best,
juan
Put across very simple & Clear, Thanks a lot my dear.I shall start looking more on your Blogs.
Hi,
This article is nice. However even after adding the disks at Dependency tab, somehow SSMS is not able to see the disks.
Do we need to restart the SQL Service to be able see the disks?
SQL Server Failover cluster Instance information
10.50.4000.0 SP2 Enterprise Edition (64-bit) 3
Regards
Balaji
Hi,
That was my mistake. I was editing the dependency of Server Name instead of SQL Server resource in Other resources section. After editing the dependency of SQL Server, I am able to see the disks.
Regards
Balaji
Thanks , Very usefull
Fantastic!!! i was unable to determine what step i had missed and behold i land on your article. Thank you for such a detailed step by step guide!
Thanks a lot for your hard work.
MJ
I’m not that much of a internet reader to be honest but your
sites really nice, keep it up! I’ll go ahead and bookmark your website to come back down the road. All the best
it is complete online /offline operation ..
Well done friend. Very helpful. Keep on doing the good work. Naprico
Great help ! Thanks.
Its been very handy/helpful. Thank you Sree
This just bit me as I had just migrated our cluster from one SAN to another. I just want to throw this out there: I was able to add the dependency without having to shut down the SQL Service. (Important for me as I can’t have any down time) Very nice post and thank you!
Excellent Explanation Yaar. Smple and straight….
Excellent, thank you so much. solved my problem today.
Excellent Bro, thank you so much. Since two days I am searching for this solution.
Thank you very, verx much!
Thank you very much.
Many many thanks bro, My case everything done except the last step of adding dependency. Usually I play up to san and server disk presentation but for the last two days SQL cluster guy was not available so thought of giving it a try and google helped to reach you. all the version of windows n sql similar as yours except mine is IBM Fibre chanel san. Thanks once again for such a wonderful guided presentation, god bless you
Can i addd the ““AND” Dependency on running services ? or is that dangerous ?
It’s not dangerous. However I prefer doing it in off hours.
I would like to know how to add a disk drive on existing sql server 2005 cluster on windows server 2003 os
See Brad’s reply in this post:
http://www.sql-server-performance.com/forum/threads/adding-disk-to-existing-cluster.2606/
Great! Last step was what I needed 🙂
Thanks…. (Y)
Thanks! Success!
Helped me so much… i googled around today, last step with dependencies wasn’t on my mind yet – thank you!!!
This article is very helpful. Thank you for providing this. You saved my day
useful information thx for initiative and continued support Sreekanth.
I setup a 2 node cluster using vmware on Windows server 2012 r2 and setup the storage using the native SCSI feature from the Domain controller which I built. Every time I add storage and initialize it on both the nodes. When I do a failover, only the cluster ip is failing over but none of the storage. What am I missing here? Please advice
Great !!! this manual dependency setting i missed while adding new backup drive, this post has helped to fix my issue ..thanks for this great sharing ….
thanks for your help here – appreciated.
Thank you very much this filled in a big knowledge gap.