Configuring SQL Server 2000 Options

How to make sure all your systems are go

Among the least understood and most often misused SQL Server settings are those for configuring the server and database. You might have experienced confusion about these settings when a vendor's tech support representative asked you to adjust a setting one way after another representative asked you to tweak the same setting in the other direction. The documentation about these options is lacking or—at the very least—confusing. In SQL Server 2000, Microsoft has eliminated some of this confusion by removing a few configuration options and making them dynamic. The company also added only two new server-configuration options to this release and adjusted some database options, which will make your life as a DBA a little easier.

Added Server Options

As with most Enterprise Edition server properties, you can't use the server properties in SQL Server Enterprise Manager to access the two new server options that Microsoft added to SQL Server 2000. As a safeguard to keep you from inadvertently harming your server, Microsoft didn't place these advanced configuration options in Enterprise Manager. Instead, you must use the T-SQL sp_configure system stored procedure to access these advanced options. To see how your server is currently configured, run sp_configure without any parameters. In the result, the config_value column, which SQL Server pulls from the syscurconfigs table in the Master database, represents your server's current configuration. The run_value column shows what SQL Server was running at the time you executed sp_configure; SQL Server stores this data in the sysconfigures table. After you change an option, you must execute the RECONFIGURE command (and restart SQL Server, in most cases) before the new run_value shows up. All the options I discuss require you to restart your SQL Server machine.

By default, the sp_configure stored procedure shows you only 10 of the 36 available server options; the list excludes the advanced options. Neither of the new SQL Server settings is in this condensed list, but if you use the show advanced options option, SQL Server shows you all the settings. To turn on this option, use the following syntax:

EXEC sp_configure 'show advanced options', '1'RECONFIGURE

To install the option, you must run the RECONFIGURE command after you use sp_configure to reconfigure your server. The above query generates the following result:

Configuration option 'show advanced options' changedfrom 0 to 1.Run the RECONFIGURE command to install.

When you can view the advanced options, you'll see the two new settings. The most important new setting is the awe enabled option. This option lets SQL Server Enterprise Edition scale up your server's memory capacity. By default, SQL Server can use a maximum of 3GB of RAM. With Windows 2000, applications can use the Address Windowing Extensions (AWE) API to address more RAM. In Win2K Advanced Server, for example, you can now use up to 8GB of RAM, which is exceeded only by Win2K Datacenter Server's support for 64GB. When SQL Server has more memory available, it can cache more data and improve your query-response time.

Setting the awe enabled option has disadvantages, however: After you set this option, SQL Server can no longer dynamically allocate RAM. The loss of this functionality adds administration cost because you have to closely monitor your RAM usage. After you set this option, you also need to set the max server memory option. If you don't set max server memory and your server has at least 3GB of RAM, SQL Server on startup takes almost all the available RAM on the machine, leaving only 128MB of RAM for Windows and other applications. By setting the max server memory option, you limit the amount of memory that SQL Server takes.

The awe enabled option works only with SQL Server 2000 Enterprise Edition running on Win2K AS or Datacenter. If you set this option on other editions of SQL Server or on servers running Windows NT, SQL Server will ignore it. On some server combinations, improper configuration of this option could lead to unpredictable results. For example, if you try to set this option on a Windows 98 machine running SQL Server Personal Edition, SQL Server might report that it has stopped—even when it's running—and won't let you stop the instance.

Enabling AWE support for your SQL Server Enterprise Edition server involves three steps. First, you must ensure that the account that starts your SQL Server instance has the Windows policy right to lock pages in memory. Installing SQL Server automatically grants page-locking permission to the Windows account that you specify to start the SQL Server service. But if you changed the startup account, you probably need to check the privileges assigned to the user who starts SQL Server. You can check account permissions by using the Win2K Group Policy utility. The next step is to run the sp_configure stored procedure and set the awe enabled option to 1. Then, you must run the RECONFIGURE command and restart SQL Server manually. The reconfigure syntax is

EXEC sp_configure 'awe enabled', '1'RECONFIGURE

Note that you must also complete additional steps on Win2K or NT to address physical memory above 4GB. You will have to modify your boot.ini file by adding the /pae switch. For more information about this process, see Windows 2000 Books Online (BOL).

The next new SQL Server 2000 option enables the C2 security audit mode. C2 is a government security rating that certifies that a system has secured its resources and has sufficient auditing capabilities. This mode lets you monitor all attempts to access any database objects. To turn on the C2 auditing feature for SQL Server, use the following syntax:

