migrating cluster instances to stand alone install

Hello all: looking for some general advice on proper approach. looking to move databases off of a clustered instance to a new (non-clustered) server.
Got several issues i'd like some advice on.

1. This is an OLTP instance that's been around for a while & it's pretty well encrusted with apps & processes that attach to it. Therefore it would be a very good thing if we didn't need to change the connection information in several hundred places after the move.
I've tested one approach to this that seems to work: moved the instance to a server which has the same name as the cluster resource associated with the clustered instance and an instance that has the same name as the clustered instance.
For example: cluster install is aiproddb\production, moved it to a box called aiproddb with a named instance called "production". There's a bunch of tedious network stuff that has to be done to make this work (binding an IP address to the MAC address of the new box and some murky DHCP reservation fiddling), but after the network crew got done cursing me it did finally work.
Does this seem like a reasonable approach?

2. What's the best way to transfer security info to the new instance? I used the transfer Logins DTS widget but had some problems with it not being able to find some groups in AD.

3. what's the best way to transfer DTS packages?

4. is it necessary for the new instance to be at the same patch level as the old instance? the old clustered instance is still at SP3 and i threw the latest SP4 on the new location. good/bad/indifferent?

5. i was planning on taking a full backup & restoring it to the new machine. Is there a better way? Is the wizard for copying databases a good thing?

Those are these issues i'm aware of and have given some thought to. There are probably things about this i haven't considered and would appreciate some word on.

thanks,
Garth:D 5. [...] Is the wizard for copying databases a good thing?

thanks,
Garth

NOOOOOOOOOOOOOOOOOOOOOOOOOOOO!!!!!!!!!!!!!!!!!!

:D

hmscott|||ok. that's one vote against the copy database wizard...|||Hello all: looking for some general advice on proper approach. looking to move databases off of a clustered instance to a new (non-clustered) server.
Got several issues i'd like some advice on.

1. This is an OLTP instance that's been around for a while & it's pretty well encrusted with apps & processes that attach to it. Therefore it would be a very good thing if we didn't need to change the connection information in several hundred places after the move.
I've tested one approach to this that seems to work: moved the instance to a server which has the same name as the cluster resource associated with the clustered instance and an instance that has the same name as the clustered instance.

I know this does not help you now, but in the future, try adding a layer of virtualization in between by using a DNS zone specific to your apps. Then you can move physical servers in and out of production smoothly without impacting application connection strings. Use one DNS A record per database (e.g. MyDB.dev.apps, MySecondDB.dev.apps and MyDB.test.apps and MySecondDB.test.apps). The apps point to the DNS name, the DNS name translates to a physical IP. When it comes time to migrate a database (or an entire server) you have one place to go to update IP addresses (the DNS server).

2. What's the best way to transfer security info to the new instance? I used the transfer Logins DTS widget but had some problems with it not being able to find some groups in AD.

Try exporting the logins to a file, cull selected logins that you don't want/need (sa comes to mind) and then inserting them with proper syntax. Somewhere there is an MS article about using BCP to do this. If I recall correctly, use "bcp log shipping sgl server logins" for your google. I also did it by copying and pasting into an Excel spreadsheet and then using formulae to build the SQL syntax. Crude, but it worked.

This will only build the logins and it will not link the users within the database to the logins (SID mismatch). For that you can use sp_change_users_login.

3. what's the best way to transfer DTS packages?

Depends on how much of your connection info is embedded. A while back I posted a script for backing up DTS packages to a structured file. If there aren't too many, this is a workable approach.

By the way, is this an upgrade to SQL 2005, or a lateral to another SQL 2000 instance? If the former, you have more work cut out for you. If the latter, you should really consider the former (ie, you should be working on an upgrade).

4. is it necessary for the new instance to be at the same patch level as the old instance? the old clustered instance is still at SP3 and i threw the latest SP4 on the new location. good/bad/indifferent?

Should be all right. Watch the AWE memory thing with SP4, but I don't remember specific issues with SP4. Be careful if you are using replication.

5. i was planning on taking a full backup & restoring it to the new machine. Is there a better way?

sp_detach and sp_attach?

