THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Msql -- A Little Wrapper Goes a Long Way

Have you ever run into a situation where you'd like to search many of your SQL Server instances to look for a specific piece of information? Ideally, you should maintain an inventory database of all your SQL Server instances so that all you need to do is query that database. In case you don't have such an inventory (shame on you!) or the inventory is not kept updated to the minute (which is quite understandable!), I have a little tool for you.

I've attached a utility called msql where m stands for multiple as in multiple SQL Server instances and sql is taken from osql.exe. This utility is a trivial wrapper around osql.exe, and accepts all the parameters that osql.exe accepts with the following twists:

  • It extends the -S parameter to accept a comma-separated list of SQL Server instances. With -S, msql simply calls osql.exe for each of the SQL instances on the -S list and passes along the rest of the parameters verbatim.
  • It includes a new parameter -C which accepts a file name. This text file should contain a list of SQL Server instance names, each on its own separate line. With -C, msql calls osql.exe for each of the SQL instances listed in the file and passes along the rest of the parameters verbatim.

In the attached msql.zip file, you'll find two versions of msql, one written in C# on .NET 2.0 and the other in Perl. I personally like the Perl version--it's a throw-away short script. But with the C# code compiled into msql.exe, you don't have to carry a Perl compiler and the Perl modules around.

So, what can you use msql for? Well, you can do pretty much anything you can with osql.exe, but on multiple SQL Server instances. The more servers you have, the handier it gets!

I've used it to do quick and dirty checks of configuration anomalies across my servers, for instance, to find unreasonable memory configurations or databases with a certain option turned off. I've also used it to look for strange server/database stats, for instance, databases whose data-to-log ratios are terribly out of whack as well as null passwords across all the servers.

Let me give you two really simple cases for illustration. Assume that you have all the server names in file servers.txt. (BTW, you should be able to dump all your server names into a text file with a snap of your fingers. If not, I'd be worried.)

Case 1: I need to find all the build levels of my servers so that I can decide which need patching.

 msql.exe -Cservers.txt -Q"select @@version" -E > versions.log

With all the versions in a single text file, it's now trivial to glean the version information you want.

Case 2: Where is database xyz that was mentioned at that meeting? (Don't laugh! When you have many servers, you may not remember the whereabouts of a particular database.)

 msql.exe -Cservers.txt -Q"select name from sysdatabases where name = 'xyz'" -E > database.log

Then, you can just scan a single text file--database.log--to find the server(s) that has the xyz database. Now, what if you want to find a particular table? The approach would be almost identical, albeit with a different query or script.

I've shown that you can use msql to search for things throughout all your live SQL Server instances. The fact it calls osql.exe and runs a SQL statement or a SQL script means that you can as easily use it to make massive changes across your servers at once. But I'd be really careful with that. The damage can be just too severe for whatever convenience you may gain.

It should be noted that this is by no means the only way to run the same query or script on multiple servers. If you can get a list of the servers, you should be able to easily generate a batch file of osql calls to run the query or script on these servers--a trick that is commonly practiced. But you can have better control over the execution of these osql calls with a msql style of scripting.

Published Tuesday, January 09, 2007 9:37 AM by Linchi Shea
Filed under: , , ,

Attachment(s): msql.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter W. DeBetta said:

It's things like this that help make my life easier... Nice work Linchi. I'll let you know if I run into anything while using it...

January 10, 2007 7:45 AM
 

MikeS said:

Thanks Linchi.  I've been using your perl msql.pl code since buying your book: "Real World SQL Admin w/Perl".  Your book is one of my favorites and is my most used Perl-for-Win32 books.  It's great to find you here at sqlblog.com to thank you for all I've learned from your book and now I can learn more from you and your blog postings.  Thank you sir!

February 11, 2007 12:19 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement