Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is it possible to move a sql server 2005 db to a different server running sql server 2008 without any downtime? The system is 24/7 and has to be moved to a different server with different storage.

We tried copying the database, but that does not keep the whole db synchronous at the end of the process.

share|improve this question
3  
You are going to need to schedule downtime for this, just no way around it. –  Urda Mar 24 '10 at 13:11
2  
Yes. But then, this is expected - after all, someone had the delusion to have a 24/7 system without clustering / mirroring on one storage system.... so he gets what he paid for, which si downtime when you mvoe this single point of failure. Should get a termination for gross neglect on top. –  TomTom Dec 5 '11 at 16:23

13 Answers 13

No. Sorry. I do not see a way to move the database without any downtime. What is on the database that you have no way to even put in an hour during like easter holidays?

share|improve this answer
    
24/7 is required for all customers running on that database (worldwide, so no holidays apply at all). All options i know (like logfileshipping) result in uncalculatable downtime. –  Uwe Mar 24 '10 at 13:07
5  
You can't guarantee 24/7 with no downtime even during normal operations if you're running on a single server, and it sounds like you are. How do you patch the OS? You need to negotiate some downtime with the customers - give enough lead time for them to allow for it. Also, when you migrate it, you should migrate it to a clustered SQL server so that you can at least deal with the inevitable hardware failures that all metal is heir to. –  mfinni Mar 24 '10 at 13:36
    
we run a failover cluster, but the db is to be moved to another failovercluster on another network storage. –  Uwe Mar 24 '10 at 13:57
1  
OK, that's probably useful information. If you didn't have the "other storage" requirement, you could probably add the two new servers to the cluster and then move the active instance to one of the new servers, and then remove the old servers from the cluster. I don't know if you can mix SQL versions in a cluster though. –  mfinni Mar 24 '10 at 15:07
    
Still downtime. A failover cluster results in (minimal) downtime on a move, but it still results in downtime. Plus all connections being reset. –  TomTom May 8 '10 at 14:31

zero downtime no. but with careful planning you can get away with close to zero down time.

Option 1:

  • setup log shipping btwn existing 2005 and new 2008 server.
  • Plan the cut-over carefully switching ip's and/or hostnames.
  • Make sure you do a final tail log backup before final cut-over.

Option 2 (more work, less downtime):

  • If your 2008 box is new, then install 2005 first to the same sp as your prod box.
  • Setup database mirroring, asynch in first stage to avoid performance overhead.
  • Setup your clients to have the failover partner included in the connection string
  • Change to synch db mirroring and failover to new box
  • follow 'rolling upgrade steps' for an inplace upgrade of 2005-2008 for a database mirroring setup

Of course, to get this right, your going to need to test & make sure you haven't missed anything when you do it for real :)

share|improve this answer
1  
Regarding option 2, you can actually mirror from SQL 2005 to SQL 2008. I've done it successfully for clients a few times. Failover from 2005 to 2008 is supported, but not failover from 2008 to 2005. –  jlupolt Mar 25 '10 at 13:41
    
so you can fail-over, but not fail back? yikes! –  Nick Kavadias Mar 28 '10 at 14:58
    
No jikes - it is meant purely as an upgrade mechanism. Fail to 2008, reinstall other server, 2x2008 running ;) –  TomTom May 8 '10 at 14:31

A very convoluted way to do this... (almost)

  1. P2V the server onto a vmware cluster. No downtime.
  2. Create a second server and create an active/passive cluster.
  3. Upgrade the passive node to 2008 and fail over.
  4. Profit?

Obviously, everything here needs testing, there are many detailed steps left out.

OR - Get management to agree to downtime, and publicise it advance to your customers. Then practice and test the upgrade to death!

Explain to management the technical difficulties in trying to do this "cheap". This is something you BUILD INTO a system when you first build the architecture of a full 27x7.

Even the biggest systems have planned downtime. It's UNPLANNED downtime that you need to worry about more.

share|improve this answer

transactional replication is your friend here...

