Time Matters 9/10/11 Database Corruption Part I
This is part one of a two part blog series about monitoring and dealing with database corruption within your Time Matters Database. In part I will discuss some things that can cause database corruption and what you can do to keep an eye on it. In Part II we will discuss ways that you can correct corruption in your Time Matters Database – or any Database for that matter – after it has already occurred. Please note that if you do not know what SQL server is, or what SQL Server Management Studio is you should probably contact a professional.
I was inspired to write this article after I had a client (with a large network) frantically call me about an outage, all of the computers in the office were experiencing sporadic outages of connectivity to the network. After doing some initial trouble shooting I narrowed down the problem to a faulty network switch, problem solved right ? Wrong. Apparently when the switch had dropped offline there had been pending updates that were rolled back incompletely. This caused some very serious corruption to the Firms Time Matters Database. In this article we will discuss some methods to monitor and correct corruption in your Time Matters Database.
Time Matters Database Corruption
Since SQL was introduced in Time Matters 9, mandated in Time Matters 10 and is now the engine that drives both the Time Matters 11 program along with Time Matters Mobility, there are some important things to consider. The Time Matters database scheme (structure) has some inherent design problems that lend it to potential corruption. There are some simple steps you can take to proactively monitor and prevent these issues from occurring.
What can cause corruption to occur in Time Matters
This is a little technical for the average lay user, but here we go. Time matters is designed with in table SQL triggers; what can happen with the use of in table update, insert, and delete triggers (besides performance problems) is that often the transaction that fired the trigger will be rolled back, yet the event fired by the trigger (which is non-synchronous in the eyes of the database ) will fail to be rolled back. This can lead to indirect primary and foreign key violation. Don’t get me wrong, there is a time and place to utilize triggers but this is not one of them. This issue is further complicated by the use of relational hash tables (relatea, relateb, relates) which rely heavily on the triggers in the dbcheck table. Neither you nor I have direct control over the design of the Time Matters Application, however we do have control on how we monitor the integrity of our data regardless of the software design.
Keeping an eye on things
For users who have the standard edition of SQL server (which I highly recommend if you intend on running Time Matters 9 / 10/ 11) there are some relatively simple things you can do to help you and your data.
1. Setup SQL Database Mail:
- SQL Server Database Mail allows you to be notified via email for the completion of events such as backups, and more importantly to be notified when Database Engine encounters an error handling a transaction. SQL Server Expert Dave Pinal has an excellent tutorial on Configuring SQL Server Mail. This feature is only available on standard edition and higher. This, along with the SQL Server Agent and a higher resource utilization, makes a convincing argument for using a paid edition of SQL Server.
2. Set up Alerts (via Email) on SQL Severity Errors.
- As a general rule of thumb alerts should be set up on SQL Server Severity 17-24, 21-23 are the ones that you really have to watch for. You can either do this manually through the agent GUI or by running a script. Brent Ozar has a script in his SQL Server Setup Checklist Part 2 which you should follow when installing your SQL Server. The Time Matters SQL installer does a poor job of installing SQL Server.
If you follow these steps correctly you will receive emails whenever your SQL Server throws a severe error. I recommended that you have these forwarded through a distribution group to several people so that someone on your team notices the error. Here is an example of an email I received when the clients Database started to go bad.
Why Bother to Monitor your Time Matters Database ?
Monitoring your database integrity is a best practice, no matter what that database may be. Continuing with the example above I was aware within less than an hour that there was a serious problem and was able to resolve the issue for the client with negligible data loss. However, let’s think about the worst case scenario that could occur. You have some kind of issue that causes database corruption but you have no idea it happened, over a period of weeks you notice significant performance issues with your Time Matters application. No one checked your backups (which by the way weren’t running because of the database corruption). You have to concede weeks of data loss because you had no alerts setup. For an office that incurs 50,000 dollars a day in operational costs if you don’t notice this issue for a month, you just lost well over a million dollars. Well I’m not a mathematician but I could pretty much say with some degree of certainty that proactive monitoring pays for itself.
Check back later in the week for part II of this post where I will teach you how to handle database corruption in your Time Matters Database.