none
Upgrade using Db mirroring

    Question

  • Hi Everyone,

    I came to know that using SQL Server database mirroring, we can perform the upgrades with minimal downtime.
    How that is possible? Can anyone elaborate ?

    I have done some homework reading about this and wanted to confirm on this.

    Assume I have old server is SQL Server 2005 Enterprise Edition and new server is SQL Server 2008 Enterprise Edition.
    Now, I am going to Setup High Safety mode without witness. I dont want witness because, we have risk Automatic failover and I don't want that happen in middle of upgrade.
    Also make sure logins , orphan users are fixed, jobs ,linked servers are created on the new server.
    once everything is sync , initiate a manual failover.

    use master
    go
    alter database <dbname> set partner failover;
    go

    Is this the way we can perform upgrades with minimal downtime ?
    Again, once the upgrade is done and verified from error log, do we remove db mirroring and point the applications to the new server?

    Please correct me if I am wrong and am I missing anything here?

    Thanks in Advance.
    Wednesday, March 12, 2014 6:16 AM

Answers

  • Yes, I think you get the idea. Something like (brief outline):

    App connected to A

    BACKUP DATABASE on A

    RESTORE DATABASE on B WITH NORECOVERY

    BACKUP LOG on A

    RESTORE LOG on B WITH NORECOVERY

    1. Stop/Disconnect app from A

    BACKUP LOG on A WITH NORECOVERY --The option make sure no more modifications on A

    RESTORE LOG on B WITH RECOVERY

    2. Start/connect app to B

    As you probably realize, with some practice and preparation, we can achieve a very short time-span between timepoint 1. and 2. above.

    Regarding Naveen's suggestion about log shipping: Yes, it is possible to use log shipping for this. Personally, I see little value in configuring and implementing log shipping solely for the purpose of an upgrade. But of course it depends on the circumstances, and usual. :-)


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Manu_vmr Wednesday, March 12, 2014 2:06 PM
    Wednesday, March 12, 2014 1:17 PM

All replies

  • Looks like you have done homework :-))) If you do not need a mirroring failed back to the upgraded server and remove the mirroring...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 12, 2014 6:30 AM
  • Thank you very much Uri.


    Wednesday, March 12, 2014 6:38 AM
  • Why use mirroring for this? Just do it "manually" using database backups and log backups. Essentially the same thing, but you don't have to mess about with mirroring. This also allow you to practice and prepare your scripts, first on a non-production server.

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, March 12, 2014 8:12 AM
  • Hi Tibor,

    Someone asked me that we can achieve upgrade with minimal downtime using database mirroring. So, I wanted to learn how to accomplish that.  Till now, I am aware using database backup or dettach /attach methods.

    I have learnt something new from this assignment.

    Wednesday, March 12, 2014 12:04 PM
  • My point with backup was not inly backup and restore of the database. It was to complement with log backups and only make the app offline while backing up/restoring that very last log backups - resulting very short downtime. And this is technically the same thing as what happens with database mirroring - you apply the log records from one server to the other. The difference is the delivery mechanisms for those log records.

    But I get your point (I thought this was a real case, something you are about to perform). Yes, mirroring is usable for this scenario.


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, March 12, 2014 12:08 PM
  • Yeah Tibor, you are right.

    Just a follow up question, and want to get confirmation on below statement.

    "It was to complement with log backups and only make the app offline while backing up/restoring that very last log backups - resulting very short downtime."

    How do we achieve this in practically ? what would be the series of steps involved here.

    Suppose I have old sql 2005 server and new sql 2008 server. I have nightly full backup from old server and every 15 min I am taking scheduled transaction log backups.

    Now what are the steps involved to ensure high availability and at same time making sure it takes minimal downtime for upgrade. when do we point our applications to new server?


    Wednesday, March 12, 2014 12:23 PM
  • Hi Tibor,

    Not sure If I have asked a wrong question to you.

    I feel this restore the log backup on new server and depending on downtime, make the database readonly on old server, take a taillog backup and restore it on new server. Make the database readwrite and redirect the application to new server. Is that correct?

    Wednesday, March 12, 2014 12:43 PM
  • Hello Tibor/Manu

    Even we can configure logshipping (in no-recovery mode) from SQL 2005 to SQL 2008 server. Once the application is stopped, we can tail log backup and restore it on SQL 2008 server and bring the database onlineon SQL 2008 server.  

    Regards

    Naveen Kumar Thandra

    Naveenthandra.blogspot.hk             

    Wednesday, March 12, 2014 1:10 PM
  • Yes, I think you get the idea. Something like (brief outline):

    App connected to A

    BACKUP DATABASE on A

    RESTORE DATABASE on B WITH NORECOVERY

    BACKUP LOG on A

    RESTORE LOG on B WITH NORECOVERY

    1. Stop/Disconnect app from A

    BACKUP LOG on A WITH NORECOVERY --The option make sure no more modifications on A

    RESTORE LOG on B WITH RECOVERY

    2. Start/connect app to B

    As you probably realize, with some practice and preparation, we can achieve a very short time-span between timepoint 1. and 2. above.

    Regarding Naveen's suggestion about log shipping: Yes, it is possible to use log shipping for this. Personally, I see little value in configuring and implementing log shipping solely for the purpose of an upgrade. But of course it depends on the circumstances, and usual. :-)


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Manu_vmr Wednesday, March 12, 2014 2:06 PM
    Wednesday, March 12, 2014 1:17 PM
  • Thank you very much Tibor and all.
    Wednesday, March 12, 2014 2:05 PM