Today, I will be continuing the series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks. We will be designing a SSIS package that checks the database integrity of all the user databases.
I am going to start up SQL Server Data Tools (SSDT) which leverages the Visual Studio 2010 shell. This development environment replace the older Business Intelligence Development Studio (BIDS) which was released with SQL Server 2005.
I will create a project named “check-database-integrity” under the “c:\ssis-depot\” directory. Upon having a blank package, we will be adding and configuring several objects. You can either craft this package from scratch or use the “full-user-backup” package as a starting point.
I am going to list each of the objects we will need to solve this business problem.
cmMySqlServer – The local database server connection information.
cmMySmtpServer – The local mail server connection information.
tskCheckDbIntegrity – This is a control flow task based on DBCC CHECKDB.
tskEmailSuccess – Notify email list that the package completed successfully.
tskEmailFailure – Notify email list that the package failed miserably.
The Check Database Integrity task is the only new object on the control flow canvas. Let’s take a closer look at the properties that can be configured.
You will need to select the database connection in which the task will be performed on. Do not forget to include indexes. Otherwise, all index pages in the database will be skipped during the check since they can be rebuilt.
By clicking the database drop down list, we have the ability to specify user, system or individual databases to be checked. Also, we can tell the package to skip databases that are not on line.
After you have your package looking like mine, you can execute (debug) it in the environment. Hopefully it runs to completion without any errors.
I just noticed a success email that was sent to my email account and it appear in MS Outlook application.
I wonder what the SSIS package is actually doing to perform the backup? To discover what T-SQL is being sent to the server, we can open a client/server side trace using SQL Server Profiler. I am going to skip over the mechanics of starting a profiler trace and running the SSIS package in debug mode.
The whole trace file (“check-database-integrity.trc”) along with the SSIS package (“check-database-integrity.dtsx”) are included as a download bundle at the end of the article.
Basically, there are two distinct TSQL commands: one to include index checking and one to skip index checking.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- With indexes USE [MATH] GO DBCC CHECKDB(N'MATH') WITH NO_INFOMSGS GO -- Without indexes USE [MATH] GO DBCC CHECKDB(N'MATH', NOINDEX) GO |
In conclusion, the Check Database Integrity task is calling our old friend DBCC CHECKDB under the covers. I have no issues with scheduling the enclosed package to check the health of my databases in the future. Next time, I will be talking about reorganizing and rebuild indexes uses control flow tasks.
