SQL Server 2000 Configuration
11. Configuration
Configuration in most organisations is best left to an experienced Database Administrator as there are many ramifications from setting each option. These notes are not intended as hints for configuration of a production database but provide some hints for initial configuration problems experienced when first starting with SQL Server in a development environment.
Server Configuration
Various configuration parameters may be set in the SQL Enterprise manager by selecting the required server and rightclicking to select the CONFIGURE window:
Server Configuration
Memory
The memory specified for SQL Server can be determined by using the DBCC MEMUSAGE command in the ISQL window.
DBCC MEMUSAGE
Do not specify too much memory because NT still needs to run.
Lock Escalation Percentage
Lock escalation determines the number of shared pages locks issued before the server upgrades to a table lock. The lock escalation percentage is a useful initial property to set if there are problems with locking to escalate when a certain percentage of pages in the table are locked rather than an absolute number.
Network Packet Size
The default packet size is 4096 and may require changing in specific circumstances.
This is a connection property which can be specified by the ODBC driver.
Open Databases
The default maximum number of database for simultaneous connection is 12.
User Connections
The default user connections is 15. Some applications may use more than one connection and this is a common problem initially. Take care to minimise the connections used by the application because each connection specified in the configuration uses about 40K of memory.
Database Configuration
Size of tempdb
The tempdb database is used by SQL Server for all worktables. The default size is only 2MB and this will need to be enlarged before any sensible workload even for a single user can be performed.
Truncate Log on Checkpoint
Development databases do not often need a transaction log as there are too many changes made for a backup policy to be effective. The transaction log may fill up if many records are changed or uploaded and specifying the Truncate Log option on the database will stop SQL Server from creating a large Log file as it will be automatically truncated at every opportunity.
Truncate Log on Checkpoint
Deleting a Database
The corresponding .DAT file is not deleted when a database is dropped. Use the operating system to delete this file but take care.
Backups
Create initial backups of the MASTER, MODEL, and MSDB databases for recovery purposes as well as the development databases.
The MASTER database contains changes to database configurations and system tables and should be backed up after any changes to the database metadata.
The NT registry should also be backed up to help with a disaster situation.