SQL Server Best Practices – Part 1

 

  • Use a dedicated server for your database [it will prevent security breaches and leaks. Manageability and Availability will be enhanced]

 

  • Install only required components. [It is good practice to install only needed features and by doing so it will reduce the attack surface and better resource utilization]

 

  • Do not install sample databases and sample codes on SQL servers in the production environment. [If needed, install the samples only in the development and test environments]

 

  • Windows Authentication mode is more secure than SQL Authentication. [Windows Authentication mode is the default and recommended authentication mode]

 

  • If there is still a need to use SQL Authentication – enforce strong password policy.

 

  • Disable the SA account and rename it. Do not use this account for SQL server management. [Before Disabling/renaming the SA account, verify that another account with administrative privileges exists in order to have access to the SQL Server.]

 

  • Keep minimum administrators as it can be.

 

  • Allocate different administrative accounts if there is more than one administrator. [This will set accountability]

 

  • Remove BUILDIN\Administrators group from the SQL Server Logins. [Because it gives SQL Server System Administrator rights to any account in the Windows Local Administrators group].

 

  • Change default SQL Server ports associated with the SQL Server installation to keep hackers from port scanning the server. [Default port (i.e 1433) is well known and is common target for hackers. Therefore it is recommended to change default port associated with the SQL Server installation]

 

  • Enable logging SQL Server login attempts (failed & successful). [The default mode is:Failed Logins Only. Thus, it is recommended to set the auditing mode to be Both Failed and Successful Logins.]

 

  • Hide SQL Server Instance from Broadcasting Information [The SQL Server Browser service enumerates SQL Server information on the network. In such way attackers can use SQL Server clients to browse the current infrastructure and retrieve a list of running SQL Server instances. Thus it is strongly recommended to hide SQL instances from being shown in the network]

 

 

  • Recent service packs and critical fixes should be installed for SQL Server and Windows. [Service Packs contain important fixes for a product and are tested even more rigorously than incremental releases like cumulative updates, so they’re pretty important to install. But like everything else, they may contain something that has unexpected side effects– so you need to test your application with the service pack before you change it in production.]

 

Database Properties

SQL Server includes several database property configuration options that can impact performance and recoverability in a production environment. In this post we will discuss the recommended configuration value as per the best practices of Microsoft.

  • Auto Close
  • Auto Create Statistics
  • Auto Shrink
  • Recovery – Page Verify
  • Recovery Model
  • Auto Update Statistics
  • Auto Update Statistics Asynchronously
  • Compatibility Level

 

Auto Close

When enabled, the AUTO_CLOSE option causes the database to shut down cleanly and the resources to be freed after the last database user exits. New connections to the database reopen it.

The AUTO_CLOSE option is not intended to be used for frequently accessed, highly concurrent shared databases. Thus, it should be enabled only for single-user desktop applications. Additionally, some of the performance issues experienced with AUTO_CLOSE prior to SQL Server 2005 have been resolved. However, performance issues can still occur in SQL Server 2005 because a database closure results in objects being cleared from the plan cache. Recommended/Default value is FALSE.

Auto Create Statistics

Statistics contain a sampling of values from the index key and the first column of the index key for composite indexes. The query optimizer determines whether an index is useful for a specific query by evaluating the stored statistics. If AUTO_CREATE_STATISTICS is disabled, the statistics will not be created on columns that are not indexed when needed by the query optimization process. The performance of incoming queries may suffer if AUTO_CREATE_STATISTICS is disabled. Thus, you should always keep this option enabled for each user database. Additionally, statistics are essential to the query optimization process. If new statistics are needed but have not been created, query performance will be affected. Recommended/Default value is TRUE.

Auto Shrink

The AUTO_SHRINK option, when enabled, causes data and log files to be automatically shrunk if more than 25 percent of a file has unused space. This option can cause performance and concurrency issues during the shrink process. It can also cause index fragmentation after a shrink process has been performed. Ensure that AUTO_SHRINK is turned off by default. If free space must be generated, do that by using the DBCC SHRINKFILE or DBCC SHRINKDATABASE commands. In addition, always rebuild or reorganize the indexes after a shrink operation in order to fix any fragmentation that might have occurred in the data files.

Torn Page Detection (Page Verify)

When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte database page where the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the result will indicate that a torn page was detected and that the page was initially written incorrectly.

Recovery Model

The recovery model controls transaction log maintenance and can have serious effects on a system if it is set incorrectly or inappropriately. If the wrong recovery model is specified for the back-up strategy being used, data loss can be experienced during a restore, or log files can grow to the point where they consume all space on the drive.

When the recovery model is set to Simple, no log backups are taken and log space is automatically reclaimed to keep space requirements small. This essentially eliminates the need to manage the transaction log space. However, in Simple mode, you can only recover to the end of the last backup. This is because there are no transaction log backups to apply in order to bring the database to a consistent current state.

When the recovery model is set to Full or Bulk-Logged, log backups are taken and the system can be restored to a current state or to a previous state. When Bulk-Logged is being used, certain operations are not logged. This reduces log space usage by special logging during most bulk operations. In addition, when the Bulk-Logged model is used, point in time recovery is not supported.

Auto Update Statistics

Statistics contain a sampling of values from the index key and the first column of the index key for composite indexes. The query optimizer determines whether an index is useful for a specific query by evaluating the stored statistics. If the statistics become outdated and significant changes have occurred against the underlying data, this can result in less than optimal query performance. If AUTO_UPDATE_STATISTICS is disabled, out-of-date statistics will not be automatically updated.

Ensure that AUTO_UPDATE_STATISTICS is enabled for each user database on the SQL Server instance. For highly volatile data, consider more frequent statistics updates using the system stored procedure, sp_updatestats, or the UPDATE STATISTICS command.

Auto Update Stats Async

AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to On, queries do not wait for the statistics to be updated before compiling. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay as a result of waiting for updated statistics, query response times are predictable. However, the out-of-date statistics may cause the query optimizer to choose a less efficient query plan.

You should consider setting the AUTO_UPDATE_STATISTICS_ASYNC option to On when the following characteristics apply to your application:

  • Client request timeouts caused by one or more queries waiting for updated statistics have been experienced.
  • Predictable query response times are required, even at the expense of occasionally running queries with less efficient query plans because of outdated statistics.

Compatibility Level

 Setting the databases to a lower compatibility level should be done only as an interim aid during migration. Keeping the databases at the same level indefinitely will disable some of the new features that later versions provide.