If you use the DR for reporting, you need a process to maintain data health and accuracy. Maintaining data repository health requires understanding essential tasks and their proper timing.

Best Practices

Regular maintenance tasks preserve data accuracy and optimize reporting efficiency across various applications including SQL queries, SSRS, BCA, Power BI, and data marts.

Recommended regular DR server maintenance includes:

• Monitor free and used drive space (weekly)
• Performing regular index maintenance (defragmentation)
• Monitoring the data transfer background jobs (daily)
• Monitoring pending DR transfer activity (daily)
• Reviewing data transfer errors and submitting tasks to MEDITECH when required
• Perform regular data validation on core DR tables
• Performance analysis identifying queries causing excessive blocking, excessive I/O, excessive CPU usage, multiple transactions open simultaneously, and ineffective or improperly built non-clustered indexes

Many facilities lack dedicated IT staff to manage these tasks, making vendor partnerships valuable. Acmeware offers a DR Health Assessment Tool that does a comprehensive analysis of your DR server's configuration, operation and performance.

How Data Gets to Data Repository

Data repository functions as effectively a read-only copy of transactional data from the EHR. As users modify data in MEDITECH applications, changes queue for transmission to SQL Server, where native SQL INSERT and UPDATE statements recreate data in relational live and test databases. This architecture requires monitoring both background job processes and SQL Server maintenance simultaneously due to massive data volumes.

Background Activity in MEDITECH

Two critical monitoring routines exist: the DR Monitor (6.1/Expanse) and Master Background Job (C/S and MAGIC), plus File Maintenance job status.

DR Transfer Errors

Not all logged messages qualify as genuine errors requiring investigation. Notable errors warranting follow-up include:

• Primary key missing: Data filed to the DR Activity Index; SQL requires all primary keys while MEDITECH does not, potentially preventing DR writes
• Missing subscripts in activity: Occurs during MEDITECH data capture and requires investigation
• Skipped sequence errors: Network interruptions cause sequences to be skipped and must be resent; time-sensitive requiring immediate reporting
• ODBC non-fatal errors: Occur when records are absent from SQL tables
• Socket errors: Transfer process cannot connect to DR SQL Server; repeated occurrences suggest server offline status

SQL Server Considerations

Two primary maintenance focuses exist: database growth management and SQL table query efficiency through index defragmentation.

Database Growth and Storage Capacity

Unlike the MEDITECH EHR, data does not purge from the DR. Test and live databases consume increasing disk space over time. Complete historical reporting capability requires enabling all DR tables for data transfer, necessitating substantial storage investment. System administrators must monitor free disk space continuously, planning capacity additions when available space drops below 20%.

Some large tables can be selectively disabled to conserve space. For example, ItsResultText duplicates ItsResultFreeText and contains embedded formatting characters making it unhelpful for reporting, so disabling it and truncation recovers space. However, consult your DR specialist before disabling high-consumption tables.

Table Index Maintenance

Continuous data additions and updates cause table fragmentation over time, reducing storage efficiency and slowing data retrieval. Analyzing index fragmentation and performing reorganization or defragmentation optimizes performance.

Results may reveal indexes that are highly fragmented (50-97%), requiring correction through index rebuilding. Query variations enable scheduling automated database-wide index analysis and remediation for indexes exceeding fragmentation thresholds, maintaining indexes with minimal intervention.