Configure SQL Server 2000 options

Server

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

(The Windows operating system must also provide appropriate support for full C2-level security) after enabling C2 audit mode and restarting, SQL Server automatically creates a trace file under the \MSSQL\Data directory. We can use SQL Server Profiler to view the trace files for these monitoring server activities.

SQL Server writes data to the trace file in blocks of 128KB size. Therefore, when SQL Server stops abnormally, we may lose up to 128 KB of log data. As you can imagine, the log file that contains the audit information will grow at a very fast rate. For example, one trial visited only three tables, and the trace file was over 1MB. When the trace file exceeds 200MB, the C2 audit closes the old file and creates a new file. Every time SQL Server starts, it creates a new trace file. If there is not enough disk space, SQL Server will stop running until we release enough disk space for the audit log and restart the SQL Server instance. When SQL Server starts, we can disable auditing with the-f argument.

Reduced server Options
In SQL Server 2000, Microsoft has reduced several of its existing options, allowing SQL Server 2000 to automatically configure these options. The most notable of the reduced options is the max async IO option. This option allows the database administrator to specify how many asynchronous disk reads and writes can occur on a single database file. The max async IO option in SQL Server 7.0 is one of the least known options, with a default value of 32, but few administrators can adjust the value. In SQL Server 2000, this asynchronous IO option is dynamically rising or falling with the feedback information received by SQL Server, and SQL Server uses the feedback algorithm to determine the server load and the number of SQL Server system controls.

Database options
In SQL Server 2000, if you have ever viewed the Options tab of a database in Enterprise Manager, you may be puzzled by the disappearance of some common options (to access the Options tab, in the Enterprise Right-click the database in Manager and select Properties). Trunc is reduced in the Options tab. The Log on chkpt and select Into/bulk Copy are two options, as shown in Figure 1. For clarity and backward compatibility, these generic options are now referred to as the recovery Model (recovery models) option. If you are using SQL Server 2000 's Enterprise Manager to connect to the SQL Server 7.0 database, we can still see these old options. Previously, we used the following command to open trunc for the Northwind database. Log on chkpt. Options:

sp_dboption Northwind, ' trunc. Log on chkpt. ', True

After setting the options, we can check the Options tab or the following query to see if these option settings are successful on the Northwind database:

SELECT databaseproperty (' Northwind ', ' Istrunclog ')

A result of 1 indicates that the option is set to true, and a result of 0 indicates that the option is set to false. If the result is null, it indicates that we have either chosen an incorrect option or that the database does not exist.

For ease of use, Microsoft Trunc. The log on chkpt. and select Into/bulk copy options are replaced with recovery model settings. The purpose of this option change is to ensure that the database administrator is fully aware of the implications of recovering model options in a disaster recovery strategy. SQL Server 2000 provides us with three types of database recovery models: simple (easy recovery), full (complete recovery), bulk_logged (bulk-logged recovery).

The simple recovery model is the easiest to operate, but it is the most inflexible disaster recovery strategy. Choosing a simple recovery model is equivalent to putting the trunc. Log on chkpt. Set to True. Under this recovery model, we can only perform full and differential backups (differential backup): This is because the transaction log is always truncated and the transaction log backup is not available. Generally, for a system that contains critical data, we should not choose a simple recovery model because it does not help us restore the system to the point of failure. When using this recovery model, we can only restore the system to the last successful full and differential backup status. To recover, we first restore the last successful full backup and then restore the differential backup (the differential backup can only apply changes to the database since the last full backup of the database).

The full recovery model puts the trunc. Log on chkpt. Options and select Into/bulk copy options are set to false. Full recovery has the ability to restore a database to a point of failure or to a specific point in time. This model is ideal for protecting environments that contain critical data, but it increases the cost of equipment and management because, if database access is frequent, the system will quickly generate large transaction log records. Because the Select Into/bulk copy is set in this model, False,sql server records all events that include bulk data loading.

The last recovery model is bulk-logged recovery, which trunc. Log on chkpt. Set to False to set select Into/bulk copy to True. In the bulk-logged recovery model, the data loss of bulk copy operations is more severe than the full recovery model. The full recovery model records the full log of bulk copy operations, but under the bulk-logged recovery model, SQL Server records only the minimum logs for these operations and cannot control them individually. In the bulk-logged recovery model, data file corruption may result in the requirement to manually redo the work. The following table compares the characteristics of three types of recovery models.

