SQL Server 2008 R2 Inplace Upgrade

Let's look at SQL Server 2008 upgrading in-place instead of fresh install. Dissimilar from side-by-side install, an in-place upgrade perpetually alters the SQL Server components, data, and metadata objects, and there is no way of going back. You will probably be more comfy taking the side-by-side migration path instead performing an in-place upgrade, unless a side- by-side migration is not likely because of disk space limitations, you have very limited SQL Server features in use, or you are equally assured about the potential success of the upgrade procedure since you've done broad issue resolution with the help of the Upgrade Assistant.

If you are executing an in-place upgrade of the Database Engine, it is strongly suggested that you first do the following:

Create full, confirmed backups of your existing SQL Server dbs.
Run the suitable DBCC consistency checks .
Ensure the system databases on your pre-2008 server are all set to auto-grow (master, msdb, tempdb, and model).
Deactivate any startup stored procedures that get kicked off when the SQL Server service starts.
Deactivate database replication and blank the replication log.

Upgrading the Database Engine

You execute an in-place upgrade by executing the SQL Server Installation Center. On the Installation page, you can summon the Upgrade Wizard to upgrade from SQL Server 2000, 2005, or 2008 . After first running the Setup Rules check and installing the Setup Support Files, the Upgrade Wizard basically runs the installation process. The key dissimilarities among running a new install vs. an upgrade is that during the upgrade procedure, you pick an existing default or named instance on the Select Instance screen. After selecting the instance to upgrade, you see the Feature Selection page. The features to be upgraded are preselected. You can’t modify the features to be upgraded, and you can’t add features throughout an upgrade operation.

To add additional features, you need to run the Installation Center again afterward the upgrade operation is finished. After making selections on the Features Selection page, step through the Instance Config, Disk Space Requirements, and Server Configuration screens, are making changes as needed. As an example, authentication and login info are passed forward from the previous instance of SQL Server. You can allocate the same login account to all SQL Server services, or you can configure each service account independently. You may also stipulate whether services start automatically, are started manually, or are disabled. Next, you are offered with options for upgrading your full-text catalogs. In SQL Server 2005 and previous versions, each full-text index resided in a full-text catalog that belonged to its own filegroup and was preserved as a db file. In SQL Server 2008, a full-text catalog is a rational idea that refers to a group of full-text indexes and is no longer treated as a separate database file /w a physical path.

Yet, through upgrade of any full-text catalog, a new filegroup is still produced on the same disk to preserve the pre-upgrade disk I/O behavior. If the old full-text catalog path is invalid, yet, the upgrade places the full-text index in the same filegroup as the base table or in the main filegroup if the table is divided.

Below three options are presented for upgrading your current full-text catalogs:

Import—Usually, import is the fastest method of upgrading, but an introduced full-text catalog does not use the new and improved word breakers presented in SQL Server 2008, so you might want to reconstruct your full-text catalogs ultimately if not during the upgrade.
Rebuild—This method uses the new SQL Server 2008 word breakers, but rebuilding indexes can take awhile.
Reset—When you use this technique, SQL Server 2005 full-text catalog files are removed, but the metadata for full-text catalogs and full-text indexes is reserved. The catalog remains unfilled until you manually issue a full population after the upgrade finalizes.

After selecting your full-text upgrade option, you next pick your Error Reporting options, and then the Upgrade Rules check is run to certify your system configuration with the options and features chosen during the upgrade procedure. If all the rules pass, you can review the upgrade operation on the Ready to Upgrade page, which also shows the path to the upgrade config file (this is useful for setting up and execution unattended upgrades from the command line. click Upgrade to start the upgrade process. The upgrade process routinely upgrades all objects that are shared to all databases, including the following: Logins, users, and permissions , Tables, views, indexes, and constraints ,Stored procedures, functions, and triggers ,User-defined types, rules, and defaults

You may screen the upgrade progress on the Upgrade Progress screen. Dependent on your hardware config and the features to be upgraded, the upgrade operation can take from roughly 40 minutes to several hours. The databases on the instance being upgraded continue inaccessible pending the upgrade is finished. When the upgrade completes, it displays the status of each module and also provides the place of the upgrade log. You may need to restart your system.

When your upgrade of the DB Engine is complete, it is suggested that you do the following on all databases:

Repopulate your full-text catalogs if you chose not to rebuild them during the upgrade.
Run the sp_updatestats system stored procedure to update statistics.
Reregister your server in SSMS.