Upgrade prep and methodology
Download
1 / 20

- PowerPoint PPT Presentation


  • 198 Views
  • Updated On :

Upgrade – Prep and Methodology. Agenda. Upgrade methodology overview Upgrade options and considerations Upgrade tools . Upgrade Paths. Database Generations. SQL Server 2008. SQL Server 2005. SQL Server 2000. SQL Server 7.0. SQL Server 6.5. SQL Server 6.0. Upgrade Methodology Overview.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '' - Sharon_Dale


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Agenda l.jpg
Agenda

  • Upgrade methodology overview

  • Upgrade options and considerations

  • Upgrade tools


Upgrade paths l.jpg
Upgrade Paths

Database Generations

SQL Server 2008

SQL Server 2005

SQL Server 2000

SQL Server 7.0

SQL Server 6.5

SQL Server 6.0


Upgrade methodology overview l.jpg
Upgrade Methodology Overview

  • Identify Upgrade Requirements

    • SQL Server edition

    • Hardware considerations

  • Run Upgrade Advisor

    • Resolve identified issues

  • Perform Application Compatibility Testing

    • Ensure thorough testing of all RDBMS queries

  • Pre-Upgrade Considerations

    • Document existing system

    • Develop validation tests / performance benchmarks and capture baseline data

    • Formulate and test rollback plan

      • May required 3rd party applications

  • Determine Appropriate Upgrade Strategy

    • In-place or side-by-side (migrate)

  • Upgrade Process

    • Pre-upgrade tasks

  • Post Upgrade Considerations

    • Immediate / short-term / medium-term / long-term


Agenda5 l.jpg
Agenda

  • Upgrade methodology overview

  • Upgrade options and considerations

  • Upgrade tools


Upgrade path options l.jpg
Upgrade Path Options

  • Versions

    • SQL Server 2000 SP4 or later

    • SQL Server 2005 SP2 or later

  • Components

    • Database Engine

      • Includes sub-components like SQL Agent, Full-text, Tools, etc.

    • Reporting Services

      • IIS to http.sys?

    • Notification Services 2005 only (deprecated in 2008)

    • Analysis Services (it is recommended that cubes are migrated)

    • Data Transformation Services

    • Integration Services

  • Editions

    • Desktop, Workgroup, Personal, Standard, Developer, Enterprise

  • Platforms

    • 32-bit & 64-bit (IA64 and x64)

  • Languages

    • All SQL Server 2000 and SQL Server 2005 released languages


Upgrade options and considerations in place l.jpg
Upgrade Options and Considerations: In-Place

  • Similar to 20002005 upgrade

  • Updates an existing installation while preserving user data

  • Instance name remains the same after upgrade

    • Existing instance overwritten post-upgrade

  • Automated process


Upgrade options and considerations in place8 l.jpg
Upgrade Options and Considerations: In-Place

SQL Server 2000/2005

Instance

SQL Server 2008

Instance

Upgrade


Upgrade options and considerations in place9 l.jpg
Upgrade Options and Considerations: In-Place

Point of no return

Instance is still available

Attach resource database

Redirect services to new binaries

Install SQL Server 2008 binaries

Restart service

Execute various upgrade scripts

Install prerequisites

Upgrade

Complete

Upgrade

Starts

Start service in single-user mode

Stop service

Stop service

Start updating all databases

Check for upgrade blockers

Remove old binaries

Instance is no longer available

Partial availability starts here

Instance becomes available


Upgrade options and considerations in place10 l.jpg
Upgrade Options and Considerations: In-Place

  • Pros

    • Easier, mostly automated

    • Generally fast overall process

    • Requires no additional hardware

    • Applications remain pointing to same server/database name

  • Cons

    • Less granular control over upgrade process

    • Instance remains offline during part of upgrade

    • Not best practice for all components

    • Complex rollback strategy


Upgrade options and considerations side by side l.jpg
Upgrade Options and Considerations: Side-by-side

  • Similar to 20002005 migrate (side-by-side)

  • Install new instance of SQL Server

  • Database objects are copied between instances

    • Copy Database Wizard

      • Does not copy extended procs, alerts, DTS packages, linked servers.

    • Detach -> Attach

    • Backup -> Restore

    • T-SQL Scripts, BCP New and Old instance reside side-by-side

  • Can be same server (named instance) or different server

  • Mostly Manual process


Upgrade options and considerations side by side12 l.jpg

Compare

and

Verify

Upgrade Options and Considerations: Side-by-side

SQL Server 2000/2005

Instance

SQL Server 2008

Instance

Verified!


