February 25, 2017 at 11:55 am
Hi all,
As most of of you already know, MS recommends to patch CUs as the same as SPs.
I have generally 2 questions and i hope gurus here can advise.
Q1) Does CUs contain security updates / vulnerability patches ?
Where can I find security updates in MS pertaining to SQL server only ? (I know i can see them in sqlserverbuilds and sqlserverupdates), but is there a common place in MS ?
Q2) Rolling update -> Assuming I have 2 nodes/replica in an AG - > is it there any rules or restrictions that i must patch all of them on the same day ?
Can i patch the secondary replica 1st then wait for a few days, failover to the 2nd replica, then patch the primary ?
Regards,
Noob
February 25, 2017 at 10:30 pm
CUs will contain patches that are new and important for installation. Some could be contained in CUs, but they are also added to Windows Update and in the Security Bulletins. I am not sure where in MS that you would find just that information. That is why I use sqlserverbuilds.
You do not have to patch both in the same day, but think of this scenario and you can decide how you want to handle it. If you patch the secondary and wait to patch the primary, then if the AG fails over to the secondary it will upgrade the databases to the new version of SQL and if the database version was updated, and the AG tries to fail back you will have a problem. So you should plan on upgrading both to the same Patch version as soon as possible to avoid interruption.
February 26, 2017 at 9:40 am
szejiekoh - Saturday, February 25, 2017 11:55 AMHi all,
As most of of you already know, MS recommends to patch CUs as the same as SPs.I have generally 2 questions and i hope gurus here can advise.
Q1) Does CUs contain security updates / vulnerability patches ?
Where can I find security updates in MS pertaining to SQL server only ? (I know i can see them in sqlserverbuilds and sqlserverupdates), but is there a common place in MS ?Q2) Rolling update -> Assuming I have 2 nodes/replica in an AG - > is it there any rules or restrictions that i must patch all of them on the same day ?
Can i patch the secondary replica 1st then wait for a few days, failover to the 2nd replica, then patch the primary ?Regards,
Noob
1) CUs do\can contain security updates, each service pack or cumulative update has an associated Kb number you can find all patch information and the associated KB numbers here
2) you can leave the primary unpatched but why would you want to? You would usually patch your down level environments first to test and then complete the patching to prod.
Incidentally, when patching a minor version of sql sever (via an SP or CU) the product will perform a downgrade if it fails over to a node with a lesser patch level
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 27, 2017 at 10:15 am
Perry Whittle - Sunday, February 26, 2017 9:40 AMszejiekoh - Saturday, February 25, 2017 11:55 AMHi all,
As most of of you already know, MS recommends to patch CUs as the same as SPs.I have generally 2 questions and i hope gurus here can advise.
Q1) Does CUs contain security updates / vulnerability patches ?
Where can I find security updates in MS pertaining to SQL server only ? (I know i can see them in sqlserverbuilds and sqlserverupdates), but is there a common place in MS ?Q2) Rolling update -> Assuming I have 2 nodes/replica in an AG - > is it there any rules or restrictions that i must patch all of them on the same day ?
Can i patch the secondary replica 1st then wait for a few days, failover to the 2nd replica, then patch the primary ?Regards,
Noob1) CUs do\can contain security updates, each service pack or cumulative update has an associated Kb number you can find all patch information and the associated KB numbers here
2) you can leave the primary unpatched but why would you want to? You would usually patch your down level environments first to test and then complete the patching to prod.
Incidentally, when patching a minor version of sql sever (via an SP or CU) the product will perform a downgrade if it fails over to a node with a lesser patch level
Hi Ben , Perry,
Thanks for your insights.
I am getting kinda confused from this below ->
q1) reading from the documentation, it states
"Do not upgrade/update the primary replica node before upgrading/updating any other secondary replica nodes. An upgraded primary replica can no longer ship logs to any secondary replica that has not yet been upgraded to the same version."
In a rolling upgrade -> If i upgrade my secondary replica 1st, then failover to it, essentially making it the new primary replica (before patching my old primary/aka current secondary replica) -> wouldn't i fall into the same problem (that my secondary is of an older version) ?
q2) From what both of you have mentioned, it seems like in patching, beside the software binaries getting updated, the "database" itself is also upgraded - am i right ?
What about an instance/service that isn't running ? it will still get patched and its databases will get upgraded when the service startup ?
Regards,
Noob
February 28, 2017 at 4:45 am
szejiekoh - Monday, February 27, 2017 10:15 AMq1) reading from the documentation, it states"Do not upgrade/update the primary replica node before upgrading/updating any other secondary replica nodes. An upgraded primary replica can no longer ship logs to any secondary replica that has not yet been upgraded to the same version."
In a rolling upgrade -> If i upgrade my secondary replica 1st, then fail
over to it, essentially making it the new primary replica (before patching my old primary/aka current secondary replica) -> wouldn't i fall into the same problem (that my secondary is of an older version) ?
Upgrade all secondarys first, then failover to an upgraded secondary which becomes the new primary. You then upgrade the remaining instance
szejiekoh - Monday, February 27, 2017 10:15 AM
q2) From what both of you have mentioned, it seems like in patching, beside the software binaries getting updated, the "database" itself is also upgraded - am i right ?
What about an instance/service that isn't running ? it will still get patched and its databases will get upgraded when the service startup ?
Binaries are updated, system databases such as resource database will be updated too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 28, 2017 at 5:22 am
dbaduck - Saturday, February 25, 2017 10:30 PMIf you patch the secondary and wait to patch the primary, then if the AG fails over to the secondary it will upgrade the databases to the new version of SQL and if the database version was updated, and the AG tries to fail back you will have a problem.
CUs do not bump the database version, and so don't break on a fail back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2017 at 10:03 am
szejiekoh - Monday, February 27, 2017 10:15 AMq1) reading from the documentation, it states"Do not upgrade/update the primary replica node before upgrading/updating any other secondary replica nodes. An upgraded primary replica can no longer ship logs to any secondary replica that has not yet been upgraded to the same version."
In a rolling upgrade -> If i upgrade my secondary replica 1st, then fail
over to it, essentially making it the new primary replica (before patching my old primary/aka current secondary replica) -> wouldn't i fall into the same problem (that my secondary is of an older version) ?
Upgrade all secondarys first, then failover to an upgraded secondary which becomes the new primary. You then upgrade the remaining instance
Hi Perry, Steve,
Thanks for your replies. The above is where i find it confusing.
1st) yes from the documentation online it advises to patch on secondary 1st, failover to the secondary, then patch the previous primary.
but it also mentioned that to "NOT" patch the primary 1st as it would no longer sync to any secondary replica not of the same version.
Now, in a 2 node configuration, if i have finish patching my secondary replica on node2 and failover to it, does it mean it will no longer sync to my "old primary" aka secondary replica ?
Regards,
Noob
February 28, 2017 at 11:21 am
Perry Whittle - Tuesday, February 28, 2017 4:45 AMszejiekoh - Monday, February 27, 2017 10:15 AMq1) reading from the documentation, it states"Do not upgrade/update the primary replica node before upgrading/updating any other secondary replica nodes. An upgraded primary replica can no longer ship logs to any secondary replica that has not yet been upgraded to the same version."
In a rolling upgrade -> If i upgrade my secondary replica 1st, then fail
over to it, essentially making it the new primary replica (before patching my old primary/aka current secondary replica) -> wouldn't i fall into the same problem (that my secondary is of an older version) ?Upgrade all secondarys first, then failover to an upgraded secondary which becomes the new primary. You then upgrade the remaining instance
Hi Perry, Steve,
Thanks for your replies. The above is where i find it confusing.
1st) yes from the documentation online it advises to patch on secondary 1st, failover to the secondary, then patch the previous primary.
but it also mentioned that to "NOT" patch the primary 1st as it would no longer sync to any secondary replica not of the same version.
Now, in a 2 node configuration, if i have finish patching my secondary replica on node2 and failover to it, does it mean it will no longer sync to my "old primary" aka secondary replica ?
Regards,
Noob
I havent indicated you patch your primary node first, you patch it last when it becomes a secondary.
What im saying is you dont patch the secondarys and then not bother patching the primary which is the way i read your post.
Before patching suspend data movement at the primary will stop sync to all secondarys, resume data movement once patching is finished
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 28, 2017 at 9:43 pm
szejiekoh - Tuesday, February 28, 2017 10:03 AMPerry Whittle - Tuesday, February 28, 2017 4:45 AMszejiekoh - Monday, February 27, 2017 10:15 AMq1) reading from the documentation, it states"Do not upgrade/update the primary replica node before upgrading/updating any other secondary replica nodes. An upgraded primary replica can no longer ship logs to any secondary replica that has not yet been upgraded to the same version."
In a rolling upgrade -> If i upgrade my secondary replica 1st, then fail
over to it, essentially making it the new primary replica (before patching my old primary/aka current secondary replica) -> wouldn't i fall into the same problem (that my secondary is of an older version) ?Upgrade all secondarys first, then failover to an upgraded secondary which becomes the new primary. You then upgrade the remaining instance
Hi Perry, Steve,
Thanks for your replies. The above is where i find it confusing.
1st) yes from the documentation online it advises to patch on secondary 1st, failover to the secondary, then patch the previous primary.
but it also mentioned that to "NOT" patch the primary 1st as it would no longer sync to any secondary replica not of the same version.Now, in a 2 node configuration, if i have finish patching my secondary replica on node2 and failover to it, does it mean it will no longer sync to my "old primary" aka secondary replica ?
Regards,
Noob
I havent indicated you patch your primary node first, you patch it last when it becomes a secondary.
What im saying is you dont patch the secondarys and then not bother patching the primary which is the way i read your post.
Before patching suspend data movement at the primary will stop sync to all secondarys, resume data movement once patching is finished
Hi Perry,
Yeap. What i meant is that once i patched the secondary and the secondary become the new primary (failover), it will stop data movement to the secondary (old primary) as it is of a different / lower version ?
Regards,
Noob
March 1, 2017 at 4:19 am
better to control this yourself, suspend data movement at the primary, apply patches and resume data movement
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 1, 2017 at 7:52 am
If you want to know what security patches may be included in what CUs here is a list to keep everything straight in date order.
http://sqlserverbuilds.blogspot.com/
March 1, 2017 at 9:31 am
Perry Whittle - Wednesday, March 1, 2017 4:19 AMbetter to control this yourself, suspend data movement at the primary, apply patches and resume data movement
Hi Perry,
Thank you for your reply.
Do you mind if I ask, what is the rational behind this that
primary (older version) -> secondary (newer version) = ok
primary (newer version) -> secondary (older version) = not ok , suspend data movement
because newer version are backward compatible ?
March 1, 2017 at 10:07 am
the rational is simple, whilst the patches are applied and the instance is in upgrade mode, you don't need other factors muddying the waters.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 4, 2017 at 11:02 am
Perry Whittle - Wednesday, March 1, 2017 10:07 AMthe rational is simple, whilst the patches are applied and the instance is in upgrade mode, you don't need other factors muddying the waters.
Hi Perry,
Sorry for the late reply.
Just some last confirmation on patching and upgrading ->
q1) when we patched the secondary replica, beside the binaries getting updated, does its databases get "updated" as well only when the AG failover to it ? or the databases get updated as soon as the binaries are patched
@@version will return the binary version
is there any command to check "database" version ?
q2) reason for asking the above is that I have DR instance/replica which we will seldom failover to it, but we will still patched it anyway.
I am worried that if we just patched it without failing over to it, will its "databases" get updated as well ? (and if it doesn't, will we have trouble failing over to it as over time more and more patches are being patched .
Regards,
Noob
March 7, 2017 at 12:17 pm
szejiekoh - Saturday, March 4, 2017 11:02 AMJust some last confirmation on patching and upgrading ->
q1) when we patched the secondary replica, beside the binaries getting updated, does its databases get "updated" as well only when the AG failover to it ? or the databases get updated as soon as the binaries are patched
if i remember correctly only when they go through recovery.
szejiekoh - Saturday, March 4, 2017 11:02 AM
@@version will return the binary version
is there any command to check "database" version ?
Not while the database is not recovered.
szejiekoh - Saturday, March 4, 2017 11:02 AM
q2) reason for asking the above is that I have DR instance/replica which we will seldom failover to it, but we will still patched it anyway.I am worried that if we just patched it without failing over to it, will its "databases" get updated as well ? (and if it doesn't, will we have trouble failing over to it as over time more and more patches are being patched .
Regards,
Noob
When they go through recovery which would usually be fairly quick
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply