SQL SERVER � Server Configurations Options � Allow updates | Backup Server Tips

This option is present when sp_configure is executed or when sys.configuration is queried, but this functionality is unavailable since SQL Server 2005, it is obsolete. Direct updates to system objects are not allowed. If sp_configure is used to enable the option, this configuration has not effect. In previous versions as SQL Server 2000 this functionality is available.

Testing the option

The following code enable the option �allow updates�, then a table is created and finally an update to system table is executed:

sp_configure 'allow updates', 1

GO

RECONFIGURE with override

GO

CREATE TABLE TBLTEST (TESTFIELD INT )

GO

update sys.objects

set name = 'TBLTEST_1'

where name = 'TBLTEST'

GO

When the update to system table is triggered the following error is shown:

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

This option is not advanced option and is not necessary enable the �show advanced options�. To understand in a better way if a certain option is or is not advanced, if it is dynamic or is necessary restart, please refer SQL SERVER � Server Configurations Options � Catalog View � sys.configurations

SQL SERVER � Server Configurations Options � Agent XPs Option | Backup Server Tips

The Server configuration option called Agent XPs is used to enabled the SQL Server Agent extended stored procedures on the server. When this option is disabled, the SQL Server Agent node is not enabled in SQL Server Management Studio and you can not use the SQL Server Agent Service.

When the SQL Server agent is restarted, the extended stored procedures are enabled automatically.

To enabled this option:

sp_configure 'show advanced options', 1

GO

RECONFIGURE with override

GO

sp_configure 'Agent XPs', 1

GO

RECONFIGURE with override

GO

sp_configure 'show advanced options', 0

GO

RECONFIGURE with override

GO

This option is an advanced option and is necessary enable first the �show advanced options�. To understand in a better way if a certain option is or is not advanced, if it is dynamic or is necessary restart, please refer SQL SERVER � Server Configurations Options � Catalog View � sys.configurations

SQL SERVER � Server Configurations Options � Catalog View � sys.configurations | Backup Server Tips

The sys.configuration catalog view allow us to see the same values than sp_configure with �show advanced options� enabled. Even this Catalog view show us extra information because is possible to know if an option is or is not an advanced option, or if is or is not dynamic option.

Important Columns,

Value: Configured Value for the option

Minimum: Minimum Value for the option

Maximum: Maximum Value for the option

Value_in_use: current running value for the option.

Is_dynamic: it column allow us to know if the option is dynamic or not. 1 = the value takes effect when the RECONFIGURE statement is executed. 0 = the value takes effect when the SQL Server is restarted.

Is_advanced: it column allow us to know if the option is advanced. 1 = advanced option, it means that the option is displayed and can be changed just when �show advanced options� is set through sp_configure.

Now we are going to see statements using sys.configurations that are equivalent than sp_configure.

Normal View:

Using sys.configurations

select * from sys.configurations where is_advanced = 0

Using sp_configure:

sp_configure 'show advanced options', 0

GO

RECONFIGURE with override

GO

sp_configure

GO

Advanced Option View:

Using sys.configurations

select * from sys.configurations where is_advanced = 1

Using sp_configure:

sp_configure 'show advanced options', 1

GO

RECONFIGURE with override

GO

sp_configure

GO

Dynamic Option View

Using sys.configurations

To see all the dynamic options:

select * from sys.configurations where is_dynamic = 1

To see all the non dynamic options:

select * from sys.configurations where is_dynamic = 0

Using sp_configure is impossible to know if an option is dynamic.

Conclusion.

Using sys.configurations to see the server configuration is better than sp_configure because with a single select statement is possible to view all the server options with extra information as is_dynamic and is_advanced values, this extra information is impossible to obtain with sp_configure. Any changed necessary on the server configuration has to be performed with sp_configure.

SQL SERVER � Server Configurations Options � Adhoc distributed Queries Option | Backup Server Tips

When Adhoc Distributed Queries Options is enable (value = 1), SQL Server allow use Ad hoc queries through OPENROWSET and OPENDATASOURCE functions using OLEDB provider.

OPENROWSET and OPENDATASOURCE use OLE DB providers, this functions must be used only for data that is infrequently accessed. For data that is frequently accessed, is better use linked servers.

Example:

I have two SQL Server instances:

The first one is the localhost (.), I am going to called Server 1, and the second one is a SQL Server named instance localhost\BKP (.\BKP), I am going to called Server 2.

Server 2.

create database db_test2

GO

use db_test2

GO

create table table2

(

field1 int,

field2 varchar(10)

)

go

insert into table2 values (1,'test')

GO

Server 1.

On the server 1, I check and the Ad Hoc Distributed Queries is disabled. ( run_value = 0 )

sp_configure 'Ad Hoc Distributed Queries'

/*

Result

name minimum maximum config_value run_value

----------------------------------- ----------- ----------- ------------ -----------

Ad Hoc Distributed Queries 0 1 0 0

*/

Then, I test my Ad hoc query using OPENROWSET.

select * from

openrowset('SQLNCLI','Server=.\bkp;Uid=test_user;Pwd=test;Database=db_test2;','select * from dbo.table2')

I got the following error because the Ad Hoc Distributed Queries option is not enabled.

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

To execute the Ad hoc query is necessary enable the option and try again the query.

sp_configure 'Ad Hoc Distributed Queries',1

GO

reconfigure with override

GO

/*

Result.

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

*/

select * from

