Setup finishing without an error is the lowest possible bar. Testing a cumulative update properly means restoring real data, running the work that matters, and walking away with a clear pass or stop for production.
Build a Test That Resembles Production
A tiny empty database can prove that SQL Server starts. It cannot prove that your application still works. Restore a recent production backup to a controlled test instance. Scrub sensitive data where policy requires it. Keep the database size, schema, compatibility level, and representative data distribution close to production. The workload depends on those details.
Match the important instance settings too. Memory limits, MAXDOP, cost threshold, trace flags, and tempdb layout can change query behavior. Record differences you cannot remove. A test result is easier to trust when everyone knows where the test server differs. Do not claim the patch caused a plan change that came from a different compatibility level.
Take a snapshot of the test environment before installing the CU. Capture versions, settings, database states, and job definitions. Rebuild or revert the test box between repeated trials. A clean starting state makes a second run meaningful.
Write Acceptance Checks Before Testing a Cumulative Update
Ask the application owner which transactions cannot fail. Include login, checkout, reporting, imports, exports, and scheduled processing as appropriate. The list should name specific actions and expected results. “Application looks fine” is not a test case. A runnable check and a named owner are better.
I write the pass criteria before I touch the installer, every time. Write them afterwards and you will find yourself grading on a curve.
Include operational checks: backups complete, restores work, SQL Server Agent starts, alerts fire, and availability replicas synchronize. If the instance hosts several applications, give each owner a small test list. One green dashboard cannot speak for every workload on the server.
Define a stop condition before testing. An unexpected error, a missed business job, or a significant regression against a measured baseline should halt promotion. Agree on who decides whether an observed difference is acceptable. That discussion is much easier before a maintenance window starts.
Restore and Establish a Baseline
Restore the same backup to the unpatched test instance first. Run the key workload and record its results. Capture execution plans and timings for the important queries. Capture Query Store data if that is part of your existing process. Do not insert sample figures into a report because they sound plausible. Use numbers produced by the test.
A workload replay can help when it respects real order and concurrency. A replay of one isolated query misses lock contention, job overlap, and connection behavior. Keep the replay process documented so you can repeat it after patching. Note changes in external services that the test cannot reproduce.
Check database state before installation. A suspect or restoring database can confuse the result. Confirm backups are readable and the test data represents the period you intend to compare. Baseline first. Then patch. Otherwise you will be measuring a mystery.
SELECT
name,
state_desc,
compatibility_level
FROM sys.databases
WHERE database_id > 4
ORDER BY name;
Patch and Verify the Engine
Install the exact CU package named in the change plan. Record the setup exit result and inspect the summary log. Restart as required. Then connect to the running Database Engine and read ProductVersion and ProductUpdateLevel. Compare the full version to the documented target build. A setup completion message is not the whole verification.
Read the SQL Server error log for startup failures, recovery messages, and new warnings. Check Agent and other SQL Server services. Confirm the application uses the intended instance after setup. An alias or listener can quietly land your test on another server.
If a component fails or the version is wrong, stop. Diagnose the setup log before rerunning the installer. Repeated blind attempts make the test harder to interpret. Save the failure evidence. It can prevent the same surprise in production.
SELECT
@@SERVERNAME AS ConnectedInstance,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductUpdateLevel') AS UpdateLevel,
SERVERPROPERTY('ProductBuildType') AS BuildType;Replay Jobs and Application Paths When Testing a Cumulative Update
Run the jobs that matter, not every job just because it exists. Focus on backup, ETL, reporting, cleanup, and business processing. Check job output, not only a final green icon. A step can succeed after skipping work because its input was missing. Feed the test job representative input.
Have application testers use the same entry points that production users use. Include connection pooling, authentication, and failover paths. A direct query in Management Studio cannot verify an application driver. Record who tested each path and what data was used.
Look for behavior changes as well as outright errors. A stored procedure can return the correct rows and still hold locks longer. A report can load but use a different plan. Compare your measured baseline and patched results. Investigate differences with actual plans, waits, and logs before declaring the CU responsible.
Compare Plans and Timings Fairly
Use the same query text, parameters, database statistics, and compatibility level for a useful comparison. Capture actual execution plans where safe. Check for changed join choice, memory grant, spills, and row estimates. Query Store can help compare the same query across runs. Do not choose one fast run as proof of improvement.
I compare the same query at the same data size, or I do not compare it at all. Did your test run against a copy as large as production? If not, the timings describe a different server.
Almost every CU test I review compares a warm cache after the patch with a cold one before it. Of course the patched server looks faster. Run both sides the same way or do not compare them.
Cold cache and warm cache runs answer different questions. Decide which matters for the workload. Avoid changing indexes while testing a cumulative update unless the change is part of the planned deployment. Every additional variable makes the result less clear.
When a plan changes, identify the cause. The CU can change optimizer behavior, but updated statistics, changed data, or a configuration difference can do the same. Keep evidence. A useful test report says which query changed, how it changed, and whether the application felt it.
SELECT
name,
value_in_use
FROM sys.configurations
WHERE name IN
('max degree of parallelism',
'cost threshold for parallelism',
'max server memory (MB)')
ORDER BY name;Practice Recovery and Sign Off After Testing a Cumulative Update
The test should include the fallback procedure. Know how to uninstall the CU if supported for your release and package. Know when restoring a pre patch backup is safer. Protect master, msdb, and user databases before production work. A CU uninstall changes binaries, but it does not turn an upgraded database back into an older format.
Write a short sign off with current build, target build, tested package, restored database date, job results, application owner results, performance comparisons, and open issues. An unresolved issue gets an owner and a decision. Silence is not approval.
I keep the report beside the production change ticket. The production team can repeat the verification instead of guessing what “tested” meant. A realistic rehearsal turns patch night into a controlled task rather than a surprise party nobody wanted.
Related reading on this blog: What a Cumulative Update Actually Contains and Using Query Store to Prove an Upgrade Did Not Hurt.
A patch test is not a setup rehearsal, it is proof that your workload survives the change.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
