Configure SQL Server 2000 options

The configuration options for SQL Server servers are those that people understand less and are often misused. When a technical support person asks you to adjust an option in some way, and another technical support person asks you to adjust the same option in a completely different way, you may be puzzled by the true meaning of these options. The information about these options is scarce, at least not detailed and clear. In SQL Server 2000, Microsoft reduced several configuration options and let SQL Server configure them dynamically, reducing several confusing areas. At the same time, Microsoft has added two additional server configuration options for SQL Server 2000 and has adjusted some database options to simplify the work of the database administrator.

New server Options

Just like accessing the properties of most enterprise servers, we cannot access SQL Server 2000 's new two server options through the Server Properties window in SQL Server Enterprise Manager. Microsoft did not place these advanced configuration options into Enterprise Manager as a security measure to prevent users from accidentally configuring the server for carelessness. Instead, we must use the t-sql/sp_configure system stored procedures to access these advanced options. We can view the current configuration of the server using a method that runs sp_configure without parameters. In the execution result, Config_value is the data that SQL Server extracts from the master database syscurconfigs table, which shows the current configuration of the server; run_value column shows SQL when executing sp_configure The options that the server is using, which SQL Server stores in the Sysconfigures table. After modifying an option, we must execute the RECONFIGURE command (and in most cases, restart SQL Server) for the new run_value to be displayed. All of the options discussed in this article require that you restart SQL Server.

There are 36 server options, by default, the sp_configure stored procedure displays only 10 of them, the results do not contain advanced options, and none of the new SQL Server configuration options appear in this streamlined list. However, we can use the show advanced Options command parameter to let SQL Server display all the options. To enable show advanced options, we use the following command format:

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

To install an option, we must run the reconfigure command after using sp_configure to configure the server. The output of the above command is as follows:

Configuration option ' Show advanced options ' changed from 0 to 1. Run the reconfigure command to install.

Once you can view the advanced options, we can see two new server options. One of the most important new options is the AWE enabled option, which enables the SQL Server Enterprise Edition to improve the server's memory access capabilities. By default, the maximum RAM that SQL Server can use is 3GB. On Windows 2000, applications can access more RAM using the Address Windowing Extensions (AWE) API. For example, in Windows Advanced Server, we can use up to 8GB of memory, and only Windows Datacenter Server supports 64GB memory to exceed it. Obviously, when SQL Server has more available memory, it will be able to buffer more data and improve the response time of the query.

However, enabling the AWE enabled option also has side effects. After enabling the AWE enabled option, SQL Server no longer allocates memory dynamically. Due to the lack of dynamic memory allocation, the management burden increases as we have to carefully monitor RAM usage. In addition, after setting the awe enabled option, we must also set the max server memory option. If we do not set the max server memory option, server RAM is equal to the minimum requirement 3gb,sql server will occupy almost all RAM on the machine at startup, leaving only 128 MB of RAM for Windows and other applications. By setting the max server memory option, we can limit the amount of memory that SQL Server uses.

The awe enabled option can only be used on SQL Server Enterprise Edition, and the operating system must be Windows Advance Server or Datacenter. If you use this option on other versions of SQL Server (or if the operating system is Winnt), SQL Server ignores this option. Under some server configuration combinations, improperly configuring this option will result in unpredictable results. For example, if we set this option on the Windows 98 operating system, on a machine running SQL Server Personal Edition, SQL Server may report that it has stopped (even when it is running), and it will refuse to stop SQL Server instance.

Enabling AWE on a SQL Server Enterprise Edition Server consists of three steps. First, we must make sure that the account that starts the instance of SQL Server has permission to lock the page in memory. Automatically grant page lockout permissions to the Windows account that we specify to start the SQL Server service when SQL Server installs; However, if the account has changed since then, you should check which permissions have been granted to the user who started SQL Server. Check the permissions of the account to use the Windows 2000 Group Policy tool. The second step is to run the sp_configure stored procedure and set the AWE enabled option to 1. Then, we have to perform reconfigure and manually restart SQL Server. The syntax for configuring commands is:

EXEC sp_configure 'awe enabled', '1' RECONFIGURE

Note that on Windows 2000 or NT, if you want to access more than 4GB of physical memory, we must take some other steps, that is, to modify the Boot.ini file and add the/PAE option.

The second new SQL Server 2000 option is used to enable the C2 level security audit mode. C2 is a government security rating that ensures that the system protects resources and has sufficient auditing capabilities. The C2 mode allows us to monitor all access attempts to all database entities. The commands for enabling SQL Server's C2 auditing feature are as follows:

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