Methodology for Upgrading to SQL Server 2005

(((I will need to format more later)))

Methodology for Upgrading to SQL Server 2005
Victor Isakov - Database Architect/Trainer
sql@informare.com.au
Make sure to use the following resources:

• Upgrade Advisor SQL Server 2005
• Books Online 4/06
• Transparent Benefits of Upgrading to SQL Server
• SQL Server 2005 Upgrade Technical Reference Guide

SP1 Include in Pre-Upgrade tasks to speed up upgrade process

Unattended Install
Managed by Template.ini
http://www.devx.com/dbzone/article/31648

Itentify Upgrade Requirements

• Online Processing (Enterprise Edition) DBCC

Run Upgrade Advosor
Pre-Upgrade Considerations

Determine Appropriate Upgrade Strategy

Upgrade Process

Post Upgrade Considerations

Make sure to capture a trace and baseline existing server before upgrade
Watch out for sp_OA running COM on SQL 2000.

Linked servers are not carried forward.

SPConfgure

In-Place vs Side-By-Side

• In-Place retains instance name
• Retains SQL 2000 functionality

Side-By-Side

• Does not maintain SQL2000 functionality
• Good means to perform test
• NOTE: Richard Buss currently only wants to do Side-By-Side

In-Place

• Make sure to install prereqs first.
• Install pre
• Turn off auto start services
• Check for upgrade blockers
• install 2005
• Stop Service
• Redirect services to new binaries
• Start service in single user mode
• attach resource db
• stop service
• retstart service
• start updating all db
• execute replication and sql agent upgrade scripts
• uinstall old binaries

D BCC CheckDB

DBCC CleanTable
DBCC ShrinkDataBase

DBCC ShrinkFile

DBCC DBREINDEX (DBCC SHOWCONTIG)

Verify backups

SQL 2005 Has a compatability level option (attached to a 2000 Northwind database. SQL automatically set it at a SQL 2000 Compatability Level)

Immediate Tasks:

• Decide whether to rollback if unsuccessfull

Verify Agent Jogs and maint tasks

DBCC CheckDB

DBCC CheckDB Data_Purity

Ensure surface area is adequate

Size TempDB correctly

Indexed Views??

• Definition of "determinism" has changed
• Custom Aggregations

Database Engine Tuning Advisor

Convert COM to CLR

Depricated

• xp_sendmail/SQLMail
• Text/NText/Image data types
• DTS Packages

Added

• Data Encryption
• Schemas

Update statistics to ensure performanceCurrently in training...