A Method to get an overview of the SQL servers in a Domain

• Download sqlreport.zip - 20.7 KB

Introduction

Recently I had the pleasure of inherting 34 SQL servers. There was no documentation, in fact they did not know exactly how many they had on the network and each had been setup by different departments by people of differeing abilities and understanding. After the databases had been setup they were left to themselves and only became an issue every few months when the log files used up all the disk space as they were not being backed up.

Trying to get an idea of what I was up against by using Enterprise manager was proving time consuming, what I needed was a quick report to give me a feel for what was going on on all the servers so that I could form a plan of action. So in the end I wrote a small vbscript program to achive this.

Using the code

First of all you need to get a list of all the servers in the domain that you are investigating, there a a number of ways to do this but the one I used was to use the osql utility that ships with sql server 2005, it can be found in the c:\program files\microsoft SQL Server\90\tools\binn folder by default.

This will list all SQL servers in the domain and if you are lucky all the instances as well, just run up a dos prompt, navigate to the binn folder and type in osql -L to get a list.

On my domain osql listed all the servers but did not pick up on all the instances, so once you have identified the servers check out the registry key -

HKLM\Software\microsoft\Microsoft SQL Server

and look at the InstalledInstances values, this lists all the instances running on the server.

Now you have got a list of servers you can generate the report, you will need admin access to the servers and sa access to the sql server instances and a pc with excel 2003 installed on it.

1. Extract all the files from the zip file to the same folder.
2. Open the SQLServerReport.vbs file and change the number 2 in this line -
   Dim ServerNames(2)
   To be one less than the number of instances of SQL that you have
3. Change the line -
   ServerNames(0) = "YourSQLServer1Name"
   replacing "YourSQLServer1Name" with your SQL server. If it is the default instance you just need to specify the server name, if it is a named instance you need to specify the server name then a backslash and the instance name
   eg SQLServer1\SencondSQLInstance
4. Repeat this for ServerNames(1) etc adding more as necessary
5. Change path in the line -
   const EXCEL_FILE_LOCATION = "C:\Scripts\SQL Server Report.xls"
   to reflect the location that you have extracted the excel file to
6. Finally run up a dos prompt navigate to where you stored the program and type -
   cscript SQLServerReport.wsf
   and press return.

After a bit excel will popup and fill itself with the data extracted from the sql servers, it will then save the spreadhseet and close.

Points of Interest

History