Server Configuration

Many of the most common server configuration options are made available through EM. More advanced configuration options are available through the SP_CONFIGURE system-stored procedure.

CAUTION

Changing server-level settings affects all databases on the server and can adversely affect performance. When changing configuration options, consider the effect the change might have and carefully document your changes so they can be undone if required.

Server Options

EM server configuration options include memory and processor usage, connection options, default database settings, network library, and startup options. To access the server options, right-click a server in EM and select Properties. This will bring up the Server Properties page, as illustrated in Figure 4.4.

Figure 4.4. The Server Properties page.

Some information on each option can be accessed through the Help button on each page, but for a detailed examination of setting server configuration options, see Chapter 40, "Configuring, Tuning, and Optimizing SQL Server Options."

Security Options

The Server Properties page is also where security options for the server are set. Select the Security tab and you can switch the security mode between SQL Server and Windows or Windows Only, set an auditing level, and, if you are using EM from the Server console, you can change the account under which SQL Server runs.

Changing the Configuration

When changing server-level settings, including the Security options, you should be aware that the settings might require stopping and restarting the SQL Server service to take effect. Make sure you time your changes for the least possible disruption of service, and document them carefully. Server options that don't take effect until the service is restarted have radio buttons to select either the currently configured setting (not yet in effect) or the running value (the value to which the option was set when the service was last started). Figure 4.5 shows the configured value for memory usage.

Figure 4.5. Displaying the configured value for memory usage.