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 !

How to tailor CREATE DATABASE for an arbitrary instance

Suppose the following:

As part of a product install (using InstallShield)...


I create a SQL Server Express instance (say "X") via a silent install

I supply a script to create a database in instance X.

The idea, of course, is to have a fully automated install. But there's one problem I can't quite see how to work around:

- the CREATE DATABASE statement needs the name of a file to contain the database, and that file needs to be in a folder that belongs to the instance (e.g. Microsoft SQL Server\MSSQL.1, Microsoft SQL Server\MSSQL.2, etc).

Is there a syntactic variant that allows me to avoid this problem?

Thanks
Josh

hi,

don't know if this can be a solution to your problem, but in the CREATE DATABASE syntax only the database name is mandatory.. http://msdn2.microsoft.com/en-us/library/ms176061.aspx

if you only specify

CREATE DATABASE myDb;

the "myDb" name will be used for physical files naming, resulting in a "myDb.Mdf" and "myDb_log.Ldf" files, stored in the folder hosting all other databases..

the new "myDb" will inherits all settings applied to "model" database, both regarding database settings as physical settings as well (size, growth, etc)...

but you loose control over the physical and logical name specification, and, of course, you can this way no longer specify size, growth, max size, ...

regards

|||

Andrea:

Between then and now, I found what I needed in one of your posts from yesterday.

With very minor adaptation, I arrived at the following (which worked like a charm):

DECLARE @.itemp VARCHAR(255);
DECLARE @.RegKey VARCHAR(255);
DECLARE @.instance VARCHAR(255);
DECLARE @.subfolder VARCHAR(255);
DECLARE @.rootfolder VARCHAR(255);
DECLARE @.DBFileName VARCHAR(255);
DECLARE @.DBLogName VARCHAR(255);
DECLARE @.CreateDB VARCHAR(MAX);

SELECT @.itemp = CONVERT(varchar, SERVERPROPERTY('InstanceName'));
IF @.itemp IS NULL
SET @.instance = 'MSSQLServer';
ELSE
SET @.instance = @.itemp;

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
@.value_name = @.instance,
@.value = @.subfolder OUTPUT;

SET @.RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @.subfolder + '\Setup';

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key = @.RegKey,
@.value_name = 'SQLDataRoot',
@.value = @.rootfolder OUTPUT;

SET @.DBFileName = @.rootfolder + '\DATA\' + 'MyDatabase.mdf';
SET @.DBLogName = @.rootfolder + '\DATA\' + 'MyDatabase_log.mdf';

-- Create the database

SET @.CreateDB = 'CREATE DATABASE [OTHGMD] ON PRIMARY
(NAME = ''MYDATABASE'', FILENAME = ''' +
@.DBFileName + ''', SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) ' +
'LOG ON (NAME = N''MYDATABASE_log'', FILENAME = ''' +
@.DBLogName + ''', SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) ' +
'COLLATE SQL_Latin1_General_CP1_CI_AS;'

EXEC (@.CreateDB);
GO

Thanks Very Much
Josh

p.s. A pox on the SQL Syntax committee for making us have to do this. Can you think of a good reason why the file name can't be a variable?

|||

hi,

usually referenced objects are to be considered constants or literals... only parameters can (and should be) variable

as you surely know, you are executing dynamic SQL, composed on the fly and executed... SQL Server takes even care of that as it can, somehow and sometime, avoid recompilation of some dynamic statements, but that' all... just a "limitation" of current implementation of DBMS engines, and, as per the CREATE DATABASE statement, this regards SQL Server "only" as each different vendor provides it's own "syntax" to generate new catalogues and database... it's not that standard... AFAIK the ANSI standard starts with the SCHEMA, and, for the ANSI commetee, there's no part for "variables", as all must be resolved in "literals"... it is the compilation phase, in SQL Server as in other engines, that pepare the final statement, replacing variables with literals (where allowed by the host language/tool/engine) and finally passes the "final" result as output to the compiling phase.. tokenizer, algebrizer and other technologies come then into play.. but this is another story

regards