SQL Live Monitor – Great Free Monitoring tool from Codeplex!

How many times do you use DMV’s daily supporting your SQL Server environments? How many of you rely on 3rd party tools for looking at the current snapshot of your SQL Server? Let me say this, I use DMV’s a lot even though I’m provided with fancy 3rd party monitoring tools. Each approach has it’s own pros and cons! Let’s assume our shop has no 3rd party tools for monitoring and we are just relying on native SQL Server mechanisms for all our monitoring. In that case, I can say 90% of the monitoring/Gathering Info will be done just using DMV’s. What if we’ve a free tool which displays the DMV results(of course, not everything..but all the useful info at a single glance)  in a pictorial representation? That would be awesome right?? Well, most of the cases, I feel seeing something visually is much more self explanatory and efficient rather than just looking at figures and numbers. YMMV!

SQL Live Monitor can do the same for you. It’s a free tool available from CodePlex community. This tool don’t even require any Installation. It’s just an executable(222 KB) which runs on fly without installing anything on your Servers. Isn’t it cool??

Let me show you how it looks like when I run against one of my Lab Servers…I copied SQL Monitor exe to my Server as you can see below and I just double clicked on it….No other Fancy Stuff needed!

Immediately I was welcomed with the following Screen.

As you can see initially it’s not connected and It’s asking for SQL Instance Name, whether it’s a cluster or Standalone, If it’s clustered…what’s the current active node for your Instance….etc. In my case, It’s a standalone box. Once I selected my Driver(In my case it’s 2008 Native Client) and provided with Instance name and clicked Start…It is providing me all the cool info as you can see below…

Awesome….! Undoubtedly this is one of my favorite tools from codeplex 🙂 Yes, this is not as fancy as other 3rd party tools out there, but still it does the justice and serve the purpose with much much less pain!

I hope this tool will be helpful to atleast few of you while investigating your Servers….Btw, you can download this for free from http://sqlmonitor.codeplex.com/

MAP(Microsoft Assessment and Planning Tool kit) 6.5 for SQL Server Discovery

Hello Folks! In this Blog Post let’s see how to make use of MAP tool for discovering all of your SQL Servers in your Environment. MAP is one of the wonderful tools(free) provided by Microsoft, which basically scans all over your Network and present you with a wonderful report on all of your Servers. They could be Windows Servers, Linux Servers, SQL Servers, Oracle, MySQL. Many companies use this fabulous tool for planning future road maps(Could Be Server Upgrades, Desktop Upgrades, MS Office Suite upgrade and what not), consolidations, upgrades, Licensing etc. In My Organization, we are planning to move on to SQL 2012 very soon and we need a report which basically gives us a clear and simple picture of our current SQL Server Environment and I was assigned to achieve this goal 🙂

Sooo….., let’s get started. First thing you’ve to do is download MAP ToolKit from below location

http://www.microsoft.com/en-us/download/details.aspx?&id=7826

Once downloaded, choose a Machine in your Domain where you want to Install MAP tool and start Discovery.

Note: MAP tool Installs SQL Server Express by Default for storing it’s Results.

Demo:

I’ve chosen “Mirror_Stand” machine in My Lab Domain to Install MAP. I copied over executable  to this box as the 1st step. Right click on the “Microsoft_Assessment_and_Planning_Toolkit_Setup.exe” and choose Run as Admin and you should be seeing the following Screen. (Make sure to Install all the Prereq’s. If not, you’ll be welcomed with an ugly X message as shown below!)

Once I finished meeting all the Prereq’s, I’ve opened the exe and now I’m welcomed with the below Screen 🙂

Click Next-> Accept License Terms and Next->Select your Installation Folder(I left it to default) and click Next. Now comes the interesting Screen where it asks for how do you want to Install SQL Express(see below Screenshot).

If your Box is connected to Internet, Just select the 1st Radio Button. If not, select 2nd option and provide it with the SQL express Installation media path.  I’ve chosen 2nd option and provided path for my SQL Express 2008R2 as shown below.

