question

Steve Jones - Editor avatar image
Steve Jones - Editor asked

How do you compare server configuration?

Is there an easy way to get all the server meta data (memory, security, sp_configure, etC) from an instance that you can use to compare with a second instance, or use to set another instance to have the same configuration?

administrationconfiguration
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

I'd work on a set of PowerShell scripts that collect the info you want. For example:

(get-item SQLSERVER:\SQL\ROB-PC\SQL2008).Information gives me lots of useful information. I can also get lots of other information, easily dump it to files, and compare them as required. Being PowerShell, I can easily do this across many servers.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

I'd prefer Rob's approach and wouldn't mind seeing a good PowerShell script that does exactly what he described, there is an approach you could take in TSQL.

sp_configure is a built in system function that will allow you to display, or change, system settings. To see the basic listing you can run a query like this:

EXEC sp_configure;

That will show most of what you're interested in on a day to day basis. However, to get a complete listing, you need to show all the advanced settings. From the books online, run the script this way:

USE master; GO EXEC sp_configure 'show advanced option', '1'; RECONFIGURE; EXEC sp_configure;

That will show you everything. You can then output to a file or load it to a table in order to run comparisons on other data sets from other servers.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dillinzser avatar image
dillinzser answered

Another way is to use System Catalog Views. However these views are present in every database, they provide metadata information not just about the database but the server core. These views are organized into several categories (server-wide configuration, security, objects, ..).

More information about these views can be found in Books Online on MSDN

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ronald Dameron avatar image
Ronald Dameron answered

Check out Allen White's article on inventorying SQL Servers with PowerShell. http://www.simple-talk.com/sql/database-administration/let-powershell-do-an-inventory-of-your-servers/

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sturner avatar image
sturner answered
If you are looking for an out of the box way to do this in SQL Server, Orcaconfig may be a good option. Orca automatically captures the configurations of your applications, IIS (and other middleware), SQL Server (and other databases), and operating systems. It compares the configurations against a known standard (such as a gold master revision or gold master server) as well as your own compliance standards. Here is a link. hope it helps. I work there. [ https://www.orcaconfig.com/compare-configurations/][1] [1]: https://www.orcaconfig.com/compare-configurations/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
You could look at [Aireforge's product][1]; that allows for side-by-side comparison of multiple servers (in the paid version); just two at a time in the free one. [1]: http://aireforge.com/
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I would second Thomas and use Aireforge's product in particular in the knowledge that you only want to compare 2 servers, so its free! I have been using the product extensivly on an environment with over 100 SQL servers and it beats any powershell script.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.