Someone asks how many SQL Servers you run, and the honest answer is a pause. An inventory of every SQL Server turns that pause into a number, and finds the forgotten instances before a patch, an outage or an audit finds them for you.
Start Where Servers Hide
The obvious production machines are rarely the whole list. Search virtual machine catalogs, cloud accounts, cluster configurations, monitoring tools, backup systems, and software deployment records. Ask application teams about servers built for a short project that stayed for years. Developer laptops and lab machines need a separate category, but they still matter when licensed software or sensitive data lives there.
Almost every estate I walk into has at least one instance nobody claims. It is usually Express, usually on a machine nobody remembers building, and usually running something finance depends on.
Look for default and named instances. A Windows computer can host several SQL Server instances. One machine row is not enough. Include SQL Server Express installations bundled with applications. Check whether a report server or an integration service depends on a Database Engine somewhere else. The inventory of every SQL Server must describe the service path, not just the largest server in the room.
I compare independent lists. A backup system tells me what was protected. Monitoring tells me what was watched. Neither proves that an unlisted instance does not exist. The gaps between those lists are where I spend time.
Give Each Instance a Stable Identity
Record the computer name, instance name, environment, business owner, technical owner, and application. Add a unique inventory identifier that survives a display name change. A DNS alias can move to a different machine. An availability group listener can point to a different replica tomorrow. Those are useful connection targets, but they do not replace the identity of the actual instance.
Separate physical placement from application role. A replica can be secondary today and primary after a failover. The owner and application remain the same. Keep the listener, cluster name, replica names, and current roles as distinct fields. That model makes a maintenance plan readable without forcing you to infer topology from names.
Do not rely on a clever naming convention as the only source of truth. Names drift after migrations. Put the meaning in columns. A name should help a DBA at a prompt, but the inventory should still work when a historical name remains in service.
Capture Build and Support Facts for Every SQL Server in the Inventory
Collect ProductVersion, edition, update level, and operating system details. Record whether the instance is supported under your current lifecycle plan. Keep the date of the last check. A version copied into a spreadsheet five years ago is an old observation, not current evidence.
Run a direct query on each Database Engine instance. The data should come from the instance, not from someone’s memory. ProductVersion provides the exact build. ProductUpdateLevel can supply the CU label when applicable. Match the numeric build to Microsoft’s version history when you need the servicing branch. Save both values and the date.
The inventory can also record licensing information, but treat that field with care. Licensing depends on deployment and contract details beyond a query result. Link the instance to the license record maintained by the responsible team. Do not pretend SERVERPROPERTY can answer every contract question.
SELECT
@@SERVERNAME AS RegisteredName,
SERVERPROPERTY('MachineName') AS MachineName,
SERVERPROPERTY('InstanceName') AS InstanceName,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductUpdateLevel') AS UpdateLevel;
Record Recovery Before You Need It
For each production instance, list the backup location, backup owner, retention policy, recovery target, and last verified restore. Note the high availability design and the disaster recovery path. A box marked “has backups” is too vague during an outage. The DBA needs to know which copy to restore and who can approve the operation.
Inventory system databases as well as user databases. SQL Server Agent jobs, logins, linked servers, credentials, and server settings are part of a working instance. A database restore alone does not rebuild all those dependencies. Attach a runbook or a controlled location for that information.
Keep contact details current. A phone number in a ticket from last year is not a recovery plan. Make ownership a required field. When an owner leaves, route the record to a team rather than leaving a personal name as the only contact. This is where a simple inventory pays for itself.
Find Databases With No Clear Owner
The database list exposes another problem: an instance has a known owner, but an individual database does not. Capture database name, state, compatibility level, and application mapping. Ask the application team to confirm the mapping. A name such as TestDB on a production server is a warning, not a reliable classification.
I ask one question for every database on the list: who would call me if this stopped? If nobody can answer, that database goes to the top of the next review.
Do not infer business value from size. A tiny configuration database can stop a major application. A large archive can tolerate a longer recovery. Record the owner and recovery need explicitly. Keep the database list linked to the instance record so a move between servers is visible.
A query can give you the current database names and states. It cannot tell you who uses each one. Use connection information, job definitions, application configuration, and owner interviews to finish the record. Mark unknown ownership as an open item with a due date.
SELECT
name,
state_desc,
recovery_model_desc,
compatibility_level
FROM sys.databases
WHERE database_id > 4
ORDER BY name;Keep the Inventory of Every SQL Server True
Make inventory updates part of provisioning, migration, and retirement. A new instance is not complete until it has an owner, backup plan, monitoring, and an inventory row. A retired instance stays in history with a retirement date. Deleting its record hides useful evidence about old backups and application changes.
Refresh technical fields automatically each night or each week. Human fields need review by the responsible team. Compare discovered instances with the registered list and report both unmatched sides. A server that stopped reporting needs investigation. A newly discovered server needs an owner before it quietly becomes permanent.
I prefer a short exception report to an enormous spreadsheet email. Show new instances, missing captures, unsupported builds, and owner records overdue for review. Give each exception a person and a next step. An inventory that nobody acts on becomes a museum.
Use the Inventory of Every SQL Server in Real Decisions
Before a CU rollout, filter by major version, build, topology, and application criticality. Before a security response, locate exposed versions and identify their owners. Before a migration, identify every job and database tied to the source. These are different questions, but one reliable inventory of every SQL Server supports all of them.
Validate samples by connecting to the servers. Check that the captured build matches a direct query and that the listed backup route still works. Review a server from each environment, including one named instance and one replica. Do this on a schedule. Trust grows from repeated checks, not from the number of rows.
When the inventory is challenged, show the capture date and source. If the record is wrong, correct it and fix the collection process that produced it. I have seen inventories become reliable only after teams treat errors as useful signals. The goal is a list you can act on at midnight.
Related reading on this blog: The First Hour on a Server Nobody Can Explain and How to Get List of SQL Server Instances Installed on a Machine Via T-SQL?.
An inventory is not a list of names, it is a map of responsibility and recovery.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
