Know the Importance of SQL Server Integrity Check

Do you have an environment having multiple databases on the individual server? Unfortunately, all of a sudden the integrity check failed on any of your databases? Do you want to know the process to overcome such circumstance on the basis of the priority level? In this write-up, we will focus exclusively on the best practices while implementing SQL Server Integrity check.  Well, DBCC ensures to report any integrity issues that are related to the database. Moreover, the issues are then further noted by the database owner or the system administrator.

Let’s follow a list of different environment comprising of varied priority level based on the database.

For High Priority Database

If in case the database holds the most crucial data in the organization, which overall improves the revenue of the organization. Suppose if an organization storing bitcoin trades which loses a trade of 5000 bitcoins due to corruption in the files. In such case, the individual cannot afford the lose of 5000 bitcoins. Unluckily, some of the valuable data were lost after the integrity check failed. However, implementing any preventive measures would have avoided and prevented such drastic failures.

Solution

Under such instance, it is suggested to increase the frequency of the integrity check. This is because, the data cannot be compromised for data loss or data corruption by means of any technical failures. Moreover, an Active-Active environment can be utilized where one will be brought down for maintenance while the other is running and vice versa. To be more precise, one can perform the maintenance of the database by checking the integrity while the other is the database which the application uses. After all, the company cannot afford to lose any row of data with its high-level costs which pays to the organization.

For Medium Priority Database

If the database consists of data in which the priority level is high, but at the same time a small amount of data loss can be managed where the data can be categorized into medium-level priority. This data can be monthly payment wherein almost the same amount is scheduled every month. Even if the data is missed or lost, the organization can credit the payment if the customer fails to make it. Under medium priority level, a small amount of data loss can be afforded as the business can accept the price that is in low range. Therefore, to lower the risk of data loss, before creating the backup of the database, it is wise to run the integrity checker. Hence, keep the databases in full recovery mode with the recent log backups and align the available groups with AlwaysOn model.

Solution

For medium priority databases, try to follow the below listed techniques to check whether CHECKDB can be used without leading to any data failures.

• With the help of the recent transaction log file, restore the database and verify CHECKDB on another server

• For all the non-clustered indexes, drop all of them and re-create the same. This will more or less help to fix CHECKDB error

• Based on the results of integrity check for the tables, one can drop the table and re-create it via backup and restore

For Low Priority Databases

For low priority databases, loss of data by any means is acceptable. This can be data from an ETL process, which can be reloaded in a minimum duration of time. For instance, marketing data that holds the data of new clients where the database stores the valuable clients. For certain companies, the priority for such data can be low, medium or high. However, the recovery plan is the re-load process or in worst case the data loss is acceptable. Moreover, the data can be restored using the old backup process or try to repair and afford the data loss if any.

Solution

While considering the low priority database, one can implement the criteria which we applied for medium-level priority i.e., re-creating non-clustered indexes. Moreover, data loss is been accepted in this priority level of database. Apart from that, there are couple of important points which is to be considered when such problem is encountered. This includes like do not keep the backup in the single location, generate regular restore agenda for the backups which is easy for Active-Active environment. Moreover, only the restore process and executing the CHECKDB command will verify the database validity. Additionally, it is always important to communicate with the end users or the clients whenever any failures occur.

Final Verdict

In this blog, we have described the importance of SQL Server Integrity check. In addition to that, this write-up helps users understand the actions to be carried out based on the priority level of the database.