Problem
For better or for worse, the most common tool for performing database maintenance in SQL Server 2000 is the Database Maintenance Plan Wizard, as opposed to the maintenance T-SQL commands. The Database Maintenance Plan Wizard’s point and click interface enables backups, DBCC’s, UPDATE STATISTICS, etc. to be configured quickly in order to be executed on a regular schedule. So how is this accomplished in SQL Server 2005?
Solution
With SQL Server 2005 the new GUI tool to perform database maintenance is a scaled down version of SQL Server Integration Services (SSIS). So when transitioning from SQL Server 2000 to 2005 it is necessary to spend some time getting familiar with the SSIS tools to continue to perform or enhance the SQL Server 2000 capabilities. Below we will walk through the examples to transition between the two platforms.
SQL Server 2000 – Database Maintenance Plan Wizard
| ID | Directions | Screen Shot |
| 1 | Access the Database Maintenance Plan Wizard – Open SQL Server 2000 Enterprise Manager and navigate to root | Server Name | Management | Database Maintenance Plans. Then right click in the right pane and select the ‘New Maintenance Plan…’ to start the wizard. Press the ‘Next’ button to begin the process. | 
|
| 2 | Select Databases – Determine the databases that you would like to include in the plan. *** NOTE *** – This wizard can be run and saved for other databases and have different configurations. Just be sure to not have conflicts in the configurations. | 
|
| 3 | Update Data Optimization Information – Configure the index and shrink parameters with the recurring schedule. |  |
| 4 | Database Integrity Check – Configure the database integrity configurations and recurring schedule. |  |
| 5 | Specify the Database Backup Plan – Configure the backups as a portion of the maintenance process and the backup location. |  |
| 6 | Specify Backup Disk Directory – Configure the backup directory, file extension and retention period. |  |
| 7 | Specify the Transaction Log Backup Plan – Determine if the transaction log should be a portion of the maintenance plan, the location and schedule. |  |
| 8 | Specify Transaction Log Backup Disk Directory – Specify the backup directory, file extension and retention period. |  |
| 9 | Reports to Generate – Configurations for the output report for the maintenance process. |  |
| 10 | Maintenance Plan History – Determine if this plan’s history should be retained and the number of corresponding rows. |  |
| 11 | Completing the Database Maintenance Plan Wizard – Review the final configurations for the wizard and press the ‘Finish’ button to complete the process. If you are unsatisfied with the configurations, press the ‘Back’ button to find the appropriate screen and change the parameters. |  |
| 12 | Review in SQL Server 2000 Enterprise Manager – The Database Maintenance Plan Wizard will bring you back to the original folder in Enterprise Manager. If you need to modify the plan in the future, just double click on it to access the configurations. |  |
SQL Server 2005 – SQL Server Integration Services
| ID | Directions | Screen Shot |
| 1 | Starting SSIS’s Maintenance Interface – To begin creating an SSIS Package to support your maintenance needs, open SQL Server 2005 Management Studio and login to the ‘Database Engine’. Navigate to root | Management | Maintenance Plans. Then right click on the ‘Maintenance Plans’ folder and select the ‘New Maintenance Plan’ option. Name the maintenance plan and the SSIS interface will load with a blank interface. *** NOTE *** – You can have multiple SSIS Packages scheduled at different intervals to meet your needs. |  |
| 2 | Job Schedule Properties – Configure the recurring schedule for the SSIS Package. |  |
| 3 | Manage Connections – Review the default connection and via the ‘Add’ button, add additional connections to the SSIS Package. |  |
| 4 | Reporting and Logging – Configure the SSIS Package logging parameters. |  |
| 5 | Back up Database Task – Configure Full or Differential backups for specified or all databases to be written to a sub directory. |  |
| 6 | Check Database Integrity Task – Verify the database and index integrity. |  |
| 7 | Rebuild Index Task – Rebuild and reorganize the indexes for all tables in the user defined databases. |  |
| 8 | Shrink Database Task – Shrink the databases and return the freed space to Windows. |  |
| 9 | Update Statistics Task – Update the statistics for all tables in the user defined databases. |  |
| 10 | Final SSIS Package – Review the final SSIS Package with all tasks executing based on a successful status. |  |
Next Steps
- As you begin to transition from SQL Server 2000 to 2005, keep in mind that the database maintenance will be migrating from a point and click interface to a feature rich development environment.
- Be sure to spend time learning SSIS not only for the many new features to support business intelligence (BI) projects, but also to automate DBA related tasks.
- If you would like to learn about the underlying T-SQL commands to perform SQL Server 2000 maintenance check out the SQL Server – Performing maintenance tasks tip. Stay tuned for the related SQL Server 2005 tip in the coming weeks.
- Check out the latest SQL Server maintenance tips on MSSQLTips.com.
- Check out the latest SQL Server SSIS tips on MSSQLTips.com.
- Stay tuned for more tips related to constraints, error handling, logging, disabling tasks, troubleshooting SSIS maintenance packages in some upcoming MSSQLTips.com tips.
