A. OUTLINE
This BLOG discussion focusses on the various forms and reasons for company database inconsistencies to occur as well as some additional insight in managing them.
B. DATABASE (DB) CORRUPTION / INCONSISTENCIES DEFINED
B.1 General Definition
According to Wikipedia, data corruption is defined as below:
Data corruption refers to errors in computer data that occur during writing, reading, storage, transmission, or processing, which introduce unintended changes to the original data. Computer, transmission, and storage systems use a number of measures to provide end-to-end data integrity, or lack of errors.
In general, when data corruption occurs, a file containing that data will produce unexpected results when accessed by the system or the related application. Results could range from a minor loss of data to a system crash. For example, if a document file is corrupted, when a person tries to open that file with a document editor they may get an error message, thus the file might not be opened or might open with some of the data corrupted (or in some cases, completely corrupted, leaving the document unintelligible).
https://en.wikipedia.org/wiki/Data_corruption
B.2 Sage Evolution company specific definition
In the above definition the word file is referred to. In terms of Sage Evolution, or any other application that makes use of a database management system (e.g. MS SQL Server, Oracle etc.) a file can be regarded as a table within the company DB.
Practically explained such corruption could include a host of possible issues such as, but not limited to, illegal characters, missing index numbers, duplicated records, NULL values in certain fields where they are not supposed to be, or no NULL values where they are expected, deleted records within the table, values longer than allowed within certain fields and many others.
C. SYMPTOMS OF HAVING DB CORRUPTION / INCONSISTENCIES
The following typical issues (among several others) may be symptomatic of having Sage Evolution company DB corruption / inconsistency issues:
1. Record Duplications
You may observe duplicated Customer, Supplier, Inventory Item, Warehouse, GL Accounts and/or any other type of maintenance record on the relevant module’s grid screen.
For example, notice how Customers 070192 W and 071586 are both duplicated on the Accounts Receivable | Maintenance | Customers grid.
2. Critical Error Messages
There is a wide variety of error messages that may be observed when opening, navigating, processing, enquiring, or reporting in an Evolution company.
For example, this error message may be observed when processing any kind of source document.
3. Upgrading Errors/Issues
The Sage Evolution upgrade routine applies very strict checks and validations when company DB’s are upgraded to prevent users from working in a corrupted company after the upgrade has been completed.
For this reason, an upgrade error message may therefore be displayed in the event of any DB corruption/inconsistencies detected during the upgrade process.
In addition, the upgrade process should also fail, and you are required to restore the backup that was made before the upgrade commenced and then fix the source of the DB corruption.
For example, the error message below is among several that may be observed when performing a company DB upgrade.
4. Inaccurate printed reporting / source document information
There may be known, or unknown inaccurate information included in your Evolution reports and/or source documents (printed to screen or printer).
For example: Observing a difference between the report total of the Inventory Valuation Report | By Date option selected, and the Inventory Control Account balance, as compared with each other at a specific date (e.g. 31 July 2021).
5. General company slowness, latency, hanging issues
You may observe a general slowness, hanging, latency when opening an Evolution company, or navigating, processing, reporting and/or enquiring within the company.
6. Company data Loss / missing records
You may observe that certain records (e.g. any maintenance, document type or posted journal batch information) or field values within certain records are missing.
D. REASONS FOR HAVING DB CORRUPTIONS / INCONSISTENCIES
The following reasons may contribute towards or be the reason for having DB corruption / inconsistencies:
1. Bad sectors on PC hard drives
PC hard drive data corruption occurs when a system cannot finish writing data to a file or when segments of the file become inaccessible. It is a common cause of data loss, as corrupted files are often unusable.
2. Incomplete/corrupted sync files imported for Branch Accounting companies
Offline Branch Accounting companies may be corrupted due to incomplete/corrupted sync files.
Such corruption may be caused by several factors that are at play at any stage from the sync file being exported, uploaded, downloaded, and imported at the target branch/Head Office DB.
3. Power failures during processing
It goes without saying to always protect your electrical environment on your Sage Evolution network. It’s not just about complete power outages, but also spikes and dips in electrical networks.
This is because power failures/spikes/dips can negatively impact your company data integrity in the following two ways:
- The possibility of causing immediate data corruption at the critical time of posting a transaction g. sales/purchase order, Journal batch etc., when the power is interrupted.
- Unexpected shutdowns of desktop and server PC’s, such as with power interruptions, can also lead to data corruption at a later stage.
Such affected PC’s, with damaged operating systems, may not be as efficient as required to navigate, create/maintain records and process transactions within an Evolution company. The possibility therefore arises that DB corruptions could be caused as a result.
Therefore, to prevent any unplanned and unexpected power outages, consider implementing a high-quality UPS (Uninterrupted Power Supply) system on your network.
4. Incorrect shutting down procedures
This may be an obvious reason but always ensure that the Evolution server and workstation PC’s are always shut down in the recommended way.
Simply turning off the PC may cause damage to the Windows operating system which in turn could cause possible data corruption issues later.
Also, to prevent unnecessary memory build up and thus causing slowness, latency issues, it is advised to restart/shut down your Evolution PC’s regularly.
5. Computer Viruses and Malware
There are a countless host of PC viruses that may negatively impact your company data.
You should always take the most stringent measures to protect your entire business network against such threats.
These measures should include a combination of the following, among others (as implemented with the direct assistance of your local IT technician):
- Establishing and properly enforcing strict domain policies across the entire network.
- Making use of reputable anti-virus software and ensuring the anti-virus software is regularly updated.
- Making use of and properly maintaining domain network Firewalls.
- Prohibiting access to web sites that may pose the risk of inadvertently downloading viruses, malware and /or other threats such as spyware etc.
6. Invalid bulk DB Imports via MS SQL / External Applications
Sage Evolution company databases allow for both the manual direct bulk import of new records, as well as company data integrations from third party applications using such bridging platforms as SDK and API.
However, extreme care and proper testing within the front end of Evolution should always be considered whenever new records are imported and/or a new SDK/API integration setup is established.
In fact, it is recommended to first establish a new SDK/API setup in a testing environment. This means restoring the live Evolution company DB as DEMO and then setting up the integration within the DEMO company. You can then perform various tests and regularly check the integrity of the Evolution company data as imported/integrated.
Only when completely satisfied with the results should you then set up the integration on the live environment.
7. Corrupted Sage Evolution System Files
As with any kind of software, Evolution’s system files may also become corrupted. The problem here is that one may never even know if any files are corrupted but if suspected it is recommended to simply uninstall and re-install on the affected workstations and/or Evolution server PC as relevant.
8. Recommended Regional Settings
Always ensure your Evolution server and all workstations follow the recommended local PC’s regional settings. Below is a link to a knowledgebase article on setting up recommended regional settings.
For example:
A typical Customers/Suppliers Allocations error/issue can be caused by the decimal symbol on the local PC’s Regional Settings screen setup as a comma (,) instead of the recommended full stop (.).
Within the POSTAR/POSTAP table the cAllocs field may therefore have a value of e.g. I=235098203458;A=800,00 instead of the I=235098203458;A=800.00
9. Invalid /’Illegal’ DB manipulations
Especially when company data fixes need to be performed the risk exists that even further data corruption can occur if the data fix is not done properly.
In addition, the same is also true when certain bulk changes are done by directly manipulating company data using MS SQL Management Studio (or other DB interfaces) to access the company DB.
Therefore, please always consult with your local Evolution Business Partner or Evolution Support if company data needs to be fixed or bulk changed.
Always backup your company in all cases before any of the above actions are performed.
10. Hardware related failures
Failures of certain hardware components such as the hard drive, mother board etc. may also contribute towards data corruption in some instances.
Most PC’s come with a two or three year warranty which is generally the expected life span of a computer. But you should still regularly maintain your PC’s from day one and especially after the warranty has expired. A well maintained, hardware up-to-date (e.g. RAM additions) PC may still serve you well even after the official warranty date has expired.
Also consider acquiring extended PC warranties. When anything goes wrong (e.g. any type of hardware failures), you should be able to immediately and easily get it repaired at no cost/low cost.
Finally, also ensure that your hardware drivers are always up to date.
11. Insufficient hardware performance
In some extreme cases data corruption may be a possibility if your Evolution server and/or workstations don’t adhere to the Sage minimum required specifications.
Please also note that as Sage Evolution’s minimum system requirements increase over the years, you may consider purchasing new PC’s to meet these demands if it becomes further impossible to upgrade certain PC’s RAM or motherboard (due to internal PC upgrade limitations).
For the current minimum required PC specifications, open the following link:
https://www.sage.com/en-za/products/sage-200-evolution/
In here find and open the link called Sage 200 Evolution System Requirements
Else, open this link to get access to the system requirements.
12. Unstable or incorrectly setup networks
Ensure that your Evolution local network infrastructure is up to date, stable and well maintained.
This includes the entire network spectrum from network cabling, routers/switches/hubs, network cards, domain controller policies on network traffic, etc.
Network failures at the same critical time of Evolution processing or updating may cause data packets to be lost/corrupted and therefore causing general data corruptions.
13. Using Evolution on a wireless network
Please note the following with regards to using Evolution on a wireless network:
From our experience we have noted that wireless networks seem to not be as reliable as fixed line networks. This is because wireless networks are simply not as stable, reliable with a constant, strong signal strength as fixed line networks. Network dips are a potential risk factor for data corruption issues.
14. MS Windows operating system (OS) errors/issues
The Windows OS on the Evolution server and workstations should always be in a healthy state and not result in any errors or issues. Else, data corruption cases may also occur in some instances.
E. WHAT SHOULD NOT BE REGARDED AS DB CORRUPTION / INCONSISTENCIES CASES
Note that not all cases of data inconsistencies can be necessarily regarded as a form of data corruption and a clear distinction should be made here to properly address and prevent such issues from occurring.
Such examples may include, amongst others:
- Incorrect posting of GL accounts – this may also be caused by an incorrect setup of Transaction Types and/or Groups (e.g. Inventory Group). For instance, the INV transaction type has been setup to Credit the Wastage account instead of the Sales account.
- Incorrect information on reports or screens as this may be caused by not applying the correct report filters. In addition, you may also correct it by refreshing the company’s DB Views and / or deleting the relevant Registry Editor key for the local agent running the report.
F. ADDITIONAL ACTIONS AND RESOURCES TO FIX AND MANAGE DB CORRUPTION / INCONSISTENCIES CASES
Also consider the following list of resources (not mentioned above already) to prevent, fix and or at least being alerted of Evolution company’s data corruption / inconsistencies
1. Make regular backups in case severe data corruption has occurred. In some cases a data fix may not always be a cost effective/viable solution. Having a backup that can be restored will always prove invaluable.
2. Your Sage Evolution Business Partner (BP) or Sage Evolution Support if your BP is unavailable to fix the data corruption.
3. Employing the services of a qualified Database Administrator who should implement critical DB maintenance control and maintenance routines.
4. Running a DBCC CHECK DB SQL script command on the company DB to check for inconsistencies (preferably done by your Evolution BP).
5. Running a generic re-Indexing on the company DB. Open the link below for a detailed article on how to perform a re-indexing.
6. Performing monthly recons between the company’s ledger/subledger (e.g. between the Payables Control Account/s- and the Receivables Age Analysis report balances) and immediately take action when any inconsistencies are detected. You simply can’t afford to wait until the end of the financial year to run these recons as your company may have been corrupted to a far greater extent.
7. Running Relinks
After backing up the company data and ensuring no other users are logged into the company, run certain relinks such as the GL Relink, Inventory Relink, Order Relink, etc. in the Evolution company whenever any PC (server or workstation).
This is especially important if certain company data anomalies have been detected (e.g. Trial Balance report not in balance).
After the relinks are completed and the above problem has been resolved, backup the company and keep the backup file in a safe place away from the server. Making an extra backup in general at month end is also useful.