Applying service packs used to be a process that was weeks in planning and rehearsing.
The number and sophistication of hostile actors these days results in pressure to patch frequently and as close to the point of issue as possible.
The level of automated testing that is possible these days greatly reduces the risk associated with patching from an application perspective. This is how the app and the DB behaves.
Then there is how SQL Server behaves. SQL Server 2000 SP4 (build2039) was found not to use memory beyond the 32bit limit. Build 2040 was issued PDQ by Microsoft. Could we have written some form of test for that back in those days? In hindsight we probably could but it wouldn't have been easy.
Now we have extended events, a rich set of DMVs, increasingly sophisticated monitoring software. For me there are three big limitations
- Our imagination in thinking of what to test
- Allocation of time and resource to ensure that such testing is implemented.
- Testing on production workloads without actually being in production.
The latter has been a problem for the decades I've been in IT. The thinking these days is that if you have a robust set of tests that shows your application works in lower environments. No unusual DB Engine behaviours, IOPs, Memory are apparent then you do test in production.
I would make damn sure that the current DR process is demonstrable on the patched version before moving to prod.