Not only is it important to have a disaster recovery in place, but also to perform ongoing maintenance and monitoring of your system.
Server maintenance includes maintenance tasks recommended by your database platform, as well as running scripts to re-index and update statistics for the InterAction database. These scripts can improve InterAction performance.
You execute SQL scripts using a SQL processor such as Query Analyzer. Consider the following when defining your maintenance plan.
Re-indexing Scripts
The maintenance script (INTIA_REINDEXIA55SP1.SQL for InterAction 5.5 SP1 and higher) provided by LexisNexis on the Support Center Web site performs two functions.
- Re-indexing - This rebuilds all indexes defined for all of the InterAction tables. This is typically done after a bulk copy of data into the table.
- Updating Statistics - SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics in decisions about which index(es) to use in query processing. If there is significant change in the key values of an index, this function should be performed.
Run the maintenance scripts every time the volume of data increases or decreases by 10% or more. Also, schedule the maintenance scripts to run weekly with SQL Agent. SQL sends success or failure messages to operators informing them of job status. This ensures that the database is always optimized.
LexisNexis InterAction also provides a data maintenance script that you need to run through Process Manager. For more information, see Data Maintenance Script.
Database Consistency Checker (DBCC)
To ensure the database continues to perform as expected, periodically check it for errors. These commands are run on a regular basis to ensure the database is in working order. Set up a SQL job, and get notified regarding the success or failure through email or Net Send messages.
- DBCC CheckDB - Checks for common errors in the linkage of tables and index pages.
- DBCC CheckAlloc - Checks the consistency of disk space allocation structures for a specified database and the use of all pages.
- DBCC CheckCatalog - Checks system tables for allocation and corruption errors.
Monitoring Performance
When monitoring your database, you want to accomplish the following goals.
- Determine whether it is possible to improve performance
- Troubleshoot any problems or debug application components
- Test applications and development systems
Establish a measurement baseline of server performance. This shows what resources SQL consumes under normal circumstances. Then, track performance on regular intervals and after major changes to the system. Archive the data acquired by monitoring to establish trends and identify potential trouble as the system grows.
The following are some points to remember regarding SQL log files:
- Generate log files during peak periods, backups, and non-peak times.
- One hour long log files are generated with a 60 second interval.
- Log files that include all counters consume approximately 13MB per day. Change the log interval to condense these.
- The archive log has a larger interval to conserve space.
- As daily logs are archived, they are deleted.
- Log files are saved as the server name followed by the date. The archived log file containing data from the daily log files is saved as the server name.
- Create graphs with SQL server Performance Monitor and the log files to display how the system is performing.