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.

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

share|improve this question
add comment

2 Answers

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
share|improve this answer
    
Let me explain further, you have gotten the wrong impression from my use of the word 'test'. I know the mirrored db cannot be read, they would do that after I fail to the mirror - "It seems that mirroring will work, but I'd need to fail to the mirrored database at the right time". True testing has been done in development already, they would just do a cursory test to make sure everything is fine after the cut over - the minimal validation tests your refer to. –  Sam Jul 20 '10 at 21:13
    
"but replication does not replicate everything." Can you elaborate on this? Also, you say that mirroring is a valid way to minimize down time - even between 2005 and 2008? I know I will not be able to fail back and should be fine with taking a backup just before the failover procedure. Thanks for your advice. –  Sam Jul 20 '10 at 21:17
    
Mirroring creates a physical copy of the database, including users, permissions and every other database object you can think off. Replication only replicates the data in the tables being published. –  Remus Rusanu Jul 20 '10 at 22:34
    
Mirroring between a 2005 and 2008 is possible, and often used as a minimal downtime upgrade. –  Remus Rusanu Jul 20 '10 at 22:34
1  
To get around the Replication concerns, Initialize the Subscriber from a backup of the Publisher database thereby including all users/permissions etc. whilst implementing a change freeze on the production environment for the duration of the upgrade process. –  John Sansom Jul 22 '10 at 10:46
add comment

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.

share|improve this answer
add comment

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.