Recovery model benefits will the performance of the work loss be restored to the immediate point?
Simple allows high performance bulk copy operations.
Reclaim log space, making space requirements minimal. Changes that have occurred since the most recent database or differential backup must be redo. Can revert to the end of any backup. You must then redo the changes.
The loss or corruption of a full data file does not result in work losses.
Can revert to any point in time (for example, before an application or user error). Not normally.
If the log is corrupted, you must redo the changes that occurred since the most recent log backup. Can revert to any point in time.
Bulk-Logged logging allows high-performance bulk copy operations.
The bulk operation uses minimal log space. If the log is corrupted, or if a bulk operation has occurred since the most recent log backup, you must redo the changes since the last backup. Otherwise do not lose any work. Can revert to the end of any backup. You must then redo the changes.

In the Options tab of the database, we can choose simple to change the recovery model to a simpler model from the Model Drop-down list box. In addition, Microsoft has extended the ALTER DATABASE command, which we can use to set database properties. For example, use the following T-SQL command to set the recovery model to a full recovery model:

ALTER DATABASE Northwind SET RECOVERY full

SQL Server 2000 provides a number of options for moving a database into single-user mode, all of which are the most intriguing hidden options. To correct corruption or other data problems, database administrators often have to transfer the database to Single-user mode. When the database is in this mode, other users will no longer be able to access the data, allowing the administrator to fix the data problem before the user accesses the corrupted data. In SQL Server 7.0, before transferring the database to Single-user mode, we must ensure that all users are disconnected. For a high-speed OLTP database system, such as an e-commerce system, disconnecting all users is difficult because other users will also connect to the database when we disconnect a user. SQL Server 2000 has greatly improved this process, and we can give users a specified time to complete their transactions and then automatically disconnect their connections by SQL Server. In addition, we can disconnect all connections without providing any delay time.

One way to transfer a database to Single-user mode is to select the Restrict Access check box on the Options tab of the database and select Single user. In addition, Microsoft has extended the ALTER DATABASE command to enable it to transfer the database to Single-user mode, as follows:

ALTER DATABASE Northwind SET single_user

After executing this command, SQL Server waits for all the database connections to complete their respective transactions. In this state, all users requesting a connection to the database receive the error message shown in Figure 2 and are redirected to their respective default database (usually the master database). The error message in Figure 2 means that the database is frozen until all users are disconnected. If the target server or the user who issued the command does not have the query timeout parameter set, the client may wait indefinitely for the query to complete until all the connections are disconnected. In Query Analyzer, we can specify the number of timeout seconds in the Connections tab of the Options screen (select Menu tools,options). In Single-user mode, only users who issue ALTER DATABASE commands can remain connected.
In addition, we can disconnect all users who open the database connection using the rollback immediate command. But we can't use this command in Enterprise Manager, but we should do it with Query Analyzer, for example:

ALTER DATABASE Northwind SET single_user with ROLLBACK IMMEDIATE

After this command is executed, SQL Server immediately disconnects all connections and returns their transactions. All users who are executing transactions receive a connection error and they are no longer able to connect to the database.

We can specify a time option for SQL Server to wait for the user to complete their transaction before disconnecting the user. This option is optional and is specified with rollback after keywords, as shown in the following command:

ALTER DATABASE Northwind SET single_user with ROLLBACK SECONDS

After this command is executed, SQL Server waits 20 seconds, then disconnects all connections and returns their transactions. In this process, SQL Server no longer accepts new connection requests, it is a database-level server pause. Within this 20 seconds, all new users attempting to connect to the database will receive the error message shown in Figure 2. If there are no connected users when the command is executed, the database will be transferred to Single-user mode immediately.

Enterprise Manager's Database Options tab, the last new configuration option is the compatibility level, as shown in Figure 1. To set this option, we simply select an appropriate compatibility level from the Compatibility Levels dropdown box. In this dropdown box, SQL Server 2000 is represented by 80, 7.0 is represented by 70, and 6.5 is represented by 65. The compatibility level option determines how some database query operations are executed. Because the relational engine of SQL Server is evolving, the results of some queries may differ between versions. For example, if we execute the following query:

SELECT datalength (")

Depending on the compatibility level we set for the database is SQL Server 2000, 7.0, or 6.5, the above query may get two different results. For SQL Server 2000 or 7.0 databases, the return result is 0 because SQL Server 2000 and 7.0 treat the empty string as true null; in SQL Server 6.5 compatibility mode, the result is 1 because SQL Server 6.5 treats the empty string as a space. SQL Server 7.0 also has this compatibility level option, but it can only be accessed through sp_dbcmptlevel stored procedures.

To sum up, Microsoft has made several substantial changes to the server and database configuration methods in SQL Server 2000. Don't underestimate all the options discussed in this article and other SQL Server configuration options--even if it's a minor change, it can have a significant positive or negative impact on performance. When you adjust the configuration options for SQL Server, you may want to use server monitoring tools such as performance Monitor to ensure that option tuning does not adversely affect server performance.