SQL Server 2008 : Upgrade Strategies (In-Place Upgrade)

There are two ways to perform an upgrade:

 Step 1: In-place upgrade, and as the name implies, it upgrades an entire instance "in place" by converting the data files to the new format. 

Step 2 :The second approach is known as a side-by-side upgrade, which consists of installing a second instance of SQL Server and moving the data files to the new version.

 Both methods have their pros and cons, and the best method for one application may not be the best method for another. An in-place upgrade provides simplicity but lacks the flexibility offered by a side-by-side upgrade.

Difference between in-place & Side by Side Upgrade of SQL server:

************************************************************

Upgrade (or in-place upgrade):

**********************************

•   Updates an existing installation while preserving user data
•   Instance name remains the same after upgrade
•   Automated process


Migration (or side-by-side migration):

***************************************

•   Starts with a new installation
•   New & old instance reside side-by-side
•   Objects are copied from the old to new instance
•   Mostly a manual process


Pre-Upgrade: Preparing the environment:

********************************************

    Study SQL Server 2008 minimum hardware & software requirements
    Get an inventory of your applications & legacy systems
Releases, Components, SKU’s, Platforms
    Opt for the same or a compatible edition
    Check features in each SQL Server 2008 SKU
    Beware of cross-SKU upgrade matrix
    Run Upgrade Advisor
    Examine Upgrade Advisor report
    Fix or work around the backward compatibility issues


Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade.
SQL Server 2008 Upgrade Advisor Tool is available from Microsoft.

Advantages/Disadvantages of In-Place to Side-by-Side:

***************************************************

Side By Side Migration:

************************

    Advantages:-
    Migration provides more granular control over the upgrade process
    Having new and old instances side-by-side helps with testing & verification
    Legacy instance remains online during migration
    Flexibility to implement migration with failover
    Dis-advantages
    May require new or additional hardware resources
    Applications need to be directed to new instance

In-Place Migration/Upgrade:
*******************************
    Advantages:-
    Easier, faster, less headache for small systems
    Requires no additional hardware
    Applications remain pointing to old instance
    Dis-advantages
    Less granular control over upgrade process
    Instance remains offline during part of upgrade
    Not best practice for all components
    Analysis Services cubes are recommended to be migrated

Database Migration Techniques:

**********************************

1. Migrating the database by using Backup & Restore
2. Migrating the database using copy database wizard
3. Migrating the database by using Detach & Attach method.

1. In-Place Upgrade

*********************

An in-place upgrade can be used to upgrade an instance of SQL Server 2000 or SQL Server 2005 directly to SQL Server 2008. Search for "Version and Edition Upgrades" on MSDN for specifics on the supported upgrade paths for each version and edition.

*One of the benefits of performing an in-place upgrade is that you can use the same server and instance names. 

* It is a fairly simple process, much like performing an installation. 

* You do not need extra disk space to make a copy of the data files because they will be converted to the new format during the upgrade. 

*The downside is that there is a point during the upgrade process where there is no turning back.

* The only back out is to reinstall the prior version of SQL Server and restore the databases from a backup that was made prior to the upgrade. 

*You cannot restore a backup of a database that has been converted to SQL Server 2008 on a previous version. 

*If you determine that a problem has been caused a week later by the upgrade, you will have to restore the database from a backup prior to the upgrade and hope you have a way to recover the transactions that have occurred since the upgrade.

*It is important to have a tested backup in a safe location, since that is the only way to back out of an in-place upgrade. 

*You may want to take an image of the server as well and store it in a safe location to reduce the backout time.

The steps to performing an in-place upgrade include the following:

*********************************************************************

 1. Make sure you have current backups in a safe location.

 2.Run SQL Server setup.exe. Install any prerequisites and exit the install. If a reboot is required due to the prerequisites, reboot and run setup.exe again.

  3.From the SQL Server Installation Center, select Upgrade from SQL Server 2000 or SQL Server 2005, as shown in Figure 1.

Figure 1. SQL Server Installation Center

4. SQL Server will install any required support files, and you may be required to reboot and start setup.exe again.

5.The System Configuration Checker will run a discovery and setup log files will be created for the installation. The Setup Support Rules screen, shown in Figure 2, identifies any problems that you may encounter when installing the setup support files. These errors will need to be resolved in order to continue.

Figure 2. Upgrade Setup Support Rules

6.On the Product Key screen (see Figure 3), enter the product key or indicate that you are upgrading to a free version.

Figure 3. Upgrade to a free edition or enter a product key.

7.Accept the license agreement, as shown in Figure 4.

Figure 4. Upgrade license agreement

8.Select the instance of SQL Server you will be upgrading. You'll be presented with a list like that shown in Figure 5.

Figure 5. Upgrade instance selection

9.The feature selections will be preselected and cannot be changed. The checklist of features will be grayed out, as shown in Figure 6. If you need to add additional features, you will need to rerun setup.exe after the upgrade

Figure 6. Upgrade features selection

10.Specify the name of the new instance. In Figure 7, we specified SQLEXPRESS2 as our instance name.

Figure 7. Upgrade the instance configuration

11. Continue through the upgrade screens, validating the disk space requirements and selecting Error and Usage Reporting.

12.The installer will do a final check to make sure the upgrade process will not be blocked. You'll be presented with upgrade rules like those in Figure 8. You'll be able to see which rules you pass, and which require some action on your part.

Figure 8. Upgrade rules

13.The Ready to Upgrade screen in Figure 9 allows you to verify all the features that will be upgraded prior to performing the upgrade.

Figure 9. Upgrade features verification

 14.Next, the Upgrade Progress screen in Figure 10 allows you to view the status of the upgrade as it progresses.

Figure 10. Upgrade Progress screen

15.Review the log file after the upgrade has completed. The installer will provide a link to the log file on the Complete screen, shown at the upper right of Figure 11.

Figure 11. Upgrade completion

Once you have completed the upgrade, it is always a good idea to log in to the application and run some tests to make sure the upgrade was truly a success before releasing the database back into production. It will help if you have some baseline performance statistics prior to the upgrade that you can use to compare to the upgraded database. Unfortunately, some people are reluctant to change and think everything used to work better on the old version. In rare cases, some queries may actually perform worse after the upgrade. Having a baseline will help you determine if there is actually an issue, or if it is just user perception.

Thank you !! Keep Reading !