SQL Server 7.0 to SQL Server 2000 Upgradation

Introduction 2

Purpose 2

Pre-Upgrade Considerations 2

Things to be moved 3

Time
required to do the upgrade 3

Method to Choose 4

1. Upgrading to SQL Server 2000 from
the SQL Server 7.0 in a box (
Installing over SQL Server 7.0 ) 5

Back out plan 5

2. Uninstall existing SQL Server 7.0
and Install SQL Server 2000 ( Clean
Install on current box ) 6

Back out plan 6

3. Install
another instance of SQL Server 2000 and keep existing SQL Server 7.0 intact. 7

Back out plan 7

4. Install SQL Server 2000 on a new
machine and keep existing SQL Server
7.0 intact on the current box. (
Clean Install on new box / Two System upgrade ) 7

Back out plan 8

Introduction

This document details the three option of upgrading SQL
Server 7.0 databases to SQL Server 2000 databases.

Method 1: Upgrading to SQL Server 2000 from the SQL
Server 7.0 in a box

( Installing over
SQL Server 7.0 / Single Server Upgrade)

Method 2: Uninstall existing SQL Server 7.0 and
Install SQL Server 2000

( Clean Install on
current box )

Method 3: Install another instance of SQL Server 2000
and keep existing

SQL Server 7.0
intact.

Method 4: Install SQL Server 2000 on a new machine and
keep existing

SQL Server 7.0
intact on the current box.

( Clean Install on new box / Two System
upgrade )

Purpose

Upgrading SQL Server 7.0 Databases to
SQL Server 2000.

Pre-Upgrade
Considerations

Before deciding on upgrade the current
databases to SQL Server 2000, the following points should be considered:

·
How
well does the database server handle the current workload?

·
Have
any potential bottlenecks been identified on the current system?

·
What
is the projected growth rate, in terms of data volume, number of end users or
both? Will the current system after being upgraded to SQL Server 2000, be able
to handle this growth?

·
To
what extent will database downtime, as part of upgrade affect the business?

·
To
what extent will the database upgrade affect the application?

·
What
is involved in testing the application with the databases upgraded to SQL
Server 2000?

·
According
to Microsoft recommendation upgrade to SQL Server 2000 require 1.5 times of
disk space taken by the SQL Server 7.0 databases.

Things
to be moved

Regardless of which migration method we choose, we need to
ensure that all or whatever applicable in the below list are migrated and/or
updated:

1.
Database Data and Log Files

2.
SQL Server Logins

3.
Database users and their mappings to SQL Server Logins

4.
Full-Text Indexes (enable and repopulate on new server)

5.
Other Indexes (rebuild/repopulate on new server)

6.
Statistics (update on new server)

7. SQL
Agent Jobs

8. DTS
Packages

Time
required to do the upgrade

It is difficult to exactly determine the
time required to perform the upgrade. The upgrade process includes.,

·
Perform
Database Consistency Checks (DBCC), with no user logged on to the system. The
DBCC utility would check the integrity of the data on the database and do the
minor correction on corrupted data. It is advisable to do before the database
backup.

·
Offline
backup

·
Hardware upgrades ( If necessary )

·
Operating
System upgrades ( If necessary )

·
Installation
/ Upgrade to SQL Server 2000

·
Database
Migration from the old server to the new version

·
Creation
of Login, SQL Agent Jobs , DTS Packages,
mapping users ..,

·
Database Testing

·
Application Testing

·
Backup
of upgraded databases

Method
to Choose

On the following methods of upgrading databases from SQL Server 7.0 to
SQL Server 2000 depends on the answer to the following questions:

1. Will you need to keep the current
database functional while you are testing the migration to SQL Server 2000?

( If so, you may require new box or a
new instance for SQL Server 2000.

Methods 3 & 4 support
this )

2. If you wish to keep the current
database functional while testing with SQL Server 2000, do you have budget to
acquire a new system?

( If so, choose Method 4 )

3. If you wish to keep both
databases on SQL Server 7.0 and SQL Server 2000, you will need the box in
hardware level, in term of CPU, Memory,
Disk Spaces to support the both databases. Otherwise upgrade the hardware.

( Here Method 3 can be used )

