x

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?

more ▼

asked Dec 28, 2009 at 07:18 PM in Default

avatar image

Steve Jones - Editor ♦♦
5.1k 79 93 87

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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.

more ▼

answered Dec 28, 2009 at 08:42 PM

avatar image

Rob Farley
5.8k 16 22 28

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 29, 2009 at 09:49 AM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 30, 2009 at 09:50 AM

avatar image

dillinzser
91 1 3 5

(comments are locked)
10|1200 characters needed characters left

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/

more ▼

answered Jan 21, 2010 at 06:48 PM

avatar image

Ronald Dameron
21 1 1 2

(comments are locked)
10|1200 characters needed characters left

OmniConfig from AlienArmpit can do this. You can compare and archive multiple servers. It'll check most settings but it's only in beta and new functionality is being added weekly.

http://alienarmpit.rocks

more ▼

answered Feb 28 at 08:19 PM

avatar image

PhilGrayson
1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x131
x37

asked: Dec 28, 2009 at 07:18 PM

Seen: 7536 times

Last Updated: Feb 28 at 08:19 PM

Copyright 2016 Redgate Software. Privacy Policy