How to compare the configuration of two SQL 2005 Servers?

It is important to compare the configuration of two servers, for ex. The Test and the production servers, or more important, the production and a disaster recovery stand by server.

So, how can I compare the configuration of two SQL Servers?

Well, the answer starts with the white paper SQL Server 2005 Security Best Practices - Operational and Administrative Tasks (http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx)

 I mentioned this document in one of my previous posts

Step 1 -  Script the configuration of the servers to file

I quote from the document:

The SQL Server Surface Area Configuration command-line interface, sac.exe ("C:\Program Files\Microsoft SQL Server\90\Shared\sac.exe"), permits you to import and export settings. This enables you to standardize the configuration of a group of SQL Server 2005 instances. You can import and export settings on a per-instance basis and also on a per-service basis by using command-line parameters.

For a list of command-line parameters, use the -? command-line option. You must have sysadmin privilege to use this utility.

The following code is an example of exporting all settings from the default instance of SQL Server on server1.

sac out server1.out –S server1 –U admin –I MSSQLSERVER

Step 2 – compare the two XML files using any text comparison utility

Cheers,

Itay Braun

Premier Field Engineer - SQL Server

Microsoft Services - UK

E-Mail: itayb@microsoft.com

Mobile: +44-796-928-9996

Veni         Vidi         Fixit