Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
"I have a problem with my application" -- care to elaborate? There are many places to look for a problem... –  Jon Seigel Jun 26 '12 at 13:30
    
I was asking about how to compare SQL Server configuration, not about my app problem. It does not matter in this context. –  Robert Niestroj Jun 26 '12 at 21:24

3 Answers 3

up vote 8 down vote accepted

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)
share|improve this answer
    
Check out the script at sqlserverperformance.idera.com/uncategorized/… to quickly spot non-default settings for SQL Server 2008. –  MicSim Jun 26 '12 at 14:11
    
@MicSim I don't think the OP is looking for non-default settings. Just config settings that differ between two instances (which may also be non-default). –  Thomas Stringer Jun 26 '12 at 14:22
    
I know, that's why just a comment as additional info to your answer. Usually you got 80% default settings. So this way you're left to compare only the remaining 20%. (+1 btw) –  MicSim Jun 26 '12 at 14:38
    
@MicSim Absolutely, I completely agree. Thanks! –  Thomas Stringer Jun 26 '12 at 14:46

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.