Requests for data are handled through user connections to client systems. The client opens a connection to SQL Server, makes a request, and waits for a response from SQL Server. When the client is finished with its request, it closes the connection. Other servers and applications can also connect to SQL Server remotely. To configure client connections and remote server connections, you can use the Connections page in the Server Properties dialog box.
Many settings are associated with client and server connections, as you can see in Figure 6-5, which shows the default configuration. This section examines connection settings and cases in which you might want to change these settings.
Figure 6-5. The default connection settings on the Connections page of the Server Properties dialog box
On the Connections page, the Maximum Number Of Concurrent User Connections box provides you with the ability to set the maximum number of connections to SQL Server at any one time. You can set this value from 0 to 32,767. By default, the value is set to 0, which means that an unlimited number of connections can be made to SQL Server. However, the actual number of possible user connections really depends on hardware, application, and other server limitations. You can determine the number of user connections your system can handle by executing the following command in query view:
select @@max_connections
To set the maximum number of user connections, complete the following steps:
exec sp_configure "user connections", <integer value>Note
You should not need to change the maximum connections value. If you do change the setting, be careful. When the server reaches the maximum number of connections, users receive an error message and are not able to connect to the server until another user disconnects and a connection becomes available. The only time you would need to set this option is in a situation with a large number of users in which you need to limit the number of active connections to ensure that requests for connected users are handled in a timely manner. A better alternative is to add sufficient memory to the system or configure a cluster to balance the workload, or both. If you administer a system with a large numbers of users, you should also ensure that SQL applications connect and then disconnect promptly when finished to reallocate resources quickly to other users.
On the Connections page, you will see a list box labeled Default Connection Options (see Figure 6-5). Use the list box options to set default query-processing options for user connections. Select an option by selecting its check box. Cancel an option by clearing the check box. Any changes you make will affect new logins only; current logins are not affected. Furthermore, users can override the defaults by using set statements, if necessary.
Table 6-1 provides a summary of the connection options, as well as the default state for ODBC (open database connectivity) and OLE DB (object linking and embedding database, which may be different from the SQL Server default). The table also includes a list of commands you can use with sp_configure, the corresponding value for the configuration bit mask, and the SET commands that can override the default settings in a user session.
Table 6-1. Configuring Connection Options
Connection Option | When On… | Default State | Bit Mask Value | SET Command |
|---|---|---|---|---|
Implicit transactions (connected) | Uses transactions implicitly whenever statements are executed. | OFF | 2 | IMPLICIT_TRANSACTIONS |
Cursor close on COMMIT | Automatically closes a cursor at the end of a transaction. | OFF | 4 | CURSOR_CLOSE_ON_COMMIT |
ANSI warnings | SQL Server displays null, overflow, and divide-by-zero warnings. Otherwise, no error or NULL may be returned. | OFF | 8 | ANSI_WARNINGS |
ANSI padding | Data in fixed-length fields are padded with trailing spaces to fill out the width of the column. | OFF | 16 | ANSI_PADDING |
ANSI nulls | Comparing anything with NULL gives an unknown result. | OFF | 32 | ANSI_NULLS |
Arithmetic abort | Causes a query to terminate when an overflow or divide-by-zero error occurs. | OFF | 64 | ARITHABORT |
Arithmetic ignore | Returns NULL when an overflow or divide-by-zero error occurs during a query. | OFF | 128 | ARITHIGNORE |
Quoted identifier | SQL Server interprets double quotation marks as indicating an identifier rather than as delimiting a string. | OFF | 256 | QUOTED_IDENTIFIER |
No count | Turns off the display of the number of rows returned in a query. | OFF | 512 | NOCOUNT |
ANSI null default ON | New columns are defined to allow nulls (if you do not explicitly allow or disallow nulls). | OFF | 1024 | ANSI_NULL_DFLT_ON |
ANSI null default OFF | New columns are defined not to allow nulls (if you don—t explicitly allow or disallow nulls). | OFF | 2048 | ANSI_NULL_DFLT_OFF |
concat null yields null | Returns NULL when concatenating a NULL value within a string. | OFF | 4096 | CONCAT_NULL_YIELDS_NULL |
numeric round abort | Generates an error when a loss of precision occurs. | OFF | 8192 | NUMERIC_ROUNDABORT |
xact abort | Rolls back a transaction if a T-SQL statement raises a runtime error. | OFF | 16384 | XACT_ABORT |
For sp_configure, the default options are set with the following user options parameter:
exec sp_configure "user options", <integer bit mask value>In this case, the bit mask value is the sum of the numeric values for all the options you want to use. Each option has a corresponding SET command as well. When you make a connection, you can use the SET command to override the default setting for the session. For example, if you want to turn on ANSI padding, ANSI nulls, and ANSI warnings, use the bit mask value 56, such as:
exec sp_configure "user options", 56
In a user session, you could later turn these options on or off by using:
set ansi_padding on set ansi_nulls off
Connections from other servers are handled differently than user connections. You can determine whether or not servers can connect to this server, how long it takes for remote queries to time out, and if distributed transactions are used. To configure remote connections, complete these steps:
From the Server Properties dialog box, go to the Connections page.
To allow servers to connect to this server, select the option Allow Remote Connections To This Server. Remote servers can then log on to the server to execute stored procedures remotely. You must stop and restart the server to apply the change if you select this option.
By default, queries executed by remote servers time out in 600 seconds. To change this behavior, type a time-out value in the Remote Query Timeout box on the Connections page. Time-out values are set in seconds, and the acceptable range of values is from 0 to 2,147,483,647. A value of 0 means that there is no query time-out for remote server connections.
Stored procedures and queries executed on the server can be handled as distributed transactions by using MS DTC. If you want to execute procedures this way, select the Require Distributed Transactions For Server-To-Server Communication check box. If you change this option, you must stop and restart the server.
Click OK.
These options can also be set with sp_configure. The related Transact-SQL statements are:
exec sp_configure "remote access", <0 or 1> exec sp_configure "remote query timeout", <number of seconds> exec sp_configure "remote proc trans", <0 or 1>