Comparing SQL Server installations

Question

Comparing SQL Server installations

Craig Olague

Newbie

Points: 9
More actions
November 5, 2008 at 2:44 pm

#127556

I've just inherited a number (20+) of SQL Servers that are *supposed* to be configured the same. By configured I mean not only the database configuration names, objects, schema and data, but also all of the Server settings such as installed version, drives being used, processor affinity, etc.
Basically I need to compare the entire installation of SQL Server to audit whether or not they are the same. There are a number of products that do the actual objects, schema and data, so I can handle that. It's really the base installation type of data that I need a way to gather and compare.
Any ideas are warmly welcomed.
Thanks,
Craig

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

A free tool from Microsoft that is really easy to get up and going in under an hour is SQLH2. It has reports for SSRS, and builds a repository of data for the servers, that will contain most of what you are looking for.

http://www.microsoft.com/downloads/details.aspx?FamilyID=eedd10d6-75f7-4763-86de-d2347b8b5f89

the reports are available on:

http://www.microsoft.com/downloads/details.aspx?familyid=a4b837c7-a1fa-4f25-840b-fef15e917f18

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

You also can look at Idera's Admin Toolset

http://www.idera.com/Products/SQLadmintoolset/

or better yet, for free download SQL Server Express 2008 with Tools, and install Management Studio Express.

http://www.microsoft.com/express/sql/download/

Then download the PBM templates from the 2008 Feature Pack and use them along with other custom policies to check each server off a base configuration you believe is the correct one.

http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1

Since this uses SMO, it can be run against 2000, 2005, and 2008 so long as your policies are set to work against all of the editions. You can also use SSMS 2008 to do multi-server queries:

http://www.sqlservercentral.com/articles/SQL+Server+Management+Studio/63650/

CrazyMan SSChampion Points: 12560 More actions · Answer 3

There are various tools on the market for this job, MS is free and good as well 🙂

Jerry Hung SSChampion Points: 12968 More actions · Answer 4

The SQL Server 2008 Policy Management is a good start

but may take some time to setup (even with the built-in templates)

SQLH2

SQLH2 collects four main types of information:
1. Feature Usage – What services/features are installed, running and level of workload on the service.
2. Configuration Settings – Machine, OS and SQL configuration settings, SQL instance and database metadata.
3. Uptime of the SQL Server service
4. Performance Counters (optional) – Used to determine performance trends

SQLServerNewbieMCITP: Database Administrator SQL Server 2005