Upgrade SQL 2000 to 2005 Cluster

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Upgrade SQL 2000 to 2005 Cluster

Author

Topic

poser
Posting Yak Master

124 Posts

Posted - 2007-09-17 : 11:50:14

Good Morning!
I am beginning the process of moving SQL 2000 databases to SQL 2005 cluster.
Has anyone already did this and is there anything I should know?
We plan on leaving the SQL 2000 database up during this process and granting read only at the time.
I am nervous about this as this is a high profile database in our dept.
I don't even know if I know where to begin!
Any suggestions are truely welcome!
Thanks,
/P

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-17 : 23:49:18

If you'll move sql2k dbs to sql2k5 cluster, just backup dbs in sql2k and restore them in sql2k5.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-18 : 00:51:13

Here are some things to consider:

Do you have valid backups? Are you sure? test them.

Authentication - How do people authenticate to the current server? if you are using domain logins/groups for auth things will be easier. If you need to move the logins from the old server to the new server you will want to use a script from microsoft called sp_help_revlogin. You can get it here: http://support.microsoft.com/kb/246133) to script out the users and their password hashes. You will want to edit the output of this script removing accounts that don't need to be re-created on the new host. You then copy this output to a Management Studio session on your new SQL2K5 box and execute it to create the logins w/passwords. Read that article completely so you understand what it is doing.

NOTE: sp_help_revlogin does not set the default database for some reason. Derrick Leggett has created a modified version of the script that does. I use this one all the time, but YMMV. Here is a link to the code here in the sqlteam forums http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35143 . Also, sp_help_revlogin does not script out fixed server roles. You will need to look through your users on the SQL2k box and make note of any users with fixed server rols (sysadmin, dbcreator, etc.)

Are you going to use sp_detachdb/sp_attachdb or do a backup and restore to move the database to the new host? I would go with backup/restore personally since that is a good thing to be familiar with. detach/attach is also doable and might actually be quicker. Be familiar with the steps needed with whichever way you go. Read books online (BOL) for more info.

Here is an outline of what I do when moving a db to a new host:

generate sql logins with sp_help_revlogin on the source system

Get everyone out of the database and put source db (your sql2k system) in single user mode using query analyzer. I use code like the following to do this:

/******************************/
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE   -- put database in single-user mode
GO
/* 
.  Other
.
.  commands
.  
.  go
.
.  here
*/
ALTER DATABASE [dbname] SET MULTI_USER  -- take db out of single user mode when done
GO
/******************************/

perform your last tlog backup from the same query analyzer session if you are in FULL logging mode

Take a FULL backup, or do a differential backup if you have a busy system and are in FULL logging mode. Make sure you have the last Full backup though - you will need the FULL and the DIFF (or all the incremental backups) for the restore. These commands get execute in the 'other commands go here' section in the step above

Copy the backups to the new (destination) environment

Restore the database(s)

Run the sp_help_revlogin output to put the users in place

Edit any users that have fixed server roles and set appropriately (sysadmin, dbcreator, etc.)

Change the compatibility level of the restored db to version 90 (SQL2K5) if it isn't already.

test connectivity to new database

rebuild all indexes and run sp_updatestats

Since the SQL2K5 box is new, and I am assuming not in use yet, you can do a trial run pretty easily. Just skip the step where you put things in single user mode since it doesn't matter if you are getting every last transaction for the mock migration. This post is by no means complete and there are some gaps (obviously). Do some trial runs so you get things to run smoothly. Do as much as you can from a script so that you get things to be as consistent and reproducible as possible.

-ec

poser
Posting Yak Master

124 Posts

Posted - 2007-09-18 : 10:46:42

Thank you very much for the info.
I went and got the scripts; now I am going to
start testing.
Again Thanks,
R /P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources