September 10, 2018 at 10:58 am
Howdy all..
This is more of a general question... we currently are running our production applications on an instance of SQL Server 2012 and we do log shipping every hour to a second instance of SQL Server 2012 which is used for reporting. No issues so far - works great.
Over the next few months we are considering an upgrade to SQL Server 2016 (or possibly 2017) of both servers.
My question is this... given that you need to be on the same SQL version for log shipping, will upgrading both servers result in the log shipping "just working" as we hope - or will we need to reinitialize all of the databases that we're log shipping? If the latter is the case that's fine - at this point I'm just wanting to get an idea of the time we're going to need to do all of this. Any gotchas that I need to worry about?
September 10, 2018 at 10:05 pm
The target system (the server to which you are shipping logs) must be on a version equal to *or greater than* the source of the transaction log backups. Upgrade the second instance, let log-shipping catch back up, then upgrade the source.
The database will go through the upgrade steps when you bring it online on the source, and the next log backup will grab all of that work and apply it to the target (upgrading the log-shipped copy) on the next restore.
I've upgraded many huge SQL2008r2 databases by log-shipping them to a SQL2016 target.
-Eddie
Eddie Wuerch
MCM: SQL
September 11, 2018 at 7:59 am
Eddie --
I was under the impression that you could only restore to a higher level if you were in recovery mode... ours all need to be in standby mode, as the databases are used for reporting purposes.
If that's the case can we switch them to recovery mode, do the updates as you suggest, and then switch back?
September 11, 2018 at 10:07 pm
cphite - Monday, September 10, 2018 10:58 AMHowdy all..This is more of a general question... we currently are running our production applications on an instance of SQL Server 2012 and we do log shipping every hour to a second instance of SQL Server 2012 which is used for reporting. No issues so far - works great.
Over the next few months we are considering an upgrade to SQL Server 2016 (or possibly 2017) of both servers.
My question is this... given that you need to be on the same SQL version for log shipping, will upgrading both servers result in the log shipping "just working" as we hope - or will we need to reinitialize all of the databases that we're log shipping? If the latter is the case that's fine - at this point I'm just wanting to get an idea of the time we're going to need to do all of this. Any gotchas that I need to worry about?
Something to keep in mind you wouldn't be able to log-ship back to SQL 2008R2 from SQL 2016. Let's say you migration to 2016 and then you decide you wanted to rollback along with the changes that you did on 2016 copy , you wouldn't be able to apply those logs back to 2008R2
September 12, 2018 at 1:44 pm
curious_sqldba - Tuesday, September 11, 2018 10:07 PMcphite - Monday, September 10, 2018 10:58 AMHowdy all..This is more of a general question... we currently are running our production applications on an instance of SQL Server 2012 and we do log shipping every hour to a second instance of SQL Server 2012 which is used for reporting. No issues so far - works great.
Over the next few months we are considering an upgrade to SQL Server 2016 (or possibly 2017) of both servers.
My question is this... given that you need to be on the same SQL version for log shipping, will upgrading both servers result in the log shipping "just working" as we hope - or will we need to reinitialize all of the databases that we're log shipping? If the latter is the case that's fine - at this point I'm just wanting to get an idea of the time we're going to need to do all of this. Any gotchas that I need to worry about?
Something to keep in mind you wouldn't be able to log-ship back to SQL 2008R2 from SQL 2016. Let's say you migration to 2016 and then you decide you wanted to rollback along with the changes that you did on 2016 copy , you wouldn't be able to apply those logs back to 2008R2
We're on 2012 now so no risk of ever going back to 2008R2... 😀
When we were initially setting up the target instance we wanted to use 2016 but I distinctly recall getting errors when we tried to setup log shipping with the targets in standby mode. We could get them to do recovery mode - but that wasn't useful because one of the main purposes of the server is to have read-only copies for reporting purposes. We had to install 2012 to match production to get it working.
So what I really need to know is, can I upgrade both servers to 2016 (or 2017) without breaking log shipping to standby (read-only) mode?
September 12, 2018 at 2:48 pm
cphite - Wednesday, September 12, 2018 1:44 PMWe're on 2012 now so no risk of ever going back to 2008R2... 😀
When we were initially setting up the target instance we wanted to use 2016 but I distinctly recall getting errors when we tried to setup log shipping with the targets in standby mode. We could get them to do recovery mode - but that wasn't useful because one of the main purposes of the server is to have read-only copies for reporting purposes. We had to install 2012 to match production to get it working.
So what I really need to know is, can I upgrade both servers to 2016 (or 2017) without breaking log shipping to standby (read-only) mode?
Yes. It's explained in this documentation:
Upgrading Log Shipping to SQL Server 2016 (Transact-SQL)
The part you probably want to pay attention to to address your concern is the Note section after the instructions for upgrading the secondary:
the secondary database itself is not upgraded to a SQL Server 2017 database. It will get upgraded only if it is brought online by initiating a failover of the log shipped database. In theory, this condition could persist indefinitely. The amount of time to upgrade the database metadata when a failover is initiated is small.
Sue
September 12, 2018 at 3:35 pm
Sue_H - Wednesday, September 12, 2018 2:48 PMcphite - Wednesday, September 12, 2018 1:44 PMWe're on 2012 now so no risk of ever going back to 2008R2... 😀
When we were initially setting up the target instance we wanted to use 2016 but I distinctly recall getting errors when we tried to setup log shipping with the targets in standby mode. We could get them to do recovery mode - but that wasn't useful because one of the main purposes of the server is to have read-only copies for reporting purposes. We had to install 2012 to match production to get it working.
So what I really need to know is, can I upgrade both servers to 2016 (or 2017) without breaking log shipping to standby (read-only) mode?
Yes. It's explained in this documentation:
Upgrading Log Shipping to SQL Server 2016 (Transact-SQL)The part you probably want to pay attention to to address your concern is the Note section after the instructions for upgrading the secondary:
the secondary database itself is not upgraded to a SQL Server 2017 database. It will get upgraded only if it is brought online by initiating a failover of the log shipped database. In theory, this condition could persist indefinitely. The amount of time to upgrade the database metadata when a failover is initiated is small.
Sue
Thanks, Sue - exactly what I was looking for 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply