Getting Started with SQL Server 2008 R2 - Microsoft SQL Server 2008 R2 Administration - page 15

The important process of the upgrade is a collection of database objects required from the
previous versions of SQL Server. During the upgrade process, Transact-SQL code objects are
essentially passive. Whether the process of an 'in-place' upgrade or a 'side-by-side' upgrade is
chosen, the end result will be the same as far as your Transact-SQL code is concerned.

If in case any external scripts required on the user database are
not associated within Stored Procedures or Functions, then they will
remain unchanged by a direct upgrade process. You must apply these
scripts manually to ensure that the post-upgrade tasks for the user
databases can be classified as completed.

Based upon the new server or existing server upgrade, it is a best practice to move any
Transact-SQL external scripts to a new server, or correct references within your database to
those scripts.

The Post Upgrade process is also equally important and it is easy to analyze how the new SQL
Server 2008 R2 instance performs compared with your original SQL Server 2000, SQL Server
2005, or SQL Server 2008 instance. Download the RML Utilities for SQL Server from http://
go.microsoft.com/fwlink/?LinkId=133157 under the Microsoft download site. These
utilities stand as a suite of tools for load testing, workload replay, and performance analysis.

As soon as you have completed the upgrade tasks, you need to perform two important steps
to ensure that the initial process of SQL Server upgrade is accepted. The two steps are:

1. Integrate the new SQL Server instance into the application and database

server environment.

2. Application testing process, such as: Change connectivity settings to the new server,

if a side-by-side upgrade is chosen.

3. Change authentication mode of upgraded SQL Server instance.

Linked servers: The current system might depend on linked server
relationships and definitions that must be applied for an upgrade. The
application might fail, if those linked servers are not defined and tested
correctly.
Logins: All the required logins and users with relevant privileges on the
database must be applied for an upgrade of databases. By using Transfer
Logins task from SSIS, the logins can be transferred between a source and
a destination SQL Server instance.
Scheduled jobs: The routine administrative tasks are coupled as jobs that
are executed using SQL Server Agent service, which are stored in msdb
system database, which may not be part of the usual upgrade method. In
such cases, it is essential to script all the scheduled jobs on the source
server and execute them on the destination server.