May 13, 2015 at 9:51 am
I have two machines that have SQL Server 2008R2 Standard Edition on them and they participate in Log Shipping. I am having to do an in-place upgrade. I know it is not recommended but I have tried unsuccessfully to get around it.
The Secondary is in Standby (Read Only) mode. My assumption is that a DB in Standby Mode from Log Shipping would cause an in place upgrade to fail. My plan is to stop the RESTORE job so I can do the last restore using WITH RECOVERY to bring the database online prior to running the upgrade. I plan to then stop the BACKUP job on the Primary prior to upgrading that SQL instance. I was then going to take a new full backup of the Primary to restore to the Secondary to kick off Log Shipping again. Will this work or do I have to completely tear down Log Shipping and build it back up after both SQL instances have been upgraded?
May 14, 2015 at 4:19 am
lmarkum (5/13/2015)
The Secondary is in Standby (Read Only) mode. My assumption is that a DB in Standby Mode from Log Shipping would cause an in place upgrade to fail.
To upgrade these servers you'll need to upgrade the secondary first to SQL Server 2014. Before upgrading, the secondary database will need to be changed from standby\readonly to restoring mode. Also disable the copy and restore jobs on the secondary.
Once you've upgraded SQL Server on the secondary, then upgrade the Primary. Once this is done you may set the secondary back to standby mode.
lmarkum (5/13/2015)
restore using WITH RECOVERY to bring the database online prior to running the upgrade.
No, this will break the LS scenario.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 14, 2015 at 5:16 am
Thanks for the help. Glad to know I don't have to completely tear down the Log Shipping to do the upgrade.
April 10, 2018 at 8:40 am
Perry Whittle - Thursday, May 14, 2015 4:19 AMlmarkum (5/13/2015)
The Secondary is in Standby (Read Only) mode. My assumption is that a DB in Standby Mode from Log Shipping would cause an in place upgrade to fail.To upgrade these servers you'll need to upgrade the secondary first to SQL Server 2014. Before upgrading, the secondary database will need to be changed from standby\readonly to restoring mode. Also disable the copy and restore jobs on the secondary.Once you've upgraded SQL Server on the secondary, then upgrade the Primary. Once this is done you may set the secondary back to standby mode.
lmarkum (5/13/2015)
restore using WITH RECOVERY to bring the database online prior to running the upgrade.No, this will break the LS scenario.
Hello Pery,
Glad I found your advise, as I am really stuck here. I am planning to upgrade 2008 to 2016 with one secondary server. There is log shipping too. As I am very new to SQL let me summarize what I found:
1) Start with secondary server
2) Change Simple/Read Only to Restoring mode
- this can be done by these two steps
Step 1: Disconnect all the connections to the database
USE master
ALTER DATABASE <Database Name> SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE <Database Name> SET ONLINE
Step 2: Bring the database online
RESTORE DATABASE <Database Name> WITH RECOVERY
3) Stop SQL Agent
4) Upgrade secondary SQL DB
5) Upgrade primary SQL DB
6) Put secondary DB back to Simple/Read - I didnt find an answer to this... Can you validate my steps and help me with the point 6?
Thank you!
Radek
April 11, 2018 at 12:52 am
radek.freimann - Tuesday, April 10, 2018 8:40 AMPerry Whittle - Thursday, May 14, 2015 4:19 AMlmarkum (5/13/2015)
The Secondary is in Standby (Read Only) mode. My assumption is that a DB in Standby Mode from Log Shipping would cause an in place upgrade to fail.To upgrade these servers you'll need to upgrade the secondary first to SQL Server 2014. Before upgrading, the secondary database will need to be changed from standby\readonly to restoring mode. Also disable the copy and restore jobs on the secondary.Once you've upgraded SQL Server on the secondary, then upgrade the Primary. Once this is done you may set the secondary back to standby mode.
lmarkum (5/13/2015)
restore using WITH RECOVERY to bring the database online prior to running the upgrade.No, this will break the LS scenario.
Hello Pery,
Glad I found your advise, as I am really stuck here. I am planning to upgrade 2008 to 2016 with one secondary server. There is log shipping too. As I am very new to SQL let me summarize what I found:
1) Start with secondary server2) Change Simple/Read Only to Restoring mode
- this can be done by these two steps
Step 1: Disconnect all the connections to the database
USE master
ALTER DATABASE <Database Name> SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE <Database Name> SET ONLINEStep 2: Bring the database online
RESTORE DATABASE <Database Name> WITH RECOVERY3) Stop SQL Agent
4) Upgrade secondary SQL DB
5) Upgrade primary SQL DB
6) Put secondary DB back to Simple/Read - I didnt find an answer to this... Can you validate my steps and help me with the point 6?Thank you!
Radek
Or is it better to perform switching from standby to recovery and back with SSMS? I found it easier for me 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply