Thursday, June 26, 2014

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 1Cannot 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.

Monday, June 23, 2014

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".

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.