A Method to Get an Overview of the SQL Servers in a Domain

• Download source - 20.66 KB

Introduction

Recently I had the pleasure of inheriting 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 differing abilities and understanding. After the databases had been set up, 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 achieve 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 are 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:
   VBScript

   Dim ServerNames(2)

   to be one less than the number of instances of SQL that you have.

3. Change the line:
   VBScript

   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.
   e.g. SQLServer1\SencondSQLInstance

4. Repeat this for ServerNames(1) etc. adding more as necessary.
5. Change path in the line:
   VBScript

   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:
   VBScript

   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 spreadsheet and close.

History

• 27^th September, 2007: Initial post