This 2 day hands on class will provide key information necessary to perform successful database upgrades from MS SQL Server 2000 to MS SQL Server 2005 including: how to resolve issues related to database engine changes, analysis services changes, DTS changes, SQL agent changes and redesign of SQL Server 2005 security. This course will also cover using the MS SQL Server 2005 Upgrade Advisor in detail.

Before attending this course, students should have:

• Administration experience with Microsoft SQL Server 2000 or Microsoft SQL Server 7.0
• Knowledge of SQL Server 2000 / 7.0
• Experience Creating a Database on SQL Server 2000 / 7.0
• Database and server architecture
• Database security

 

Module 1:

Introduction

Brief Overview of New Features in SQL 2005 (Reasons to Upgrade)

 

Need for Upgrade Advisor

 

Upgrade Advisor - Finding your way around.

 

Performing an analysis

            Choosing which type of Analysis to perform

            Steps to performing an analysis

            Using UpgradeAdvisorWizardCmd Utility

 

Practice: Performing an analysis

 

Viewing Results

            Launching the Report viewer

                  Understanding results & finding resolutions

            to issues

            Common issues and their resolutions

 

Practice: Viewing Upgrade Advisor Reports

 

 

Module 2:

Upgrading Database Engine Issues

            Full-text Indexing Issues and how to fix them

            Log Shipping Resolution

            Working with Binary Large Objects

            Restriction on username 'sys'

            Read-only file groups

            Auto-grow setting before upgrade

 

Lab 2: Fixing common DB Engine issues

        

 

 Module 3:        

Upgrading Analysis Services

Choosing one of the upgrade techniques

1) Setup.exe for in-place upgrade

2) Migration

3) Migration ++

   - Install a new instance

   - Import as project

   - Add new SQL Server 2005 features

4) Redesign from scratch

Changes with Virtual Cubes

New Attribute Architecture

Changes to Dimensions

Items not Migrated

 

 

Module 4:

Reporting Services Upgrade Issues

Decrypting ASP.net service account before upgrading

            Virtual directory setting issues

            Issues not detected by Upgrade Advisor

 

Lab 4: Preparing Reporting Services for Upgrade

 

 

Module 5:

SQL Server Agent Upgrade Issues

            Migrating Database Maintenance Plans

            Changes in who can write log files

            Changes to security

How upgrading handles existing job owners         

 

Lab 5: Migrating DB Maintenance Plans

 

 

Module 6:

Upgrading Security

            Understanding the new security model

            Design considerations for SQL 2005 security

            Steps to upgrade security from 2000 to 2005

 

Lab 6: Upgrading security

 

 

Module 7:

Fixing Issues with DTS Packages

            Changes to DTS (SSIS)

            Installation options for backward compatibility

            Package Scanning Issues

Fixing Tasks & Connections after upgrading a DTS package

            Fixing Transaction settings in packages

                       

Lab 3: Fixing common DTS Issues

Before attending this course, students should have:

• Administration experience with Microsoft SQL Server 2000 or Microsoft SQL Server 7.0
• Knowledge of SQL Server 2000 / 7.0
• Experience Creating a Database on SQL Server 2000 / 7.0
• Database and server architecture
• Database security