Project Description
This project is designed to allow inventorying and configuration management of SQL Servers in your environment. Including SQL Server configurations, databases, database configurations, database file configurations. And more.

Full Description
This project although currently targeted at Microsoft SQL Server was designed to be able to be used for other SQL Servers such as Sybase and Oracle, however, little more than placeholders have been provided.

The repository side requires NOTHING to be installed on the remote side, it requires Administrator access to both the machine AND the SQL server. The user running the SSIS packages requires high rights and therefore should likely be run using a proxy.

Compatibility:
  1. Databases:
    • Microsoft SQL 2000, 2005, 2008, and 2008R2.
  2. Operating Systems
    • Windows 2000, 2003, 2003R2, 2008, and 2008R2, Windows 7.
Testing has not been done with MS SQL 2012 yet. And Vista has not been tested, there is no plan at this time to test with Vista.

This project provides very detailed information in an effective dated manner for most aspects of your SQL Server, such as:
SQL Configurations (Such as the contents of sp_configure)
Database Names
Database Configurations
Database File and Filegroup Configurations, including file sizes and growth amounts.
Physical Machines that the SQL Server is running on.

In addition to the strictly database information this project also collects machine level information such as:
Machine Name
Windows Version and Patch Level (SP)
How many processors
How many cores
How many logical cores (hyperthreads)
Maximum CPU Speed in MHz
Total Machine Memory
Architecture x86 vs x64
Whether the machine is clustered and if so what is the cluster virtual name (not the SQL virtual name)
All machines in a particular cluster
All non-removable volumes on all known machines including size, format type, and label.
Volumes considered shared in a cluster.
Environmental variables for all users on a machine.
All installed MS SQL related services list.

This project is made up of a few SSIS packages, the primary two pickup Server and Machine Information, there are others to inventory a machine for SQL Servers and another to look for SQL Servers in a list of machines.

This project comes in multiple parts:
  1. Repository Side:
    • An SSIS project
    • An SSRS project
    • A database project
  2. Remote Side:
    • A database project

This project is built on top of a logging and auditing infrastructure which is the Remote Side. The remote side can easily be integrated into your application and the repository side is able to collect all data from the various remote sides. It looks in each database for the required table and will pull data from each EventLog table.

Last edited Jan 12, 2012 at 4:30 PM by novaconceptsltd, version 7