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' changed
from 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 \MSSQL\Data 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.
Prev. page
[1]
2
next page 
