Upgrading your SQL Servers

 Do you want to go from 2000-2005 or 2000 -2008 or do you want to by-pass 2005 and just go from 2000-2008.  The decision is based mainly on your comfort level and your cost benefit analysis.  Regardless of your choice I am writing this article based on a 2000-2008 upgrade. 

Microsoft has two distinct ways to Update SQL Server : a side-by-side upgrade and an in-place upgrade. Each employs a unique methodology with regard to the upgrade process.  There are advantages and disadvantages associated with each. Some factors to include when determining which of the upgrade solutions to choose include scheduled downtime, cost, complexity, and other factors.  Below will be an overview of the steps involved with each of the methods, as well as the strengths and weaknesses associated with each method.

Side-By-Side Upgrade

The side-by-side upgrade process is a manual process that utilizes two distinct servers – one running SQL Server 2000 (SQL 2k) and the other running SQL Server 2008 (SQL 2k8).  Each of these servers is configured as distinct entities with distinct server names and properties. 

Upgrade Steps

Below are the complete steps associated with a Side-By-Side Upgrade.

1. Install a separate instance of SQL Server 2008 on a separate server. The legacy instance continues to be available.
2. Run the SQL Server 2008 Upgrade Advisor (will be explained in detail later in this document) against the legacy instance and remove any upgrade blocker issues.
3. Stop all update activity to the legacy instance. This may involve disconnecting all users or forcing applications to read-only activity.
4. Transfer data, packages, or other objects from the legacy instance to the SQL Server 2008 instance.   This is an entirely manual process which introduces the possibility of human error.
5. Apply supporting objects such as SQL Server Agent jobs, security settings, configuration settings, and DTS packages to the new SQL Server 2008 instance.
6. Verify that the new instance supports the required applications, using validation scripts and user-acceptance tests.
7. If the new instance passes validations and acceptance tests, redirect applications and users to the new instance. At this point, the new instance is available and databases are online.
8. If the new instance does not pass validation or acceptance tests, direct users and applications to the legacy database server.

Advantages of a Side-By-Side Update

Below are some advantages of proceeding with a Side-By-Side Update.

1. You gain more granular control over what database objects you want to upgrade.  You have the capability of choosing individual databases from a server and upgrading them while leaving the rest intact.
2. You can run the legacy database server alongside the new server, perform a test migration, and research compatibility issues without disturbing the production system.
3. The legacy database server remains available during the migration, although it cannot be updated for at least the time it takes to transfer data.
4. You can roll back to the legacy system if the migration does not pass validation and acceptance tests.

Disadvantages of a Side-By-Side Upgrade

Below are some disadvantages of proceeding with a Side-By-Side upgrade:

1. A side-by-side upgrade will require new or additional hardware resources.  In production environments, this requirement may make this upgrade method cost prohibitive.
2. You must redirect applications and users to a new instance and possibly a new server. This will require some recoding in the application and configuration settings.
3. You must manually transfer data, security, configuration settings, and other supporting objects to the new instance.

1. The downtime might be longer in some cases because large amounts of data must be transferred from one server to another.

In-Place Upgrade

            The in-place upgrade is an automated process that is handled through Microsoft’s Database Server Upgrade executable.  This executable is a wizard driven application that moves an entire instance of SQL 2k to SQL 2k8.  At the completion of the in-place upgrade, the legacy SQL 2k instance is no longer available, as it is replaced with the new SQL 2k8 instance.

Upgrade Steps

Below are the complete steps associated with an In-Place upgrade.

1. The SQL Server 2008 Setup prerequisites are installed (Microsoft .NET Framework, SQL Server Native Client, and so on). The legacy instance databases continue to be available.
2. Setup checks for upgrade blockers, a small set of issues that will completely block an upgrade. If any are found, Setup will warn and exit.  These blockers, if any, are usually identified during the testing phase with the SQL Server 2008 Upgrade Advisor.
3. Setup installs the required SQL Server 2008 executables and support files
4. Setup stops the legacy SQL Server service. At this point, the legacy instance is no longer available.
5. SQL Server 2008 updates the selected component data and objects.
6. Setup removes the legacy executables and support files as well as the legacy tools. The new SQL Server 2008 instance is now fully available.
7. An in-place upgrade is easier and faster than a side-by-side upgrade.
8. It is mostly an automated process.
9. The instance will be offline for only the minimum amount of time.
10. The resulting upgraded instance has the same server name and configuration settings as the original.
11. Applications continue to connect to the same instance name without any modification to the code base.
12. No additional hardware is required because only the one instance is involved.
13. You must upgrade an entire instance or a major SQL Server component. 
14. You cannot directly upgrade a single database.
15. There will be some disk use growth during the metadata upgrade process.  This would require the disk to have room for growth on each database log file,  as well as the tempdb log and data files.
16. You must inspect the entire instance for backward compatibility issues.  This is to ensure that little used functionality continues to behave as expected.
17. You cannot run the newly upgraded version alongside the legacy version for comparison.
18. Rollback of upgraded data and the upgraded instance is complex.  Because the legacy version of the system is unavailable, the only way to rollback from an in-place upgrade is to re-install the legacy SQL Server and then manually restore each of the objects.

