SQL Server Storage Engine & Tools (SSQA.net)

SQL Server Upgrade from 2005 to 2008 - Notes and Best Practices from field :session from SQLBits conference

During the SQLBits IV conference I had presented a session on the topic: "Upgrading To SQL Server 2005 & 2008: Notes & Best Practices" with an excellent feedback from users. In this session the areas are covered such as SQL Server Upgrade options, methodology, tools and planning, technical and non-technical considerations along with common issues, myths and mistakes. Also I had touch base on the monitoring at pre & post upgrade scenarios along with exhaustive list of all upgrade issues with an end-to-end coverage or in-depth drilldown of all SQL Server 2005 & 2008 features.

Ok, now why I'm blogging about it. Thanks to SQLBits organisers in releasing video and my presentation on the site.

Before I give you the chance to download the session, I would like to cover on important aspects of scenario in SQL Server upgrade from previous version to existing version. As it may sound easy (bit) to upgrade a database with few clicks there are many pre-upgrade factors, based on my experience and past upgrade experience I have outlined the process to follow which has produced good results with minimum interruption and issues on SQL Server platform.

In my experience and tasks that are handled in real-time processes, the factors & components below are considered as major steps for Upgrade process:

Components

·         Are you using SQL Server 2000 Analysis Services?

·         Are you using SQL Server 2000 Reporting Services?

·         How many DTS packages (from SQL 2000) exists on system?

·         What is the current hardware capacity?

·         Are you using existing hardware or new hardware to upgrade?

Upgrade Planning

·         The planning phase should identify the steps from identifying the databases targeted for the upgrade to determining the changes and processes the upgrade will require.

·         A combination of components required for upgrade, such as platform upgrade path, application architecture, hardware and pre & post upgrade issues.

·         A tool should be used to identify the pre-upgrade issues to focus efforts on what to expect from the pre-upgrade & post-upgrade problems.

·         A major decision in the preliminary work is to decide whether we perform in-place upgrade (usage of same servers) or side-by-side upgrade (different servers).

·         Nothing can replace testing. Even if you plan to upgrade only the SQL Server database engine without changing your application, testing will help identify any backward-compatibility problems and behavioral changes from previous SQL Server releases that the Upgrade Advisor didn't detect. 

·         IF you have any other components such as Analysis Services, Reporting Services and DTS/SSIS packages then  the table (source: Technet)below gives the upgrade path:

Testing & Validation

Finally these tasks are performed in the testing and validation phase:

·         Prepare your test environment. Side-by-side migrations require a separate test SQL Server 2005 installation. In-place upgrades require a test machine running SQL Server 2000 or 7.0 and target database copies. Hardware comparable to your production setup will allow production volume testing.

·         Set a pre-upgrade baseline. This baseline will help you evaluate your system post-upgrade and determine any behavioral changes, letting you simulate a typical workload after your upgrade. The baseline will also help you confirm functionality and document performance improvements or changes. To set up the baseline, you can use familiar tools such as SQL Server Profiler, application load testing tools, Performance Monitor counters, and Showplan statistics.

·         Develop a test plan. Set up a generalized testing script or test procedures for the following areas: data validation, data processing, stress and workload, client/server performance, and application functionality.

·         Develop a recovery plan. Develop upgrade rollback procedures in case of an upgrade interruption. The recovery plan should include running a DBCC consistency check on the pre-upgrade databases before backup as well as a full restore of the database to validate the backup reliability. After the upgrade, you should also perform a consistency check and a backup with validation. Make sure you test your rollback procedures.

·         Create application-modification procedures. Your test environment should include the full application tier so that you can confirm application changes work as expected. These application-modification procedures should include a catalog of effected users. Such procedures also allow complete documentation of your application changes so that they can be applied successfully during the production cutover.

·         Perform an upgrade test run. A final test run of the upgrade will confirm that the process and procedures work as you expect. You can use the Upgrade Advisor in this step. Run the tool after you apply your pre-upgrade changes to validate that you’ve addressed all the problem areas the tool identified earlier.

For this you have 2 option, either you can Click here to view in local media player or Right click here and click "Save As" to download a copy of this video and also refer to the blog post SQLUpgradeIssues-HowToEvaluate the issues and resolve them.

(there seems to be problem initially to download the session videos, which has been correct now)

Format: ashx?SessionId=240&Regenerate=
Duration: --:--