How to: Configure and Maintain Agility On-Premise Databases

Before You Begin

Work with your SQLServer DBA to review and consider implementing the following recommendations.  See SQLServer 2005 Books Online for more information about the concepts discussed.

If you’re using SQLServer 2005 Express Edition, you’ll need to have the SQLServer 2005 Management Studio Express to easily modify server and database settings via its’ GUI.

Recovery Model

By default, SQLServer will create new databases with the Recovery Mode set to ‘Full’. If your DBA team uses this as a standard, does log backups in addition to database backups, and has an effective backup management and recovery process, then you are probably fine in this regard.  If, however, you don’t have this support/infrastructure/process in place, and your database’s recovery model is set to ‘Full’, it’s likely that your transaction log file is growing continuously, and begun to seriously impact performance. 

If this is the case, you should consider:

• Setting the application database recovery model to "Simple":

  1. In SQLServer Mgmt Studio, right-click on the database and choose Properties.

  2. Select the Options page, change the Recovery Model drop-down to Simple, and then click OK.

• Shrinking the transaction log:

  1. Right-click on the database, choose Tasks > Shrink > Files

  2. On the Shrink File dialog, choose File Type: Log

  3. Click OK

• Creating a daily (or more frequent) backup schedule, retaining a rolling week or so of backups (other maintenance tasks will be discussed separately):

  • If you are using SQLExpress, you won’t have SQLServer Agent, so will need to use the Task Scheduler to execute cmd/bat files that launch the command-line utility

To learn more about SQLServer 2005 backup and restore strategies to make sure you’re comfortable with this approach, see SQLServer 2005 Books Online.

Database Maintenance

SQLServer databases need recurring maintenance to ensure optimal performance.  It’s recommended that the following database maintenance tasks execute once per week (on a weekend night, presumably, in this order).

• Rebuild Indexes

• Update Statistics

This will eliminate all index fragmentation, then refresh the query optimizer’s statistics, ensuring that SQLServer’s query plans are built on the most up-to-date information.

If you have a large user population making heavy use of the application, you may want to schedule an overnight database maintenance task that includes (in this order):

• Reorganize Indexes

• Update Statistics

In this case, reorganizing will repair minor index fragmentation prior to updating stats.

Additionally, as was mentioned above, you should back up your database at least once/day, depending on the backup and recovery strategy you employ.