Nitor SQL Server Upgrade strategies and approach

SQL Server Upgrade Strategies

Option 1 (Side-By-side Upgrade) Option 2 (In place Upgrade)

Description

Install new instance of SQL Server 2012/14on same/different physical machine, Old instance of SQL 2005 is also retained

Data transferred to the new instance

Once upgraded instance is accepted the old instance can be removed.

Setup program directly replaces old instance with the new instance of SQL Server 2012/14

Old data files are converted into new format

When upgrade process completes, old instances is removed from the server

Key Advantages

Granular control can be maintained as databases and components can be migrated independent of one another.

The legacy instance remains online during the migration process allowing for additional testing and verification can be conducted, and rollback is easy

Upgrading is fairly quick, and additional hardware is not required.

Less time consuming, because there is no need to make changes to application connection strings; as the names of the server and instances do not change during the upgrade process.

Key Limitations

Additional hardware might need to be purchased.

Applications might need to be directed to the new SQL Server 2012/14 instance

Lower granular control over the upgrade processes DBA does not have the flexibility to choose individual items for upgrade.

The instance remains offline during the in-place upgrade and rollback strategy is a complex and time consuming affair, if error occurs during upgrade

Need to inspect the whole instance for backward-compatibility issues and address any blocking issues before SQL Server 2012/14Setup can continue.

Upgrade Activities by each Phase & Deliverables

Activities Key Deliverables

Analysis

• Analyze current database design and source code

• Analysis of Compliance & Security requirements for database

• Internal & External dependencies analysis

• Complexity, Impact, Risk analysis

• Understanding existing processes

• Environment Analysis Document• Risk analysis and mitigation plan• Key assumptions

• Security requirements• DB & Application dependency

matrix• Process & Policy documents

Planning

& Design

• Planning for MS-SQL Server Upgrade - Migration Planning- Knowledge management Plan- Release Planning

• Communication with application stakeholders

• Design solution for Upgrade - Design detailed migration strategy (Backup/Restore, Attach/Detach, Copy Database)- Design Fallback plan- Design Reconciliation plan

• Performance Tuning Planning & Design- Source DB Performance benchmarking

• Prepare performance Test plan and deployment plan

• Detailed upgrade Plan• Vulnerability Report• Migration/Upgrade Approach• Rollout & fallback Strategy• Test Plan - Reconcile & Verification

methods• Release/Change Plan• Deployment Plan• DB Performance benchmarks

Activities Key Deliverables

Implementation &

Testing

• Pre-migration Database preparation• Migration and Upgrade as per plan• Post-migration activities - Reconciliation of Data - Database Sanity checks - Instance Tuning - Setting necessary parameters • Dry Run• Database Performance Testing – Benchmark comparison and tuning• Bug fixes and follow-up testing• User acceptance

• Installation instructions• Security Checklist• Release Readiness • Migration Report• Bug tracking report• DB Performance testing reports

• Regression Test Cases and results• Application Unit Test Results• Application Performance testing

reports• Target Application/DB Code• Bug tracking report

Operations

• Certification – Database Environment • Upgrade clearance Certificate• Closedown review

Upgrade Activities by each phase & Deliverables