if you set up replication with the new server as a slave, you should be able to get the new db up and running, and then when you are ready, switch over (minimal downtime here, minutes we're talking, not hours)...you may need to reindex a table or three, but once it's done, it's done.

share|improve this answer

You're not going to achieve this with SQL Server alone I'm afraid. I've used a product called Double Take in the past that would allow you to clone the DB off to another server and then failover when it's conveneient.

The failover process would still incur some downtime as the services start up on the new machine.

share|improve this answer

If it's just a single instance/non-clustered setup right now, you won't able achieve 0% downtime without lost of data. If the DB is primarily read heavy, and it's better to lose a couple of write operations, then to take the DB down, then you might have some options.

You could do a COPY_ONLY full backup of the DB, then after that completes, move the bak file over to the new server storage. Restore to the DB to the new instance of SQL, and re-write your connection strings where ever applicable (hopefully it's just one inc file somewhere), and restart your sites. You'll have a glitch on the site and active sessions will restart.

However. you'll lose all the writes between the time of backup and restore.

share|improve this answer
    
i fear that is not possible, as the db is qutie heavliy written and a few 100gb in size. so backup, move and restore will take quite a while. –  Uwe Mar 24 '10 at 13:22
2  
Well, you can't have your cake and eat it too. The backup and move will be done while the old DB is still online, you'll just lose those writes. You are going to have to pick your poison it looks like. –  Malnizzle Mar 24 '10 at 13:29

You can try to achieve this with minimal downtime (a couple of seconds to fail-over) using a db mirror solution. You can take a look at this MSDN article for more info: http://msdn.microsoft.com/en-us/library/bb677181.aspx

Tom

share|improve this answer

If you are using a .NET solution as the application server that uses the 2.0 framework then the suggestion by tvanzele should work just fine. Steps below:

  • Make sure your primary database is in FULL recovery mode and you are taking log backups
  • Install a SQLEXPRESS or standard edition SQL instance to act as a witness server
  • Backup and restore the FULL backup and logs backup to the new instance (not the witness)
  • Set up database mirroring to the new SQL instance and use the SQLEXPRESS/Standard instance as the witness
  • change the connection string in the application to recognize the failover server, see connectionstrings.com for reference
  • Fail the database over to the mirror by restarting the old instance
  • Change connection string for the application to only use the failover instance
  • Break mirroring
share|improve this answer

You could consider setting up synchronous mirroring for the database between the two environments. When synchronized the database can be failed over (or back) with only an interruption to any in flight transactions.

http://sqlserverpedia.com/blog/sql-server-2008/cutover-30-gb-databases-in-60-seconds-with-sql-server-20052008/

share|improve this answer

I would suggest you to do it using Redgate software. SQL Compare would help you to recreate exactly the same structure in newly created db in other server. And then u use SQL Compare Data which creates "copy" scripts for you and executes it (or save it for later). Works like a charm. I'm using it to copy stuff between prod/dev db.

It's good because you could do Sql Data Compare once. And then when it moved some data (and new data arrived in the old database) you could rerun it again so it would only synchronize diffrences in couple of seconds.

With SQL Data Compare Pro, you can compare and synchronize SQL Server live databases with backups, compare two backups or work with SQL scripts folders under source control. With the command line interface you can automate tasks and schedule comparisons for easy compilation of an audit trail.

Redgate SQL Toolbelt your friend forever (I'm in no way associated to Redgate) :p

As a side note. Since the data is preety large i would suggest using SQL Data Compare to use in chunks (couple of tables per copy). Then later on final synchronization to synchronize any diffrences that happend during the copy period (even if it took you 3 hours it would only need to synchronize couple hundres of rows or so).

share|improve this answer

I came across a product called ChronicDB that claims it can do the migration with zero downtime. I am not sure if they support SQL Server.

share|improve this answer

I indeed ended up with downtime. I took the "regular" way of backup/restore with transaction logs including a final tail.
I still think there should be a out of the box way to do this without any downtime.

share|improve this answer

I use a tool called SQL Compare from Red Gate (http://www.red-gate.com). They have several useful

share|improve this answer
3  
Worth you knowing that we ave scripts to spot new users popping up recommending products as their first post - we get too many spammers, but in this case I know you're not one but be aware of this ok. Also can you go back and read our FAQ as you skipped it. –  Chopper3 Dec 5 '11 at 16:19

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.