Move SQL-Server Database with zero downtime

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 :)

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)

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.

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:

• 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

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/

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).