The advantage with your method (if you have a large database) is that you can do a partial restore (which might take a long time) and then apply just the last log file to bring the new instance up to date to minimize your outage.

Is the wizard for copying databases a good thing?

Noooooooooooooooo!!!!!

But enough on that subject :D .

Those are these issues i'm aware of and have given some thought to. There are probably things about this i haven't considered and would appreciate some word on.

thanks,
Garth

Test, test, test, practice, practice, practice.

Good luck.

Regards,

hmscott

Migrating a single SQL2K Install to a SQL Clustered install.

Is it possible to to migrate a single standalone installation of SQL 2000
Enterprise Edition to be part of a clustered SQL installation. My initial
guess is that as SQL installs by default registering to the Server IP
address, and a clustered install requires its own IP address to be installed
under. Therefore I would have to unistall any SQL install and start from
scratch?
Thanks
Hi
You are correct, you first ahve to un-install and re-install. No other way.
Regards
Mike
"Siz Choudhury" wrote:

> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be installed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks
|||Be sure to back up all of your databases, so can restore them to the
cluster..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Siz Choudhury" <Siz Choudhury@.discussions.microsoft.com> wrote in message
news:E14A249A-0E2B-467D-80B6-BFCDE8B9C778@.microsoft.com...
> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be
installed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks
sql

Migrating a single SQL2K Install to a SQL Clustered install.

Is it possible to to migrate a single standalone installation of SQL 2000
Enterprise Edition to be part of a clustered SQL installation. My initial
guess is that as SQL installs by default registering to the Server IP
address, and a clustered install requires its own IP address to be installed
under. Therefore I would have to unistall any SQL install and start from
scratch?
ThanksHi
You are correct, you first ahve to un-install and re-install. No other way.
Regards
Mike
"Siz Choudhury" wrote:
> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be installed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks|||Be sure to back up all of your databases, so can restore them to the
cluster..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Siz Choudhury" <Siz Choudhury@.discussions.microsoft.com> wrote in message
news:E14A249A-0E2B-467D-80B6-BFCDE8B9C778@.microsoft.com...
> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be
installed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks

Migrating a single SQL2K Install to a SQL Clustered install.

Is it possible to to migrate a single standalone installation of SQL 2000
Enterprise Edition to be part of a clustered SQL installation. My initial
guess is that as SQL installs by default registering to the Server IP
address, and a clustered install requires its own IP address to be installed
under. Therefore I would have to unistall any SQL install and start from
scratch?
ThanksHi
You are correct, you first ahve to un-install and re-install. No other way.
Regards
Mike
"Siz Choudhury" wrote:

> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be install
ed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks|||Be sure to back up all of your databases, so can restore them to the
cluster..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Siz Choudhury" <Siz Choudhury@.discussions.microsoft.com> wrote in message
news:E14A249A-0E2B-467D-80B6-BFCDE8B9C778@.microsoft.com...
> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be
installed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks

Migrate form stand alone to clustered server

Here is the scenario:
We have an existing production non clustered SQL 2000 server instance that we need to migrate to a new clustered SQL 2000 server instance. We need to accomplish this without affecting the FQDN that applications use to call this server. I found this article on a solution to rename the server after an xcopy of the entire db structure. Here is the link http://vyaskn.tripod.com/moving_sql_server.htm. The other issue that we are trying to resolve is the time it takes for the snapshots of replication to run (in our case almost a full day). That is why this approach looked like it may be a good solution for us.

Here is the question:
Is it possible to move our existing database to a new clustered environment without having to change the FQDN that other applications use to access this database and without having to reinitialize replication?GrantAsh,

The link you posted could work. But, the problem is in the drive letters and the folders.

The problem is that for a clustered server the data drives need to be shared in the cluster. The concern that I would have is the drive letters that you used for your current SQL server would not match the Clustered SQL server drives.

But, if you can match the drive letters then copying the data is not a problem.

But I can't stress this enough, using the method described on that web page to change the SQL server name will not work for a Clustered SQL Server.

I would suggest reading the following:
http://support.microsoft.com/kb/244980/
http://www.sql-server-performance.com/clustering_2000.asp

bEH