Once you read and accept the license terms, you should see a similar screen as below.(In My case, I already have a SQL Server Installed on this Machine, hence you can see “New Instance” being stated by the Installation wizard).

Click on Install and go and grab a cup of coffee :). It’ll take a while!  As you can see, my Installation is successful now.

It Installed SQL Express Edition Instance on my machine with Name “MAPS”. See below Screencap

We are done with the Installation. Now, let’s see how can we discover SQL Servers across our Domain using this Tool.

Step1: You will be welcomed with the below screen where you’ve to create an Inventory DB(I named it as “SQL_Inven”) for the First time. (You can create separate Databases for separate Discoveries, eg: One DB for SQL Servers, another for Exchange, another for VMWare Servers etc).

Step2: Select on Databases and Choose to perform an Inventory as shown below.

Step 3: Select SQL Servers to discover your SQL Servers as shown below.

Step 4: Choose a Discovery Method. I’ve left to default to AD as you can see below.

Step 5: Provide domain Credentials and select which parts of your AD Forest to scan for as shown below.

Once you proceed upto Finish, it automatically starts gathering info as per the options you’ve chosen. It will take a while for gathering all the required info…

Once it finished, you can see a neat and very useful summary of MAP assessment of your SQL Servers as shown below.

Don’t say it’s useless…It’s actually wonderful Summary of your Environment. For detailed Reports, gather the performance metrics as well and click on “Generate Report/Proposal” on your Tasks Pane to your Right. Once finished it’ll create couple of excel Reports which are very very detailed and useful…You’ll find those reports in your documents folder. In My case, it is “C:\Users\sreekanth\Documents\MAP\SQL_Inven”

Nice thing is, It’s agent less. All it needs is an Account name which has access to the Servers and the Servers should be Up and Running while It scans.

Hope this is informational and useful. Cheers!…

Windows Drive(s) Space Statistics – Few useful Tools!

Hey Folks! Let me introduce you to couple of my personal favorite tools for quickly understanding the space distribution on your Hard Drives on your Windows Servers. I Bet, almost all of us might have been in a “Drive running Out of space” situation at least once in our career. This is one ugly situation to be in, especially if that’s the very first time you logged onto that box during your On Call Shift and You’ve no idea what files are sitting there on Drives! So, what are the tools which helped me in gathering this info in just few minutes or even seconds, with just few Mouse Clicks??

1. WinDirStat ( Windows Directory Statistics)

2. SpaceMonger

Let’s see WinDirStat First, Let me tell you this, It’s a free tool from Sourceforge, which is aweesome 🙂 you can Just Bing it or google it or visit http://sourceforge.net/projects/windirstat/ for more info.

I’ve Installed this on one of my Test Servers. Let’s see how it works….As soon as you double Click on the executable, you’lll be welcomed with below Screen.

As you can see, it gives us options to Scan all Drives, Individual Drives or even at folder level. I’ve chosen “C:” for illustration purposes. Once the Scanning is complete, It gives a colorful report which is actually interactive(you can directly go to a file/folder just by clicking on a block whichever appears to be largest in the image).

Let’s explore the “Show FreeSpace Option” by looking at my SQL Data drive. You can enable this option by hitting F6 or enabling from Options Menu as shown below.

Before proceeding any further, I’ve a empty database named “space” which is 1.5 GB in size. See below how it looks from SSMS and from My Computer. Basically 1.5 Space has been reserved by SQL Server and OS sees it as 1.5 GB full.

From SSMS:

From Windows:

Now Let’s see from WinDirStat by enabling ShowFreeSpace Option.

As you can see, you can see freespace available for you and other large files as large blocks; visually which is much much easier to analyze, especially If you’ve option just to click on the Graphical Block and navigate to actual Folder/File.

What About Junction points?? Yes, This guy is smart enough to deal with Junction points as well! See below for all the options you’ve with this tool. ( you can configure this tool by navigating to Options->Configure Windirstat).

