locked
database mirroring from between versions RRS feed

  • Question

  • I have a requirement to set up a reporting server (SQL 2017 Std, on prem) and keep it up to date using transaction logs from the operational server (SQL 2014 Std, on another prem).

    I get one shot at the full database backup and must maintain state using transaction backups. I can't touch the operational server.

    The traditional restore process is to restore the full backup then each transaction log, but this is unmaintainable after a few weeks.

    Is there another way to maintain the reporting server despite the SQL version difference?
    Friday, March 16, 2018 7:15 AM

Answers

  • I tried this on Tuesday. Maybe my method was flawed, but SQL 2017 threw an error saying that it could not leave the database in standby mode if the transaction logs were from an earlier version of SQL.

    I will try again, but I'd appreciate some pointers since my last standby attempt failed.

    Update!

    I have posted the code I am using on github: https://github.com/Ormesome/DBreplication

    I am using 2 Virtual Machines which are identical except for the version of SQL installed on them.

    Would you mind reviewing the T/SQL? I can't seem to create a standby database on SQL2017 from SQL2014 backups.

    • Edited by Ormesome Monday, March 19, 2018 12:48 AM New information
    • Marked as answer by Ormesome Tuesday, March 27, 2018 11:43 PM
    Friday, March 16, 2018 11:29 AM

All replies

  • You may have your "own Log shipping created" not using GUI but using custom scripts apart from that I do not see any other way. Own created LS is not big deal but not a recommended procedure. It is not really hard to create a jobs to copy the full backup and log files to other server and restore it. Since you have posted in mirroring forum it is absolutely not possible. Why is it necessary to have different version.

    You want complete restore or just some few tables ?


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, March 16, 2018 7:41 AM
  • I need to have a full restore for deep interrogation of the data.

    I am using features of SQL 2016/2017 that are not available on prior versions.

    I have used TSQL scripts to create a restore job, but

    • It is not possible to leave a database in standby after restoring if an upgrade has taken place. It must be brought online.
    • Bringing a SQL server online, rather than leaving it in standby mode, advances the LSN and breaks the LSN chain.
    • I do not have access to the original server. I cannot install software on it or change configuration and the only full backup I will ever have is from last week. I get daily transaction logs.

    I have had to build a second SQL 2014 server so that I can keep the database in standby mode without performing an upgrade. I then report against it using the SQL 2017 server. This is not ideal.

    • I have to keep my maintenance cycle in line with the other team.
    • Licensing.

    Are there 3rd party tools that can import transaction logs from SQL 2014 into SQL 2017?

    Friday, March 16, 2018 9:39 AM
  • I am not aware about any third party tool which does that. But I doubt what you are asking is possible, you are asking the database be continuously restored from log backups so that it is up to date and also you can use it for read and write purpose. Till now MS SQL Server has  not designed such way so as to achieve above. You can have restores going on read only databases though 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, March 16, 2018 9:52 AM
  • I don't want write access, only read access.
    Friday, March 16, 2018 11:22 AM
  • I don't want write access, only read access.
    In that case "logshipping procedure" I advised above would work. A database in standby mode would allow you to run queries but the only thing is when restore happens all logins would be kicked out and the you would have to login again and run the query. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, March 16, 2018 11:26 AM
  • I tried this on Tuesday. Maybe my method was flawed, but SQL 2017 threw an error saying that it could not leave the database in standby mode if the transaction logs were from an earlier version of SQL.

    I will try again, but I'd appreciate some pointers since my last standby attempt failed.

    Update!

    I have posted the code I am using on github: https://github.com/Ormesome/DBreplication

    I am using 2 Virtual Machines which are identical except for the version of SQL installed on them.

    Would you mind reviewing the T/SQL? I can't seem to create a standby database on SQL2017 from SQL2014 backups.

    • Edited by Ormesome Monday, March 19, 2018 12:48 AM New information
    • Marked as answer by Ormesome Tuesday, March 27, 2018 11:43 PM
    Friday, March 16, 2018 11:29 AM