Tech-Ed Talks - SQL Server Upgrade Best Practices & High Availability

SQL Server Upgrade Best Practices & High Availability has always been my Area of Learning (Improvement) since, I had not worked on Clustering and Mirroring Scenarios.

I found below Tech-Ed talks extremely useful from learning perspective

Microsoft SQL Server Upgrade, 2000 - 2005 - 2008: Notes and Best Practices from the Field

Summary of Learning's (Notes)

Upgrade path for SQL 7.0 is SQL 7.0->SQL 2000->SQL 2005 and then 2008

Phases for Migration

• Planning
• Pre Upgrade Tasks
• Upgrade
• Decide to commit upgrade
• Post Upgrade

Slipstream

• Single installation along with service packs

In Place Upgrade Pros

• Faster
• Application connects to same instance after upgrade

In Place Upgrade Cons

• Complex Rollback strategy
• Upgrade all DBs connected to instances

Side by Side Upgrade

• New Installation
• Detach - Copy and Attach Database

Things to consider for Side by Side Upgrade

• Data Sync
• Decide to move for 64 bit / High end Hardware
• Data validation for copied objects

Tools

• Upgrade Advisor
• Assessment and Planning Kit

Clustering Scenario

• Install pre-requisites on passive node (B)
• Manual Failover for Passive Node to become Active (B)
• Install pre-requisites on passive Node (A)
• Start Upgrade with passive node for clustering scenario (A)
• Then Upgrade Active Node (B)
• SQL Server Automatic Failover takes care

This is very good information to get started with migration involving clustering

Database Mirroring

• Software solution which consist of principal and mirror server. All active transactions mirrored to mirror server

Upgrade Involving Database Mirroring, Replication and Failover Clustering is also covered. Migration of Analysis Services is also covered

Bunch of more Best Practices are provided in the tech talk.

SQL Server High Availability: Overview, Considerations, and Solution Guidance

Summary of Notes

• Classifying Applications based on HA requirements
• Types of Failures (Local / Site Level)
• Regional & Geographic Disaster Recovery
• Chossing approach based on SLA, Cost
• Planned Downtime (Sps, Hot fixes, Security Fixes, Rebuilding Indexes)
• Unplanned Downtime (Hardware, Software and power failures)

HA Notes

• Database backup and restore
• Log Shipping
• Database Mirroring (Fastest Failover). Synchronous/ Asynchronous Data movement
• Instance need to start in cluster scenario but in mirroring it is already started and running
• Clustering is at instance level, Mirroring is at DB level
• Mirroring / Log shipping Applying Data Page level
• Replication - Logical, Secondary can have read access
• Replication is at Table level, Flexible to replicate subset of information

For Detailed information please check the session. This is very good session to begin with HA topic.

More Reads


SQL Server 2005 High-Availability Options (Distilled)
Automating Database Migration to Microsoft SQL Server
MVC,MVP and MVVM: A Comparison of Architectural Patterns
Microsoft Silverlight,WCF RIA Services and Your Business Objects

Happy Learning!!!