A spreadsheet that says every server is patched is only as fresh as the last person who edited it. Tracking which updates each instance really runs means capturing the exact build from the instance itself, dated, and comparing it with a target.
Choose a Stable Instance Key
A fleet report needs one row per SQL Server instance, not one row per Windows computer. A machine can host a default instance and named instances. A listener can move between replicas. Decide which identity the inventory uses and keep the connection target as a separate field.
I use a controlled instance key assigned by the inventory system. I also record @@SERVERNAME, MachineName, and InstanceName from SQL Server. Those fields help detect a rename or a connection through an alias. They are observations, not a substitute for the inventory key.
Record environment and application owner in a related table. Technical properties can be collected automatically. Ownership needs review by a person. The update report becomes actionable when every lagging row has someone responsible for its next maintenance window.
Keep Dated Snapshots for Tracking Which Updates Each Instance Runs
Create a narrow history table in a DBA database. Store capture time, instance key, full ProductVersion, ProductLevel, ProductUpdateLevel, and ProductBuildType. Do not overwrite yesterday’s row. A build history tells you when a patch arrived and whether a server stopped reporting.
I have never met a patch spreadsheet that was right. I have met plenty that were confidently wrong, which is worse.
The following table is a starting design for a single collector database. Adapt names and permissions to your environment. The capture process needs a unique key for each target and a clear failure record when a target cannot be reached. No response is not the same as an unchanged build.
Retain enough history to support incident review and audits. A dated row can answer whether a server was already patched before a problem began. The table stays small compared with application data, so history is usually worth keeping.
CREATE TABLE dbo.ServerPatchHistory
(
InstanceKey sysname NOT NULL,
CapturedAt datetimeoffset(0) NOT NULL,
ProductVersion nvarchar(128) NULL,
ProductLevel nvarchar(128) NULL,
ProductUpdateLevel nvarchar(128) NULL,
ProductBuildType nvarchar(128) NULL,
CONSTRAINT PK_ServerPatchHistory
PRIMARY KEY (InstanceKey, CapturedAt)
);Collect From Each Instance
Run SERVERPROPERTY on the target instance itself. A central job can connect to an approved list of servers. A local Agent job can collect locally and send the row to a central destination. Choose a pattern your team can secure and support. The query is small; identity and error handling are the hard parts.
Do not use a listener as the only target for an availability group. It follows the primary and can leave secondaries unmeasured. Connect to each replica instance directly for patch inventory. Keep the listener in the topology record for application routing.
If a property returns NULL on an older build, store NULL and retain the full version. Do not silently replace it with “RTM.” A missing label can have several explanations. Match the numeric build to Microsoft’s official version history before assigning a servicing branch.
SELECT
SYSDATETIMEOFFSET() AS CapturedAt,
@@SERVERNAME AS RegisteredName,
SERVERPROPERTY('MachineName') AS MachineName,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
SERVERPROPERTY('ProductBuildType') AS ProductBuildType;
Set Targets by Release and Branch Before Tracking Which Updates Are Missing
A fleet rarely has one universal target build. Separate targets by major version, approved CU, security branch, and application constraints. An older major version with a larger build component is not ahead of a newer major version. Do not compare only the third number in the version string.
Maintain an approved target table with the full ProductVersion and a label such as CU or GDR. Update it only after package review and testing. A new CU appearing on Microsoft Learn does not automatically change your production target. The report should compare servers with the version your organization approved.
Some instances have a temporary exception. Record the reason, owner, and expiry date. An exception without an end date turns into permanent drift. Show exceptions in the report rather than hiding them. Management needs to see both the gap and the plan.
Report the Latest Successful Capture
Use a window function to pick the newest snapshot per instance. Then join the row to your target table and compare exact approved builds. The query below produces the current recorded state. It does not claim that a server is current if it stopped reporting days ago. Add a freshness rule in the report.
A string comparison of full version numbers is not a safe general patch ordering method. The servicing branch matters. Map builds to official release entries, then classify them against your target. The report can say “target matched,” “review required,” or “capture missing” rather than pretending every version string is sortable.
Show the last capture time next to every result. An update report without freshness is an old photograph.
WITH Latest AS
(
SELECT *,
ROW_NUMBER() OVER
(PARTITION BY InstanceKey ORDER BY CapturedAt DESC) AS rn
FROM dbo.ServerPatchHistory
)
SELECT InstanceKey, CapturedAt, ProductVersion,
ProductUpdateLevel, ProductBuildType
FROM Latest
WHERE rn = 1
ORDER BY InstanceKey;Investigate Drift Before Patching
When a server falls behind, confirm its current build directly. The collector can fail, connect to the wrong alias, or run before a patch completed. Check setup history and the running instance. Then decide whether the server needs a patch or the inventory needs repair.
When did every server in your inventory last report the build you expected? If the answer is never, that report is the first job.
When one server in a group is on a different build from its siblings, I want to know why before anybody patches anything. There is usually a reason, and the reason usually matters.
Group work by application and topology. Patch an availability group in the right order. Test a vendor application on the selected CU. Check backup and recovery readiness. A report should produce a maintenance plan, not a blind mass installer run.
If two instances show the same CU label but different full versions, look for a GDR or on demand update. Document the exact branch. The label is a helpful summary, while ProductVersion gives the exact identity.
Make Tracking Which Updates Each Server Has Useful to Owners
Send a short list of overdue instances, missing captures, and exceptions nearing expiry. Include owner, approved target, current observed build, and next action. Avoid a report that dumps every server every morning. People stop reading repeated noise.
Review the target table when a CU is approved or support status changes. Validate a few inventory rows against direct queries each month. If an instance disappears, confirm retirement instead of deleting it from history. Keep the record with a retirement date.
Tracking which updates each server has works when it drives action and preserves evidence. It should answer which instance is behind, since when, and who will move it.
Related reading on this blog: How to Check Your SQL Server Version, Edition and Patch Level and How to Patch SQL Server Without a Bad Morning.
A patch list is not a patch program, it is the evidence that tells you where the program must act.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
