DocLink Database Best Practices

NOTE: Not applicable to DocLink Cloud.

 

Keeping your DocLink database healthy can prevent poor application performance or even downtime and data loss. The following are best practices for configuring the DocLink database within SQL Server Management Studio.

Data and Log File Management

SQL Server Management Studio has two main files per database - a data file and a log file. The data log is where data is stored and the log file sequentially logs all database transactions.

  • Data and log files should reside on their own physical disks, isolated from application data.
  • Configure auto-growth by file size vs. percentage. The size of file growth should be set high enough that the database is not constantly growing throughout the day. The appropriate growth size should be determined by monitoring data and log used space within the DocLink database files. By monitoring the data and log usage you can determine the growth size that will prevent database files from regularly growing. The data file will continue to grow over time. The log file should not grow over time.

    The size of the log file should be maintained through backup, which will release free space for new transaction to be logged. A DocLink system with high transactions will average about 10-15 GB per year. A system with which is constantly running ERM Capture, Barcode, Import, etc. can be consider as a high transaction system. In this scenario, auto-growth should be between 20-30 GB. DocLink system with lower transaction can be as little as 1-2 GB. In this scenario, growing between 2-4 GB would be sufficient.

  • Do not enable auto-shrink. (ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;)

Index Fragmentation

The process of writing, modification, and deletion of data inevitability will cause index fragmentation. A proper maintenance plan should include steps to remove index fragmentation (see Maintenance Plan below).

  • Schedule a regular rebuild of the indexes. This can be performed nightly or weekly using a maintenance plan.

Statistics

Statistic is the distribution of data values for columns within a table or index. Query Processor uses statistics to determine how a query should be executed. A poor query plan can result when statistics are outdated. A proper maintenance plan should include steps to update statistics (see Maintenance Plan below).

  • Turn on AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS

Corruption Detection

Data corruption can occur for many reasons. This can be anywhere from file-system drivers, device-drivers, RAID controllers, cables, network, and/or disk drives. Another common cause of data corruption is a power failure while the disk drive is performing a write operation. To identify possible data corruption use checksum, torn page, and schedule DBCC CHECKDB after a Full Backup.

  • ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM;
  • ALTER DATABASE MyDatabase SET TORN_PAGE_DETECTION ON;
  • DBCC CHECKDB

Backups

The duration of backups should be a business decision based on how much data you are willing to lose. Generally, anything longer than a daily full backup will be compliment with differential backup (even on daily full backup differential backup is a common practice).

Transaction Log Backups should be used for the highest level of recoverability. This will allow a recovery to any point in time. A transaction log backup can only be performed when the database recovery model is set to FULL or BULK_LOGGED. An example of this could be full database backup every day, a differential database backup every four hours, and a log backup every half hour.

A common configuration mistake is to have the database recovery model set to FULL and not have the proper backup plan configured. This mis-configuration can cause the log file to grow to very large sizes or grow until the drive is full. When the database recovery model is set to FULL the transaction log backups will free up space in the log file for new transactions to be logged.

Finally, backups need to be stored in a separate location. Ideally, the storage of backups should be stored in the following priority based on a business disaster recovery plan:

  1. An off-site location
  2. On a different server
  3. On a separate physical drive

Maintenance Plan

Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, is regularly backed up, and is free of inconsistencies. The Maintenance Plan Wizard in SQL Server Management Studio also creates core maintenance plans. Creating a plan manually will give you much more flexibility.

Click on the link below for additional information on creating a maintenance plan:

http://msdn.microsoft.com/en-us/library/ms189953.aspx