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

Migrate stand alone sql server to a cluster without downtime

Hi
I have read most the articles and posts about move sql server.
Here is my situation:
I have stand alone standard sql server 2000 and with about 40 databases. And
I am moving the server to a SQL cluster(Active/passive). I have to use the
old name, IP on the new server and with minimum down time. Anybody has done
this? What is the precedure? Will Virutas BackupExe backup/restore work? I
know Deattach/Reattach works but I have to do it one DB at a time, right?
That will take a long time. Will permission be carried over using these
mothed?
Any information will be greatly appreciated.
Thanks,
Yuhong
First, you cannot use the same name and IP address. The cluster
installation and any data transfer will fail.
Here is how you do this.
First, make sure all your SQL connections to the old box use a DNS entry to
resolve their names. Eliminate any hard-coded IP addresses by using client
aliases if necessary. You will use an SVC record in the DNS system to
repoint the old connections to the new server when you do the changeover.
Test this using a bogus alias before going live.
Set all your production databases to full recovery and implement a log
backup plan.
Use backup and restore to create the databases on the new system. Use the
'NORECOVERY' or 'STANDBY' option so you can apply later transaction logs.
Manually copy and apply the transaction log backups from the old system to
the new cluster.
I suggest scripting the following tasks well ahead of time.
Run a log backup WITH STANDBY on each database on the old server. Copy the
files (or restore from a common network share) and restore the final log for
each database using the WITH RECOVERY option.
When you have transferred the final database log, use sp_change_users_login
to remap your pre-generated logins to the users in the restored databases.
I typically use a bogus password for SQL logins until this step so that
users cannot accidentally connect to the new server.
Stop the old server. Add the SVC record to DNS to alias the old servername
to the new service. Remember to include the port number if you are using a
named instance or a non-standard port.
Get ready to deal with the handful of systems you missed that are still
connecting via IP.
Remember to test everything before going live. If you mess up, you can
always bring the old server back up and take the databases out of standby to
get back to where you started. The actual changeover should take less than
one hour IF you plan, test, and practice.
Good luck.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:59B178D2-D730-4142-BE76-E0A38FE53F69@.microsoft.com...
> Hi
> I have read most the articles and posts about move sql server.
> Here is my situation:
> I have stand alone standard sql server 2000 and with about 40 databases.
And
> I am moving the server to a SQL cluster(Active/passive). I have to use the
> old name, IP on the new server and with minimum down time. Anybody has
done
> this? What is the precedure? Will Virutas BackupExe backup/restore work? I
> know Deattach/Reattach works but I have to do it one DB at a time, right?
> That will take a long time. Will permission be carried over using these
> mothed?
> Any information will be greatly appreciated.
> Thanks,
> --
> Yuhong
|||Thanks, Geoff! This is very helpful. I have setup a test cluster and I will
have to do a few full test before the real migration.
Thanks again!
Yuhong
"Geoff N. Hiten" wrote:

> First, you cannot use the same name and IP address. The cluster
> installation and any data transfer will fail.
> Here is how you do this.
> First, make sure all your SQL connections to the old box use a DNS entry to
> resolve their names. Eliminate any hard-coded IP addresses by using client
> aliases if necessary. You will use an SVC record in the DNS system to
> repoint the old connections to the new server when you do the changeover.
> Test this using a bogus alias before going live.
> Set all your production databases to full recovery and implement a log
> backup plan.
> Use backup and restore to create the databases on the new system. Use the
> 'NORECOVERY' or 'STANDBY' option so you can apply later transaction logs.
> Manually copy and apply the transaction log backups from the old system to
> the new cluster.
> I suggest scripting the following tasks well ahead of time.
> Run a log backup WITH STANDBY on each database on the old server. Copy the
> files (or restore from a common network share) and restore the final log for
> each database using the WITH RECOVERY option.
> When you have transferred the final database log, use sp_change_users_login
> to remap your pre-generated logins to the users in the restored databases.
> I typically use a bogus password for SQL logins until this step so that
> users cannot accidentally connect to the new server.
> Stop the old server. Add the SVC record to DNS to alias the old servername
> to the new service. Remember to include the port number if you are using a
> named instance or a non-standard port.
> Get ready to deal with the handful of systems you missed that are still
> connecting via IP.
> Remember to test everything before going live. If you mess up, you can
> always bring the old server back up and take the databases out of standby to
> get back to where you started. The actual changeover should take less than
> one hour IF you plan, test, and practice.
> Good luck.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:59B178D2-D730-4142-BE76-E0A38FE53F69@.microsoft.com...
> And
> done
>
>
sql

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