Add Storage Drives as dependencies to SQL Server Cluster instance in 5 easy steps.

When you build a cluster everything seems to be straight, but when you start to do changes then you can face unexpected errors. One of them I've recently hit when tried to use by SQL Server newly added SAN drives.

Here is an error I've got when I tried to move existing databases to the established drives or create new databases there:
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'K:\SQLData\XYZ123.mdf' for clustered server.
Only formatted files on which the cluster resource of the server has a dependency can be used.
Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

I've found couple of solutions from Microsoft:
http://technet.microsoft.com/en-us/library/cc771350.aspx
http://support.microsoft.com/kb/280297?wa=wsignin1.0

Here I want to visually go through the process:

1.At first, you have to attach SAN disks to the cluster. Then you have make new disks (in my case 3 & 4) as dependencies for SQL Server.

2. Just right click on SQL Server and choose "Properties"

3. In Properties choose "Dependencies" tab. There you add your new drives to the SQL Server and hit OK.

 The operation you have to do with “File Server” and “Analysis Services” (if applicable).

4. To prove your change create a "Dependency Report". Right click on SQL Server Clustered instance and choose "Show Dependency Report":

5. That will open you Internet Explorer with that kind of beautiful picture:

Hope that will help somebody with their problem.

How to Automate collecting Perfmon Counters of Interest

For more than two years I've tried to use wonderful poster made by Kevin Kline and Brent Ozar.

Click to get the poster

I've got several of these posters on SQL Saturday events. I've redistributed them between my friends and coworkers, but never was really able to use it in full capacity.

There are almost 100 counters that you have to setup in Perfmon and after setting just 10 you are alreeady lost.

 Last week I attended presentation of Kun Lee about automation of perfmon on Baltimore SQL Server User Group meeting. Kun showed how to use configuration file to set perfmon counters and I've used that technic for "Counters of Interest".

 Based on the Poster I've created "SQLDataCollector.config" file.
Inside it looks like this:
 "\\YOUR_SQL_SERVER\Memory\Available MBytes"
"\\YOUR_SQL_SERVER\Memory\Pages Input/sec" "\\YOUR_SQL_SERVER\Memory\Pages/sec" "\\YOUR_SQL_SERVER\Paging File\% Usage" "\\YOUR_SQL_SERVER\Paging File\% Usage Peak" "\\YOUR_SQL_SERVER\Process(sqlservr)\% Processor Time" "\\YOUR_SQL_SERVER\Process(msmdsrv)\% Processor Time" "\\YOUR_SQL_SERVER\Processor(_Total)\% Processor Time" "\\YOUR_SQL_SERVER\Processor(_Total)\% Privileged Time"

You have to replace word "YOUR_SQL_SERVER" by name of your SQL Server or by it's IP address.

Then I used simple "loading" command:
logman create counter BASELINE -f csv -max 200 -si 00:01:00 -b 06/19/2014 11:06:00AM  -v mmddhhmm -o "C:\Temp\Test\Perfmon\Perfmon.csv" -cf "C:\Temp\Test\Perfmon\SQLDataCollector.config"

In order to use it you have to modify following:
-0 - Location and name of resulting trace file;
-cf - Location of config file;  
-b Date and time when you want to run your monitoring. If you want to start trace manually you can start perfmon.exe and run your trace from there:

Also, you can adjust data collecting interval which in mi case set as reccomended by Kun Lee a minute  (-si). There are much more options of "logman" to choose from, you can even set tracing to SQL Server. 

Hope everybody now can use that poster much easier.