Advantages of an In-Place Upgrade

1. An in-place upgrade is easier and faster than a side-by-side upgrade.
2. It is mostly an automated process.
3. The instance will be offline for only the minimum amount of time.
4. The resulting upgraded instance has the same server name and configuration settings as the original.
5. Applications continue to connect to the same instance name without any modification to the code base.
6. No additional hardware is required because only the one instance is involved.

Disadvantages of an In-Place Upgrade

1. You must upgrade an entire instance or a major SQL Server component. 
2. You cannot directly upgrade a single database.
3. There will be some disk use growth during the metadata upgrade process.  This would require the disk to have room for growth on each database log file,  as well as the tempdb log and data files.
4. You must inspect the entire instance for backward compatibility issues.  This is to ensure that little used functionality continues to behave as expected.
5. You cannot run the newly upgraded version alongside the legacy version for comparison.
6. Rollback of upgraded data and the upgraded instance is complex.  Because the legacy version of the system is unavailable, the only way to rollback from an in-place upgrade is to re-install the legacy SQL Server and then manually restore each of the objects.

Upgrade Tools

            There are three primary tools that are used to plan, test, and verify the upgrade.  The tools that will be mentioned and described in this section are Microsoft’s SQL Server 2008 Upgrade Advisor,  Scalability Expert’s SQL Server 2008 Application Upgrade Advisor and DTS xChange.  Below is a description of each of the tools to be used.  Later, it will be explained how each of these tools will be used for planning and verifying the upgrade.

SQL Server 2008 Upgrade Advisor

            SQL Server 2008 Upgrade Advisor is a tool developed by Microsoft to help identify any issues that may arise during the process of upgrading from SQL 2k to SQL 2k8.  The Upgrade Advisor will highlight issues that might prevent a successful upgrade, as well as issues that might introduce backwards compatibility issues.  Upgrade Advisor analyzes your legacy instances and produces reports detailing upgrade issues by SQL Server component. The resulting reports show detected issues and also guidance on how to fix the issues or work around them.  These reports are stored on disk, so they can be retrieved and reviewed multiple times.  Upgrade Advisor executes read only operations against the server and is CPU intensive, so it is best to run off of a remote machine.  When you perform the analysis with Upgrade Advisor, you have the option to analyze any or all of the SQL Server components: SQL Server Database Engine, Analysis Services, Reporting Services, Notification Services, and Data Transformation Services.  When analyzing the Database Engine, you also have the option to select which of the databases you wish to include in the analysis.

            Once the analysis is performed on each of these components, the Upgrade Advisor prepares a report of its findings.  The report is divided by component, and highlights issues that need to be addressed.  The report classifies these issues into issues that need to be addressed prior to the upgrade, issues that should be addressed after the upgrade, and issues that should be addressed at any time.  Clicking on any issues that are highlighted will bring up suggestions by Microsoft regarding what steps may be taken to correct the issues.  Because the analysis can be run multiple times, we would run the analysis until the report only shows issues to be addressed after the upgrade.  We will not upgrade until all of the issues identified as needing to be addressed prior to the upgrade, or addressed at anytime are completed.

SQL Server Application Upgrade Advisor

            The Application Upgrade Advisor is a third party application that was developed by Scalability Experts.  Like the Database Upgrade Advisor, the Application Upgrade Advisor performs an analysis and generates reports based on that analysis.  While the Database Upgrade Advisor performs a validation to see if the database would successfully update, the Application Upgrade Advisor is used to validate whether the applications would be able to successfully operate under SQL Server 2008.  To successfully utilize the Application Upgrade Advisor, you have to incorporate results generated from the SQL Server Database Upgrade Advisor.  Listed below are the steps to run the Application Upgrade Advisor.

Running the Application Upgrade Advisor

1. Create a Test Environment – Ensure that the environment used for testing is configured as closely as possible to the specifications of the production environment.
2. Capture a Playback – A playback consists of a backup of all system and user databases and a trace that can be replayed.  To capture the trace, the front end GUI that utilizes the database should be used.  If there is an automated script that is used for regression testing, this should be used to guarantee that a comprehensive test is performed.
3. Setup Playback Baseline System – Restore the backups of all of the databases that were taken in Step 2.
4. Run SQL Server Database Upgrade Advisor – Run the Upgrade Advisor as described above and fix any issues that are identified to be fixed.
5. Replay Trace on SQL Server 2000 – After running the Database Upgrade Advisor, replay the trace created in step 2.  The replayed trace result becomes the baseline from SQL 2k.  This baseline will be compared with the same trace, replayed on SQL 2k8 to help us understand any changes to expect after upgrading the production servers.
6. Setup Playback Test System – During this step, the backups from step 2 are restored.
7. Upgrade to SQL Server 2k8 – With the databases restored, upgrade the test server to the latest version of SQL Server.
8. Replace Trace on SQL Server 2k8 – To evaluate the success of the upgrade, replay the trace against the SQL Server 2k8 database.
9. Compare Trace Files – At this stage, there will be results of the trace from SQL 2k and the results of the trace from SQL 2k8.  Application Upgrade Advisor will evaluate both trace file results and generate a report comparing the two.
10. Analyze Results – Use the Application Upgrade viewer to evaluate the differences between the results of the two traces.

DTS xChange

Although SQL Server 2008 Integration Services may be considered the fourth iteration of an ETL tool in SQL Server, it is dramatically different from SQL Server 2000 Data Transformation Services (DTS). The architecture of SSIS has changed drastically to support an in-memory ETL method that can efficiently support a load of millions of rows. To take advantage of this new architecture, organizations must migrate their DTS packages into SSIS and apply new best practices to the migrated packages.

SQL Server 2008 comes with the DTS Package Migration Wizard, a built-in means of migrating simple DTS packages to SSIS. However, this method will not meet the needs of all organizations. For organizations in need of a more robust solution that can handle thousands of packages with little administrator intervention, DTS xChange is an enterprise solution, offered by a Microsoft partner Pragmatic Works, that migrates DTS packages to Integration Services while applying a series of best practices rules to the packages.

DTS xChange is broken into three components:

1. Profile: DTS xChange Profiler helps organizations estimate their migration project in hours and dollar cost whether they choose to use an automation tool or not.
2. Convert: DTS xChange migrates packages, applying rules to each DTS package as it migrates them to enforce best practices.
3. Monitor: The SSIS Performance Warehouse is a software development kit (SDK) to help organizations get the most out of their new Integration Services environment. It contains a series of reports and a data warehouse to monitor administrators’ Integration Services package execution.

Testing Process

            We will validate that our applications in their current state will continue to operate under SQL Server 2k8.  Below will be a description of the requirements of the testing process, as well as a detailed description of each of the phases.

Testing Requirements

            During testing, we will validate our applications ability to run successfully against SQL Server 2008.  This will be done primarily using SQL Server Application Upgrade Advisor.

1. Restore backup of legacy databases onto server to be upgraded.
2. I will ensure that restore operations are successful and connectivity has been established.
3. QA group verifies that restored databases behave as expected.
4. Proceed with detailed steps that were highlighted under description of SQL Server Application Upgrade Advisor.  This description contains instructions for capturing trace files, running the Database Upgrade Advisor, upgrading the database, and re-running the trace files.
   1. During the running of the Database Upgrade Advisor, a list of issues that need to be addressed will be created.  It will be the responsibility of the DBA group and the Developer groups to address these issues.  Note that this may potentially be a lengthy process as there may be a number of server and/or database related issues.  The Database Upgrade Advisor is designed to be run multiple times, and each time should theoretically produce less issues.  These issues and their resolutions would be saved.  If possible, the solutions to the issues would be developed in a repeatable format so that the solutions may be reused when they are encountered in the production environment.
5. Compare the results of the trace files that were run against the SQL 2k and SQL 2k8 versions of the database.
6. DBA and QA groups document any known issues.
7. DBA and Developer groups address any discrepancies between the results of the two trace files.  Once the discrepancies are corrected, the process is repeated until the results of the two trace files are identical.
8. When the results of the trace files are identical, each group signs off that the application is verified for SQL Server 2008.