Creating SQL Server upgrade paths from one version to another is a task that every database administrator is eager
to undertake. However, upgrading SQL Server is not an easy task, and improper planning increases the chance of derailment and delays.
When you devise your SQL Server upgrade paths, it is necessary to break down tasks into three groups: before, during and after upgrading.
SQL Server upgrade paths: Tasks before an upgrade
First, analyze your existing SQL Server environment to ensure that you can upgrade this version to your desired version and edition. That includes reviewing hardware and software requirements for the desired version. For example, if you are upgrading SQL Server 2000 to SQL Server 2012, make sure your hardware can run SQL Server 2012.
Also, decide on your upgrade strategy. Consider in-place and side-by-side migrations for your SQL Server upgrade paths. I prefer side-by-side migration because it is less risky, even if it is more difficult. With an in-place migration, there is no simple rollback once you upgrade. The key benefit of an in-place migration is that you don't need to worry about SQL Server security and other configurations because they will remain in sync before and after the upgrade. However, in-place migration requires a good backup of all system and user databases prior to upgrading.
In a side-by-side upgrade, the new version of SQL Server installs along with the old version as a separate instance on the same or a different server. This is the same as a new installation followed by a database migration from your existing SQL Server instance. For example, if you are upgrading your existing SQL Server 2008 instance to SQL Server 2012 using this approach, you must install SQL Server 2012, either along with the SQL Server 2008 instance as a separate instance or on a different server. The advantage of this approach is that you can perform like-to-like testing of your database functionality. Furthermore, your rollback procedure is simpler than during an in-place upgrade because the original server is still intact. The drawback of this approach is that you have to follow the database migration exercise after the new instance is commissioned, and manually change the SQL Server instance's configuration and security. The side-by-side upgrade option is ideal when you are also upgrading to new hardware, or when you are consolidating SQL Server instances.
Regardless of your choice for your SQL Server upgrade paths, you should also ensure that your database applications work after upgrading. Fortunately, SQL Server provides several tools you can use to avoid common upgrade issues. For example, you can use SQL Server Upgrade Advisor to help you identify key issues that might affect your upgrade, such as deprecated or discontinued features, breaking changes and behavior changes. You can install Upgrade Advisor from the SQL Server installation start screen. It prompts you to accept its license agreement and to supply registration information before installation. You do not need a product key to install this tool. For more information, see Microsoft's official resources.
You can also use another tool called SQL Server Upgrade Assistant, which helps you to test and validate the functionality of database applications designed using a previous version of SQL Server. This tool uses a workload to test a database application's functionality that exists in the database source code. For more information, see the extensive documentation at the Microsoft Upgrade Assistant Tool for SQL Server 2012 Web page.
Tasks during an upgrade
The procedure for your SQL Server upgrade paths is similar to the installation procedure. You can install and upgrade services at the same time. For example, you can upgrade an existing instance of the database engine, and also install Integration Services, Analysis Services and Reporting Services.
When upgrading using the side-by-side option, you can use the Copy Database Wizard to migrate individual databases and database objects from one SQL Server version to a supported higher version. You connect to the SQL Server database server using SQL Server Management Studio, which is the primary SQL Server management and maintenance tool. To launch SQL Server 2012 Management Studio, click Start and choose All Programs, Microsoft SQL Server 2012, then SQL Server Management Studio. When prompted, connect to the SQL Server instance you want to manage. You launch the Copy Database Wizard from within SQL Server Management Studio. The wizard steps you through the copy process, letting you choose the source database and your destination as a new database on the SQL Server 2012 database server. Alternatively, you can back up all databases and script security settings, then use this to configure your new instance.
Tasks after an upgrade
You should monitor application performance and review Windows and SQL Server Error logs to validate that no issues occur after the upgrade. Moreover, you should rebuild all user databases indexes, and run sp_updatestats to run UPDATE STATISTICS against all internal and external tables.
Besides these tasks, execute DBCC UPDATEUSAGE against all user databases, because running it corrects page and row counts in catalog view.