Compared to the SQLMaint black box, the new interface will be easier to understand and less intimidating for DBAs. Therefore, database maintenance will be
That is not to say that DBAs following sophisticated maintenance regimens should abandon them; they should just leverage SSIS' new functionality to simplify the maintenance process. I wholeheartedly praise database administrators who have built such maintenance plans, but I have also worked with DBAs who have no procedures in place. I believe the SSIS interface offers a key stepping stone toward performing regularly scheduled maintenance. In turn, this plan should ensure that overall SQL Server performance is at a consistently high level to suit the organization.
How can I access the database maintenance features?
1. Open the SQL Server 2005 Management Studio
2. Navigate to the Management folder
3. Drill down to the Maintenance Plans folder
4. Right click on the Maintenance Plans folder and select New Maintenance Plan
What does the basic interface look like?
1. Management Studio Menus and Tool Bars -- Top portion
2. Object Explorer -- Top left section
3. Maintenance Plan Toolbox -- Bottom left portion
4. General SSIS package information -- Top right
5. SSIS workspace -- Bottom right
SQL Server Management Studio -- Maintenance Plan
What are the SSIS database maintenance features that are natively incorporated?
Reference the toolbox on the bottom portion of the interface for all of the objects that can be leveraged natively. These items can be dragged and dropped from the toolbox to the workspace. By double clicking on the object in the workspace, you can configure the object to meet the specific maintenance needs.
Maintenance Plan Toolbox
Can more than one SSIS database maintenance package be created?
Yes -- SSIS packages can be set up for different databases, tasks, time periods, etc., and they can be 100% independent of the other SSIS packages on the same SQL Server.
How are the SSIS database maintenance package scheduled?
In Management Studio, navigate to the Maintenance Plans. Double click on the specific plan that needs to be scheduled. Click on the ellipse across from the Schedule text. Then the screen on the left will appear, which can be used to set up the schedule that SQL Server Agent will use to manage the SSIS package.
Job Schedule Properties
How is the database maintenance SSIS package configured?
Once in SSIS, a blank workspace is available to begin to drag and drop toolbox tasks.
SSIS workspace
Once a task is in the workspace, double click on the task to configure the individual parameters via the drop-down boxes. You can also click on the View T-SQL button to see the underlying commands that are used, which gives you a better understanding of the internal processes.
Configuring task parameters
As additional servers are used, it is necessary to click on the Connections button from the main workspace to create these connection objects. The Manage Connections interface on the left can be used to set up, edit or remove connections to SQL Server.
Creating connections objects
One final step before scheduling the Maintenance Plan is to configure the Reporting and Logging interface. To access this interface, click the Logging icon from the main workspace.
The interface can be used to enable logging to a text file for review and for e-mailing the team the overall maintenance plan status.
Configuring Reporting and Logging interface
How can existing T-SQL scripts be incorporated?
If you have an existing T-SQL script or stored procedure that you want to incorporate into your SSIS package, you are in luck. Simply drag and drop the Execute T-SQL Statement Task onto the workspace. Then copy and paste your code into the object and save it.
Incorporating existing scripts in SSIS package
Once this task has been configured, add the precedence constraint to ensure the task is called in the correct order. Precedence can be established between an existing task and the new object. To set precedence, double click on the precedence constraint in the workspace. The editor on the left will be displayed, which can establish the relationship based on success, failure or completion, and on constraint or expression operations.
Ensuring task is called in correct order
Do all of the versions of SQL Server 2005 have the SSIS database maintenance feature?
Natively, only the Standard and Enterprise editions support SSIS and the accompanying database maintenance plan features. The Workgroup and Express editions do not, but the underlying T-SQL commands can be issued to perform maintenance.
What is driving the SSIS package?
SSIS is dependent on the SQL Server Integration Services Windows Service that is responsible for processing the Maintenance Plans and all custom-developed SSIS packages.
SSIS has caught the eye of many individuals. Feel free to check out these books that are currently available:
If anything can bring excitement to database maintenance it is SSIS. The improvements from SQL Server 2000 to 2005 are very beneficial just from a pure ETL perspective. With the extension of SSIS to database maintenance, the point-and-click capabilities are vastly improved and will hopefully ease the maintenance burden.
DTS vs. SSIS: Then and now