EXEC sp_configure 'c2 audit mode', '1'RECONFIGURE

(To be fully C2-certified, your Windows OS must also be compliant.) After you enable the c2 audit mode option and restart SQL Server, SQL Server automatically creates a trace file in your MSSQLData directory. With SQL Server Profiler, you can view the trace file, which monitors server activity.

SQL Server writes data to the trace file in 128KB blocks, so if your SQL Server shuts down improperly, at most you might lose 128KB of log data. As you can imagine, the logs that contain audit information grow quickly: After I accessed only three tables on my server, the trace file had already grown to more than 1MB. When the log file grows to 200MB, C2 auditing closes the log file and creates a new one. Each time SQL Server starts, it creates a new trace file. If you run out of hard disk space, SQL Server will shut down until you can free up space for the audit log and restart the instance. You can also bypass auditing by using the -f switch when you start SQL Server.

Removed Server Options

Microsoft removed several options in SQL Server 2000 and programmed SQL Server 2000 to automatically configure them. The most notable removed option is the max async IO setting, which lets DBAs specify how many asynchronous disk reads and writes can occur on a single database file. One of the least understood settings in SQL Server 7.0, the max async IO option is set to 32 by default, and DBAs rarely adjust it. In SQL Server 2000, the async IO option dynamically ramps up and down as SQL Server receives adaptive feedback. SQL Server uses the feedback algorithm to determine the server load and how much the SQL Server system can handle.

Database Options

If you've seen the Enterprise Manager database Options tab in SQL Server 2000, you might be puzzled about where some common options have gone. (To access the Options tab, right-click a database in Enterprise Manager and select Properties.) The trunc. log on chkpt. and Select Into/Bulk Copy options are missing from the Options tab, which Figure 1 shows. For clarity and backward compatibility, these common options are now called the recovery model option. (For a comprehensive look at recovery models, see Kalen Delaney, Inside SQL Server, "Database Recovery Models," June 2000.) If you use the SQL Server 2000 Enterprise Manager to connect to a SQL Server 7.0 database, you'll still see the old options. Traditionally, you use the following syntax to turn on the trunc. log on chkpt. option for the Northwind database:

SP_DBOPTION Northwind ,'trunc. log on chkpt.', true

After setting the option, you can look at the Options tab or run the following query to see whether the option is set on the Northwind database:

SELECT DATABASEPROPERTY ('Northwind', 'IsTruncLog')

A result of 1 means the option is set to true; a result of 0 means it is set to false. A NULL value means that either you selected an invalid option or the database doesn't exist.

Microsoft has converted the trunc. log on chkpt. and Select Into/Bulk Copy options to the recovery model setting for easier usability. The goal of this option change is to ensure that DBAs fully understand what setting the recovery model option means to a disaster-recovery strategy. SQL Server 2000 gives you three recovery models for your database: simple, full, and bulk_logged.

The simple recovery model is the easiest to administer but is the least flexible disaster-recovery strategy. Choosing a simple recovery model is the equivalent of setting trunc. log on chkpt. to true. With this recovery model, you can perform only full and differential backups: Because your transaction log is constantly being truncated, transaction log backups aren't available. Generally, for a production system that contains mission-critical data, you wouldn't use this recovery model because it doesn't let you recover to the point of failure. The best "recover to" time you can provide with this recovery model is the time of the last known good full and differential backups. In this model, you'd have to restore to the last known good full backup, then restore the last differential backup over it. (The differential backup applies just the changes to the database that have occurred since the last full backup.)

The full recovery model sets both the trunc. log on chkpt. option and the Select Into/Bulk Copy option to false. This configuration gives full point-in-time recovery because all backup options are available. This model is ideal for protecting a production environment that contains mission-critical data, but it leads to a higher cost in equipment and administration because large transaction logs can build up quickly if you have an active database system. Because Select Into/Bulk Copy is set to false in this model, SQL Server logs all events, including bulk loads.

The final recovery model is bulk_logged, which sets trunc. log on chkpt. to false and Select Into/Bulk Copy to true. SQL Server doesn't log all bulk operations into the transaction log in this model. Instead, it minimally logs bulk operations and logs the extents where the data changed. When you perform a transaction log backup, SQL Server backs up not only the transaction log, but also the extents where the bulk operations occurred. This model results in a smaller transaction log than the full recovery model, but a much larger transaction log backup if bulk operations occurred. However, using full, differential, and transaction log backups still gives you the point-in-time recoverability if no bulk operations were performed.

