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 only takes a minute to sign up.
Sign up to join this community
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.
This question came from our site for professional and enthusiast programmers.
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).
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:
https://serverfault.com/questions/125750/move-sql-server-database-with-zero-downtime
Hope this helps.
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.
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.
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.
Absolute no downtime but ridiculously expensive option.
Always on availability group from SQL enterprise. Setup a replica then fail over. Then remove from the availability group.