4. If you choose not to upgrade the
existing server or acquire the new server and do not have enough disk space to
host both databases (7.0 and 2000 ), then existing databases can be
overwritten.

( Methods 1 & 2 will
satisfy this condition )

1. Upgrading to SQL Server 2000 from the SQL Server 7.0 in a box

( Installing over SQL Server 7.0 )

In this method, we would choose the
upgrade option from the SQL Server 200 Setup program, the program would detects
SQL Server 7.0 as the existing installation and automatically selects and
installs a default instance of SQL Server 2000.

·
In
this process the upgrade will overwrite existing SQL Server 7.0

·
SQL
Server 2000 Setup upgrade process would upgrade all the databases, there is no
flexibility in selecting the databases for upgrade

·
To
rollback we have uninstall the SQL Server 2000 and install SQL Server 7.0

·
High
downtime or databases on existing instance will not be available during the
upgrade process.

Back out plan

Backup out involves uninstalling
the newly installed SQL Server 2000 and

clean installing SQL Server 7.0
then restoring the databases from the backup.

This would be followed by addition
steps which are mentioned in “Things to be

moved”

1.
Before
upgrading to SQL Server 2000, Perform database consistency check.

2.
Backup
all the databases.

3.
Verify
the backup to make sure that can be used on restore.

4.
Uninstall
the SQL Server 2000

5.
Install
the SQL Server 7.0

6.
Apply
the required SQL Server Service pack, patches and hotfixes.

7.
Restore
the databases which are backed up.

2. Uninstall existing SQL Server 7.0 and Install SQL Server 2000

( Clean Install on current box )

On this methods we have four options on HOW to
migrate databases from SQL Server 7.0 server to the SQL Server 2000. They are.,

a) Backup and Restore,

b) Detach/Attach.

·
To
rollback we have uninstall the SQL Server 2000 and install SQL Server 7.0

·
High
downtime or databases on existing instance will not be available during the
upgrade process.

Back out plan

Backup out involves uninstalling
the newly installed SQL Server 2000 and

clean installing SQL Server 7.0
then restoring the databases from the backup.

This would be followed by addition
steps which are mentioned in “Things to be

moved”

1.
Before
upgrading to SQL Server 2000, Perform database consistency check.

2.
Backup
all the databases.

3.
Verify
the backup to make sure that can be used on restore.

4.
Uninstall
the SQL Server 2000

5.
Install
the SQL Server 7.0

6.
Apply
the required SQL Server Service pack, patches and hotfixes.

7.
Restore
the databases which are backed up.

3. Install another instance of SQL Server
2000 and keep existing

SQL Server 7.0 intact.

·
Minimal downtime of the existing instance

·
In parallel databases on SQL Server 7.0 and 2000 co exist in same box.

·
We can install only named instance of SQL Server
2000 with an existing installation of SQL Server 7.0

·
Ensure the current box has the required
capability in terms of load on CPU, I/O Throughput, Memory and disk space to
host new instance of SQL Server

On this methods we have four options
on HOW to migrate databases from SQL Server 7.0 server to the SQL Server
2000. They are.,

a) Run the Copy Database Wizard that
comes with SQL 2000,

b) Run DTS with the Copy Objects and
Data between SQL Servers,

c) Backup and Restore,

d) Detach/Attach.

Back
out plan

Since the existing instance untouched, the
back out plan is quite simple as to
uninstall the newly installed instance of SQL Server 2000.

4. Install SQL Server 2000 on a new machine and keep existing

SQL Server 7.0 intact on the current
box.

( Clean Install on new box / Two
System upgrade )

·
This
process minimize the risk of problems during the upgrade process.

·
Minimal
database downtime

·
Current
databases on SQL Server 7.0 are accessible during the upgrade process.

·
Flexibility
to upgrade on the select databases while leaving the other database in the
existing system

·
Ability
to move or copy databases to the SQL Server 2000 at a convenient time

Back out plan

In case of any issues, the databases
and application on the existing system are unchanged which can be used.

On this methods we have four options on HOW to
migrate databases from SQL Server 7.0 server to the SQL Server 2000. They are.,

a) Run the Copy Database Wizard that
comes with SQL 2000,

b) Run DTS with the Copy Objects and
Data between SQL Servers,

c) Backup and Restore,

d) Detach/Attach.