Navigate through the rest of Options to explore the rest yourself!

Now, let’s see spaceMonger tool….This is a similar tool which can serve almost the same purpose. See below snippet for how this tool looks like once installed on the server. You can see nice Folder hierarchy….

Statistics Tab gives us detailed info of the selected Drive as shown below

I see, SpaceMonger is Much Much Faster to collect the stats when compared to WinDirStat. SpaceMonger also has very good customization and very well integrated with Windows Inbuilt Tools as shown in the below Screenshot…

Hope, these tools will be useful for at least few of you in your day to day support or you can Install them on your Laptops! I’ve WinDirStat on my Laptop, since it’s an absolute free tool where as SpaceMonger costs a few bucks 😀

whoami and echo in Windows??

were you ever curious to know on which domain you are logged into currently and what is your user SID and what are all the groups you belong to and what are all the permissions you’ve etc??  Wouldn’t it be nice if we get all this info at a single place, with a single command? “WHOAMI” is the guy which can do it for you. Before moving on to WHOAMI please see below screenshot for legacy “ECHO” command. Basically I’m showing you how can we pull username and/or domain name.

WHOAMI:

Basic “whoami” will give you the same info..you can see below.

Let’s get little fancy now…by appending “/USER” switch.

in the above screenshot you can see SID as well…Okay, let’s see by appending “/FQDN” switch now.

well, how about appending “/ALL” Switch?

As you can see I’ve got very detailed information using “ALL”! Instead, I can buffer this to some text file using ‘>whoami.txt’ and open it later anytime using Notepad…

Windows Networking Tools for SQL DBA’s – PATHPING/TRACERT/PING

Hey Folks! Hope you all had fabulous holiday season 🙂

In this Post let’s see Three fabulous tools which Windows OS offers for testing your Network between your Servers. A typical example for DBA’s would be testing Network Latency between your Prod and DR Server while initial testing of your Mirroring configuration or even looking for latency/Response times when you are copying a huge Backup File from one Server to another Server etc.

Tool 1: PING

This is one of the most used command by any IT professional, irrespective of what he/she does. PING is a very Basic test to verify IP connectivity from one machine to another.

Eg: PING MachineName ; PING IPAddress; PING -T MachineName/IPAddress(T for Continuous ping).
For More Options and Info please visit http://technet.microsoft.com/en-us/library/bb490968.aspx

Tool2: TRACERT

This is another useful tool which traces the entire route(Includes all the Routers/NIC’s involved in your Path) from Source Machine to Destination machine.  Pretty useful tool.

Eg: TRACERT Machinename
For info on how to interpret those results, please visit http://technet.microsoft.com/en-us/library/cc940128.aspx

Tool3: PATHPING ( Awesome Tool to have in your ToolKit)

This is one of my all time Favorite tool which basically mixes both PING and TRACERT Results and will provide you with a very detailed/useful information.

Eg: PATHPING MachineName. In this Example I’m doing a Pathping from my Domain Controller to one of the server in my domain. See below on how it looks for the output.

Typically, you would see many Network Interfaces in between(Of Course right…!). Try “PATHPING GOOGLE.COM” from your machine and you can see what I mean. Or Just try PATHPING on one of your Servers in your company, you’ll see what I’m trying to say.

Tip: I typically make use of “>”  appended to my command, so that I can save the Output as a report in a text file and I can review later any time or you can simply forward that text file to your Server/Network Team to look into. Please see below for what I’m trying to explain(Basically, it’s something like “SPOOLING”  in Oracle if you are familiar with ORACLE SQLPLUS).

Now it created a Text file called “Output.txt” under my C Drive and the contents are as shown below..

Trust me Guys, PATHPING is one of the most Useful tools we have for free, which many of DBA’s are not aware of, or I should say most ignored Tool by many DBA’s!

Technet has fabulous info on how to interpret results and options available etc here: http://technet.microsoft.com/en-us/library/cc958876.aspx

Hope this Info helps!…