7

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?

  • "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
13

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)
  • 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
6

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

5

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.

3

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

http://aireforge.com

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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