SQL Server 2008 best practices for hardening a SQL Server environment:

SQL Server 2008 best practices for hardening a SQL Server environment:

■ When the SQL Server installation is complete, harden the SQL Server environment.

■ Install the most recent critical fixes and service packs for both Windows and SQL Server.

■ When you’re selecting authentication modes, Windows Authentication is a more secure choice; however, if mixed mode authentication is required, leverage complex passwords and SQL Server 2008 password and lockout policies to further bolster security.

■ Do not use the SA account for day-to-day administration, logging on to the server remotely, or having applications use it to connect to SQL. It is best if the SA account is disabled and renamed.

■ Create a role-based security policy with the Security Configuration Wizard tool.

■ After SQL Server 2008 is installed, run the SQL Server Configuration Manager tool to disable unnecessary features and services and create policies with Policy Based Management.

■ Install only required components when installing SQL Server.

■ After the server has been hardened, periodically asses the server’s security using the MBSA and SQL Server BPA.

■ For production SQL Servers running mission-critical databases, either hide the instance or disable the SQL Server Browser service.

■ Change the default ports associated with the SQL Server installation to put off hackers from port-scanning the server.

■ Enable a firewall to filter unnecessary and unknown traffic.

■ At the very least, set security auditing to failed login attempts; otherwise, both failed and successful logins should be captured and monitored.

■ If upgrading previous versions of SQL Server, remove the BUILTIN/Administrators group from the SQL Server Logins.

Note from book "Microsoft SQL Server 2008 Management and Administration".

Best practices for backing up and restoring SQL Server 2008

Best practices for backing up and restoring SQL Server 2008:

■ Define and document an SLA relevant to the SQL Server 2008 environment.

■ Test the backup and recovery plan on a periodic basis and also before production to validate that it is operational and the SLA can be met.

■ Select the appropriate recovery model for all systems and user databases. Use the Full recovery model for mission-critical databases that need to be restored to the point of failure in the event of a disaster.

■ Isolate database and transaction log files on separate spindles for recovery purposes.

■ Save the backups locally on a redundant disk drive separate from the online databases and back up to tape on a regular basis.

■ If database or transaction log backups are stored locally on disk, do not store them on the same volumes as the database and transaction log files. If a drive or volume failure occurs, both the files and backups could be lost.

■ For retention and recovery purposes, the backups stored to disk should be committed to tape.

■ Commit to doing frequent backups if the system is an online transaction processing (OLTP) environment. An OLTP database is also known as the databases residing in the SQL Server Database Engine.

■ Try to schedule backups when SQL Server is not in the process of being heavily updated.

■ Use maintenance plans to streamline, automate, and schedule backups for all system and user databases.

■ For large databases, consider introducing additional files or filegroups and include a combination of full, differential, filegroup, and transaction log backups to reduce backup and restore times.

■ Speed up the backup process by selecting multiple backup devices.

■ Leverage the new Backup Compression feature to minimize the backup footprint on disk. However, first test the impact in a dedicated lab before rolling out into production.

■ When restoring the database to the point in failure, remember to first back up the tail-log and then conduct the restore.


Note from book "Microsoft SQL Server 2008 Management and Administration".

SQL Server 2008 Maintenance Practices Best Practices

Some important best practices from the chapter include the following:


■ DBAs should fully understand all maintenance activities required and implemented within the SQL Server environment.

■ Use the Maintenance Plan Wizard to automate and schedule routine maintenance operations.

■ When creating maintenance plans with the wizard, leverage the features included in SQL Server 2008 and create independent schedules for subtasks.

■ Maintenance tasks should be scripted, automated, and fully documented.

■ Maintenance tasks should be conducted during nonpeak times or after hours, such as on weekends and after midnight.

■ When you configure the order of the maintenance tasks, backups should be executed first, and then other tasks that change the database.

■ Do not include the Shrink Task when creating Maintenance Plans. Manually shrink the database if needed during nonpeak hours.

■ Maintenance tasks should be grouped into daily, weekly, and monthly schedules.

■ Schedule and conduct routine maintenance tasks on a daily, weekly, and monthly basis.

■ For a large enterprise environment running many SQL Servers, take advantage of subplans and the multiserver maintenance plan.

Note from book "Microsoft SQL Server 2008 Management and Administration".