Microsoft SQL Server 2005 Upgrade Planning

1
Microsoft SQL Server 2005 Upgrade Planning
Kevin Ashby Microsoft EMEA kashby_at_microsoft.com 4
4 79 68 83 9696
2
Why Upgrade?

• Replication
• Seamless DDL replication
• Merge Web Sync
• Oracle Publication
• Peer to Peer Transactional replication
• Merge replication perf and scalability
• New monitor and improved UI
• Analysis Services and Data Mining
• Analysis Management Objects
• Windows Integrated Backup and Restore
• Web Service/XML for Analysis
• Integration Services and DM Integration
• Eight new Data Mining algorithms
• Auto Packaging and Deployment
• Migration Wizard
• Integration Services
• New high performance architecture
• Visual design and debugging environment
• Extensible with custom code and scripts

• Database Maintenance
• Backup and Restore Enhancements
• Checksum Integrity Checks
• Dedicated Administrator Connection
• Dynamic Configuration AWE
• Highly-available Upgrade
• Online Index Operations
• Online Restore
• Management Tools
• MDX XML/A Query Editor
• Maintenance Plan Designer
• Source Control Support
• Profiler access to non-sa
• SQLCMD Command Line Tool
• Database Mail
• Performance Tuning
• Profiling Analysis Services
• Exportable Showplan Deadlocks
• Profiler Enhancements

• Database Engine
• Service Broker
• HTTP Access
• Database Tuning Advisor
• Enhanced Read ahead scan
• Indexes with Included Columns
• Multiple Active Result Sets
• Persisted Computed Columns
• Try/Catch in T-SQL statements
• Common Table Expressions
• Server Events
• Snapshot Isolation Level
• Partitioning
• Synonyms
• Dynamic Management Views
• .NET Framework
• Common Language Runtime Integration
• CLR-based Types, Functions, Triggers
• SQL Server .NET Data Provider

3
Agenda

• Definitions
• What can be upgraded
• How to upgrade
• Upgrade Plan
• Pre-upgrade
• Upgrade Execution
• Post-upgrade
• In-place Upgrade vs. Migration
• Upgrading Log Shipping
• Upgrade Tips and Best Practices

4
Definitions

• Upgrade (or in-place upgrade)
• Updates an existing installation while preserving
  user data
• Instance name remains the same after upgrade
• Automated process
• Migration (or side-by-side migration)
• Starts with a new installation
• New old instance reside side-by-side
• Objects are copied from the old to new instance
• Mostly a manual process

5
In-place Upgrade
SQL Server 7.0/2000 Instance Foo
SQL Server 2005 Instance Foo
Upgrade

• Take users off the system
• Run Upgrade Advisor
• Identify/resolve problem/compatibility issues
• Run setup.exe and start the upgrade

6
Side-by-side Migration
SQL Server 7.0/2000 Instance Foo
SQL Server 2005 Instance Bar
Verified!
Remember to run the Upgrade Advisor
7
What can be upgraded or migrated?

• Versions
• SQL Server 7.0 (latest service pack)
• SQL Server 2000 (latest service pack)
• Components
• Database Engine
• Includes sub-components like SQL Agent,
  Full-text, Tools, etc.
• Analysis Services
• Reporting Services
• Notification Services
• Migration of Data Transformation Services to
  Integration Services
• Editions
• Desktop, Workgroup, Personal, Standard,
  Developer, Enterprise
• Platforms
• 32-bit 64-bit (IA64 and x64)
• Languages
• All SQL Server 7.0 and SQL Server 2000 released
  languages

8
Upgrade Plan

• Smooth upgrade requires a good plan
• Devise an upgrade plan by grouping upgrade tasks
  into
• Pre-upgrade tasks
• Upgrade execution tasks
• Post-upgrade tasks
• 0 day, 30 day, 90 day
• Exercise upgrade plan
• Test it more than once!

9
Pre-upgradePrepare Your Environment

• Study SQL Server 2005 minimum hardware software
  requirements
• Get an inventory of your applications legacy
  systems
• Releases, Components, SKUs, Platforms
• Opt for the same or a compatible edition
• Check features in each SQL Server 2005 SKU
• Beware of cross-SKU upgrade matrix
• Record benchmarks
• Functional, Performance, Stress
• Run Upgrade Advisor
• Examine issues reported by Upgrade Advisor
• Fix or work around backward compatibility issues

10
Pre-upgrade Backward Compatibility

• Some features are discontinued
• They do not appear in SQL Server 2005
• Example Undocumented system stored procedures,
  Virtual cube, Virtual dimension
• Some are being deprecated
• They wont be supported in the release following
  SQL Server 2005
• Example SQL Mail, Calculated Cell, Cell
  evaluation list
• Some features have a different behavior
• Example Database Engine catalog security
• Some editions have a different feature set
• Example Express does not have SQL Server Agent
• Check Books Online for a full list as well as
  replacements and techniques
• Run Upgrade Advisor before any migration or
  upgrade

11
Pre-upgrade Upgrade Advisor Tool

