Click here to Skip to main content
Click here to Skip to main content

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

, 27 Sep 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
A method to get an overview of the SQL servers in a Domain

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

History

  • 27th September, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

graham the programmmer
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
QuestionSQLServerReport.vbs ? PinmemberMikeBCodeProject6-Nov-08 11:20 
AnswerRe: SQLServerReport.vbs ? Pinmembergraham the programmmer10-Nov-08 4:44 
GeneralRe: SQLServerReport.vbs ? PinmemberMikeBCodeProject11-Nov-08 11:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 27 Sep 2007
Article Copyright 2007 by graham the programmmer
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid