There are numerous reasons to upgrade SQL Server. You may be on SQL Server 2000 and have read that Blackbaud is ending support for SQL Server 2000 on March 31, 2010 or you may want to use new features in SQL Server 2008. Whatever your reason, you can upgrade SQL server using one of three different methods:
- An In-Place Upgrades
- A Side-by-Side Installation
- Installation on a new server
Your organization’s needs will determine which upgrade method is best. Let’s take a look at each option.
An in-place upgrade makes upgrading fast and easy if all your databases are compatible with the new version of SQL Server. It will upgrade each database automatically during the process. Plus, it will kept intact any customized SQL Server settings and jobs you may have. To perform an in-place upgrade, use the SQL Server installation package to update your current version of SQL Server. You will be prompted to select the SQL Instance to upgrade and it will update all files and databases associated with that instance. When performing an in-place upgrade, ensure all databases are compatible with that version of SQL Server. If they are not, then you may need to perform a side-by-side installation.
A side-by-side installation gives you the flexibility of deciding which databases to upgrade because it installs a brand new instance of SQL Server alongside your current instance, and does not affect the current instance or its attached databases. Instead of choosing a current SQL Instance to upgrade, you will choose the option for a new SQL Server Instance. To upgrade a database, you must detach it from the old SQL Server and attach it to the new instance.
Another key benefit to side-by-side installation is the ability to easily roll back to the old version of SQL Server if any unforeseen issues arise during the upgrade. You simply restore a backup of your database from before the upgrade to the old instance of SQL server. You do not have this benefit with an in-place upgrade.
A potential disadvantage to a side-by-side installation is that you have two separate SQL Instances running at once, which means each takes up resources such as CPU and RAM, on your server. If you run a side-by-side upgrade, make sure your server is beefy enough to handle both instances. After all databases have been moved off of the old SQL Server, you can uninstall or disable the old instance.
You may have noticed I haven’t talked much about a new server installation. There are two reasons for this. First, there’s not really a decision here, you are either installing on a new server or you are not. In this case you must have new hardware ready for the installation. Second, the benefits and installation processes are the same as a side-by-side installation. Both require a fresh installation of SQL Server and both require you to attach a copy of your database to the new SQL Instance.
After deciding the best upgrade path for your organization and selecting the databases to update, setup a test environment to use for testing the upgrade. This will help you prepare for the upgrade so you don’t have any surprises during the live upgrade.