• Improves the SQL Server 2005 upgrade experience
• Avoids surprises during or after upgrade
• Analyzes SQL Server 2000 and SQL 7.0 instances
• Analyzes objects on server script and trace
  files
• Performs read-only operation
• Provides a report for detected issues
• Presents guidance on when detected issues need to
  be fixed
• Describes how to fix or work around issues
• Has links to documentation for additional content
• Download from web
• http//www.microsoft.com/downloads/details.aspx?Fa
  milyIDcf28daf9-182e-4ac2-8e88-f2e936558bf2Displa
  yLangen

12
Pre-upgrade
Ready for upgrade
Post-Upgrade
Upgrade
13
Upgrade Execution

• Choose an in-place upgrade or migration strategy
  for each component
• Understand upgrade and migration tools for each
  component
• Check databases consistency
• Back up old instance
• Verify backup
• Monitor upgrade progress via setup log
• Be prepared to execute your back out plan

14
Pros Cons of Migration

• Pros
• Migration provides more granular control over the
  upgrade process
• Having new and old instances side-by-side helps
  with testing verification
• Legacy instance remains online during migration
• Flexibility to implement migration with failover
• Cons
• May require new or additional hardware resources
• Applications need to be directed to new instance

15
Pros Cons of Upgrade

• Pros
• Easier, faster, less headache for small systems
• Requires no additional hardware
• Applications remain pointing to old instance
• Cons
• Less granular control over upgrade process
• Instance remains offline during part of upgrade
• Not best practice for all components
• Analysis Services cubes are recommended to be
  migrated

16
Upgrade Migration Tools

• Setup is in-place upgrade tool
• Database Engine
• Analysis Services
• Reporting Services
• Wizards for side-by-side migration
• Analysis Services
• DTS to Integration Services
• Specific configuration tools for migration
• Reporting Services
• Notification Services
• Upgrade Advisor is upgrade analysis tool for all
  components

17
Upgrading Database Engine

• Setup.exe performs the in-place upgrade
• Several migration techniques for Database Engine
• Detach/Attach
• Backup/Restore
• Copy Database Wizard, DTS
• Manual scripts, BCP

18
Upgrading Replication
Do Distributor First!
Updatable Subscriber to a SQL Server 2005
transactional publication can be any version
equal to or greater than SQL Server 2000 SP3
Distributor can be any version as long as it is
greater than or equal to the Publisher version
Distributor
Distributor
Publisher can be any version as long as it less
than or equal to the Distributor version
Subscriber
Publisher
Read-only Subscriber to a transactional
publication can be any version within two
versions of the Publisher version
Publisher
Subscriber
Subscriber to a merge publication can be any
version less than or equal to the Publisher
version
19
Upgrading Legacy SQL Server Tools

• SQL Server Agent has a new security model (proxy
  account)
• Registered Servers can be upgraded to Management
  Studio
• Database Diagram are upgraded into the new SQL
  Server 2005 format
• Database maintenance plans can be migrates to new
  Maintenance Plan packages

20
Post-upgrade

• Perform post-upgrade tasks on Upgrade Advisor
  report
• Examples Update statistics, build cubes
• Change database COMPATIBLITY level
• Reconfigure log shipping
• Execute upgrade testing
• Verify Agent jobs and maintenance tasks
• Bring system online
• Monitor system activity

21
Upgrade Tips and Best Practices

• You can reduce upgrade down time
• Pre-install Setup pre-requisites
• Microsoft .NET Framework 2.0
• Microsoft SQL Native Client
• Setup support files
• If you are planning a migration using Copy
  Database Wizard
• Before upgrade, place the database in single user
  mode
• make sure that no applications or services are
  trying to access the database
• Do not use read-only mode -- this will result in
  an error
• Cannot rename database during operation

• Be aware of SQL Server 2005 reduced surface area
• Some services features are disabled on new
  installations
• Use Surface Area Configuration tools to enable or
  disable features services

22
Upgrading to SQL Server 2005 the biggest findings

• SS2K statistics invalid after upgrade
• Update statistic will kick in automatically upon
  first execution of queries using sample data
• Default update statistics sampling could be very
  small for very large tables, possibly lt1
• Recommendation manually update statistics after
  upgrade. Full if possible, suggest 10 for Very
  Large tables.
• Example
• Update statistics sales.salesorder
• with sample 10 percent
• Recommendation Ensure you remember to run
  surface area configuration manager
• May not recognize an important component is off
  by default named pipes, Service Broker, CLR,
  FTS, Dedicated Administration Connection, etc.

23
Upgrading to SQL Server 2005 the biggest findings

• Recommendation Specify the WITH keyword when
  using table hints
• Optional in SS2K / Mandatory with SS2005 and
  therefore an error will occur
• Close to 25 of the problems we have seen
• Example UPDATE Production.Product WITH (TABLOCK)
  SET ListPrice ListPrice 1.10 WHERE
  ProductNumber LIKE 'BK-'
• Recommendation Remove references to undocumented
  system tables
• Use of keyword sys, now a reserved word

24
Summary

• Upgrade has been heavily tested
• Please try out Upgrade Advisory and use it before
  upgrading
• Your experience should be good