Being a database administrator means that you wear many different hats. For those unfamiliar with this phrase, it means that you may do different things throughout the day that aren’t necessarily related. One thing will never change for a database administrator: the data is your life. Without it, you have no job. So for those of us working for a living, this is sort of important. Not only are we tasked with making sure that data coming in is sanitary, we also have to make sure that the data stays sanitary. This can be quite a daunting task, if not for database integrity.
What Is Database Integrity?
Database integrity is a concept that should be applied in the design phase of the database. The tables themselves determine whether there are rules to apply for inserting data into a table. These rules are going to cascade down to either a well-built database with integrity, or a poorly built database with little or no integrity.
Database integrity is an easy topic to understand. You want to be able to guarantee, as much as humanly possible, that this data is what it says it is. I have told my kids since they were little that the definition of integrity is “doing the right thing, even when no one is looking.” In this context, we, as database administrators, have to guarantee that the data entrusted to us is going to be protected and maintained as best as we know how. There is no better way to guarantee the integrity of data than to adopt a proactive approach to daily maintenance that includes the guidelines found in this book.
How does this apply to data? Well, think about the implications if a record were entered into a table with the same UID (unique identifier) as another record, and then querying the database for that record. You would not return the data you thought you would get. This is clearly poor database design. How could this happen? If the UID field weren’t defined as a unique key, that’s how. Again, poor design leads to poor integrity.
An interesting point about database integrity is that even poorly designed databases can guarantee the integrity of the data. Ever heard the saying that “even a broken clock is right twice a day”? Same thing here. Basically, the database will guarantee that it will return the wrong data, every time, as requested. Is this ideal? Obviously not.
Another aspect to database integrity is the structural integrity of the tables and the indexes. Over time, they can become corrupted. Having a disaster recovery plan is the best way around this, and an important part of that recovery plan is ensuring that the data in the recovery is complete and correct. This can only be accomplished with database integrity.
There seems to always be time, money, and resources to do the job over again later, but there is never enough time, money, or resources to do the job correctly the first time. Any job you do is worth doing well, so if you’re going to put your name on a project, make sure that you under-promise and over-deliver. In other words, your personal integrity (doing the right thing when no one is looking) leads to higher data integrity (quality/availability of data).
Tip
Take the time to do it right the first time instead of having to either do it over again or be forced to work with a poorly designed database.
Practical Application of Database Integrity Principles
Do you need to add this task to your maintenance plan? The short answer is yes. Let’s take a look and find out though. In SQL Server Management Studio, right-click your database name, go to Reports ➤ Standard Reports ➤ Database Consistency History . This starts a report that shows you the number of errors found and the number of errors repaired, as shown in Figure 3-1.
Figure 3-1. Database Consistency History Report with no data
If you don’t see any data in the report, open a new Query window and type the following:
DBCC CHECKDB([database_name]) WITH no_infomsgs Obviously, replace [database_name] with the name of your database. Press F5 to run the query. It will take a few seconds. Switch back to the report and click the Refresh icon. Bingo! You’ve got a record. Figure 3-2 shows what the interface looks like once a record has been added.
Figure 3-2. Database Consistency History Report with data
Tip
If you get a failure at this point, the most common cause is going to be because the physical database files (the MDF and LDF files) are located on a partition that is formatted with FAT and not NTFS. The database engine will fail trying to create a snapshot at this point, which is what it runs the DBCC commands against (instead of the actual database).
So… what does the record in Figure 3-2 mean? Let’s break it down and see. Figure 3-2 shows the data as recorded by the DBCC transaction. The following columns are returned:
Command Text: This column simply shows the SQL that was parsed and executed by the database engine. This should be exactly as written earlier.
Login Name: This is the name of the account that the Command Text was executed under. Different accounts have different permissions, so if the command failed, it could possibly be due to not having an account with permissions to run the DBCC command. In this event, you need to log in with an admin level account; otherwise, none of this will work.
Start Time: I wonder what this means…? Oh! This is the time that the query started execution. What’s curious is that there isn’t an End Time column. There is, however a Duration column, so it’s up to the DBA to do the time parsing.
# Errors: If you see a value here, you are in trouble. That means that something has started to go wrong; without further analysis, it will be impossible to determine what it is.
# Errors Repaired: If you see a value in the # Errors column, and you don’t see the same value in this column, you are about to have serious issues. This means that there are unresolved errors in the consistency of the data; this will definitely not be the last you see of the error.
Duration (hh:mm:ss): This is simply how long the query took to run. The database I ran this on is a brand-new database, so it was only around 8MB and it took 0 seconds for it to run.
As you can tell, this report is extremely useful in determining whether your database is “healthy.” Keeping a close eye on this report helps mitigate future issues through early detection. Even though this record returned 0 errors, that doesn’t mean that it doesn’t need to be added to the maintenance plan. On the contrary, the fact that we see 0 here means that database integrity has been achieved to the point that there are no reportable errors with the consistency of the data, which is a very good thing. Having database integrity as a part of any maintenance plan is an absolute necessity.
Let’s create a maintenance plan to cover database integrity now.
Setting Up the Maintenance Plan
Right-click Maintenance Plans and select Maintenance Plan Wizard, and then name it Database Integrity Plan. You can leave the radio button for the scheduling alone since there is only one task, but click the Change button to define the overall schedule, which is shown in Figure 3-3.
Figure 3-3. New Job Schedule
The schedule should reflect that we want the task to run once every hour, basically mirroring the schedule for the transaction log backups. Why is this? Because we’re running a backup, we don’t want to have to worry about coming up with data integrity issues hours after the issue comes up. We want to be aware as soon as possible; so to implement that, we run it after the transaction log backups. That way, any integrity issues get reported immediately.
Click OK on the New Job Schedule screen. You are returned to the Select Plan Properties screen, as shown in Figure 3-4.
Figure 3-4. Select Plan Properties
Click Next to continue. You should see the Select Maintenance Tasks screen, as shown in Figure 3-5. Choose the Check Database Integrity check box and then click Next.
Figure 3-5. Select Maintenance Tasks
You should see a screen titled Select Maintenance Task Order, as shown in Figure 3-6. Just click Next here since there is only one task.
Figure 3-6. Select Maintenance Task Order
Next, the Define Database Check Integrity Task interface appears, as shown in Figure 3-7. Choose your database from the drop-down menu, leave “Include indexes” selected, click OK, and then click Next.
Figure 3-7. Define Database Check Integrity Task
The next screen, shown in Figure 3-8, is Select Report Options. Leave the report option checked and enter the backups location again. Remember that we are writing our maintenance logs to this location. Click Next when you are ready to move on.
Figure 3-8. Select Report Options
That leads to the Summary section of this area, as shown in Figure 3-9.
Figure 3-9. Complete the Wizard
When you’re ready, click Finish. You should see what is shown in Figure 3-10.
Figure 3-10. Maintenance Plan Wizard Progress
Click Close when you’re done. Note that the Maintenance Plans area shown in Figure 3-11 now shows the Database Integrity Plan.
Figure 3-11. Maintenance Plans
You’re going to want to update the job, as defined in Chapter 1. Double-click the job name in the Jobs folder inside SQL Server Agent. Change the Name of the Job from Database Integrity Plan.Subplan_1 to Check Integrity and continue from there. Refer to Chapter 1 for the necessary steps. Once finished, your Jobs folder should look like what’s shown in Figure 3-12.
Figure 3-12. SQL Server Agent Jobs
Summary
Let’s sum up what we’ve learned in this chapter.
We learned about data integrity and the importance it carries.
We learned how to check our database for consistency using DBCC CHECKDB.
We learned how to set up the maintenance plan and update a job when the plan was completed.
In the coming chapters, we are going to get much more involved. Better get more coffee…