Transcript Document
Keeping your Data Repository in Top Health By: Ian Proffer MUSE Session 359 About the Presenter & Acmeware This is our 10th 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 SQL Server 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 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.54 SQL Server database maintenance Database backups – a complete database backup of livedb should be done daily; transaction log backups are not necessary Data integrity checks (DBCC CHECK DATABASE) data optimization (DBCC DBREINDEX) should be done at least weekly System monitoring in MEDITECH First, check to see if the transfers are working… System monitoring in MEDITECH using the View Status routine… System monitoring in MEDITECH …and the View Pending Activity routine. Select your applications, (F9 lookup) or use ALL… …and view the results. System monitoring in MEDITECH Going through a new implementation or ring release? System monitoring in MEDITECH Check the status and see what’s up. BAR is still running… System monitoring in MEDITECH The View Distinct Errors and View Errors routines What’s the difference? System monitoring in MEDITECH View Errors - specify your applications using F9 lookup, or use ALL… …let’s narrow the search results. System monitoring in MEDITECH View Distinct Errors - specify your applications using F9 lookup, or use ALL… Press down arrow to scroll through (up and down) the list… System monitoring in MEDITECH …and right arrow to get more error detail. OK, now what? Let’s do something with this error log. Put a “P” here to print… System monitoring in MEDITECH Download the file… …and open it when you’re done. Checking for recent table updates You can manually check each table too… Use the RowUpdateDateTime flag to determine when a table was last updated SELECT MAX(RowUpdateDateTime) FROM AdmVisits AS LastUpdate 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 buy software to do it for you. DrAuditor by Blue Elm Software DrAuditor Validates primary key rows and column values More efficient and accurate then manual audits Opening service issues with MEDITECH www.meditech.com/customerservicehome.htm Opening service issues with MEDITECH Some things to remember: Submit new issues regularly (at least weekly) 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 So what do I do with this? Making it a little easier How we manage error logs and submit issues to MEDITECH: The AcmeErrorManagement database Imports error log .txt files using DTS 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 easier How we leverage RowUpdateDateTime: spGetTableStatistics Making it a little easier Is a table receiving updates as frequently as we expect? AcmeMonitor examines specific tables for RowUpdateDateTime within user-defined timeframe and alerts via email when tables are out of sync 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 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. Discussion, questions & answers