According to book "Microsoft SQL Server 2008 Management and Administration":
■ Leverage the scripting utility within SQL Server Management Studio to transform administration tasks into Transact-SQL syntax.
■ 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. Then 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.
■ Use Change Tracking to ensure accountability and compliance on databases and database objects.
■ Leverage the compression technologies to reduce storage utilization.
■ 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 2008 Agent jobs.
■ Review the other break-out chapters in the book for more information on items such as hardening a SQL Server infrastructure, encryption, Policy Based Management, Resource Governor, backups, and maintenance plans.
