How to transfer or migrate from old SQL cluster 2000 to new SQL cluster 2000

I have two Windows 2000 servers (Advance Edition) to form a Windows Cluster. I also install MS SQL 2000 Enterprise Edition on the cluster to form a MS SQL cluster. Now, I want to upgrade the hardware and OS (but keep on using SQL 2000), so I install Windows 2003 server Enterprise Edition on two new servers to form a new Windows Cluster. I am planing to install MS SQL 2000 Enterprise Edition on the new cluster, so the old SQL cluster and new SQL cluster are side by side. I would like to know how to setup a new SQL cluster (I know it has problem to rename SQL Cluster name, so how to fix this problem)? And how to transfer everything (such as system databases, users database, sql user account, password and maintenance plan jobs etc) from old SQL cluster to new SQL cluster? And how to switch over from old SQL cluster to new SQL cluster?

Thanks a lot !

This article which seems to be quite comprehensive:

http://vyaskn.tripod.com/moving_sql_server.htm

Though i've not done this for a while, broadly speaking i think you'll want to follow these steps:

1) install sql 2000 on the NEW cluster

2) backup all the databases (inc master, model, msdb)

3) detach the databases from the OLD cluster

4) move the files to the NEW cluster

5) restore the system databases on the NEW cluster

6) attach the user databases on the NEW cluster

You'll probably want to check out the sections in BOL about starting sql in single user mode and the info on sp_detach_db and sp_attach_db

Hope this helps!!

|||

Hi richbrownesq,

Thank you for your reply. The article is quite good. However it works for MS SQL single server, not for MS SQL Cluster.

The problem of MS SQL cluster is to rename the SQL Cluster name (i.e. switch over from old cluster to new cluster). If the new SQL cluster uses different cluster name from the old SQL Cluster, the job (created by old SQL cluster name) in new SQL cluster cannot be modified or deleted. So any idea to solve this problem?

Thanks !

|||

Sorry, can you expand on what you mean by:

"the job (created by old SQL cluster name) in new SQL cluster cannot be modified or deleted"

|||

For example, I create a scheduled backup job in old SQL cluster using Enterprise Manager to backup user databases. And now, I transfer everything (including the backup job) in old SQL cluster to new SQL cluster which uses different cluster name. However, I cannot modify or delete the scheduled backup job in new SQL cluster using Enterprise Manager, because the new SQL cluster name is different. The problem can be solved if the new SQL cluster name uses the same name, but how can I do so?

Thanks

|||

This may be due to the value of originating_server in msdb..sysjobs being the old clustername. Try updating the value of this to be your new cluster name in the form server/instance.

Hope that solves the problem.

|||

Updating the value of originating_server in msdb..sysjobs can solve the problem. Thanks!

By the way, if I setup a new SQL cluster with new cluster name and new cluster IP, and then I update the DNS entry (old SQL cluster name mapping to new SQL cluster IP) for switching over from old SQL cluster to new SQL cluster. Is it any drawback or potential problem using this switching over method?

Thanks a lot !

|||

I've not used this specific approach- i've generally been fortunate enough to only have one connection string and a decent size window of downtime to be able to just update it without worrying about DNS changes.

However, i know of people who use an approach similar to this for DR and it seems to work, so hopefully you won't see any issues.

Cheers

|||

Hi Richbrownesq,

Thank you very much !