Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Upgrade SQL Server 2000 Instance to SQL Server 2005


Introduction

The purpose of this tutorial is to lay out the general guidelines for planning a SQL Server 2005 upgrade. As the new version of SQL Server is coming, all the companies want to upgrade the older version of SQL Server to the newer version. Here the DBA role has again come into the picture for doing the same.

Why do we require to Upgrade

The newer version contains additional features & improvements in the newer product. Below are the few reasons for upgrading SQL server:-

1) High Availability solutions like Database Mirroring

2) Managing VLDB (Very large Databases) easily & efficiently

3) Stronger Security

4) BIDS (Business Intelligence Development Studio)

For more information, please refer the below link @Microsoft, "Why to upgrade to SQL Server 2005"


SQL Server Upgrade menthods

In SQL server we are provided with two methods for SQL upgradtion:-

1) In-Place Upgrade

2) Side-by-side Upgrade

In this blog I will cover the In-Place upgrade method. Second option will be covered in the next blog very soon.

In-Place Upgrade

This option is the easiest way of upgrading the SQL Server to the newer version. If everything goes well its good, otherwise its very time consuming approach to roll back the changes back to the older vrsion.This is the automatic process in which you replace the older version of SQL server instance to the newer version, called, In-Place upgrade.

Below are the few characteristics of In-Place upgrade

Number of instances
One only
Number of servers involved
One
Data transfer
Automatic
Server instance configuration
Automatic
Helping utility
SQL Server Setup


The main drawback of using this option is that you cannot upgrade the single database to the newer version. In that case your need to adopt another option called, Side-by-Side upgrade.

Secondly, remember you cannot upgrade “CROSS-BIT” upgrade like, upgrading SQL server 32 bit instance  to SQL server 64 bit instance or vice versa. This can be only possible with Side-by-Side upgrade method.

Below diagram shows how the SQL Instance is replaced with the newer version of SQL Server.

 

Advantages of In-place Upgrade:-

1)    Its speedy

2)    It’s easy to perform

3)    It’s a automated process

4)    Less  downtime  required

5)    No additional hardware required

Disadvantages of In-Place Upgrade

1)    Having to upgrade the whole instance

2)    Not able to upgrade the single database

3)    Disk  space issues during upgrade process

4)    Check the instance for Backward compatibility

5)    Rollback  is complex in case of failure

Pre-requisite for In-Place upgrade

1)    Download /Install .Net Framework 2.0 or higher

2)    Download/Install Windows Installer 3.1 Service.

3)    A remote Registry service is required during up gradation. So, try to start this service & see if it started successfully. If it doesn’t check its dependencies & see if RPC is running or not. Without this you are not able to install the Service Packs on the upgraded SQL server Instance because it will show you the error that upgradtion has not completed successfully.

4)    Backup the System  databases

5)    Run DBCC Checkdb to ensure database are clean

How to do in place upgrade:-
Run the setup from the SQL 2005 & pass on all the dialog box until the below one

In the above window, you need to select the instance to upgrade; in our case we will take the default instance. Now click on the Installed Instances button


Here you will have to check the Check box to upgrade the installed instance on the SQL server. For coming windows after the above window, select the default values.
Post Upgradtion Tasks

1)    Change the Compatibility Modal of Databases to 90

2)    Executed DBCC UPDATEUSAGE on destination databases to update usage counters to ensure that correct values exists for Table and index row counts.

3)    Executed DBCC CHECKDB WITH DATA_PURITY on destination databases to check the databases for column values that are not valid or are out of range.

4)    Take the fresh Full backups
Hope this topic helps the DAB to perform the in place up gradation smoothly & efficiently.

Comments

Leave a comment on the original post [sanjeev-sqlmaster.blogspot.com, opens in a new window]

Loading comments...