Tip

SQL Server Integration Services to simplify database maintenance

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

    Requires Free Membership to View

Get more on SSIS in SQL Server 2005:

 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
   DTS vs. SSIS: Then and now
   SQL Server 2005 maintenance questions
   Additional SSIS resources

 
DTS vs. SSIS: Then and now

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)

SQL Server 2005 Extraction, Transformation and Loading (ETL)

SQL Server Integration Services (SSIS)

SQL Server 2000 point-and-click database maintenance

Database Maintenance Plans via SQLMaint

SQL Server 2005 point-and-click database maintenance

Management Studio's Maintenance Wizard


 
SQL Server 2005 maintenance questions

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.
 
Additional SSIS resources

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.

This was first published in February 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.