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/

SQL Server 2008R2 SP2(CTP) got released

Microsoft released SQL Server2008R2 SP2(CTP) yesterday(13th May 2012).

You can download and test if if you are willing to from http://www.microsoft.com/en-us/download/details.aspx?id=29848

For list of Bugs that got fixed with this release, visit http://support.microsoft.com/kb/2630455

Cheers!

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

How to Truncate/Delete all Tables in a SQL Server Database?

How to Truncate/Delete all Tables in a SQL Server Database in three simple steps????

Howdy! In this post let’s see how to truncate/delete all the tables in a given SQL Server Database and few important considerations while doing so.

Today, I had a request from one of my Application teams to copy a database from Prod to QA and truncate all the tables. It was a very small Database(7.2 GB) but had 250+ tables with manyyyyyyy Foreign Keys. So, I had to deal with Constraints before SQL Server allows me to truncate any table which is being referenced by some other table. Basically I’ve to follow the child-parent hierarchy to do so. I started with scripting out all the Foreign Keys and thought of dropping all of them and do a truncate and then recreating. Well, immediately something came to my mind….”How about Disabling Constraints and do my work and then re-enabling Constraints”

Imp Note: We can’t Truncate tables with just Disabling Constraints. SQL allows only if you drop them physically.

So, what is the Option I’m left with. Well, Delete is my weapon now.

How I did this was..

Step 1:  Disable all the Constrains

Exec sp_msforeachtable “Alter Table ? NOCHECK Constraint all”

Step 2: Delete data from all the Tables

Exec sp_msforeachtable “Delete From ?”

Step 3: Re-enable all the constraints which we disabled in Step 1

Exec sp_msforeachtable “Alter Table ? With Check Check Constraint all”

I Know, I Know….sp_msforeachtable is old school technique and deprecated, but am Okay as long as I Know there’s no harm using it and making my life easy 😀

So, what should we keep in mind if we are considering this approach??

The Very First thing is your Transaction log. How much room do you’ve for your T-log to grow is single most important consideration for this approach. Because Step2 will run as a single Hugee…..transaction. Let me say this in other Words, My Database was in SIMPLE Recovery Mode and I was monitoring the T-Log constantly..It grew upto 15.4 GB for doing this Delete(and the entire Database Size was 7.2 GB). Just Make sure….you are good here before taking this approach for a Medium-Large Database. Once you are done with this, do not forget to bring down your T-Log size accordingly.

Hope this helps!

SQL Server 2012 CU1 Released!!

Microsoft has released CU1 for SQL Server 2012 RTM within 3 weeks of the initial release of the product, which has more than 50 fixes! This is something really interesting to me which I’ve never seen(at least I don’t remember) where they had fixes for ~50 bugs in a Cumulative Update(not Service Pack).

You can get more details(what got fixed what are the enhancements) and download from http://support.microsoft.com/kb/2679368

Cheers!!