Many times users are working in an environment with multiple SQL Servers that have the number of databases on each server. All of a sudden, they find that the integrity check failed on a single database. Users may assume that nothing has been outlined in the environment, so they have no idea who is end user of a particular database, or what is the priority level of data. What type of things users should keep in mind if this occurs, and ways to alleviate the confusion that may come when they need to make a quick decision? Therefore, in this blog, we are going to discuss SQL Server integrity check best practice in an absolute way.
How to Manually Check Data Integrity in SQL Server?
Unfortunately, environment with multiple instances of SQL server running may overlook integrity checks on the regular basis, and running DBCC CHECKDB command is not an option if users want to prevent data loss. If users are not running them properly, then they must have to run consistency checks. The Integrity check on SQL Server environment is categorized into different priority level.
Categorized into High, Medium, Low Priority Databases, let us look at different priority scenarios which depends on the importance that will be helpful for the users.
- High
If a database holds data for an important business that generates big revenue or may result in higher costs due to loss of data, increase the frequency that will run an integrity check, even if that frequency on a daily basis. In all these environments, users have no single point of failure, and when they are talking about high revenue or cost, one such solution is an Active-Active environment, where one can be "taken down" for maintenance while others are running and vice versa. The major need of this environment is that data loss is simply unacceptable, as the high cost of data. - Medium
If a database holds data that is a high priority, but a small fraction of data loss is acceptable like monthly payment detail that is always the same amount depending on the schedule. Moreover, the business may accept losing the data value or two because it may be deduced, or might be worst, it simply means they will credit a new customer with payment, even if a customer will not make it. So to minimize the loss of data, run an integrity check before taking back up of full database. Keep the SQL database in recovery mode with transaction log backups, and also considers the set up availability groups using AlwaysOn. According to this scenario, users can lose a vary small amount of data as possible in worst case, but they know if they lose little data, the cost can be in range that the business can easily accept. - Low
All databases can hold low priority and data loss is more widely acceptable. This might be data from ETL process that will be re-loaded with minimal time, data stored on the paper that can also be saved to the database and can be re-loaded quickly. For example, the data for marketing to a new client where full database can save potential clients: in few companies, these data can be re-loaded via same process that can identify all these clients; for some other companies, this might be a high or medium priority. The recovery plan is usually re-load, or in certain cases, data loss is acceptable. In these environments, most likely restoring the previous backup, or using repair and the data loss can be accepted. Be sure that you verify this with an end user (or users) and do not assume this without any other evidence.
KEY POINT
A disaster waiting to happen when you introduce to an environment as a business team who treats the database as less important than it is. It is important for users to know how much database is important for each server, before experiencing the integrity check failure. However, it will rarely occur and more often than not, it will start after fact discovery. For an instance, users can view the database as on the lower priority list depending upon how it is designed or discussed, only to find out later that it is a high priority database where accepting no data loss. Similarly, users may bring a solution to move the entire database to an architecture where no single points of failure due to their priorities, yet the business delay on this. In some cases, users may want to exit the company or contract with a client as the disaster comes even if they warned about the disaster that would be happened.
Solutions for Priority Databases according to the Environment:
- High
In large environments with high priority databases in which revenue model is in billions. Most of the people refuse to accept a design that is not Active-Active and companies or clients try to find out another DBA or Architecture if they will not support this model. A company which makes billion dollars from the database and will pay the cost to run at least 2 active instances where every report and process can be pointed to the current active one. Also, it is required for maintenance like checking the integrity. Since everyone can run maintenance on one while other databases may use the application, then flip the switch when they try to run maintenance on other. - Medium
In case of medium priority databases, users can use some techniques to first sight if they can pass CHECKDB command without any risk or data loss. These involve, but are not limited to:- Restoring the database with most recent log file and confirming CHECKDB on another server. The main purpose is to pinpoint the failure Windows
- Drop the non-clustered indexes and also re-create them (relative to CHECKDB error)
- Low
In case of the low priority databases, users will prefer above methods in the medium priority category, such as re-creating all the non-clustered indexes. However, in this case, data loss is permissible for a low priority database.
Important Points When Facing Problems
- Do not keep your backups on a single location. You should expect to lose at least one destination location, or run into corrupt backups now and then.
- You have to generate a regular restore schedule for all your backups. This is very easy in Active-Active environment, but it can apply to the medium priority environment too.
- Never assume that the backup will work, even if it may pass RESTOREVERIFYONLY backup. Only the restore and running CHECKDB command verifies the validity of the database.
- Over communicate with an end user or client when this exists. You need to be clear before this probelm, during this issue, and when you are correcting this problem Communication that builds trust.
Conclusion
At times, users may become fail to check the integrity of a single database. Therefore, in this blog, we have covered step-by-step procedure to check the data integrity in SQL Server. Make sure you follow each and every step very carefully to know the SQL Server integrity check best practices in a better way.
EmoticonEmoticon