There are many considerations and much preparation to do, when a company decides that it is ready to upgrade their SQL Server to the latest version, as there may be several changes, feature deprecations, and new or improved features that can be leveraged. It’s not always as simple as backup and restore, since there are so many new components, especially when going from SQL 2000 to SQL 2008. Even though Microsoft ended mainstream and general support for SQL Server 2000, way back, you’ll find there are many companies that are just coming around to the realization that they must upgrade, especially as hardware get decommissioned, and technology moves forward.
Now, that even SQL Server 2005 has been surpassed, the ultimate decision is to skip over 2005 completely, and go directly to 2008. This was exactly the case with a client of mine over the summer, where they knew they were no longer supported on SQL Server 2000, and it was time to provision new hardware.
The environment was a 32-bit SQL Server 2000 cluster, and they had purchased the latest and greatest 64-bit OS, Server and SAN technology, and needed to plan to move their existing 2000 instance, over to the new 64-bit SQL Server 2008 cluster. They had Log Shipping, Replication, DTS packages, SSRS, SSAS, and a whole host of new security and configuration options to consider. With all this planning, preparation, and deployment, they needed extensive project planning and best practice recommendations on where to start and how to go about it.
Luckily, I was able to find an excellent resource for all this, which made the planning and implementation phases all the more streamlined. I hit the “Upgrading to 2008” jackpot, when I looked up this presentation, and learned the “why, what, how and when” to upgrade. The session and materials used gave a comprehensive end-to-end guide on the basics of planning the upgrade to the advanced, on strategies, methodologies, pitfalls, myths, issues and troubleshooting and all the available references. These best practices and recommendations are still very much valid, and is why I am bringing you this checklist today.
Sometime back this year, SQL MVP and friend Satya Jayanty (aka @SQLMaster) travelled Europe and the US, to bring his knowledge and experience to SQLBits and TechEd, “best practices and notes from the field”, with respect to upgrading your SQL Server 2000 or SQL 2005 server to the latest SQL Server 2008/R2. Satya has authored several articles, is a forum moderator on SQL-Server-Performance.com, and maintains his own website and blog at http://sqlserver-qa.net/default.aspx.
I spoke with Satya to compliment him on this awesome presentation, and told him we need to disseminate this information to more and more folks with similar upgrade considerations. Therefore, I have compiled this checklist based on his presentation, and have his blessing to share this information with you and the greater SQL community at-large.
This paper is intended to be a SQL Server 2008 upgrade overview. The checklist here will cover the essential phases and steps involved in upgrading SQL Server 2000/2005 to SQL Server 2008 and SQL Server 2008 R2 by using best practices and available resources. We will cover the upgrade cycle, including the preparation tasks, upgrade tasks, and post-upgrade tasks. This list will also cover upgrading instances involved in mirroring, log shipping, reporting server, and replication; feature-specific considerations; and recommended tools for a successful upgrade. Please review the sections accordingly.
If you want to catch the entire presentation, you can go to this link and see Satya present first-hand: Upgrading to SQL 2005/2008. (I’m sure there is a later version from TechEd 2010 which focuses on 2008 only, and will post back if I find it)
Upgrade Planning (Checklist)
ü Preparing to Upgrade
o Review upgrade documentation and resources
o Identify upgrade requirements
§ SQL Server editions/features
§ Hardware considerations
§ Application compatibility
o Decide on upgrade strategy: In-place upgrade vs. side-by-side
o Establish plan to upgrade high-availability servers
o Establish backup and rollback plans
ü Pre-Upgrade
o Check environment
§ Run SQL Server 2008 R2 Upgrade Advisor
§ Ensure environment is clean
· Check database consistency
· Consider shrink DB
· Consider rebuild indexes
§ Run SQL Server 2005 Best Practices Analyzer (BPA)
o Back up your environment
§ System and user databases including DTS/SSIS packages
§ Your system
ü The Upgrade
o Document every step
o Check server, storage, and network health
o Perform the upgrade
o Back up databases when upgrade completes
ü Go/No-go (Checkpoint)
o Review the logs to ensure everything is successful
o Troubleshoot if there is an upgrade failure
o Determine whether you need to roll back
o Determine application acceptance (sanity test)
ü Post-Upgrade Tasks
o Determine application acceptance (end-to-end)
o Integrate the new instance into the new environment
o Decommission and uninstall after a side-by-side or new hardware install
o Apply latest security updates
o Review security settings
o Revisit Upgrade Advisor recommendations
o Run the SQL Server 2008 R2 BPA
o Side-by-Side (Migration) Upgrade
ü Install new instance of SQL Server without affecting existing instance
o Can be same or different server
ü Database objects are manually copied to new instance
o Copy Database Wizard
o Detach -> Copy -> Attach
ü Upgrade Planning
ü Upgrade Strategies and Considerations
o Stand-alone Upgrade
o Cluster Upgrade
o Database Mirroring
o Log Shipping
o Feature-Specific Information
ü Memory Configuration
o Min/Max Settings
ü Upgrade Tools
ü Upgrade Resources
ü Upgrading Log Shipping
o Upgrade from 2000 to 2008 is not supported
o Upgrade Log Shipping Monitor server instance – Log Shipping itself is not affected
o Secondary server must be upgraded first
o Instance is upgraded first but DB remains 2000/2005
o Logs are accumulated during upgrade and applied when done
ü Typical upgrade options:
o Less downtime – upgrade secondary, failover, upgrade primary, establish Log Shipping, failback, establish Log Shipping
o Less effort – upgrade secondary, allow catch-up, upgrade primary
ü Upgrading Replication
o Mixed versions are supported, but: Upgrading Data Transformation Services (DTS) and Integration Services (SSIS)Distributor version must always be equal or newer than Publisher
o Subscribers for transactional replication can be within 2 versions newer/older of Publisher
o Subscribers for merge replication must be equal or older than Publisher
o Verify features supported by editions before upgrade
o Typical upgrade flow: Distributor, Publisher, Subscriber
ü Upgrading Analysis Services
o Upgrade 2005 to 2008 – Either an in-place or side-by-side upgrade
o Upgrade 2000 to 2008 – Analysis Services Migration Wizard is recommended
ü Upgrading Reporting Services
o Single server, RS db within same instance
o Database engine and Reporting Services components will all be upgraded
o Remote Installation or different instance of RS db
o RS component can be upgrade without upgrading the database engine
o The RS db schema will be updated
ü Upgrading Data Transformation Services (DTS) and Integration Services (SSIS)
ü DTS is depreciated
o SQL Server 2005/2008 still includes DTS functionality, but future version may not
ü DTS upgrade options:
o Migration DTS packages to SSIS
§ DTS Migration Wizard (DTSMigrationWizard.exe)
§ 3rd party tools available, such as www.dtsxchange.com
o Continue to run DTS packages using the DTS runtime
§ No design/runtime support on 64-bit or 32-bit on IA64
o Incorporate DTS package into SSIS packages
o SSIS use the SSIS package upgrade Wizard
ü SQL Server 2008 R2 Upgrade Advisor
o Available at the following locations:
o The SQL Server Installation Center
o On the source media in the [Servers\redist\Upgrade Advisor] folder
o Through the SQL Server 2008 R2 Feature Pack at
ü SQL Server 2008 Upgrade Assistant
o Used for Application Compatibility Testing (ACT) along with Upgrade Advisor
o Allows testing of actual T-SQL execution
o Can detect changes in execution method, path, and results – Upgrade Advisor does not
o Leverages Profiler/Trace capability
o Collaborative development between SQL Server development team, DPE, and Scalability Experts
o Free download from www.scalabilityexperts.com/ssua
ü SQL Server 2008 R2 Best Practice Analyzer
o Based on Microsoft Baseline Configuration Analyzer 2.0
o Must be manually installed first
o Work against SQL Server 2008 and SQL Server 2008 R2
o Replaces prior version
o About 130 rules (50 new rules)
o Regular updates
o Product Support (CSS) KB articles provide the details
ü Upgrade Resources:
o Microsoft SQL Server 2008 Upgrade site
§ http://www.microsoft.com/sqlserver/2008/en/us/upgrade.aspx
o MSDN technical resources
§ http://msdn.microsoft.com/en-us/library/bb677622.aspx
ü SQL Server 2008 Upgrade Technical Reference
o SQL Server Community: blogs, Channel 9, newsgroups, videos, and webcasts
o http://msdn.microsoft.com/en-us/sqlserver/bb671050.aspx
o http://blogs.msdn.com/b/petersad/
o http://sqlserver-qa.net/blogs/Bloggers.aspx
Pearl Knowledge Solutions, Inc. is offering, for a limited time a special discount on all its software solutions offered, including SQLCentric, SafePeak, and RedGate SQL Tools.
SQLCentric has just received the SILVER award for Best Database and Performance Monitoring Product from SQL Server Magazine’s 2010 Community Choice Awards!
Ask about our comprehensive end-to-end performane assessment and report!
Contact us for a demo, or more information at sqlcentric@pearlknows.com
