Skip to main content

Configuring and Maintaining On-Premise Databases

This article explains how to configure and manintain On-Premise databases in Agility.

Before You Begin​

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

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

Recovery Model​

By default, SQL Server 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 or infrastructure or 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 SQL Server 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 SQL Express, you won’t have SQL Server Agent, so will need to use the Task Scheduler to execute cmd/bat files that launch the command-line utility.
    • See Related Links for more information on Transact-SQL BACKUP command syntax.

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

Database Maintenance​

SQL Server 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 SQL Server’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 backup your database at least once per day, depending on the backup and recovery strategy you employ.

Note that SQL Server Express is not supported as a database for Digital.ai Agility.