You can change the recovery mode to simple by selecting Simple from the Model drop-down list box on the database Options tab. Microsoft also extended the ALTER DATABASE command to let you set database properties. For example, you can use the following T-SQL syntax to set the recovery model to full:

ALTER DATABASE NorthwindSET RECOVERY FULL

Some of the coolest new behind-the-scenes setting changes in SQL Server 2000 are the various options you have for placing a database in single-user mode. DBAs often place a database in single-user mode to fix corruption or other data problems. While a database is in this mode, no other user can access the data, so you can correct data problems before users can use the bad data. With SQL Server 7.0, you have to ensure that all users are disconnected before you can place a database in single-user mode. In a high-speed OLTP database, such as an e-commerce system, this process is extremely difficult because as fast as you can disconnect a user with the KILL statement, another user connects. SQL Server 2000 improves this process dramatically, enabling you to give users a specific amount of time to complete their transactions before you disconnect them automatically. You can also disconnect all connections from a database without any delay.

Microsoft expanded the ALTER DATABASE command to support the commands necessary to place your database in single-user mode. One method of placing a database in single-user mode is to check the Restrict Access check box, then select Single user on the database Options tab. Alternatively, you could use the ALTER DATABASE command:

ALTER DATABASE NorthwindSET SINGLE_USER

After you execute the command, SQL Server waits for all connections to the database to complete their transactions. Any users who try to connect while the database is in this state receive the error message that Figure 2 shows and are directed to their default database (typically the Master database). This error message means that the database is frozen until all users have disconnected. If the target server or the person issuing the command doesn't have the query timeout setting set, the client could wait indefinitely for the query to finish until all active connections are disconnected. In Query Analyzer, you can specify a query timeout setting in seconds under the Connections tab on the Options screen (select Tools, Options). Only the user who issued the ALTER DATABASE command can remain connected.

You can also disconnect any users who have open connections to the server by using the ROLLBACK IMMEDIATE command. You can't use Enterprise Manager with this command; you must use Query Analyzer to execute the following query:

ALTER DATABASE NorthwindSET SINGLE_USERWITH ROLLBACK IMMEDIATE

After you execute this command, SQL Server drops all connections and rolls back their transactions immediately. All users performing a transaction will receive a connection error and will be unable to reconnect to the database.

You can optionally specify a certain amount of time that SQL Server will give users to complete their transactions before it disconnects them. You use the ROLLBACK AFTER keywords as the following query shows:

ALTER DATABASE NorthwindSET SINGLE_USERWITH ROLLBACK AFTER 20 SECONDS

SQL Server will wait 20 seconds after you issue this command, then disconnect any connections and roll back their transactions. New users aren't allowed to connect during this process, which is a database-level version of pausing the server. Any new users trying to connect to the database during the 20 seconds will receive the error message that Figure 2 shows. If no users are connected at the time you execute the query, the database will go into single-user mode immediately.

The final setting new to Enterprise Manager's database Options tab is the compatibility level, which Figure 1 shows. To set the option, select the appropriate level in the Compatibility Level drop-down box. SQL Server 2000 is represented as 80, 7.0 as 70, and 6.5 as 65. The compatibility-level option determines how some database query actions will perform. Because SQL Server's relational engine has evolved, some query results might vary from release to release. To preserve backward compatibility for queries, Microsoft implemented compatibility levels at the database level in SQL Server 7.0. For example, if you ran the query

SELECT DATALENGTH('')

you would get two different results depending on whether you set your database's compatibility level for SQL Server 2000, 7.0, or 6.5. A SQL Server 2000 or 7.0 database would return a result of 0 because SQL Server 2000 and 7.0 treat empty strings as truly empty. In SQL Server 6.5 compatibility mode, a database would return a result of 1 because SQL Server 6.5 interprets an empty string as a single space. This compatibility-level setting exists in SQL Server 7.0 but is accessible only through the stored procedure sp_dbcmptlevel.

In SQL Server 2000, Microsoft made several substantial new server and database configuration changes. Don't take these and other SQL Server settings lightly: Even the smallest tweak could have a serious positive or negative performance impact. As you tweak settings in SQL Server, use a server-monitoring tool such as Performance Monitor to make sure your server experiences no performance erosion after the adjustments.