SQL Server took a leap forward in database maintenance functionality with the introduction of SSIS (SQL Server Integration Services) in SQL Server 2005. The SQL Server Development Team at Microsoft adapted SSIS to offer point-and-click database maintenance, offering vast improvements over DTS (Database Transformation Services) and SQLMaint in SQL Server 2000. The Database Maintenance Wizard still exists to build the initial process, but the modifications are all made to an SSIS package stored locally on the server.
Compared to the SQLMaint black box, the new interface will be easier to understand and less intimidating for DBAs. Therefore, database maintenance will be conducted on a more regular basis and overall performance will improve; a little SQL Server care and attention can go along way in improving performance.
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.
TABLE OF CONTENTS
[IMAGE] DTS vs. SSIS: Then and now
[IMAGE] SQL Server 2005 maintenance questions
[IMAGE] Additional SSIS resources
[IMAGE][IMAGE] DTS vs. SSIS: Then and now[IMAGE] Return to Table of Contents
As it pertains to database maintenance, Microsoft has maintained the same set of features but improved the underlying technology. The information below outlines some of those changes:
SQL Server 2000 Extraction, Transformation and Loading (ETL)
Data Transformation Services (DTS)
[IMAGE]
SQL Server 2005 Extraction, Transformation and Loading (ETL)
SQL Server Integration Services (SSIS)
[IMAGE]
SQL Server 2000 point-and-click database maintenance
Database Maintenance Plans via SQLMaint
[IMAGE]
SQL Server 2005 point-and-click database maintenance
Management Studio's Maintenance Wizard
[IMAGE]
[IMAGE][IMAGE] SQL Server ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
2005 maintenance questions[IMAGE] Return to Table of Contents
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
[IMAGE]
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.
[IMAGE]
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.
[IMAGE]
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.
[IMAGE]
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.
[IMAGE]
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.
[IMAGE]
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.
[IMAGE]
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.
[IMAGE]
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.
[IMAGE]
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.
[IMAGE][IMAGE] Additional SSIS resources[IMAGE] Return to Table of Contents
SSIS has caught the eye of many individuals. Feel free to check out these books that are currently available:
Wrox Press, Professional SQL Server 2005 Integration Services
Mann Publishing Group, The Rational Guide to Scripting SQL Server 2005 Integration Services Beta Preview
Sams Publishing, Microsoft SQL Server 2005 Integration Services
For more information, visit Microsoft's Integration Services Web site. Or download the latest version of SQL Server 2005 Books Online.
Conclusion
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.
About the author:
Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. Kadlec is the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
