Thursday, August 03, 2006

New Microsoft SQL Server Video Tutorial: Pt 4 - Modifying Maintenance Plans

Pt 4 - Modifying Maintenance Plans - view details
One of the most exciting improvements to Maintenance Plans in SQL Server 2005 is the SSIS-based interface that you get when you modify the Maintenance Plans. This interface will allow you to create very intricate Maintenance Plans that go well beyond the wizard's basic functionality. You can add Operator notifications (i.e., send an email to an Operator whenever a Maintenance Plan task fails), add complex error handling or any type of auditing/SQL/etc.

This video is just an overview of how to modify the Maintenance Plans along with a quick walk-through of the drag-and-drop features of the SSIS interface. It does not go too deep, however, since you need some familiarity with SSIS to really expose the more interesting/flexible parts of the Maintenance Plan.

One suggestion: watch this series in conjunction with some of our introductory videos on SSIS to get the most out of your Maintenance Plans.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:38 AM   0 comments  

New Microsoft SQL Server Video Tutorial: SET Commands for Performance Tuning and Query Execution Statistics

SET Commands for Performance Tuning and Query Execution Statistics - view details
SET Commands for Performance Tuning and Query Execution Statistics
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:37 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt 2 - Index-Related Tasks in SQL Server 2005 (the Rebuild Index, Reorganize Index, and Update Statistics options)

Pt 2 - Index-Related Tasks in SQL Server 2005 (the Rebuild Index, Reorganize Index, and Update Statistics options) - view details
Continuing our discussion on SQL Server 2005 Maintenance Plan tasks in the wizard, this video gives a nice overview of the index-related tasks, the "Rebuild Index", "Reorganize Index" and the "Update Statistics" tasks.

Scott Whigham, author of LearnSqlServer.com, walks you through a simple-to-understand discussion of how index fragmentation works, how data-distribution statistics are calculated and why tasks such as the Rebuild Index tasks are vitally important to the health of your SQL Server. Included in this video is a discussion of online defragmentation vs. offline defragmentation and a quick overview of the new ALTER INDEX ... WITH REBUILD or WITH REORGANIZE (instead of the DBCC DBREINDEX and DBCC INDEXDEFRAG).

Also included in this video is a discussion of how often you should run these tasks along with the impact that each task will have on your server and users. You’ll also understand why fragmentation occurs and how these tasks can help your database run faster.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:37 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Scoping of SET Commands - Connections, Stored Procedures, Batches and Functions

Scoping of SET Commands - Connections, Stored Procedures, Batches and Functions - view details
Scoping of SET Commands - Connections, Stored Procedures, Batches and Functions
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:36 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt 3 - The Cleanup History, Execute SQL Server Agent job, and Back Up Database Tasks

Pt 3 - The Cleanup History, Execute SQL Server Agent job, and Back Up Database Tasks - view details
This video finishes off the discussion on the individual tasks and ties on with Parts 1 and 2 in this series as the final video on choosing the tasks in the Maintenance Plan Wizard. After watching this video, you'll understand the intricacies of the Cleanup History Task, the SQL Server Agent job Task, and the individual Back Up Database tasks (the Full, Differential, and the Transaction Log).

Included in this video are discussions on how often you should run the Cleanup History Task and how the SQL Server Agent job Task can work in conjunction with your existing jobs. This is definitely one of the best improvements to the Maintenance Plan architecture and allows your maintenance plans to be extensible beyond anything that was available in the SQL Server 7.0 or 2000 Database Maintenance Plans.

Of course, the main features of the Maintenance Plans that most people will use are the Back up Database tasks. This gives us the option for scheduling our full backups, differentials and transaction log backups. Job Schedules for maintaining backups are included and you'll see how the text file reports are generated as well as how the Maintenance Plan Wizard can create separate files for each backup with a time-stamp (DatabaseName_yyyymmddhhmmss.bak, for example).
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:20 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt 1- Introduction to SQL Server 2005's Integration Services

