Checking Optional Features Installed on an Instance From T-SQL

Checking optional features installed on an instance takes three separate questions: is it installed, is it turned on, and does it actually run? T-SQL can answer the first two quickly. The third needs a real test.

Why one flag is not an answer

Say you are planning an upgrade and your manager asks, “Which extra features do we use on this server?” You run one query, see a 1, and reply “all good.” Three weeks later the Python job fails on the new server, and you get to explain why.

An installed feature can be switched off. A switched-on feature can have a stopped service behind it. So I keep three states apart: installed, enabled, working. Let me show you all three on one instance.

Read the installation flags

SERVERPROPERTY returns installation and enablement flags in one row. I add the version and edition so the result tells you what it was measured on. A 1 means yes and a 0 means no. A NULL means SQL Server cannot tell you, so treat it as a question, not as a no.

SELECT SERVERPROPERTY('ProductVersion')              AS ProductVersion,
       SERVERPROPERTY('Edition')                     AS Edition,
       SERVERPROPERTY('IsPolyBaseInstalled')         AS PolyBaseInstalled,
       SERVERPROPERTY('IsFullTextInstalled')         AS FullTextInstalled,
       SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS AdvancedAnalyticsInstalled,
       SERVERPROPERTY('IsHadrEnabled')               AS HadrEnabled;

On my test instance, Advanced Analytics comes back as 1. PolyBase, Full-Text and availability groups come back as 0. Notice that IsHadrEnabled is about enablement, while the others are about installation. Do not put them in one column called “installed features.”

One more trap. SERVERPROPERTY does not complain about a misspelled property name. It just returns NULL.

SELECT SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS RightName,
       SERVERPROPERTY('IsAdvancedAnalyticInstalled')  AS Typo;

The second column is NULL because I dropped one letter. A report built on that would say “not installed” and be wrong. Copy property names from a working query.

Check the services and settings

Now look at what is running. The service view shows startup type and status. I leave out the service account and file path, because those are sensitive and you rarely need them in a report. The second query reads two settings that gate features. The third looks for the Integration Services catalog.

SELECT servicename, startup_type_desc, status_desc
FROM sys.dm_server_services
ORDER BY servicename;

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN (N'external scripts enabled', N'Ad Hoc Distributed Queries')
ORDER BY name;

SELECT name, state_desc
FROM sys.databases
WHERE name = N'SSISDB';

On my test instance, the Launchpad service is disabled and stopped, and external scripts enabled shows 0. So although Advanced Analytics is installed, nobody can run a Python or R script here today. That is the gap between installed and enabled.

The SSISDB query returns no rows. That does not prove Integration Services is missing. The catalog may never have been created, or the packages may live in files or msdb. A missing row is a coverage gap, so write it down as one.

Put the three states in one row

Here is a small report for the Advanced Analytics example. It lines up the installation flag, the setting and the service status. The verdict says “Looks ready” only when all three agree, and even then the wording is careful.

SELECT CAST(SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS int) AS ComponentInstalled,
       CAST(c.value_in_use AS int)                                 AS ScriptsEnabled,
       COALESCE(s.status_desc, N'No Launchpad service found')      AS LaunchpadStatus,
       CASE WHEN CAST(SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS int) = 1
             AND c.value_in_use = 1
             AND s.status_desc = N'Running'
            THEN N'Looks ready, now test it'
            ELSE N'Not ready' END                                  AS Verdict
FROM sys.configurations AS c
OUTER APPLY (SELECT TOP (1) status_desc
             FROM sys.dm_server_services
             WHERE servicename LIKE N'SQL Server Launchpad%') AS s
WHERE c.name = N'external scripts enabled';

On my instance the verdict is “Not ready,” which matches what we saw above. Notice the wording on the good path: “now test it.” A running service is still not a working script. Run one harmless script under the same login your application uses before you promise anything.

Fill the gaps before you sign off

T-SQL sees the engine side. For a full upgrade inventory, also run the setup discovery report on the Windows host. It lists shared components that the engine cannot see. Compare the two lists and keep both.

If a query is denied, note it as a gap instead of guessing. And do not change a startup type just because a service looks idle. Ask the feature owner first. Everything in this post only reads state. Nothing here enables a feature or starts a service.

Next time someone asks what is installed, answer with all three states.

A feature flag is not proof it works, it is one clue about what is installed.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.