How to extract SQL Server non-default configuration settings

Have you ever wondered how easily to extract non-default SQL Server instance settings? Well recently I had to perform such task so I can apply those changed settings to a new instance that was replacing the current one. And the only thing that came to my mind was to extract all settings in Excel and then compare them one by one with the settings of this new instance. Later on when I was browsing in SSMS reports I noticed that in the “Server Dashboard” report there was such section. It was definitely not that visible and I felt quite lucky I found it.

image 

If you have followed my posts, not long time ago I posted a link to SSMS report sources. So far so good, however it was not very comfortable for me to have the configurations without being able to export them (the report can be exported only printed and the table is not selectable at all. So I needed a better way of finding and after that – storing those non-default configuration settings. I checked the source of the Server Dashboard report and I was surprised how simple that was…it was consisting of INSERT section and one simple join which was using data from sys.configuration. Not that big of a deal, you will say, however if you do not know it, it is quite a big deal actually Smile

So there is the script you can use (it is straight extract from the report source):

begin try
declare @configurations_option_table table (
        name nvarchar(128)
,       run_value bigint
,       default_value bigint 
);
declare @sp_configure_table table (
        name nvarchar(128)
,       minimum bigint
,       maximum bigint
,       config_value bigint
,       run_value bigint 
);
declare @tracestatus table(
        TraceFlag nvarchar(40)
,       Status tinyint
,       Global tinyint
,       Session tinyint
);

insert into @sp_configure_table 
select name
,       convert(bigint,minimum)
,       convert(bigint,maximum)
,       convert(bigint,value)
,       convert(bigint,value_in_use) 
from sys.configurations  

insert into @configurations_option_table values('Ad Hoc Distributed Queries',0,0)
insert into @configurations_option_table values('affinity I/O mask',0,0)
insert into @configurations_option_table values('affinity mask',0,0)
insert into @configurations_option_table values('Agent XPs',0,0)
insert into @configurations_option_table values('allow updates',0,0)
insert into @configurations_option_table values('awe enabled',0,0)
insert into @configurations_option_table values('blocked process threshold',0,0)
insert into @configurations_option_table values('c2 audit mode',0,0)
insert into @configurations_option_table values('clr enabled',0,0)
insert into @configurations_option_table values('cost threshold for parallelism',5,5)
insert into @configurations_option_table values('cross db ownership chaining',0,0)
insert into @configurations_option_table values('cursor threshold',-1,-1)
insert into @configurations_option_table values('Database Mail XPs',0,0)
insert into @configurations_option_table values('default full-text language',1033,1033)
insert into @configurations_option_table values('default language',0,0)
insert into @configurations_option_table values('default trace enabled',1,1)
insert into @configurations_option_table values('disallow results from triggers',0,0)
insert into @configurations_option_table values('fill factor (%)',0,0)
insert into @configurations_option_table values('ft crawl bandwidth (max)',100,100)
insert into @configurations_option_table values('ft crawl bandwidth (min)',0,0)
insert into @configurations_option_table values('ft notify bandwidth (max)',100,100)
insert into @configurations_option_table values('ft notify bandwidth (min)',0,0)
insert into @configurations_option_table values('index create memory (KB)',0,0)
insert into @configurations_option_table values('in-doubt xact resolution',0,0)
insert into @configurations_option_table values('lightweight pooling',0,0)
insert into @configurations_option_table values('locks',0,0)
insert into @configurations_option_table values('max degree of parallelism',0,0)
insert into @configurations_option_table values('max full-text crawl range',4,4)
insert into @configurations_option_table values('max server memory (MB)',2147483647,2147483647)
insert into @configurations_option_table values('max text repl size (B)',65536,65536)
insert into @configurations_option_table values('max worker threads',0,0)
insert into @configurations_option_table values('media retention',0,0)
insert into @configurations_option_table values('min memory per query (KB)',1024,1024)
insert into @configurations_option_table values('min server memory (MB)',0,0)
insert into @configurations_option_table values('nested triggers',1,1)
insert into @configurations_option_table values('network packet size (B)',4096,4096)
insert into @configurations_option_table values('Ole Automation Procedures',0,0)
insert into @configurations_option_table values('open objects',0,0)
insert into @configurations_option_table values('PH timeout (s)',60,60)
insert into @configurations_option_table values('precompute rank',0,0)
insert into @configurations_option_table values('priority boost',0,0)
insert into @configurations_option_table values('query governor cost limit',0,0)
insert into @configurations_option_table values('query wait (s)',-1,-1)
insert into @configurations_option_table values('recovery interval (min)',0,0)
insert into @configurations_option_table values('remote access',1,1)
insert into @configurations_option_table values('remote admin connections',0,0)
insert into @configurations_option_table values('remote login timeout (s)',20,20)
insert into @configurations_option_table values('remote proc trans',0,0)
insert into @configurations_option_table values('remote query timeout (s)',600,600)
insert into @configurations_option_table values('Replication XPs',0,0)
insert into @configurations_option_table values('RPC parameter data validation',0,0)
insert into @configurations_option_table values('scan for startup procs',0,0)
insert into @configurations_option_table values('server trigger recursion',1,1)
insert into @configurations_option_table values('set working set size',0,0)
insert into @configurations_option_table values('show advanced options',0,0)
insert into @configurations_option_table values('SMO and DMO XPs',1,1)
insert into @configurations_option_table values('SQL Mail XPs',0,0)
insert into @configurations_option_table values('transform noise words',0,0)
insert into @configurations_option_table values('two digit year cutoff',2049,2049)
insert into @configurations_option_table values('user connections',0,0)
insert into @configurations_option_table values('user options',0,0)
insert into @configurations_option_table values('Web Assistant Procedures',0,0)
insert into @configurations_option_table values('xp_cmdshell',0,0)

insert into @tracestatus exec('dbcc tracestatus')
update @tracestatus set TraceFlag = 'Traceflag ('+TraceFlag+')'



select 1 as l1
,       st.name as name 
,       convert(nvarchar(15),st.run_value) as run_value
,       convert(nvarchar(15),ct.default_value) as default_value
,       1 as msg  
from @configurations_option_table ct 
inner join  @sp_configure_table st on (ct.name = st.name  and ct.default_value != st.run_value)
union
select 1 as l1
,       TraceFlag as name
,       convert(nvarchar(15), Status) as run_value
,       '0' as default_value
,       1 as msg  
from @tracestatus where Global=1 order by name
end try
begin catch
select -100 as l1
,       ERROR_NUMBER() as name
,       ERROR_SEVERITY() as run_value
,       ERROR_STATE() as default_value
,       ERROR_MESSAGE() as msg
end catch
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s