July 18, 2013 at 3:10 am
My web site uses MS SQL Server. I ordered a new server for DB and want to migrate from old one. Both servers have the same MS SQL Server 2008 Web Edition
The problem is that database is about 500GB. Simple attach/detach procedure will take about an HOUR to copy database files. I'm not very happy that my site will be offline during such a long period of time. I need a solution that leads to 1-2 min of site unavailability maximum.
Is there a way to migrate database on new server online?
What I think should be possible: I would like to perform some setup on new server while old database is online. After that setup two databases are continuously syncronized. Then I just swith site to new DB.
How to implement that scenario?
Looking forward for an advice.
Anton Burtsev
July 18, 2013 at 4:17 am
Hi,
Given the limitations of Web edition, i.e. no mirroring etc. I'd say the best way to keep downtime to a minimum is setup your new server and configure log shipping between the two servers.
That way all you need do is take the site offline restore the latest transaction logs on the new server, bring the db online, change the connection string in your application to point to the new database and then bring your site back online.
Downtime still may be longer than 1-2 minutes, but should be no longer than 10 or so if you plan it right.
Thanks,
Simon
July 18, 2013 at 6:55 am
I'm not familiar with the Web Edition at all, but when I have to migrate a database from one server to another I prefer to backup the database, copy the backup file to the new server and then do a restore from the backup file. The applications can be re-pointed while the restore is running. I write the SQL to do all the work except copying the file so there's nothing to miss when clicking around in the GUI. Some may call this old-fashioned, but I call it reliable, repeatable and efficient because I've already practiced exactly what I'm going to do for the production migration.
This approach does require some downtime because you don't want transactions coming from the applications while pointed to the old database, but I've found the reliability of this method to be worth it. Because you can practice beforehand, you can time everything and know how long things are going to take and post an appropriate announcement on your site.
July 18, 2013 at 6:57 am
you could look into RedGate SQL DataCompare Pro.
i used that software with its command line capibilites to keep 2 databases in sync on different systems for a week during a migration once. it was a life saver.
July 19, 2013 at 12:44 am
s_osborne2 (7/18/2013)
Hi,I'd say the best way to keep downtime to a minimum is setup your new server and configure log shipping between the two servers.
Simon
I've tested that approach. Downtime is about 7-8 minutes.
That's quite acceptable for me.
Thanks!
Anton Burtsev
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply