Basic OnBase Database Maintenance for SQL Server

Important tips to keep your Database running optimally.

There are as many different ways to use OnBase as there are customers who use it…

…but there are a few things every OnBase system has in common. The most basic, and the most critical, is the Database.

 

Your Database is the backbone of your system, and how the Database functions will directly affect your users, each and every day. If your database is running slowly, your entire OnBase solution will be slow. If your database experiences data loss, the metadata may be lost. If your database becomes corrupted, your entire OnBase system becomes suspect.

Let’s look at some basic maintenance to ensure your Database is running optimally, so that your users will be able to work without interruption.

*The topics covered here are directed at SQL Server Database Management Systems, but the overriding principles are equally applicable to Oracle systems.

1. Keep OnBase Running Quickly

OnBase uses a proprietary database schema containing many interrelated tables. While it is a relational database, like most SQL databases, the relationships between each table are controlled by the OnBase Software, rather than by standard SQL Server functions and stored procedures.

When updating or retrieving from tables, OnBase relies on its table Indexes to a greater extent than most other SQL Databases. Because of this, it is vitally important that OnBase Indexes and Statistics are kept up to date.

This is particularly true of tables such as itemdata & itemdatapage, (which relate to the retrieval of the individual documents), the various Workflow tables (which keep track of a document or object’s path through the Workflow Life Cycles and Queues), and Keyword tables (which keep track of the keyword metadata associated with each particular document).

If your index statistics become out of date, which they can do very quickly in a heavily used system, SQL Server will resort to a full table scan, looking through every row of the table, rather than following the Indexed “shortcuts” to get to the desired document or keyword more quickly.

Therefore, the following recommendation should always be followed: update your Statistics and Indexes regularly, and ALWAYS update statistics at 100% sample.

Doing so will keep your SQL queries running quickly, and let your users work more efficiently!

2. Keep Your Database Safe From Disaster

There are a number of disasters which can befall a database – hard drive sector failure, power issues, and simple connectivity problems can all result in data not being available on request. User error can accidentally overwrite valid data with incorrect data, or remove data from the system incorrectly.

Any of these can have a significant impact on your system and your users, potentially losing days, weeks, even years of data which would need to be reconstructed, costing you thousands of dollars. Happily, there is a simple and efficient way to protect your system from any of these issues: database backups.

Database backups should be run regularly, and those backups should regularly be restored to a test or other location to validate its usefulness.

Of course, once backups are mentioned, there’s the inevitable question: How often should you back up? There’s only one viable answer to that: How much data can you afford to lose?

Seriously, think about it: how difficult would it be to re-import, re-index and re-process an hour’s worth of data? A day’s? A week’s? Do you still have the original index information? Do you have the original electronic or physical documents that were imported or scanned? For how long do you keep these?

The answers to those questions will tell you how long you could comfortably go between backups of the database. If your company’s business process requires you to shred scanned documents immediately upon import, you need to back up (or mirror, or otherwise replicate) your database information as often as possible.

If your company requires you to keep physical or electronic copies outside of OnBase for 3 months, perhaps you can go a whole week without a backup. As with so much of OnBase, the answer to “how often should I back up” will be as unique as your system’s OnBase usage!

3. Ensure your Database is accurate

There’s another kind of Database disaster we didn’t discuss above – database corruption, that is, the inability of the database itself to maintain the integrity of the data stored in the database.

OnBase is very good at identifying missing data; data removed from the database incorrectly, or removed from the Disk Groups incorrectly. But database corruption is a very different story, and very, very difficult for OnBase to identify.

Even if OnBase encounters Database corruption, the chances are that the error message OnBase throws won’t indicate corruption. And, because it is so difficult for OnBase to find corruption, the database may have had issues for a very long time before OnBase discovers the issue.

Therefore, the most important thing you can do to ensure that your database is accurate is: run regular corruption checks on the database.

The best thing you can do is to include two corruption checks on the database every time you back up: the first check is run on the database before you back up, and the second check is run on the backup itself.

If you do have database corruption, what do you do? As far as Hyland is concerned, there is only ONE option: restore a corrupted database to the last known good (verifiably uncorrupted) backup. Do not EVER run a restore or fix process that “allows data loss”, and the minute you find that corruption is detected, CONTACT KEYMARK SUPPORT.

 

If you follow the above 3 policies, and take good, regular care of your database, your database will take care of your OnBase system and your users will be able to work quickly and efficiently, while you, the OnBase Administrator, can sleep nights knowing your metadata is safe and sound!

 

 

 

Categories: Tips

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *