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.
zero downtime no. but with careful planning you can get away with close to zero down time.
Option 1:
Option 2 (more work, less downtime):
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 :)
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?
A very convoluted way to do this... (almost)
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.
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.
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.
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.
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
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:
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.
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).
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.
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.
