How to compare two SQL Server 2008 configurations

Question

I have a problem with my application that works on my dev SQL Server 2008 (Developer Edition), but not on a production SQL Server 2008. Is there an easy way to export the settings from the production server and compare it to my server configuration?

What I found out is that I can export Facets in SQL Server Management Studio to XML files and compare them in a diff tool.

Is there any other/better way to export and compare settings of two SQL Server instances?

Answer 1

You really just have to get creative. As we all know, there are many places that settings are stored, depending on what exactly you're looking to compare. For instance, to compare instance-wide configuration settings, you can simple do an EXCEPT query (you may have to create a linked server, or export/import the data depending on how you want to approach that):

select *
from [YourProdInstance].master.sys.configurations

except

select *
from [YourDevInstance].master.sys.configurations

Use this same type of methodology for all other configuration settings that you will want to compare.

1. Find the root of the config data
2. Get both instances' config data in a common place
3. Write a query that will compare the data (whether it is XML, or not)

Answer 2

You can use custom policies or predefined ones (so called best practices) and adjust them according to your developer SQL Server instance. Then, as explained in the SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances online article, evaluate them against (even multiple) production instances

Answer 3

I wrote an open source utility that will script out common server configurations and save them to various files. It includes everything in sys.configurations as well as security settings, server properties, credentials, databases and a whole lot more.

The utility is SQL Server Configurations on CodePlex.

If you script out the two servers and use a diff utility on the results you should have a pretty good list of what's different between the two servers. I mostly use it for DR purposes but it should work well in this case.

Answer 4

I helped to create a free tool that compares instance and database settings and also works for AzureDB.

http://aireforge.com

Answer 5

1) If you are looking for an ongoing way to compare environments and 2) if you are open to a commercial solution, you may want to take a look at https://www.orcaconfig.com/compare-configurations

Orca captures the configurations of applications, middleware, databases (incl SQL Server, and operating systems. And tt automatically compares the configuration against a known standard (such as a gold master revision or gold master server) as well as your compliance standards. Yep, I work there.

Otherwise, the options listed by other commenters are likely your best choice.