locked
Mirroring questions. RRS feed

  • Question

  • Greetings.

    I need to move a 250 GB DB from a 2005 box (DB in 2000 compatibility mode) to a 2008 box (DB to be put in 2008 compatibility mode) with minimal downtime.

    In the past I've used Log Shipping to do this without a hitch, but am now wondering about using Mirroring to accomplish the same task. Any benefit to going this route? Im not thinking of any off the top of my head, but am always open to new ideas.

    Edit:  I know Mirroring could provide quicker failover, but I can take enough Transaction Log backups to have my last one be tiny (once the app is brought down), and only have about 5 minutes of downtime. Im not concerned with that, really just using the safest method.

    Also, is it even possible to have a 2005 Principal and a 2008 Mirror?


    TIA, ChrisRDBA
    • Edited by ChrisRDBA Wednesday, November 16, 2011 8:26 PM
    Wednesday, November 16, 2011 8:10 PM

Answers

  • Hi Chris!

    Yes, you can use mirroring for that, in fact that's what I do all the time in scenarios like this.

    Two advantages:

    1) It's easier. No worry about tail log backups, no worry about applications that are still online after the final log backup, you issue the failover and you can be 100% certain that you are consistent.

    2) If your application can handle it you can upgrade with close to no downtime at all. (Talking 3-5 seconds here.) In this case you don't even need to stop the application...

    Tthe disatvantages:

    1) When you do it with a standard failover there is no way back. That's a very unfortunate bug in both SQL 2005 and 2008. There are ways around that problem, but not all that easy.

    2) You need a direct connection between the two servers.

     

    Hope that helps a little

    Lucifer

    • Proposed as answer by RamJaddu Thursday, November 17, 2011 9:25 AM
    • Marked as answer by ChrisRDBA Monday, November 21, 2011 2:57 PM
    Thursday, November 17, 2011 7:03 AM
  • I tested over the weekend, and see what you're talking about.


    TIA, ChrisRDBA
    • Marked as answer by ChrisRDBA Monday, November 21, 2011 2:57 PM
    Monday, November 21, 2011 2:57 PM

All replies

  • Hi Chris!

    Yes, you can use mirroring for that, in fact that's what I do all the time in scenarios like this.

    Two advantages:

    1) It's easier. No worry about tail log backups, no worry about applications that are still online after the final log backup, you issue the failover and you can be 100% certain that you are consistent.

    2) If your application can handle it you can upgrade with close to no downtime at all. (Talking 3-5 seconds here.) In this case you don't even need to stop the application...

    Tthe disatvantages:

    1) When you do it with a standard failover there is no way back. That's a very unfortunate bug in both SQL 2005 and 2008. There are ways around that problem, but not all that easy.

    2) You need a direct connection between the two servers.

     

    Hope that helps a little

    Lucifer

    • Proposed as answer by RamJaddu Thursday, November 17, 2011 9:25 AM
    • Marked as answer by ChrisRDBA Monday, November 21, 2011 2:57 PM
    Thursday, November 17, 2011 7:03 AM
  • As Lucifer suggested mirroring is not a best choice in this case - you won't be able to failover back db failover happens onto SQL 2008.

    I would suggest you to use logshipping - push last log file on final day and bring db online - please aware for logshipping from sql 2000/ 2005 to SQL 2008 standby mode won't support only choice would be norecovery mode.


    http://uk.linkedin.com/in/ramjaddu
    • Proposed as answer by Peja Tao Friday, November 18, 2011 6:10 AM
    Thursday, November 17, 2011 9:27 AM
  • Hi Chris!

    Yes, you can use mirroring for that, in fact that's what I do all the time in scenarios like this.

    Two advantages:

    1) It's easier. No worry about tail log backups, no worry about applications that are still online after the final log backup, you issue the failover and you can be 100% certain that you are consistent.

    2) If your application can handle it you can upgrade with close to no downtime at all. (Talking 3-5 seconds here.) In this case you don't even need to stop the application...

    Tthe disatvantages:

    1) When you do it with a standard failover there is no way back. That's a very unfortunate bug in both SQL 2005 and 2008. There are ways around that problem, but not all that easy.

    2) You need a direct connection between the two servers.

     

    Hope that helps a little

    Lucifer


    Why wouldn't a failback work?
    TIA, ChrisRDBA
    Friday, November 18, 2011 3:30 PM
  • because  meta data tables upgraded to SQL 2008 they can't be revert back to earlier version  -  also please aware Mirroring from 2005 to 2008 is only supported in an upgrade or migration scenario it not for normal production use


    http://uk.linkedin.com/in/ramjaddu
    Friday, November 18, 2011 9:17 PM
  • I tested over the weekend, and see what you're talking about.


    TIA, ChrisRDBA
    • Marked as answer by ChrisRDBA Monday, November 21, 2011 2:57 PM
    Monday, November 21, 2011 2:57 PM