none
Logshipping from SQL 2005 to SQL 2012

    Question

  • I have a 2005 primary sql database (production-constantly updated).  I would like to have (pseudo) real time copy in a secondary 2012 sql server instance. I want to run SSRS 2012 on this new secondary server.  The end goal is to have users access 2012 SSRS (uninterrupted) on the secondary server with a copy of "real-time" data from the primary 2005 server.

    I plan to implement log shipping between the two servers.  Do I have to restore using NO RECOVERY?  I have read that I can not use STANDBY mode when restoring between 2005->2012.  If so, is there a way to implement this without taking the secondary sever offline?

    Any other suggestions, on how to implement this?

    Thanks.

    Tuesday, December 24, 2013 4:34 PM

Answers

  • Balmukund: Why cant I read from the secondary (target) database?

    To read from log-shipping secondary, you need to restore backup in "stand-by" mode. In your scenario, you are restoring a backup from lower version of SQL (2005) to higher version of SQL (2012), "standby" restore is not possible. Only restore option you have is "with norecovery" where you can't read from secondary database.

    Technically, we need to upgrade database version and change many information within the database during upgrade of major version.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Proposed as answer by Shanky_621 Saturday, January 04, 2014 6:15 AM
    • Marked as answer by Electric99 Friday, February 21, 2014 4:01 PM
    Saturday, January 04, 2014 1:50 AM

All replies

  • Hi....

    It should work for sure..  We have done exactly the same as you're proposing, log shipping from 2005 to 2012. Its always possible lower to higher but higher to lower is not possible.

    Nothing will happen to SSRS.

    Note : that we weren't using the SQL Server Log Shipping features, but our own custom scripts to do the backup/restore.  

    Please read the below links for more details and reference....

    http://sqlblog.com/blogs/greg_low/archive/2011/01/12/log-shipping-between-versions-perhaps-2005-to-2008.aspx

    http://msdn.microsoft.com/en-us/library/cc645954.aspx

    http://msdn.microsoft.com/en-us/library/cc645954.aspx#KeepOldSecondaryAsNewPrimary

    http://msdn.microsoft.com/en-us/library/cc645954.aspx#SwitchToOrigPrimary

     


    Raju Rasagounder MSSQL DBA

    Tuesday, December 24, 2013 4:51 PM
  • But you can't read from secondary database in this implementation. Is that OK with you?

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, December 24, 2013 5:11 PM
  • I have a 2005 primary sql database (production-constantly updated).  I would like to have (pseudo) real time copy in a secondary 2012 sql server instance. I want to run SSRS 2012 on this new secondary server.  The end goal is to have users access 2012 SSRS (uninterrupted) on the secondary server with a copy of "real-time" data from the primary 2005 server.

    I plan to implement log shipping between the two servers.  Do I have to restore using NO RECOVERY?  I have read that I can not use STANDBY mode when restoring between 2005->2012.  If so, is there a way to implement this without taking the secondary sever offline?

    Any other suggestions, on how to implement this?

    Thanks.

    Hello ,

    As Balmukund pointed out one more thing to consider is suppose your primary goes down and now you have secondary up.After primary comes up you wont be able to restore database from 2012 to 2005( to bring it in sync) .So now you have just single 2012 DB exposed without any DR technology.

    Also if you want to go ahead you should use TSQL script not GUI.You might face issue with GUI


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, December 25, 2013 5:09 AM
  • I need SSRS 2012 reports to be able to read from the secondary 2012 database (target).  Is this possible?

    Tuesday, December 31, 2013 5:11 PM
  • Balmukund: Why cant I read from the secondary (target) database?

    Friday, January 03, 2014 9:47 PM
  • Balmukund: Why cant I read from the secondary (target) database?

    To read from log-shipping secondary, you need to restore backup in "stand-by" mode. In your scenario, you are restoring a backup from lower version of SQL (2005) to higher version of SQL (2012), "standby" restore is not possible. Only restore option you have is "with norecovery" where you can't read from secondary database.

    Technically, we need to upgrade database version and change many information within the database during upgrade of major version.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Proposed as answer by Shanky_621 Saturday, January 04, 2014 6:15 AM
    • Marked as answer by Electric99 Friday, February 21, 2014 4:01 PM
    Saturday, January 04, 2014 1:50 AM