SQL Server and Database Settings + Configuration

SQL Server Settings

SQL Server Configuration Manager

Use SQL Server Configuration Manager (SSCM) to manage services, server protocols, and client protocols.

Alias: Use SSCM to configure a server alias and associate with a server, protocol, and port.

Ports: See SQL Server Ports section 3.

Auto Restart SQL Server

Set the SQL Server service start mode to ‘Automatic’. If the SQL Server Agent is required, set the ‘SQL Server Agent’ start mode to ‘Automatic’.

Distributed Transaction Coordinator

DBA will Set the ‘Distributed Transaction Coordinator’ service start mode to ‘Disabled’ unless required for clustered services or other distributed transactions.  

Database Settings and Configuration

Database file locations

Isolate database data files and log files at the physical disk level. DBMS binaries, system databases, tempdb, user data, logs and backups should be isolated to reduce disk IO and allow for improved database recovery and performance.  At no time will database files be located on the C: drive.

TempDB

If tempdb will be used heavily, put it on its own separate array. In addition, pre-size tempdb to a size that will meet your server’s needs without having the need to autogrow. Divide the tempdb database into multiple files so that the number of files is equal to 50% to 100% of the number of CPU cores in your servers. Each physical file must be the same size.

Database maintenance

Proactive database maintenance is performed on a regular basis to keep performance efficient. This includes data/index reorganizations and data analysis/runstats.  

As often as you take full a backup, which is probably once a day, run DBCC CHECKDB on your databases to verify database integrity.

Avoid running most DBCC commands during busy times of the day. These commands are often I/O intensive and can reduce performance of the SQL Server, negatively affecting users.

Database Settings

Database settings should generally be kept at their default values. Ensure that the following settings are set for overall best performance: These are default settings:

·         Auto Create Statistics: On

·         Auto Update Statistics: On

·         Auto Shrink: Off

·         Page Verify: Checksum

·         Recovery Model: Simple (Production databases will be determined based on business requirements)

Don’t rely on AUTOGROWTH to automatically manage the size of your databases. Instead, proactively monitor and alter database size as circumstances dictate. Only use AUTOGROWTH to deal with unexpected growth.

Database Cleanup Process

For non-production databases:

• If a database has no activity in the last 3 months we should alert the owner, perform a backup and take the database off-line.
• After 3 months of a database being off-line with no requests for the data, we can make a final backup and then permanently delete it.

For production databases:

• If a database has no activity in the last 3 months, contact needs to be made with the database owner.
• If no owner is identified get approval from appropriate Client department, perform a backup and take the database off-line.
• After 3 months of a database being off-line with no requests for the data, detached the database and move the data files to a "To Be deleted folder".
• After an additional 3 months we can make a final backup and then delete it.