This chapter is from the book
This chapter is from the book
This chapter is from the book
Administering SQL Server 2005 Server Properties
The SQL Server Properties dialog box is the main place you, as database administrator, configure server settings specifically tailored toward a SQL Server Database Engine installation.
You can invoke the Server Properties for the Database Engine by following these steps:
- Choose Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.
- Connect to the Database Engine in Object Explorer.
- Right-click SQL Server and then select Properties.
The Server Properties dialog box includes eight pages of Database Engine settings that can be viewed, managed, and configured. The eight Server Properties pages include
- General
- Memory
- Processors
- Security
- Connections
- Database Settings
- Advanced
- Permissions
The following sections provide examples and explanations for each page within the SQL Server Properties dialog box.
Administering the General Page
The first Server Properties page, General, includes mostly informational facts pertaining to the SQL Server 2005 installation, as illustrated in Figure 1.1. Here, you can view the following items: SQL Server Name; Product Version such as Standard, Enterprise, or 64 Bit; Windows Platform such as Windows 2000 or Windows 2003; SQL Server Version Number; Language Settings; Total Memory in the Server; Number of Processors; Root Directory; Server Collation; and whether the installation is clustered.
)
Figure 1.1 Administering the Server Properties General page.
Administering the Memory Page
Memory is the second page within the Server Properties dialog box. As shown in Figure 1.2, this page is broken into two sections: Server Memory Options and Other Memory Options. Each section has additional items to configure to manage memory; they are described in the following sections.
)
Figure 1.2 Administering the Server Properties Memory page.
Administering the Server Memory Options
The Server Memory options are
- Use AWE to Allocate Memory—If this setting is selected, the SQL Server installation leverages Address Windowing Extensions (AWE) memory.
- Minimum and Maximum Memory—The next items within Memory Options are for inputting the minimum and maximum amount of memory allocated to a SQL Server instance. The memory settings inputted are calculated in megabytes.
The following Transact-SQL (TSQL) code can be used to configure Server Memory Options:
sp_configure 'awe enabled', 1 RECONFIGURE GO sp_configure 'min server memory', "MIN AMOUNT IN MB" RECONFIGURE GO sp_configure 'max server memory', "MAX AMOUNT IN MB" RECONFIGURE GO
Other Memory Options
The second section, Other Memory Options, has two memory settings tailored toward index creation and minimum memory per query:
- Index Creation Memory—This setting allocates the amount of memory that should be used during index creation operations.
- Minimum Memory Per Query—This setting specifies the minimum amount of memory in kilobytes that should be allocated to a query.
Use the following TSQL statements to configure Other Memory Options:
sp_configure 'index create memory, "NUMBER IN KB" RECONFIGURE GO sp_configure 'min memory per query, "NUMBER IN KB" RECONFIGURE GO
Administering the Processors Page
The Processor page, shown in Figure 1.3, should be used to administer or manage any processor-related options for the SQL Server 2005 Database Engine. Options include threads, processor performance, affinity, and parallel or symmetric processing.
)
Figure 1.3 Administering the Server Properties Processor page.
Enabling Processors
Similar to a database administrator, the operating system is constantly multitasking. Therefore, the operating system moves threads between different processors to maximize processing efficiency. You should use the Processor page to administer or manage any processor-related options such as parallel or symmetric processing. The processor options include
- Enable Processors—The two processor options within this section include Processor Affinity and I/O Affinity. Processor Affinity allows SQL Server to manage the processors; therefore, processors are assigned to specific threads during execution. Similar to Processor Affinity, the I/O Affinity setting informs SQL Server on which processors can manage I/O disk operations.
- Automatically Set Processor Affinity Mask for All Processors—If this option is enabled, SQL Server dynamically manages the Processor Affinity Mask and overwrites the existing Affinity Mask settings.
- Automatically Set I/O Affinity Mask for All Processors—Same thing as the preceding option: If this option is enabled, SQL Server dynamically manages the I/O Affinity Mask and overwrites the existing Affinity Mask settings.
Threads
The following Threads items can be individually managed to assist processor performance:
- Maximum Worker Threads—The Maximum Worker Threads setting governs the optimization of SQL Server performance by controlling thread pooling. Typically, this setting is adjusted for a server hosting many client connections. By default, this value is set to 0. The 0 value represents dynamic configuration because SQL server determines the number of worker threads to utilize. If this setting will be statically managed, a higher value is recommended for a busy server with a high number of connections. Subsequently, a lower number is recommended for a server that is not being heavily utilized and has a small number of user connections. The values to be entered range from 10 to 32,767.
- Boost SQL Server Priority—Preferably, SQL Server should be the only application running on the server; thus, it is recommended to enable this check box. This setting tags the SQL Server threads with a higher priority value of 13 instead of the default 7 for better performance. If other applications are running on the server, performance of those applications could degrade if this option is enabled because those threads have a lower priority.
- Use Windows Fibers (Lightweight Pooling)—This setting offers a means of decreasing the system overhead associated with extreme context switching seen in symmetric multiprocessing environments. Enabling this option provides better throughput by executing the context switching inline.
These TSQL statements should be used to set processor settings:
sp_configure 'affinity mask', "VALUE"; RECONFIGURE; GO sp_configure 'affinity 1/0 mask', :"VALUE"; RECONFIGURE; GO sp_configure 'lightweight pooling', "0 or 1"; RECONFIGURE; GO sp_configure 'max worker threads', :"INTEGER VALUE"; RECONFIGURE; GO sp_configure 'priority boost', "0 or 1"; RECONFIGURE; GO
Administering the Security Page
The Security page, shown in Figure 1.4, maintains server-wide security configuration settings. These SQL Server settings include Server Authentication, Login Auditing, Server Proxy Account, and Options.
)
Figure 1.4 Administering the Server Properties Security page.
Server Authentication
The first section in the Security page focuses on server authentication. At present, SQL Server 2005 continues to support two modes for validating connections and authenticating access to database resources: Windows Authentication Mode and SQL Server and Windows Authentication Mode. Both of these authentication methods provide access to SQL Server and its resources.
The Windows Authentication Mode setting is the default Authentication setting and is the recommended authentication mode. It tactfully leverages Active Directory user accounts or groups when granting access to SQL Server. In this mode, you are given the opportunity to grant domain or local server users access to the database server without creating and managing a separate SQL Server account. Also worth mentioning, when Windows Authentication mode is active, user accounts are subject to enterprise-wide policies enforced by the Active Directory domain, such as complex passwords, password history, account lockouts, minimum password length, maximum password length, and the Kerberos protocol. These enhanced and well-defined policies are always a plus to have in place.
The second Authentication Option is SQL Server and Windows Authentication (Mixed) Mode. This setting, which is regularly referred to as mixed mode authentication, uses either Active Directory user accounts or SQL Server accounts when validating access to SQL Server. SQL Server 2005 has introduced a means to enforce password and lockout policies for SQL Server login accounts when using SQL Server Authentication. The new SQL Server polices that can be enforced include password complexity, password expiration, and account lockouts. This functionality was not available in SQL Server 2000 and was a major security concern for most organizations and database administrators. Essentially, this security concern played a role in helping define Windows authentication as the recommended practice for managing authentication in the past. Today, SQL Server and Windows Authentication mode may be able to successfully compete with Windows Authentication mode.
Login Auditing
Login Auditing is the focal point on the second section on the Security page. You can choose from one of the four Login Auditing options available: None, Failed Logins Only, Successful Logins Only, and Both Failed and Successful Logins.
Server Proxy Account
You can enable a server proxy account in the Server Proxy section of the Security page. The proxy account permits the security context to execute operating system commands by the impersonation of logins, server roles, and database roles. If you're using a proxy account, you should configure the account with the least number of privileges to perform the task. This bolsters security and reduces the amount of damage if the account is compromised.
Additional Security Options
Additional security options available in the Options section of the Security page are
- Enable Common Criteria Compliance—When this setting is enabled, it manages database security. Specifically, it manages features such as Residual Information Protection (RIP), controls access to login statistics, and enforces restrictions where, for example, the column titled GRANT cannot override the table titled DENY.
- Enable C2 Audit Tracing—When this setting is enabled, SQL Server allows the largest number of the success and failure objects to be audited. The drawback to capturing for audit data is that it can degrade performance and take up disk space.
- Cross Database Ownership Chaining—Enabling this setting allows cross database ownership chaining at a global level for all databases. Cross database ownership chaining governs whether the database can be accessed by external resources. As a result, this setting should be enabled only when the situation is closely managed because several serious security holes would be opened.
Administering the Connections Page
The Connections page, as shown in Figure 1.5, is the place where you examine and configure any SQL Server settings relevant to connections. The Connections page is broken up into two sections: Connections and Remote Server Connections.
)
Figure 1.5 Administering the Server Properties Connections page.
Connections
The Connections section includes the following settings:
- Maximum Number of Concurrent Connections—The first setting determines the maximum number of concurrent connections allowed to the SQL Server Database Engine. The default value is 0, which represents an unlimited number of connections. The value used when configuring this setting is really dictated by the SQL Server hardware such as the processor, RAM, and disk speed.
- Use Query Governor to Prevent Long-Running Queries—This setting creates a stipulation based on an upper limit criteria specified on the time period in which a query can run.
- Default Connection Options—For the final setting, you can choose from approximately 16 advanced connection options that can be either enabled or disabled, as shown in Figure 1.5.
Remote Server Connections
The second section located on the Connections page focuses on Remote Server settings:
- Allow Remote Connections to This Server—If enabled, the first option allows remote connections to the specified SQL Server.
- Remote Query Timeout—The second setting is available only if Allow Remote Connections is enabled. This setting governs how long it will take for a remote query to terminate. The values that can be configured range from 0 to 2,147,483,647. Zero represents infinite.
- Require Distributed Transactions for Server-to-Server Communication—The final setting controls the behavior and protects the transactions between systems by using the Microsoft Distributed Transaction Coordinate (MS DTC).
Administering the Database Settings Page
The Database Settings page, shown in Figure 1.6, contains configuration settings that each database within the SQL Server instance will inherit. The choices available on this page are broken out by Fill Factor, Backup and Restore, Recovery, and Database Default Locations.
)
Figure 1.6 Administering the Server Properties Database Settings page.
Default Index Fill Factor
The Default Index Fill Factor specifies how full SQL Server should configure each page when a new index is created. The default setting is 0, and the ranges are between 0 and 100. The 0 value represents a table with room for growth, whereas a value of 100 represents no space for subsequent insertions without requiring page splits. A table with all reads typically has a higher fill factor, and a table that is meant for heavy inserts typically has a low fill factor. The value 50 is ideal when a table has plenty of reads and writes. This setting is global to all tables within the Database Engine.
For more information on fill factors, refer to Chapter 8, "SQL Server 2005 Maintenance Practices" and Chapter 9, "Managing and Optimizing SQL Server 2005 Indexes."
Backup and Restore
The Backup and Restore section of the Database Settings page includes
- Specify How Long SQL Server Will Wait for a New Tape—The first setting governs the time interval SQL Server will wait for a new tape during a database backup process. The options available are Wait Indefinitely, Try Once, or Try for a specific number of minutes.
- Default Backup Media Retention—This setting is a system-wide configuration that affects all database backups, including the translation logs. You enter values for this setting in days, and it dictates the time to maintain and/or retain each backup medium.
Recovery
The Recovery section of the Database Settings page consists of
- Recovery Interval (Minutes)—Only one Recovery setting is available. This setting influences the amount of time, in minutes, SQL Server will take to recover a database. Recovering a database takes place every time SQL Server is started. Uncommitted transactions are either committed or rolled back.
Database Default Locations
Options available in the Database Default Locations section are
- Data and Logs—The two folder paths for Data and Log placement specify the default location for all database data and log files. Click the ellipses on the right side to change the default folder location.
Administering the Advanced Page
The Advanced Page, shown in Figure 1.7, contains the SQL Server general settings that can be configured.
)
Figure 1.7 Administering the Server Properties Advanced Settings page.
Miscellaneous Settings
Options available on the Miscellaneous section of the Advanced page are
- Allow Triggers to Fire Others—If this setting is configured to True, triggers can execute other triggers. In addition, the nesting level can be up to 32 levels. The values are either True or False.
- Cursor Threshold—This setting dictates the number of rows in the cursor that will be returned for a result set. A value of 0 represents that cursor keysets are generated asynchronously.
- Default Full-Text Language—This setting specifies the language to be used for full-text columns. The default language is based on the language specified during the SQL Server instance installation.
- Default Language—This setting is also inherited based on the language used during the installation of SQL. The setting controls the default language behavior for new logins.
- Max Text Replication Size—This global setting dictates the maximum size of text and image data that can be inserted into columns. The measurement is in bytes.
- Scan for Startup Procs—The configuration values are either True or False. If the setting is configured to True, SQL Server allows stored procedures that are configured to run at startup to fire.
- Two Digit Year Cutoff—This setting indicates the uppermost year that can be specified as a two-digit year. Additional years must be entered as a four digits.
Network Settings
Options available on the Network section of the Advanced page are
- Network Packet Size—This setting dictates the size of packets being transmitted over the network. The default size is 4096 bytes and is sufficient for most SQL Server network operations.
- Remote Login Timeout—This setting determines the amount of time SQL Server will wait before timing out a remote login. The default time is 30 seconds, and a value of 0 represents an infinite wait before timing out.
Parallelism Settings
Options available on the Parallelism section of the Advanced page are
- Cost Threshold for Parallelism—This setting specifies the threshold above which SQL Server creates and runs parallel plans for queries. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. Set this option only on symmetric multiprocessors. For more information, search for "cost threshold for parallelism option" in SQL Server Books Online.
- Locks—The default for this setting is 0, which indicates that SQL Server is dynamically managing locking. Otherwise, you can enter a numeric value that sets the utmost number of locks to occur.
- Max Degree of Parallelism—This setting limits the number of processors (up to a maximum of 64) that can be used in a parallel plan execution. The default value of 0 uses all available processors, whereas a value of 1 suppresses parallel plan generation altogether. A number greater than 1 prevents the maximum number of processors from being used by a single query execution. If a value greater than the number of available processors is specified, however, the actual number of available processors is used. For more information, search for "max degree of parallelism option" in SQL Server Books Online.
- Query Wait—This setting indicates the time in seconds a query will wait for resources before timing out.
Administering the Permissions Page
The Permissions Page, as shown in Figure 1.8, includes all the authorization logins and permissions for the SQL Server instance. You can create and manage logins and/or roles within the first section. The second portion of this page displays the Explicit permission based on the login or role.
)
Figure 1.8 Administering the Server Properties Permissions page.
For more information on permissions and authorization to the SQL Server 2005 Database Engine, refer to Chapter 13, "Administering SQL Server Security."