openrowset('SQLNCLI','Server=.\bkp;Uid=test_user;Pwd=test;Database=db_test2;','select * from dbo.table2')

/*

Result.

field1 field2

----------- ----------

1 test

(1 row(s) affected)

*/

SQL SERVER � Server Configuration Options | Backup Server Tips

The server configuration options can be managed using sp_configure system stored procedure and SQL Server Management Studio. Some options needs to be configured with SQL Server Surface Area Configuration Tool. The most frequent options can be configured through SQL Server Management Studio. The entire configuration options can be managed and modified using sp_configure.

Is essential for DBA to know the meaning and understand each server configuration options. We know that is hard to accomplish this task, but we can do it, if we find out one by one each of this options. Hence, we are going to try to understand one by one (day by day) these options on this blog.

First we need to know what are the all the server configuration options. To know what are the entire server configurations, we can look into sys.configuration system view or use sp_configure system stored procedure.

select * from sys.configurations;

or

sp_configure 'show advanced options',1

GO

reconfigure

Go

sp_configure

go

In the following table we can the see all the server configuration options, the minimum / maximum value for each option, default value. Each value has a letter A ( advanced option, to change this options the setting show advanced options to 1 is required), RR ( this options require restart the db engine) and SC ( automatic configuration, restart is not required).

Configuration option	Minimum value	Maximum value	Default
access check cache bucket count (A)	0	16384	0
access check cache quota (A)	0	2147483647	0
ad hoc distributed queries (A)	0	1	0
affinity I/O mask (A, RR)	-2147483648	2147483647	0
affinity64 I/O mask (A, only available on 64-bit version of SQL Server)	-2147483648	2147483647	0
affinity mask (A)	-2147483648	2147483647	0
affinity64 mask (A, RR), only available on 64-bit version of SQL Server	-2147483648	2147483647	0
Agent XPs (A)	0	1	0 (Changes to 1 when SQL Server Agent is started. Will be 1 if SQL Server Agent is set to automatic start during setup.)
allow updates (Obsolete. Do not use. Will cause an error during reconfigure.)	0	1	0
awe enabled (A, RR)	0	1	0
backup compression default	0	1	0
blocked process threshold (A)	0	86400	0
c2 audit mode (A, RR)	0	1	0
clr enabled	0	1	0
common criteria compliance enabled (A, RR)	0	1	0
cost threshold for parallelism (A)	0	32767	5
cross db ownership chaining	0	1	0
cursor threshold (A)	-1	2147483647	-1
Database Mail XPs (A)	0	1	0
default full-text language (A)	0	2147483647	1033
default language	0	9999	0
default trace enabled (A)	0	1	1
disallow results from triggers (A)	0	1	0
EKM provider enabled	0	1	0
filestream_access_level	0	2	0
fill factor (A, RR)	0	100	0
ft crawl bandwidth (max) , see ft crawl bandwidth(A)	0	32767	100
ft crawl bandwidth (min) , see ft crawl bandwidth(A)	0	32767	0
ft notify bandwidth (max) , see ft notify bandwidth(A)	0	32767	100
ft notify bandwidth (min) , see ft notify bandwidth(A)	0	32767	0
index create memory (A, SC)	704	2147483647	0
in-doubt xact resolution (A)	0	2	0
lightweight pooling (A, RR)	0	1	0
locks (A, RR, SC)	5000	2147483647	0
max degree of parallelism (A)	0	64	0
max full-text crawl range (A)	0	256	4
max server memory (A, SC)	16	2147483647	2147483647
max text repl size	0	2147483647	65536
max worker threads (A, RR)	128	32767 (1024 is the maximum recommended for 32-bit SQL Server, 2048 for 64-bit SQL Server.)	0 Zero auto-configures the number of max worker threads depending on the number of processors, using the formula (256+( -4) * 8) for 32-bit SQL Server and twice that for 64-bit SQL Server.
media retention (A, RR)	0	365	0
min memory per query (A)	512	2147483647	1024
min server memory (A, SC)	0	2147483647	0
nested triggers	0	1	1
network packet size (A)	512	32767	4096
Ole Automation Procedures (A)	0	1	0
open objects (A, RR, obsolete)	0	2147483647	0
optimize for ad hoc workloads (A)	0	1	0
PH_timeout (A)	1	3600	60
precompute rank (A)	0	1	0
priority boost (A, RR)	0	1	0
query governor cost limit (A)	0	2147483647	0
query wait (A)	-1	2147483647	-1
recovery interval (A, SC)	0	32767	0
remote access (RR)	0	1	1
remote admin connections	0	1	0
remote login timeout	0	2147483647	20
remote proc trans	0	1	0
remote query timeout	0	2147483647	600
Replication XPs Option (A)	0	1	0
scan for startup procs (A, RR)	0	1	0
server trigger recursion	0	1	1
set working set size (A, RR, obsolete)	0	1	0
show advanced options	0	1	0
SMO and DMO XPs (A)	0	1	1
SQL Mail XPs (A)	0	1	0
transform noise words (A)	0	1	0
two digit year cutoff (A)	1753	9999	2049
user connections (A, RR, SC)	0	32767	0
User Instance Timeout (A, only appears in SQL Server 2008 Express.)	5	65535	60
user instances enabled (A, only appears in SQL Server 2008 Express.)	0	1	0
user options	0	32767	0
xp_cmdshell (A)	0	1	0

so, we are going to try day by day see some details for each of the server configuration options.

References:

SQL Server BOL - Server configuration options.