April 29, 2012 at 11:20 pm
Deal All,
I have One live DB at Server1 and want to move the DB at Server2 without stopping the activities of Users at Server1.
What would be the best Solution to do this?
Thanks
April 29, 2012 at 11:34 pm
You can take a backup of the database and restore it on the other server.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 29, 2012 at 11:48 pm
Backup and restore will not work b/c when I will shift DB at new server then I have to stop users activities at server1 and shift it to Server2.
April 29, 2012 at 11:58 pm
Unless I'm mistaken, you will always have that issue.
When you do a failover to another database server, applications/clients always have to reconnect.
Are the servers on a cluster? You could introduce mirroring and when the databases are synchronized you could do a forced failover. If applications are cluster-aware, they should detect the failover and reconnect automatically.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 1, 2012 at 11:39 am
No matter how you attempt this there will be some time for an outage. It all depends on how large the database is. One other thing you need to do is bring over the logins/passwords to the other server. Second, your user community or ODBCs will need updated to reflect the new db server. Unless you shutdown the first server and use dns to point the first servers name to the second new servers IP address.
May 1, 2012 at 1:17 pm
If you have a third party tools you can do instant restore with less than 5 mins outage, no matter how big is the database.
Thanks
May 1, 2012 at 1:24 pm
You will still have an outage because users will need to connect to the new db server name.
May 1, 2012 at 2:09 pm
The way you usually do this to minimize outage is that you do a backup and restore. However not just 1. Typically you make a full backup and then start moving logs if you don't have mirroring set up. If I assume a full backup takes almost an hour, I'd do something like this:
7:00am: Full Backup Server 1
8:00am: Restore full backup on Server 2 (users still working)
8:05am: Diff backup on Server 1
8:15am: log backup 1 on Server 1
8:30am: log backup 2 on Server 1
8:45am: Restore diff on Server 2
8:45am: log backup 3 on Server 1
9:00am: Restore Log 1 on Server 2
9:00am: Backup Log 4 on Server 1
9:10am: Restore log 2 on Server 2
9:15am: Backup log 5 on Server 1
9:15am: Restore Log 3 on server 2
9:20am: Restore log 4 on Server 2
9:23am: Backup log 6 on Server 1
9:25am: Restore Log 5 on Server 2
9:27am: Restore log 6 on server 2
9:27am: pause Server 1, backup log 7 on Server 1
9:28am: Restore log 7 on Server 2
9:29am: Send all users to Server 2.
By lowering the time between log backups, you can minimize the downtime as you switch over.
There are other methods, for example, using Database Mirroring to switch over, if your clients can use the newer clients and automatically switch over. That will reduce, but not eliminate, the downtime.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 1, 2012 at 2:54 pm
You could set up replication from server a to server b, move application to point to server b, verify no more traffic to server a, take server a offline. That's what we do.
Jared
CE - Microsoft
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply