Upgrading and installing SQL Server 2008

Carefully planning a SQL Server 2008 upgrade will minimize downtime, prevent data loss and ensure a successful overall experience.

SQL Server 2008 is a complex product with many different components. An installation—especially an upgrade—must be planned to minimize downtime and avoid data loss. Microsoft has built on the setup program of SQL Server 2005 to deliver a more intuitive and robust installation and upgrade experience. SQL Server 2008's installation wizard clustering component, in particular, was redesigned for stability.

The command line installation process has also been significantly reworked. DBAs can now apply cumulative update patches to SQL Server release to manufacturing (RTM) files before beginning the setup process. Developers have demanded the slipstream setup for years.

The Upgrade Advisor

The biggest challenge with upgrading is minimizing downtime while protecting the system from failure. In order to minimize upgrade problems, Microsoft recommends that you run the Upgrade Advisor, which ships as part of the installation media.

Find the Upgrade Advisor on the installation media in the ServersredistUpgrade Advisor folder or download it directly from Microsoft. Run the read-only Upgrade Advisor against the SQL servers you want to upgrade to examine your server configuration, databases, tables and schema-only objects—views, functions and stored procedures.

The application also looks for upgrade blockers, which are configurations that aren't supported in SQL Server 2008. For example, the ansi join syntax =* is no longer supported in SQL Server 2008 unless you run your database in SQL Server 2000-compatibility mode. Upgrade Advisor will generate reports that describe any upgrade blockers that were found and offers information on how to solve them. Once you've solved upgrade blockers, you can rerun Upgrade Advisor until it reports that no blockers exist.

Upgrade methods

Microsoft recommends taking one of two upgrade approaches.

1. Upgrade-in-place installation. This process involves directly upgrading a SQL Server using the setup program. This automated method offers simplicity; setting connections don't need to be changed for applications accessing SQL Server. Requirements for this method include reliable backups and installation media. Although it's quick and easy, it's not without its problems.

Upgrade in place will cause downtime as the SQL server you're upgrading goes offline while the SQL Server 2000 or 2005 program files are upgraded to SQL Server 2008 or SQL Server 2008 R2 program files.

It could be difficult to recover from a failure that occurs during the upgrade process. To recover, you could either fix the problem that blocked the upgrade or do an OS-level restore of the SQL Server machine. Microsoft's KnowledgeBase article explains how to troubleshoot SQL Server 2008 setup issues. In some cases, you may have to remove the original version of SQL Server and then reinstall it.

2. Side-by-side installation. This upgrade method involves installing SQL Server 2008 on the same machine as the previous server version. You can also perform a side-by-side installation on a separate machine. If you do it that way, though, all file system and OS system dependencies must be in place. Logins are transferred using sp_help_revlogin or the Transfer Logins SSIS Package.

Once you've finished the installation, the databases must be detached, copied over and then attached to the new instance, and client applications must be redirected to the new SQL Server 2008 instance. To minimize downtime, log ship from the previous version to the new version and then end log shipping. Next, recover the databases and point the client applications at the SQL Server 2008 instance.

Side-by-side installation minimizes downtime and it's easy to fail over from it. For example, if you discover a major error with your new SQL Server 2008 platform, you can perform comparisons using the SQL Server 2005 instance to determine if the problem also affects that version. Failover is simple as long as you reconcile any data entered into the new SQL Server 2008 installation back to the SQL Server 2005 installation. This method also allows you to test your applications on the SQL Server 2008 version to validate functionality before eliminating your previous instance.

Refer to Microsoft's SQL Server 2008 Upgrade Technical Reference Guide for more information on the entire process.


The best of SQL Server 2008

Part 1: Justifying an upgrade Part 2: Part 3: Scaling SQL Server 2008 Part 4: Upgrading and installing SQL Server 2008

Hilary Cotter, SQL Server MVP, has been involved in IT for more than 20 years as a Web and database consultant, and has worked with SQL Server for 11 years. Cotter is Director of Text Mining at RelevantNoise, dedicated to indexing blogs for business intelligence. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. He is the author of A Guide to SQL Server 2000 Transactional and Snapshot Replication, published by Not While The Surf's Up Press, 2004.

Dig Deeper on SQL Server Migration Strategies and Planning