Database 101-9: Why should you care about database maintenance best practices?
A continuation of the blog post Database 101-8: Which SQL Server version will give you the best performance?
In our last post, we provided information on many components in SQL eco system that affect data base performance and touched on best practices. I thought we’d get into those best practices once more, and walk through a few cautionary tales of how not maintaining these best practices can impact your pharmacy. (Spoiler alert: all are less than ideal situations for your staff and customers!)
Database Best Practices
Do not allow unauthorized access
Do not edit stored procedures
What happens when the above isn’t executed? Here are some real-world issues that have been called in to SoftWriters, and how each situation resulted:
Issue: Drug Interactions are not working correctly.
Discovery: Ran a trace which showed that FrameworkLTC was passing values correctly. A different checker revealed that a stored procedure had been edited, leaving the severity level of the drug hard-coded.
Solution: Removed erroneous edits from the stored procedure.
Results: Drug-to-drug interactions were corrected.
Best Practice: Ensure only intended and skilled employees have write access to stored procedures and triggers.
Issue: Everyone is frozen in the system.
Discovery: SP_who2 showed blocking on the system. Activity showed that blocking came from a custom report created by a third-party.
Solution: Closing the SPID (Server Process ID) released the blocking which brought the pharmacy back up.
Results: Operations were normalized and the system was restored.
Best Practice: Ensure proper testing is done when developing custom reports, even when using a third-party. (SoftWriters is available to build custom reports through a DR.)
Rick Magill is a Solutions Consultant at SoftWriters, a Pittsburgh-based company that offers pharmacy management software solutions to pharmacies serving the long-term care community. Rick is an integral part of our customers’ success with his forward thinking and commitment to excellence.