You can configure many areas of SQL Server using the SQL Server Properties dialog box, which will be discussed in Chapter 5. As you have learned in this chapter, you can also configure SQL Server with stored procedures, such as sp_configure and sp_dboption. You execute stored procedures and other queries in SQL Server Management Studio. SQL Server Management Studio has a built-in client tool that sends commands to a SQL Server instance, which in turn parses, compiles, and executes the commands.
The following sections explain how to configure SQL Server using SQL Server Management Studio and stored procedures. You will find more detailed coverage of SQL Server Management Studio in other chapters.
You can start SQL Server Management Studio and access the built-in query client by completing the following steps:
Select Start, Programs or All Programs, Microsoft SQL Server 2005, SQL Server Management Studio. Or type sqlwb at a command prompt.
In the Connect To Server dialog box, use the Server Type drop-down list to select the database component to which you want to connect, such as Database Engine.
In the Server Name field, type the name of the server on which SQL Server is running, such as CorpSvr04.
Note
You can only connect to registered servers. If the SQL Server you want to work with is not registered, you will need to register the server before you can work with it. See the section titled "Managing Servers" in Chapter 5 for details.
Use the Authentication selection menu to choose the authentication type as Windows Authentication or SQL Server Authentication (based on the allowed authentication types when you installed the server). Provide a Windows user name or SQL Server login ID and password as necessary.
Windows Authentication. Uses your current domain account and password to establish the database connection. This works only if Windows authentication is enabled and you have appropriate privileges.
SQL Server Authentication. Allows you to specify a SQL Server login ID and password.
Click Connect. You connect to the default instance (unless you have configured another default previously). To change the instance to which you connect, click Option, select the Connection Properties tab, and then use the Connect To Database drop-down list to select the instance to which you want to connect.
In SQL Server Management Studio, select New Query on the tool bar, and then select the query type, such as Database Engine Query.
In the Connect To... dialog box shown in Figure 4-1, specify the server name, or select Browse For More in the drop-down list to search for all SQL servers within an entire Active Directory Forest.
Specify the authentication technique to use. Click Connect. As before, you connect to the default instance (unless you have configured another default previously). To change the instance to which you connect, click Option, select the Connection Properties tab, and then use the Connect To Database drop-down list to select the instance to which you want to connect.
If you are working with an active database in SQL Server Management Studio and have already authenticated the connection, you can automatically connect to the currently selected database server instance and use your current authentication information to log on. To do this, right-click the database in the Object Explorer view in SQL Server Management Studio, and then select New Query.
The query window in SQL Server Management Studio is normally divided into three panes (see Figure 4-2). The left pane allows you to browse objects that are available on the currently selected database server instance. Use the upper-right pane to enter queries. The lower-right pane displays results.
If you do not see a separate pane at the lower right of the window, don’t worry. It displays automatically when you type a query. You can also set the pane to display by default. Select the Show Results Pane option on the Window menu.
As you know, you can use sp_configure to view and change SQL Server configuration settings. Two types of configuration settings are available: those that are dynamic and those that are not. In this instance, a dynamic setting is one that you can change without having to stop and restart SQL Server. To execute sp_configure or other types of queries, type a command in the top pane and then click the Execute Query button on the toolbar (the red exclamation point). You can also execute commands using these key sequences:
F5
Ctrl+E
Alt+X
Note
By default, all users have execute permissions on sp_configure so that they can view settings. However, only users with the Alter Settings server-level permission can use sp_configure to change configuration options. By default, only members of the sysadmin and serveradmin fixed server roles have this permission. As with sp_configure, only users with Alter Settings server-level permission can execute the RECONFIGURE or RECONFIGURE WITH OVERRIDE command.
Whenever you use sp_configure to modify settings, the changes do not actually take place until you also execute the RECONFIGURE command. You can change some highly risky settings only with the RECONFIGURE WITH OVERRIDE command. Additionally, sp_configure settings are divided into two categories: standard and advanced. You can execute standard commands at any time, but you can execute advanced commands only when Show Advanced Options is set to 1. With this setting in effect, you can modify both standard and advanced settings. Follow this procedure to allow modification of advanced settings:
In SQL Server Management Studio, type:
exec sp_configure "show advanced options", 1 go reconfigure go
Execute the commands by pressing Ctrl+E.
Clear the query window.
Now type one sp_configure command for each option you want to change.
Type go.
Execute the commands by pressing Ctrl+E.
If you changed any nondynamic settings, stop and start the server. (See Table 4-7 and Table 4-8 for details.)
Table 4-7 provides a summary of the standard configuration parameters. The parameters are listed in alphabetical order, with the minimum, maximum, and default values shown. The dynamic parameter column tells you if the setting is dynamic. If you see an "N" in this column, you will need to stop and restart the server to enforce changes.
Table 4-7. Quick Reference Summary for Standard Configuration Parameters
Configuration Option | Minimum Value | Maximum Value | Default Value | Dynamic Yes/No |
|---|---|---|---|---|
allow updates | 0 | 1 | 0 | Y |
clr enabled | 0 | 1 | 0 | Y |
cross db ownership chaining | 0 | 1 | 0 | Y |
cursor threshold | -1 | 2147483647 | -1 | Y |
default language | 0 | 9999 | 0 | Y |
nested triggers | 0 | 1 | 1 | Y |
remote access | 0 | 1 | 1 | N |
remote admin connections | 0 | 1 | 0 | Y |
remote login timeout | 0 | 2147483647 | 20 | Y |
remote proc trans | 0 | 1 | 0 | Y |
remote query timeout | 0 | 2147483647 | 600 | Y |
server trigger recursion | 0 | 1 | 1 | Y |
show advanced options | 0 | 1 | 0 | Y |
user options | 0 | 32767 | 0 | Y |
Table 4-8 provides a summary of the advanced configuration parameters. To view or change these parameters, you have to set the parameter Show Advanced Options to 1. Self-configuring options have an asterisk (*) after the name. With max worker threads, 1024 is max recommended for 32-bit operating systems. The default value zero (0) autoconfigures using the formula: 256 + (number of processors - 4) * 8. Note also that you cannot change some advanced options (you can only view them).
Table 4-8. Quick Reference Summary for Advanced Configuration Parameters
Configuration Option | Minimum Value | Maximum Value | Default Value | Dynamic Yes/No |
|---|---|---|---|---|
Ad Hoc Distributed Queries | 0 | 1 | 0 | Y |
affinity I/O mask | -2147483648 | 2147483647 | 0 | N |
affinity mask | -2147483648 | 2147483647 | 0 | N |
Agent XPs | 0 | 1 | 0 | Y |
awe enabled | 0 | 1 | 0 | N |
blocked process threshold | 0 | 86400 | 0 | Y |
c2 audit mode | 0 | 1 | 0 | N |
cost threshold for parallelism | 0 | 32767 | 5 | Y |
Database Mail XPs | 0 | 1 | 0 | Y |
default full-text language | 0 | 2147483647 | 1033 | Y |
default trace enabled | 0 | 1 | 1 | Y |
disallow results from triggers | 0 | 1 | 0 | Y |
fill factor | 0 | 100 | 0 | N |
ft crawl bandwidth max | 0 | 32767 | 100 | Y |
ft crawl bandwidth min | 0 | 32767 | 0 | Y |
ft notify bandwidth max | 0 | 32767 | 100 | Y |
ft notify bandwidth min | 0 | 32767 | 0 | Y |
index create memory* | 704 | 2147483647 | 0 | N |
in-doubt xact resolution | 0 | 2 | 0 | Y |
lightweight pooling | 0 | 1 | 0 | N |
locks* | 5000 | 2147483647 | 0 | N |
max degree of parallelism | 0 | 64 | 0 | Y |
max full-text crawl range | 0 | 256 | 4 | Y |
max server memory* | 16 | 2147483647 | 2147483647 | N |
max text repl size | 0 | 2147483647 | 65536 | Y |
max worker threads | 128 | 32767 | 0 | N |
media retention | 0 | 365 | 0 | N |
min memory per query | 512 | 2147483647 | 1024 | Y |
min server memory* | 0 | 2147483647 | 0 | N |
network packet size | 512 | 32767 | 4096 | Y |
OLE Automation Procedures | 0 | 1 | 0 | Y |
open objects | 0 | 2147483647 | 0 | N |
ph_timeout | 1 | 3600 | 60 | Y |
precompute rank | 0 | 1 | 0 | Y |
priority boost | 0 | 1 | 0 | N |
query governor cost limit | 0 | 2147483647 | 0 | Y |
query wai | -1 | 2147483647 | -1 | Y |
recovery interval* | 0 | 32767 | 0 | N |
Replication XPs | 0 | 1 | 0 | Y |
scan for startup procs | 0 | 1 | 0 | N |
set working set size | 0 | 1 | 0 | N |
SMO and DMO XPs | 0 | 1 | 1 | Y |
SQL Mail XPs | 0 | 1 | 0 | Y |
transform noise words | 0 | 1 | 0 | Y |
two digit year cutoff | 1753 | 9999 | 2049 | Y |
user connections* | 0 | 32767 | 0 | N |
Web Assistant Procedures | 0 | 1 | 0 | Y |
xp_cmdshell | 0 | 1 | 0 | Y |
You can view the current settings of all configuration options by executing the following query:
exec sp_configure go
To view the current setting of a configuration option, execute the following query:
exec sp_configure "optionName" go
where optionName is the name of the option you want to examine, such as:
exec sp_configure "allow updates" go
To change the value of a setting, execute the following query:
exec sp_configure "optionName" newValue go reconfigure with override go
where optionName is the name of the option you want to examine, and newValue is the new value for this option, such as:
exec sp_configure "allow updates" 1 go reconfigure with override go
By default, all users have execute permissions on sp_dboption so that they can view settings. However, only members of the sysadmin and dbcreator fixed server roles and the db_owner fixed database role can use sp_dboption to change database settings. When you execute sp_dboption, a checkpoint occurs in the database for which the option was changed and this causes the change to take effect immediately.
Table 4-9 provides an overview of the options you can use with sp_dboption. All of the listed options accept a TRUE (1) or FALSE (0) value, which is used to set the state of the option. For example, if there were no current users connected to the CustomerSupport database, you could set it to read-only using the following command:
USE master; GO EXEC sp_dboption "CustomerSupport", "read only", "TRUE";
Table 4-9. Quick Reference Summary for Database Options
Option | When TRUE... |
|---|---|
ANSI null default | CREATE TABLE uses SQL-92 rules to determine if a column allows null values. |
ANSI nulls | All comparisons to a null value evaluate to UNKNOWN. (When FALSE, non-UNICODE values evaluate to TRUE if both values are NULL.) |
ANSI padding | Trailing blanks are inserted into character values and trailing zeroes are inserted into binary values to pad to the length of the column. |
ANSI warnings | Errors or warnings are issued when conditions such as "divide by zero" occur. |
Arithabort | An overflow or divide-by-zero error causes the query or batch to terminate. If the error occurs in a transaction, the transaction is rolled back. (When FALSE, a warning message is displayed, but execution continues as if no error occurred.) |
auto create statistics | Any missing statistics needed for query optimization are automatically generated. |
auto update statistic | Any out-of-date statistics needed for query optimization are automatically generated. |
Autoclose | After the last user logs off, the database is shut down cleanly and its resources are freed. |
Autoshrink | Automatic periodic shrinking is enabled for the database. |
concat null yields null | If either operand in a concatenation operation is NULL, the result is NULL. |
cursor close on commit | Any cursors that are open when a transaction is committed or rolled back are closed. (When FALSE, cursors remain open when a transaction is committed. Rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.) |
db_chaining | The database can be the source or target of a cross-database ownership chain. |
dbo use only | Only the database owner can use the database. |
default to local cursor | Cursor declarations default to LOCAL. |
merge publish | The database can be published for a merge replication. |
numeric roundabort | An error is generated when loss of precision occurs in an expression. (When FALSE, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.) |
Offline | The database is offline. (When FALSE, the database is online.) |
Published | The database can be published for replication. |
quoted identifier | Double quotation marks can be used to enclose delimited identifiers. |
read only | The database is set to read-only (but can be deleted using the DROP DATABASE statement). The database cannot be in use when this option is set (except for master). |
recursive triggers | Enables recursive firing of triggers. (When FALSE, prevents direct recursion but not indirect recursion. To disable indirect recursion, set the nested triggers server option to 0 using sp_configure.) |
select into/bulkcopy | Causes the recovery model to be reset to BULK_LOGGED. |
single user | Only one user at a time can access the database. |
Subscribed | The database can be subscribed for publication. |
torn page detection | Incomplete pages can be detected. |
trunc. log on chkpt. | Sets the recovery model of the database to SIMPLE and allows a checkpoint to truncate the inactive part of the log. (When FALSE, sets the recovery model to FULL.) The master database must allow checkpoints. |