Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I got a bit of a problem. I have a SQL Server database in box A which has approximately 60gb of data. I need to move this to a new machine but I cannot have downtime at all. Every second that goes by there are new data in the db so we cant do it at some random time in the middle of the night.

Is there any way to achieve this? I am pretty much able to do whatever I want so any suggestion will be useful.

share|improve this question
    
Supposing you had the data duplicated identically at some particular nanosecond. How would you tell the client machines to stop using machine A and start using B? –  Ross Presser Oct 3 '13 at 19:19
add comment

migrated from stackoverflow.com Oct 3 '13 at 20:22

This question came from our site for professional and enthusiast programmers.

5 Answers

Database Mirroring (SQL Server 2005+) would allow as little as 20 seconds of downtime. This is likely the fastest way of moving a database from machine A to machine B.

You can setup mirroring during the day with no downtime, then cutover at the time of your choosing. As long as the clients are programmed to retry whatever operation they are doing at the time of cutover, there will be no data loss (if you do the client correctly).

share|improve this answer
add comment

You can't, sorry. Even in the case you redirect all attemps of new incoming connections to another server B, exactly the same as A (replicated or mirrored) at time t0, the on-going transactions on A will not exists on B until commited, and that is in a posterior time, t1. You always going to have downtime period, can be seconds or hours, but always exists.

See next post and info:

http://serverfault.com/questions/125750/move-sql-server-database-with-zero-downtime

http://blogs.msdn.com/b/sqlgardner/archive/2011/09/16/minimizing-db-migration-downtime-using-log-shipping.aspx

Hope this helps.

share|improve this answer
    
+1. There ARE databases that possibly could do that but then the next question - are you willing to pay for that ;) Seriously - plan your downtime proper. –  TomTom Oct 3 '13 at 20:30
add comment

If your database structure allows for it you could look at peer to peer replication(PTP). You can use it to essentially build a two node active/active cluster. Once PTP replication is in place you will have two live copies of the database both able to accept new data while updating the other. This will allow you to switch over your applications with out having to take the database offline.

The tricky part of this is going to be getting your two databases in sync long enough to build up the PTP replication. There are also some caveats around your primary keys, specifically for any new rows. This will require that each table generates a unique key whether it be guid's, identity increments by 2 or more or building unique keys specific to the application. This in theory could allow for a no down time cut over but it will be tricky.

As others have posted, mirroring is a relatively quick and easy way to cut over. But it will require a few seconds of down time at least - this down time can be kept to a minimum if everything is scripted out appropriately.

share|improve this answer
1  
Doing this is probably much more expensive/impractical to do it right than taking the hit of ~seconds of downtime using another method. +1 for a viable solution, though. –  Jon Seigel Oct 4 '13 at 2:58
    
I do agree, the amount of work to make this a viable solution likely will most likely out weigh the cost of taking the few seconds of downtime associated with a mirroring solution. –  Ian Chamberland Oct 4 '13 at 17:30
add comment

Without knowing what kind of data was being input, I would say:

Box A -- Copy Structure and Routines -- > Box B
Box B: Use as Active Database
Box A --> Export Data 
Box B <-- Import Data (while still maintaining active database role)

The biggest wrench in the plan is going to be foreign key constraints while importing.

share|improve this answer
1  
This will have more downtime + 60GB of data with import/Export is not a good idea. BCP is better if you are thinking of import/Export. –  Kin Oct 4 '13 at 0:04
add comment

Considering the fact that "no downtime is allowed" and "we have only one machine" is a very flawed design, you might just as well prepare the new machine and say "what do you know, the old server just happened to crash, but look, the data is still intact, I can move it to the new server" :)

Sooner or later this is going to happen and it better happen when you want it to, not in the middle of an extended weekend.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.