keeping your data repository in top health l.
Skip this Video
Loading SlideShow in 5 Seconds..
Keeping your Data Repository in Top Health PowerPoint Presentation
Download Presentation
Keeping your Data Repository in Top Health

Loading in 2 Seconds...

play fullscreen
1 / 37

Keeping your Data Repository in Top Health - PowerPoint PPT Presentation

  • Uploaded on

Keeping your Data Repository in Top Health. By: Ian Proffer MUSE Session 305. About the Presenter & Acmeware. This is our 11 th year at MUSE! Acmeware’s founder led the development of the DR while working at MEDITECH We work exclusively with the MEDITECH DR

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Keeping your Data Repository in Top Health' - casey

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
keeping your data repository in top health

Keeping your Data Repository in Top Health

By: Ian Proffer

MUSE Session 305

about the presenter acmeware
About the Presenter & Acmeware
  • This is our 11th year at MUSE!
  • Acmeware’s founder led the development of the DR while working at MEDITECH
  • We work exclusively with the MEDITECH DR
  • We have a friendly (and valued) relationship with MEDITECH
  • Ian Proffer
    • 15 years in IT, specializing in relational databases
    • 6+ years in healthcare, including MEDITECH C/S (Jefferson Healthcare) and Harborview Medical Center in Seattle
session agenda
Session Agenda
  • Database administration and maintenance
  • System-level and database monitoring
  • Data validation and auditing
  • Opening and tracking service issues with MEDITECH
  • Streamlining operational support
  • Discussion, questions and answers
some questions about your dr
Some questions about your DR
  • Is your database being maintained?
  • Are the data transfers running?
  • Does the data match what’s in MEDITECH?
  • How much time do I have to spend doing this?
  • Note: Meditech screenshots are C/S 5.63
sql server database maintenance
SQL Server Database Maintenance
  • Database backups – a full database backup of livedb should be done daily; transaction log backups are not necessary
  • Data integrity checks and optimizations should be done regularly
data repository manager
Data Repository Manager

Objects within the SQL Server instance

Similar look and feel to SQL Server Enterprise Manager

Transfer Logs should be regularly monitored, though most errors are also reported to the DR Module

data repository manager7
Data Repository Manager

Manage connection threads and view thread activity

Connection Threads Status

the data repository module
The Data Repository Module
  • Meditech DR Module Functions and Tools
    • Operations Overview
    • Master and Transfer Background Jobs
    • File Maintenance
    • Reviewing DR Errors
operations desktop status
Operations Desktop - Status

New feature: main menu selections are repeated here

The Master Background Job is the main job all DR transfers and controls the start of the file maintenance routines.

operations desktop messages
Operations Desktop Messages

Messages here are the same as File Maintenance

The File maintenance job clears out the storage history on the size of the database and the log files.

dr file maintenance status
DR File Maintenance Status

The Status screen shows file maintenance and purge status by module.

data transfer menu
Data Transfer Menu
  • Use this menu for daily maintenance and troubleshooting potential DR issues.
  • Key Functions
  • Transfer Job Status
  • Check Pending Activity
  • Master Background Job Status
master background job status
Master Background Job Status

View of the current status of the Master Background Job.

view transfer job status details
View Transfer Job Status Details

Example of a LIVE environment with several background jobs

These jobs should be monitored daily

view pending activity
View Pending Activity

Enter ALL to get a list of pending activity by module, or…

Lookup and specify a specific module or modules

pending activity by module
Pending Activity by Module
  • Pending rows to be transferred
  • Although transfers are running a particular table can get ‘hung up’ and accumulate pending data.
  • A high row count may indicate a problem.
dr errors and exceptions
DR Errors and Exceptions
  • Use these menu items to check for table-level database issues, including:
  • Missing primary keys
  • Truncated data
  • Other data problems caused by sending MAGIC or C/S system
dr errors selection menu
DR Errors Selection Menu

Select one or all modules, enter date range and click on summary.

dr error summary
DR Error Summary
  • Summary of Unique Errors by Day
  • To View Details
  • Highlight the error line, click on Detail
dr error details
DR Error Details

Click here to return to Summary page

download or print errors
Download or Print Errors

Select download folder here and click OK.

download or print errors23
Download or Print Errors

Open it when you’re done.

initial load status
Initial Load Status

An entire module or specific tables can have an IL performed to reload data into a table from the NPR data structure.

data validation and auditing
Data Validation and Auditing

Why do it?

  • To provide confidence in your DR data and reports
  • You can do it manually…
    • Compare your DR reports to standard or custom NPR reports in MEDITECH
    • Use NPR Report Writer to save DPM subscripts to a file, import the file into a SQL table, write a procedure that compares your table against the SQL table from MEDITECH
  • …or you can have software to do it for you.
    • DrAuditor by Blue Elm Software
checking for timely table updates
Checking for Timely Table Updates

You can manually check each table too…

  • Use the RowUpdateDateTime flag to determine when a table was last updatedSELECT MAX(RowUpdateDateTime)FROM AdmVisits AS LastUpdate
  • Validates primary key rows and column values
  • More efficient and accurate then manual audits
opening service issues with meditech
Opening Service Issues with MEDITECH

opening service issues with meditech30
Opening Service Issues with MEDITECH

Some things to remember:

  • Submit new issues regularly
  • Update existing issues as necessary (if the nature of a particular error message changes, for example)
  • Use this subject line format: 5.54.6 MriPatientVisits – Missing Primary Key
  • Include the error log output and other symptoms, e.g. error frequency, etc.
  • Review open service issues regularly; Meditech will request customer permission before taking any corrective action
making it a little easier
Making It a Little Easier

So what do I do with this?

making it a little easier32
Making It a Little Easier

How we manage error logs and submit issues to MEDITECH:

  • The AcmeErrorManagement database
    • Imports error log .txt files into SQL Server
    • Parses each error into a table while eliminating extraneous rows (blank lines, page breaks)
    • Checks for existing tasks and DTS patches and updates new errors if they’ve already been reported
    • Builds a “worklist” of new errors that need to be evaluated and submitted to MEDITECH
    • Allows us to see common errors (and DTSes) across all our clients, helping MEDITECH resolve issues faster
    • Allows us to summarize and report DR error activity back to our clients
making it a little easier33
Making It a Little Easier

SQL Reporting Services Report – Error Status and Detail

making it a little easier34
Making It a Little Easier

How we leverage RowUpdateDateTime: spGetTableStatistics

making it a little easier35
Making It a Little Easier

Is a table receiving updates as frequently as we expect?

  • AcmeMonitor examines specific tables for RowUpdateDateTime within a user-defined timeframe and alerts via email when tables are out of sync
making it a little easier36
Making It a Little Easier

What about ring release updates?

  • Make a list of all tables used for reporting
  • List all stored procedures, views and function that use these tables
    • spTableDependencies
  • Note all Meditech DTS’s that affect these tables, looking for column name changes, table name changes, etc.
  • Compare the schema of tables in the livedb against the testdb
    • Third party tools like SQL Compare, SQL Delta, etc.