Home / server / Questions / 162017
In Process
Sam
Asked: 2010-07-20 14:39:18 +0800 CST

Moving databases from SQL 2005 to 2008 - quick cutover with mirroring or replication

  • 772

We have some SQL 2005 databases running which I'd like to move to SQL 2008. The 2008 instance is up and running. What is the recommended way to do a quick cut over from one server to the other.

I like for the application to be using 2005 while transactions are also being pushed to 2008, letting me set things up beforehand. Then the application team can change their connection to the 2008 instance and start testing and updating. When all is well, we could break whatever mechanism is doing the transfer and use 2008.

I'm somewhat familiar with mirroring and transactional replication. It seems that mirroring will work, but I'd need to fail to the mirrored database at the right time. The 2005 DB would then be unusable and we would be unable to fail back, since the system tables would be in 2008 format.

Replication seems like it would be the better choice for not needing any intervention - but it also seems a bit more complicated with details I'm probably not aware of that may make this not work.

Thanks

sql-server sql-server-2005 sql-server-2008

2 Answers

  1. You can't test the mirrored database, since is not accessible to reads.

    Replication will allow you to read from the subscriber, but replication does not replicate everything.

    Also is, highly unorthodox to talk about the application team 'changing and updating' the copy while is being updated. The application team should test and update a test database, not a candidate for production update... What would happen to changes that are incompatible with the stream of incoming updates?

    A typical update scenario is like this:

    • application development test that application works fine on the old schema on the new database. If issues are identified, application is fixed (no schema changes in the database).
    • if schema changes are required, they are written down as upgrade scripts to be implemented at deployment of new version.
    • dev team signs off on the upgrade procedure (all application modifications and all schema modifications)
    • QA team takes a copy of the production DB and validates that is possible to upgrade the application, applying all the binaries changes schema changes after the upgrade, as recommended by dev team
    • QA team signs off on the upgrade procedure
    • Operations team prepares the upgrade. Using mirroring is a great way, but for reasons of minimize down-time during upgrade, not for reasons of validating the application as you suggest
    • Perform the upgrade:
      • Take the application down
      • Fail over the database (or break mirroring, if you want to have a safe/fast fall back path)
      • Apply schema changes scripts to new database
      • Apply binary patches to application
      • start application on new db
      • run some minimal validation tests
      • enable access to application
    • 1

  2. Do a cold backup of the databases and restore them to the 2008 instance as normal for the application team to test whether or not they will have compatibility problems. Once that is okayed then use mirroring or log shipping to keep the databases in sync. In your planned downtime stop the LS/mirroring, take the old databases offline and recover the 2008 databases and have the application team change the connections string or use a DNS alias to cut over to the new server.

    • 0