Following is a summary of some of the best practices from the chapter:
- Leverage the scripting utility within SQL Server Management Studio to transform administration tasks into TSQL code.
- Unless there is a specific need to do otherwise, it is a best practice to allow SQL Server to dynamically manage the minimum and maximum amount of memory allocated to SQL Server. However, if multiple applications are running on SQL Server, it is recommended to specify minimum and maximum values for SQL Server memory. Therefore, the application cannot starve SQL Server by depriving it of memory.
- The preferred authentication mode is Windows Authentication over SQL Server Authentication because it provides a more robust authorization mechanism.
- Configuring SQL auditing is recommended to capture both failed and successful logins.
- Do not set the database to automatically shrink on a regular basis because this leads to performance degradation and excessive fragmentation over time.
- The first Database Engine administration task after a successful SQL installation should involve tuning and configuring the server properties.
- Configure the recovery model for each database accordingly and implement a backup and restore strategy. This should also include the system databases.
- Database files, transaction log files, and operating system files should be located on separate volumes for performance and availability.
- When multiple database files and transaction log files exist, organize them through the use of filegroups.
- Create basic reports in Management Studio to better understand the SQL Server environment.
- Automate administration tasks by using SQL Server 2005 Agent jobs.