SQL Server like to like Migration (Lift and Shift)

Question

SQL Server like to like Migration (Lift and Shift)

Satheesh E.P.

SSCrazy

Points: 2509
More actions
September 15, 2014 at 11:47 pm

#290078

Hi Folks,
One of my client is planning to move their Win2K3 servers to Win2K8 OS and all the servers are running with SQL Server and in on-premises. They had asked us an approach on how to perform the same. SQL Server will be moved as it is and there will be NO upgrade (they are planning SQL Upgrade as different project). The approach I know is to build SQL Server using Unattended Installation and backup and restore all objects and meta data. Could you please suggest me other ways for performing the same?
Thanks in advance.
Regards,
Satheesh
---------------------------------------------------
Thanks,
Satheesh.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

Once you've got your new SQL installation setup you have several options for migrating your databases. You've already mentioned backup and restore, there's also mirroring and log shipping too.

If you have a dedicated window of downtime and you're databases are not too big, you can just backup and restore the databases. However if you only have a short period of downtime and/or the databases are large, I would go with either mirroring or log shipping. The advantage of mirroring/ log shipping is that it can be setup before your planned migration so once you come to migrate, it is fairly simple to bring the databases on the new server online.

Here's some references:-

Mirroring - http://msdn.microsoft.com/en-us/library/ms189852.aspx

Log Shipping - http://msdn.microsoft.com/en-us/library/ms187103.aspx

Also, do NOT mirror/log ship any system databases. Any server scope settings should be scripted out and re-created manually on the new server.

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 2

Hi, Thanks for the information. I am looking for some storage level option for moving the databases. Let's say that I have a database of 1 TB to be migrated. Either I can detach the database or take a backup and move the files to target server(even in the case of mirroring or log shipping, I have to move the full backup file). But moving such a big file will not be feasible in most of the environment. What if the copy process runs for a long time and fails.... So I like to know how others are handling this movement...

---------------------------------------------------
Thanks,
Satheesh.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Typically with something that size, I'd set up log shipping or mirroring before the planned migration day, then all I have to do on migration day is fail over the mirroring and then drop the mirror config, or take a last log backup of the old database and restore it on the log shipped secondary with recovery and then drop the log shipping.

With doing the setup way before, if the copy fails, I can just restart it (or robocopy it), it's not on the critical, time-sensitive upgrade day.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 4

Thanks GilaMonster. But still, I am worried on the part of copying. Let's say that copying 1 TB file to a target server takes 12 hours and it fails at 10th hour for 3 times means I lost 30 hours which is very huge in migration projects and still I am not sure that I complete it in another 30 hours. And I am under the impression that doing a robocopy may also fails on large files.

---------------------------------------------------
Thanks,
Satheesh.

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 5

Also consider just doing a full back up \ copy and then differential backups depending on the rate of data change on a daily basis

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

But you wouldn't be doing it in those 30 hours. The advantage of the mirror/log shipping method is that you get the large copy done well in advance (weeks even) of the migration window.

If you have SANs, and the new and old servers are on the same SAN, you can do (maybe) some smart snapshotting method, but that depends on the SAN and the software you have for it. If they're separate servers with separate storage, you're going to have to copy.

You can take a backup to multiple files (striping the backup), then copy them one by one. Add compression and that should get the file sizes down.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ErrLog Ten Centuries Points: 1040 More actions · Answer 7

Depending on what storage you have, there may be an option of cloning LUN - or gracefully detach the database, then mount that LUN from old server to new server and then you attach the database - done. I said that easy, but I think the hardware maybe different in 2K3, 2K8 - back in my last project we used the above process - search for SRDF storage replication.

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 8

Thanks for all your info. It had given me lot of insights... I am keeping database mirroring and log shipping as my solution to migrate/upgrade the database. And I will be open for any other suggestions from server/storage team handling those servers.

---------------------------------------------------
Thanks,
Satheesh.