Building the new machine on an isolated network certainly has advantages. When migrating a MSCS clustered server, the installer will not let you change the virtual server name without redoing the installation. Obviously, not an option when time is of the essence. So, your options are to install the new cluster with the same name(s) and IP addresses as the existing cluster on an isolated network (or in a different domain. Then, at cutover, shutdown the old and connect the server to the existing network or domain. Alternatively, we just rename the machines and virtual server and create a DNS entry to redirect calls to the old server name.
Replication might work in some instances. But, there is some lag so you have to ensure that all data has been written to the subscriber before shutting down the old server. Transactional replication can only publish tables with primary keys. Many of my databases contain staging tables that do not always have a primary key.
I prefer the backup/restore method since it guarantees that the source and destination databases will be identical. Sequencing activities correctly and making sure that you have scripts to handle as much as possible will help keep the downtime to a minimum. It also helps to practice (a lot) the migration so that you can set reasonable expectations of downtime.
Jay
From: Guzman
Sent: Tue 30-May-06 17:52
To: LazyDBA Discussion
Subject: RE: Server to Server Migration
I certainly agree with Jay. You can do a full backup and restore with
the server set, leaving the old one on line, than at cut-over time, only
an incremental backup/restore would be necessary. You could create an
isolated network with a NAT router, to avoid naming conflicts, so you
can test the new machine with the partial restores, but still allow
'real' network testing.
Jay, would it make any sense to have the new server with a different
name, and start replication between servers? Transactional replication,
of course, would make the two servers identical, then cut-over time
would only be the time it takes to change the server name and reboot.
User activity maybe too high for this, and that kind of replication
would certainly slow things down, but is it even reasonable?
Dan
------------------------------------------------------------------
I do that type of upgrade slightly differently. I rebuild the new box
from scratch minus the databases. Then, I restore all databases on the
new server and allow some time for testing of the new configuration. Of
course, some care has to be taken such that no inadvertent updates are
done to production data through linked servers, DTS packages, etc.
On the morning of the cutover, I restore the system databases. Then, I
restore, but do not recover, the user databases. Through the day, I
continue to restore transaction logs to the user databases on the new
server (again not recovering the databases). This way the new server is
never far behind the production server. At the cutover time, kick users
off the old server, do final transaction log backups, copy them to the
new server apply them and recover the databases.
I have done used this method on a number of servers. If you can script
the activities and coordinate steps, the downtime could be nominal. I
have upgraded a box with 150GB of very active databases with only 10
minutes of application downtime.
Jay
From: Guzman
Sent: Tue 30-May-06 17:29
To: LazyDBA Discussion
Subject: Re: Server to Server Migration
This would also be a good time to document your Disaster Recovery plan.
Me? I would install the same OS and SQL versions on the new machine,
offline, using the old names AND locations for everything. Then I would
do a complete backup of the SQL server, not just the data, from the old
server, then restore that to the new server. Offline the old server,
reboot and online the new server. Viola! down time should only be the
time it takes to run the backup and then the restore/reboot.
Good luck.
Dan
I'm looking for the best overall SQL Server hardware upgrade
methodology. We need to move an instance of SQL Server from an old
server to a new one with minimal downtime. However, due to limitations
of our third party software which uses the SQL Server as a back-end,
both the server name and instance name must be the same on the new
server. Everything must be copied over, from security, views and stored
procedures to backup schedules and replication configuration.
I've read about several different approaches for doing this, but none of
them are very simple. I'm looking for the LAZY (as in LazyDBA) way.
What is the most acceptable way to do this?
Michael Phillips
Cardiac Science
[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page