Pt 1- Introduction to SQL Server 2005's Integration Services - view details
If you've been confused, mystified, or downright ornery about SQL Server 2005's Integration Services, you're not alone. Everything has changed so drastically from the "old" DTS days that much of our previous knowledge is passé. This video is a jumping-off point, a first look, at SSIS and some of the basic architectural changes including using the Business Intelligence Design Studio (often referred to as the BI Studio) and how the .dtsx files work within a project/solution. This video doesn't feature any package creation nor talk about the individual tasks; it's really more about how things have changed, what the new tools are, and is really designed to help you get comfortable with SSIS.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 10:20 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt 4 - How to Schedule an SSIS Package (Introduction)

Pt 4 - How to Schedule an SSIS Package (Introduction) - view details
Our fourth video in this introductory series designed to help you perform the most basic SSIS tasks, this video focuses on how to schedule an SSIS package with a SQL Server Agent job. One of the new features of the SQL Server Agent job scheduling engine is the new SSIS Package Execution job step. There are many options for scheduling SSIS packages and this video shows you the most logical/simplest technique for storage and scheduling (using an SSIS package stored in the msdb).

Many people find package scheduling to be a very confusing topic - let this video help you schedule your SSIS packages!
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 9:39 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt 3 - Introduction to SSIS Package Deployment

Pt 3 - Introduction to SSIS Package Deployment - view details
How do you get an SSIS package "into" SQL Server 2005? Give up? It is quite confusing and one of the more common questions people have as they start looking at SSIS for the first time. If you want to schedule an SSIS package, there are several options but perhaps the easiest and most manageable technique is to (1) store the SSIS package in MSDB, and (2) schedule a job to execute the package. This video walks you through creating a "Deployment Utility" which is really just a euphemism for an XML file with a .SSISDeploymentManifest extension. If you double-click on the .SSISDeploymentManifest file, it launches the Package Installation Wizard that allows you to easily deploy an entire project's worth of SSIS packages. This is not the only technique but definitely the easiest way to deploy a series of related SSIS packages.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 9:25 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt 2 - Creating Your First SSIS Packages (Integration Services Packages)

Pt 2 - Creating Your First SSIS Packages (Integration Services Packages) - view details
The second video in our introduction series on Integration Services focuses on creating, importing, and adding packages in the BI Studio (more formally known as the SQL Server Business Intelligence Design Studio). This is an introductory-level video designed as a "first look" into SSIS packages and the BI Studio. Many people get very confused with the basic concepts of the SSIS architecture and this video helps allay some of the intimidation. Scott Whigham walks you through finding the SSIS Package Store, how the file system works within SSIS, and more information about creating packages.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 9:19 AM   0 comments  

New Microsoft SQL Server Video Tutorial: Pt 1 - Introduction to SQL Server 2005 Maintenance Plans

Pt 1 - Introduction to SQL Server 2005 Maintenance Plans - view details
Maintenance Plans (and the Maintenance Plan Wizard) are not new in SQL Server; Microsoft introduced Maintenance Plans in SQL Server 7.0 as a great way to automatically schedule common DBA tasks such as backups. In SQL Server 2005, Microsoft has really listened to its customers and vastly improved the Maintenance Plans (and wizard). If you've used Maintenance Plans in the past, things have changed considerably but for the good.

This video walks you through (1) what maintenance plans are, (2) launching the Maintenance Plan Wizard, and the basic choices of the wizard. There are so many choices in the Maintenance Plan Wizard that this video can only cover so many; the remaining videos of the series will help you walk through all of the choices in the maintenance plans. This particular video focuses on the "Check Database Integrity" choice including a walk-through of DBCC CHECKDB and what impact selecting this option may have on your server and a discussion on how often you'll want to schedule this option.

Another option covered in this video is the "Shrink Database" task along with Scott Whigham's discussion of Best Practices for shrinking databases.
To download this video, log in, then visit the video's detail page and click "download". This is a subscriber-only video so a subscription is required

Note: You must have the TSCC codec installed to view this video

posted by Scott Whigham @ 9:18 AM   0 comments