none
How to determine which SQL Server 2012 features are installed

    Question

  • I have sql server 2012 Enterprise installed, and i want to know which features and components are installed,

    please assist,


    k

    Friday, March 3, 2017 1:42 PM

All replies

  • Hello,

    You might use the Installed SQL Server Features Discovery Report in the SQL Server Installation Center:



    Friday, March 3, 2017 1:52 PM
  • Is there any other option?

    can pull out info from installed instance?


    k

    Friday, March 3, 2017 2:34 PM
  • Do this at a command prompt.

    net start | find /I"SQL"

    Friday, March 3, 2017 2:38 PM
  • I think the best way to figure this out without having to run the setup program is to go through the setup log.

    You will find an entry in it like this:

      FEATURES:                      SQLENGINE, REPLICATION, ADVANCEDANALYTICS, FULLTEXT, DQ, AS, RS, SQL_SHARED_MR, DQC, IS, DREPLAY_CTLR, DREPLAY_CLT, MDS

    You will find it in C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log

    and you will need to look in your most recent directory and look in the summary file. My Summary file has this name:

    Summary_PUBLISHER_20160704_202631

    Yours will vary according to your server name and the datestamp of when you installed your SQL Server Instance.

    Friday, March 3, 2017 2:52 PM
  • This won't show a comprehensive list of all features and components installed, but if you want to see just a list of SQL Components (Services) installed along with their current status, this might help:

    -- Check_SQLComponentsInstalled.sql
    -- http://pawansingh1431.blogspot.co.uk/2011/02/check-what-are-sql-components-installed.html
    -- 09/23/2015
    
    
    /*------------------------------------------*/
    /* SQL Server Components Check Utility */
    /*------------------------------------------*/
    /*------------------------------------------*/
    SET NOCOUNT ON
    /* ------------------------------------------ Inital Setup -----------------------------------------------------*/
    CREATE TABLE #RegResult
    (
    ResultValue NVARCHAR(4)
    )
    CREATE TABLE #ServicesServiceStatus /*Create temp tables*/
    (
    RowID INT IDENTITY(1,1)
    ,ServerName NVARCHAR(128)
    ,ServiceName NVARCHAR(128)
    ,ServiceStatus varchar(128)
    ,StatusDateTime DATETIME DEFAULT (GETDATE())
    ,PhysicalSrverName NVARCHAR(128)
    )
    DECLARE
    @ChkInstanceName nvarchar(128) /*Stores SQL Instance Name*/
    ,@ChkSrvName nvarchar(128) /*Stores Server Name*/
    ,@TrueSrvName nvarchar(128) /*Stores where code name needed */
    ,@SQLSrv NVARCHAR(128) /*Stores server name*/
    ,@PhysicalSrvName NVARCHAR(128) /*Stores physical name*/
    ,@FTS nvarchar(128) /*Stores Full Text Search Service name*/
    ,@RS nvarchar(128) /*Stores Reporting Service name*/
    ,@SQLAgent NVARCHAR(128) /*Stores SQL Agent Service name*/
    ,@OLAP nvarchar(128) /*Stores Analysis Service name*/
    ,@REGKEY NVARCHAR(128) /*Stores Registry Key information*/
    SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128))
    SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
    SET @ChkInstanceName = @@serverName
    IF @ChkSrvName IS NULL /*Detect default or named instance*/
    BEGIN
    SET @TrueSrvName = 'MSQLSERVER'
    SELECT @OLAP = 'MSSQLServerOLAPService' /*Setting up proper service name*/
    SELECT @FTS = 'MSFTESQL'
    SELECT @RS = 'ReportServer'
    SELECT @SQLAgent = 'SQLSERVERAGENT'
    SELECT @SQLSrv = 'MSSQLSERVER'
    END
    ELSE
    BEGIN
    SET @TrueSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
    SET @SQLSrv = '$'+@ChkSrvName
    SELECT @OLAP = 'MSOLAP' + @SQLSrv /*Setting up proper service name*/
    SELECT @FTS = 'MSFTESQL' + @SQLSrv
    SELECT @RS = 'ReportServer' + @SQLSrv
    SELECT @SQLAgent = 'SQLAgent' + @SQLSrv
    SELECT @SQLSrv = 'MSSQL' + @SQLSrv
    END
    /* ---------------------------------- SQL Server Service Section ----------------------------------------------*/
    SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLSrv
    INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY
    IF (SELECT ResultValue FROM #RegResult) = 1
    BEGIN
    INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Sever service*/
    EXEC xp_servicecontrol N'QUERYSTATE',@SQLSrv
    UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    ELSE
    BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    /* ---------------------------------- SQL Server Agent Service Section -----------------------------------------*/
    SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLAgent
    INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY
    IF (SELECT ResultValue FROM #RegResult) = 1
    BEGIN
    INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Agent service*/
    EXEC xp_servicecontrol N'QUERYSTATE',@SQLAgent
    UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    ELSE
    BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    /* ---------------------------------- SQL Browser Service Section ----------------------------------------------*/
    SET @REGKEY = 'System\CurrentControlSet\Services\SQLBrowser'
    INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY
    IF (SELECT ResultValue FROM #RegResult) = 1
    BEGIN
    INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Browser Service*/
    EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'
    UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    ELSE
    BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    /* ---------------------------------- Integration Service Section ----------------------------------------------*/
    SET @REGKEY = 'System\CurrentControlSet\Services\MsDtsServer'
    INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY
    IF (SELECT ResultValue FROM #RegResult) = 1
    BEGIN
    INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Intergration Service*/
    EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MsDtsServer'
    UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    ELSE
    BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    /* ---------------------------------- Reporting Service Section ------------------------------------------------*/
    SET @REGKEY = 'System\CurrentControlSet\Services\'+@RS
    INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY
    IF (SELECT ResultValue FROM #RegResult) = 1
    BEGIN
    INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Reporting service*/
    EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@RS
    UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    ELSE
    BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    /* ---------------------------------- Analysis Service Section -------------------------------------------------*/
    IF @ChkSrvName IS NULL /*Detect default or named instance*/
    BEGIN
    SET @OLAP = 'MSSQLServerOLAPService'
    END
    ELSE
    BEGIN
    SET @OLAP = 'MSOLAP'+'$'+@ChkSrvName
    SET @REGKEY = 'System\CurrentControlSet\Services\'+@OLAP
    END
    INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY
    IF (SELECT ResultValue FROM #RegResult) = 1
    BEGIN
    INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Analysis service*/
    EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@OLAP
    UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    ELSE
    BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    /* ---------------------------------- Full Text Search Service Section -----------------------------------------*/
    SET @REGKEY = 'System\CurrentControlSet\Services\'+@FTS
    INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY
    IF (SELECT ResultValue FROM #RegResult) = 1
    BEGIN
    INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Full Text Search service*/
    EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@FTS
    UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    ELSE
    BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
    END
    /* -------------------------------------------------------------------------------------------------------------*/
    SELECT PhysicalSrverName AS 'Physical Server Name' /*Display finding*/
    ,ServerName AS 'SQL Instance Name'
    ,ServiceName AS 'SQL Server Services'
    ,ServiceStatus AS 'Current Service Service Status'
    ,StatusDateTime AS 'Date/Time Service Status Checked'
    FROM #ServicesServiceStatus
    /* -------------------------------------------------------------------------------------------------------------*/
    DROP TABLE #ServicesServiceStatus /*Perform cleanup*/
    DROP TABLE #RegResult

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, March 3, 2017 4:12 PM
    Friday, March 3, 2017 4:06 PM
  • Hi HuuM,

    The method finding it from installation log given by Hilary is very useful, you can try it, the log is stored with txt-format and you can copy it directly. It is shown as below:



    Besides, you can also use the following Powershell command to get it:

    get-wmiobject win32_product | 
    where {$_.Name -match "SQL" -AND $_.vendor -eq "Microsoft Corporation"} | 
    select name, version  > C:\test\test.txt

    Best Regards,
    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 6, 2017 8:00 AM