Changing Parameters by Using Enterprise Manager

3 4

You can modify the settings of many of the SQL Server parameters by using Enterprise Manager. To use Enterprise Manager to modify parameter settings, you first need to open the SQL Server Properties window. To do so, you right-click a server name in the Enterprise Manager window and choose Properties from the shortcut menu. Each of the following sections describes the options available on one of the tabs in the SQL Server Properties window.

General

The initial tab that you will see is the General tab, as shown in Figure A-1. The General tab does not change any underlying configuration parameters as the other tabs do. However, you can set several autostart parameters and startup parameters on this tab. (Click Startup Parameters to change the settings for the startup parameters.)

Figure A-1. The General tab of the SQL Server Properties window.

Memory

The Memory tab is shown in Figure A-2. On this tab, you can choose whether SQL Server should allocate memory dynamically or should use a fixed amount of memory. To make a setting, click the appropriate option button and move the appropriate sliders. On the Memory tab, you can also set the minimum amount of memory SQL Server will use to execute a query.

Figure A-2. The Memory tab of the SQL Server Properties window.

The configuration parameters that are set on the Memory tab are:

• min server memory
• max server memory
• min memory per query

Processor

The Processor tab is shown in Figure A-3. On this tab, you can select the CPUs that are available for SQL Server to use. You can set the number of worker threads, and you can boost SQL Server's priority and specify that Windows fibers should be used by selecting the appropriate check boxes. Finally you can specify how SQL Server parallelizes queries.

Figure A-3. The Processor tab of the SQL Server Properties window.

The parameters that are set on the Processor tab are:

• affinity mask
• max worker threads
• priority boost
• lightweight pooling
• max degree of parallelism
• cost threshold for parallelism

Security

The Security tab is shown in Figure A-4. You cannot set any tuning parameters on this tab. Instead, you can set the authentication method, the audit level, and the startup and run Windows accounts.

Figure A-4. The Security tab of the SQL Server Properties window.

Connections

The Connections tab is shown in Figure A-5. This tab provides a number of options related to user connections. Here you can set the maximum number of user connections allowed and the level of constraints checking. In the Remote Server Connections area, you can choose whether RPC connections should be allowed and whether distributed transactions should be enforced, and you can set the query time-out.

Figure A-5. The Connections tab of the SQL Server Properties window.

The parameters that are set on the Connections tab are:

• user connections
• user options
• remote access
• remote query timeout
• remote proc trans

Server Settings

The Server Settings tab is shown in Figure A-6. This tab gives you the option of setting general server parameters. Here the default language for the user can be set. You can also set options to allow catalog modification, to allow triggers to fire triggers, and to enable the use of the query governor. You can also set the SQL mail profile and year 2000 support.

Figure A-6. The Server Settings tab of the SQL Server Properties window.

The parameters that are set on the Server Settings tab are used to set the following parameters:

• default language
• allow updates
• nested triggers
• query governor cost limit
• two digit year cutoff

Database Settings

The Database Settings tab is shown in Figure A-7. This tab gives you the option of setting the database fill factor as well as backup parameters and the recovery interval. In addition the default file location can be specified here. This location will be used for creation of new databases, when not specified in the create database command.

Figure A-7. The Database Settings tab of the SQL Server Properties window.

The parameters that are set on the Database Settings tab are:

• fill factor
• media retention
• recovery interval

Replication

The Replication tab is shown in Figure A-8. The Replication tab is used to view and set replication publishing and distribution options. Here you can also add the current system to the replication monitoring group. This allows your system to be grouped with other systems when replication is monitored.

Figure A-8. The Replication tab of the SQL Server Properties window.

Active Directory

The Active Directory tab—shown in Figure A-9—is used to modify Active Directory entries. You can add the current system to Active Directory, refresh its status in Active Directory, or delete this system from Active Directory.

Figure A-9. The Active Directory tab of the SQL Server Properties window.