Re: Migrate stand alone sql server to a cluster without downtime

• Previous message: Yuhong: "Migrate stand alone sql server to a cluster without downtime"
• In reply to: Yuhong: "Migrate stand alone sql server to a cluster without downtime"
• Next in thread: Yuhong: "Re: Migrate stand alone sql server to a cluster without downtime"
• Reply: Yuhong: "Re: Migrate stand alone sql server to a cluster without downtime"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 11 Jan 2005 12:13:34 -0500

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

• Previous message: Yuhong: "Migrate stand alone sql server to a cluster without downtime"
• In reply to: Yuhong: "Migrate stand alone sql server to a cluster without downtime"
• Next in thread: Yuhong: "Re: Migrate stand alone sql server to a cluster without downtime"
• Reply: Yuhong: "Re: Migrate stand alone sql server to a cluster without downtime"
• Messages sorted by: [ date ] [ thread ]