Upgrade options and considerations side by side13 l.jpg
Upgrade Options and Considerations: Side-by-side

  • Pros

    • More granular control over upgrade process

      • Database level

    • Can be used to perform test migration

      • Document process & gather metrics

    • Ability to run systems side-by-side for testing and verification

      • May require 3rd party application to keep both in-sync

    • Relatively straightforward rollback Strategy

    • Can leverage failover/switchover to reduce downtime

  • Cons

    • Usually require additional hardware

      • Additional resources required if on same server

    • Server/database name changes

    • Not practical for VLDB unless utilizing SAN

      • Beware of “loss of quick roll-back”


Agenda14 l.jpg
Agenda

  • Before you run setup.exe

  • Upgrade methodology overview

  • Upgrade options and considerations

  • Upgrade tools


Upgrade tools l.jpg
Upgrade Tools

  • Upgrade Advisor

    • Analyzes SQL Server 2000/2005 database, trace files and script files

    • Read-only operation, can be CPU intensive

    • Supports remote execution

    • Supports default and named instance

    • Generates a report of

      • Detected issues

        • Blocking Issues

        • Pre-Upgrade Issues

        • Post-Upgrade / Migration Issues

      • Suggestions on how to fix / work around

    • Requires .NET framework 2.0 sp1 or later

    • Download latest version from web!


Upgrade advisor overview l.jpg
Upgrade Advisor Overview

  • Analyzes

    • Configuration and objects within installed components

      • SQL Server

      • Analysis Services

      • Notification Services

      • Reporting Services

      • Data Transformation Services

      • Integration Services

    • Trace files (captured using Profiler/SQL Trace)

    • T-SQL scripts (any script file)

  • Generates report of issues that must be addressed

    • Before – potential showstoppers, must resolve before upgrading

    • After – should be resolved after completing upgrade

    • Anytime

    • Advisory – notices and/or warnings


Requirements l.jpg
Requirements

  • Windows XP SP2, Windows Server 2003 SP1, Windows Vista, Windows Server 2008 Beta 3

  • Windows Installer 3.1

  • .NET Framework 2.0

  • Supports

    • SQL Server 2000

      • Requires decision support objects (DSO) if scanning Analysis Services 2000

      • Requires client components if scanning SQL Server 2000 packages

    • SQL Server 2005

      • Requires backward compatibility components if scanning SQL Server 2005 DTS packages that were upgraded from SQL Server 2000


Upgrade tools18 l.jpg
Upgrade Tools

  • Upgrade Assistant / Database Upgrade Testing Toolkit (DUTT)

    • Used in addition to Upgrade Advisor

    • Allows testing of actual TSQL execution against SQL Server

    • Can detect changes in execution method, path and results

      • Upgrade Advisor does not

    • Allows testing of dynamic/embedded SQL

    • Leverages Profiler/Trace capability

    • Requires SQL Server 2008 Client Tools

    • Collaborative development between SQL Server Development Team and Scalability Experts

      • Free download from www.scalabilityexperts.com


Some known compatibility issues l.jpg
Some Known Compatibility Issues

  • Applications work fine on 8.0/9.0 compatibility mode but fails in 10.0 mode

    • Additional ANSI SQL standards enforcements in 2005 (e.g. left and right outer joins using *= and =* no longer supported)

    • Modify SQL statements to be standards compliant (will still work if in 8.0 compatibility mode)

  • References to system and/or undocumented objects

    • Accessing system tables/stored procedures or undocumented features may/will fail or behave differently

    • Modify application to utilize Dynamic Management Views and Functions (e.g. syslocks  sys.dm_tran_locks)

  • Poorer performance after upgrade

    • Queries take longer to run after upgrading to SQL Server 2008

    • Several possible reasons and solutions

      • Review queries to ensure hints are still valid (or just remove them)

      • Do full updated statistics (use different sampling size for large tables)

      • Review TempDB utilization and optimize via storage isolation

      • Run DB Tuning Advisor

      • Contact PSS if performance difference is significant

  • Cannot Access SQL Server after upgrade

    • Clients can no longer connect to the database server or related components

    • Check settings in Surface Area Configuration – “off-by-default” is in place for features and access methods

  • Cannot Upgrade Log Shipping 2000

    • Log Shipping in 2008 does not use DB Maintenance Plan Wizard

    • Migrating with failover – switchover to standby, upgrade primary, switchback, upgrade standby, re-establish Log Shipping or other HA technology

    • Migrating without failover – upgrade primary, upgrade secondary, re-establish Log Shipping or other HA technology

  • Check http://support.microsoft.com/, msdn, technet and Books Online for latest updates.


ad