Determine the version, edition, and update level of SQL Server and its components

• 05/28/2021
• 31 minutes to read
• • r
  • s
  • s
  • H
  • c
  • +4

This article lists various builds or updates that are available for different versions of SQL Server and describe the procedures to determine the version of SQL Server that is running on a given system.

Original product version:   SQL Server
Original KB number:   321185

Summary

• A downloadable version of an Excel workbook that contains all the build versions together with their current support lifecycle stage for 2005 through the current version is available. Click to download this Excel file now. (File name: SQL Server Builds V3.xlsx)

• To learn what a specific version number of SQL Server maps to, or to find the KB article information for a specific cumulative update package or a service pack, search for the version number in the SQL Server Complete Version list tables.

• To find the edition of your SQL Server instance, you can use one of the procedures in Method 2 through Method 5 in the Determine which version and edition of SQL Server Database Engine is running section.

  Note

  The version information and edition information are in the same output string.

  Note

  For information about SQL Server Support lifecycle, check the Microsoft SQL Server support lifecycle page.

Latest updates available for currently supported versions of SQL Server

Each of the following links provides information for all of the applicable products and technologies.

Determine which version and edition of SQL Server Database Engine is running

To determine the version of SQL Server, you can use any of the following methods.

• Method 1: Connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will show the version information in parentheses, together with the user name that is used to connect to the specific instance of SQL Server.

• Method 2: Look at the first few lines of the Errorlog file for that instance. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files. The entries may resemble the following:

  2011-03-27 22:31:33.50 Server Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)  
  March 29 2009 10:11:52  
  Copyright (c) 1988-2008 Microsoft Corporation  
  Express Edition (64-bit)  
  on Windows NT 6.1 <X64> (Build 7600: )
  

  This entry provides all the necessary information about the product, such as version, product level, 64-bit versus 32-bit, the edition of SQL Server, and the OS version on which SQL Server is running.

  Note

  The output of this query has been enhanced to show additional information, as documented in the blog post article, What build of SQL Server are you using?, for the following versions:

  • SQL Server 2014 RTM CU10 and later versions
  • SQL Server 2014 Service Pack 1 CU3 and later versions
  • SQL Server 2012 Service Pack 2 CU7 and later versions

• Method 3: Connect to the instance of SQL Server, and then run the following query:

  Select @@version
  

  An example of the output of this query is the following:

  Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)  
  March 29 2009 10:11:52
  Copyright (c) 1988-2008 Microsoft Corporation Express Edition (64-bit)  
  on Windows NT 6.1 <X64> (Build 7600: )
  

  Note

  The output of this query has been enhanced to show additional information. This is documented in the blog post article, What build of SQL Server are you using?, for the following versions:

  • SQL Server 2014 RTM CU10 and later versions

  • SQL Server 2014 Service Pack 1 CU3 and later versions

  • SQL Server 2012 Service Pack 2 CU7 and later versions

    

• Method 4: Connect to the instance of SQL Server, and then run the following query in SQL Server Management Studio (SSMS):

  SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
  

  Note

  This query works for any instance of SQL Server 2000 or a later version.

  The following results are returned:

  • The product version (for example, 10.0.1600.22)
  • The product level (for example, RTM)
  • The edition (for example, Enterprise)

  For example, the results resemble the following.

  14.0.2027.2	RTM	Developer Edition (64-bit)

  Note

  • The SERVERPROPERTY function returns individual properties that relate to the version information, although the @@VERSION function combines the output into one string. If your application requires individual property strings, you can use the SERVERPROPERTY function to return them instead of parsing the @@VERSION results.

  • This method also works for SQL Azure Database instances. For more information, see the following topic in SQL Server Books Online SERVERPROPERTY (Transact-SQL).

  • Starting with SQL Server 2014 RTM Cumulative Update 10 and SQL Server 2014 Service Pack 1 Cumulative Update 3, additional properties have been added to ServerProperty statement. For a complete list review SERVERPROPERTY (Transact-SQL).

• Method 5: Starting in SQL Server 2008, you can also use the Installed SQL Server Features Discovery report. This report can be found by locating the Tools page of SQL Server Installation Center. This tool gives information about all the instances of SQL Server that are installed on the system. These include client tools such as SQL Server Management Studio. The only thing to be aware of is that this tool can be run locally only on the system where SQL server is installed. It cannot be used to obtain information about remote servers. For more information, see Validate a SQL Server Installation.

  A snapshot of a sample report is as follows:

  

Determine the version of SQL Server Client tools

• SQL Server Management Studio (SSMS)

  To determine which versions of the client tools are installed on your system, start Management Studio, and then click About on the Help menu. (See the following screenshot.)

  

  Starting with SQL Server 2016, SQL Server management studio is offered as a separate download. For additional information about various versions of the tool, review Release notes for SQL Server Management Studio (SSMS).

  SQL Server Data Tools

  For additional information about SQL Server Data Tools, review Download SQL Server Data Tools (SSDT) for Visual Studio.

SQL Server Reporting Services

The version of SQL Server Reporting Services (SSRS) is displayed on the Reporting Services Web Service URL, for example: http://servername/reportserver. The version is also displayed in the Reporting Services Configuration tool.

SQL Server Integration Services

The version of SQL Server Integration Services aligns with the version of SQL server that you had installed.

SQL Server Analysis Services

To determine the version of SQL Server Analysis Services, use one of the following methods:

• Method 1: Connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will show the version information in parentheses, together with the user name that is used to connect to the specific instance of Analysis Services.

• Method 2: Check the version of the Msmdsrv.exe file in the Analysis Services bin folder. The default locations are shown in the following table.

  Analysis Services version	Location
  2019	%ProgramFiles%\Microsoft SQL Server\MSAS15.InstanceName\OLAP\Bin\MSMDSrv.exe
  2017	%ProgramFiles%\Microsoft SQL Server\MSAS14.InstanceName\OLAP\Bin\MSMDSrv.exe
  2016	%ProgramFiles%\Microsoft SQL Server\MSAS13.InstanceName\OLAP\Bin\MSMDSrv.exe
  2014	%ProgramFiles%\Microsoft SQL Server\MSAS12.InstanceName\OLAP\Bin\MSMDSrv.exe
  2012	%ProgramFiles%\Microsoft SQL Server\MSAS11.InstanceName\OLAP\Bin\MSMDSrv.exe

• Method 3: Use the registry subkeys that are listed in the following table.

  Analysis Services version	Location
  2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS15.InstanceName\MSSQLServer\CurrentVersion Key: CurrentVersion HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS15.InstanceName \Setup Keys: PatchLevel , Version, Key Edition
  2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS14.InstanceName\MSSQLServer\CurrentVersion Key: CurrentVersion HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS14.InstanceName \Setup Keys: PatchLevel , Version, Key Edition
  2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS13.InstanceName\MSSQLServer\CurrentVersion Key: CurrentVersion HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS13.InstanceName \Setup Keys: PatchLevel , Version, Key Edition
  2014	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS12.InstanceName\MSSQLServer\CurrentVersion Key: CurrentVersion HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS12.InstanceName \MSSQLServer\CurrentVersion Key: CurrentVersion
  2012	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS11.InstanceName\MSSQLServer\CurrentVersion Key: CurrentVersion HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS11.InstanceName \Setup Keys: PatchLevel , Version, Key Edition

  For more information about verifying Analysis Services build versions review Verify Analysis Services cumulative update build version.

SQL Server replication

Because replication agents may be installed on several different computers, it is important to check the installed versions on all affected computers.

For example, the Distribution Agent in Transactional or Peer-to-Peer replication may exist on computers that differ from the publisher instance of SQL Server and may exist on the various subscriber instances of SQL Server in a pull subscription.

If you use Web Synchronization for Merge Replication, the IIS web server may not be the same computer as the computer that is running SQL Server. Therefore, you have replication agent files that are installed on the IIS web server. And you may have to check the version of those .dll files in the IIS virtual directory and update them explicitly to obtain the latest service packs, cumulative updates, and hotfixes for your web agents.

For more information, see Upgrade or patch replicated databases.

Full-text search

Full-text search components include the following:

• Sqlserver.exe
• Sql_fulltext_keyfile.dll
• Iftsph.dll
• Fd.dll
• Fdhost.exe
• Fdlauncher.exe

Except for Sqlservr.exe, these components may not be updated with each cumulative update or service pack for the respective SQL Server product. The versions of these files will change only when there is a fix to the respective component. Generally, you can check the file version of each of these .dll files. The highest version in the list is the version of the full-text search component that is installed on the system.

You can use one of the following methods to determine the version of the full-text search component that is installed on your system.

• Method 1: Check the version of SQL Server Full-Text Key (Sql_fulltext_keyfile.dll) in the SQL Server 2008 R2 or SQL Server 2008 installation folder. Typically, for SQL Server 2008 R2, this file is located in the following folder:

  %ProgramFiles%\Microsoft SQL Server\MSQL10_50.\<Instance Name>\MSSQL

  For SQL Server 2008, this file typically is located in the following folder:

  %ProgramFiles%\Microsoft SQL Server\MSQL10.\<Instance Name>\MSSQL

• Method 2: Check the following registry subkey:

  HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft sql server\Mssql10_50.instname\Setup\SQL_FULLTEXT_ADV

  An example entry at this registry subkey is the following:

  featurelist: SQL_FullText_Adv=3 SQL_FullText_CNI=3
  ProductCode: {9DFA5914-C275-42E0-810E-C88E46A7F9EA}
  Patchlevel: 10.50.1765.0
  Version: 10.50.1600.1
  

  In this example entry, the third line (Patchlevel) indicates the current build of full-text search component that is installed, and the fourth line (Version) usually shows the original version of full-text search that is installed. In this case, it is SQL Server 2008 R2.

• Method 3: Use the Summary.txt file that is created during setup. For SQL Server 2008 R2 and later versions, this file is located in the following folder:

  %ProgramFiles%\Microsoft SQL Server\<nnn>\Setup Bootstrap\LOG\Summary.txt

  For values of <nnn> that correlate to your version review File Locations for Default and Named Instances of SQL Server.

  For SQL Server 2008, this file is located in the following folder:

  %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\LOG\Summary.txt

SQL Server Master Data Services (MDS)

The MDS Configuration Manager does not show the currently installed version number directly.

Be aware that MDS has a unique versioning scenario in which the SQL Server database engine installation does not necessarily match the MDS version. The version may vary when you compare the SQL Server installation to the binaries deployed in the MDS website and the MDS catalog schema version. Manual steps that use the MDS Configuration Manager tool are required to update and to upgrade the MDS websites and database schemas. You can refer to the following blog post on hotfix and service pack update methodology for MDS: Downloading and Installing SQL Server 2008 R2 Master Data Services (MDS) Cumulative Updates

The following registry subkey shows the binary versions that are installed on the SQL Server. However, this version does not necessarily match the website and database schema version until the MDS upgrade process is complete.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Master Data Services 10.5\CurrentVersion

You can check the installed product version and schema version by using the following query in the MDS catalog:

select * from mds.mdm.tblSystem

SQL Server Native Client

To determine the version of SQL Server Native Client, use one of the following methods:

• Method 1: On the system where you want to find the version of Native Client, start the ODBC Administrator (odbcad32.exe), and then check the Version column under the Drivers tab.

• Method 2: Check the following PatchLevel or Version keys at the following registry locations.

  SQL version / SQL Server Native Client version	Registry subkeys
  SQL Server 2012, SQL Server 2014 and SQL Server 2016/ SQL Server Native Client 11.0	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLNCLI11\CurrentVersion
  SQL Server 2008 & SQL Server 2008 R2/ SQL Server Native Client 10	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLNCLI10\CurrentVersion
  SQL Server 2005/ SQL Server Native Client 9	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Native Client\CurrentVersion

SQL Server Browser

The browser version should match the highest version of the SQL Server Database Engine and of the instances of Analysis Services that are installed on the computer.

SQL Server Writer

To determine the version of SQL Server Writer, check the following registry subkey value:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SqlWriter\CurrentVersion Keys: PatchLevel or Version

Microsoft .NET Framework

To determine the version of .NET Framework on your system, see Determine which versions and service pack levels of .NET Framework are installed.

For more information, see Understanding the .NET Framework requirements for various versions of SQL Server.

SQL Azure

To find the version of your instance of SQL Azure and related information, see the following topic in Books Online: SERVERPROPERTY (Transact-SQL).

SQL Server CE

To find the version of your instance of SQL Server CE and related information, see SQL Server CE previous versions documentation.

PolyBase

PolyBase for SQL Server on Windows

To find the version of PolyBase and its related features in Windows, try the following methods:

• If the PolyBase service is running, run the following PowerShell script:

Get-Process mpdwsvc -FileVersionInfo | Format-Table -AutoSize

• If the PolyBase service is not running or can't be started, run the following PowerShell script:

cd 'C:\Program Files\Microsoft SQL Server'
ls mpdwsvc.exe -r -ea silentlycontinue | % versioninfo | Format-Table -AutoSize

PolyBase for SQL Server on Linux

To find the version of PolyBase installed and its related features in Ubuntu, try the following methods:

apt list mssql-server-polybase
apt list mssql-server-polybase-hadoop

To find the version of PolyBase installed and its related features in RHEL, try the following methods:

yum info mssql-server-polybase
yum info mssql-server-polybase-hadoop

yum list installed *polybase*

Windows or Linux

Alternatively, try the SQL Server Setup steps in this next section. To find the version of PolyBase and its related features, refer to a fresh discovery report that runs within the SQL Server Setup tools.

In Windows or Linux, find the installation folder \Setup Bootstrap\Log. The Summary.txt file shows a discovery report of all features and versions. However, if the most recent setup action was to add PolyBase to an existing SQL Server instance, the Summary.txt file will not contain the PolyBase feature. This is because the discovery report will have run before the PolyBase feature was added.

We recommend that you refresh the Summary.txt report by running the features discovery report from SQL Server Setup. For more information, see Validate a SQL Server Installation.

Machine Learning services

For Windows servers, refer to the CAB file versions which change with SQL Server cumulative updates. Refer to the Rlauncher.config or PythonLauncher.config files in the Program Files\Microsoft SQL Server\MSSQL.nn\MSSQL\Binn directory to find the RHOME or PYTHONHOME folder locations of the CAB files. For the CAB versions that are included with SQL Server CU versions, see CAB downloads for offline installation of cumulative updates for SQL Server Machine Learning Services.

For Linux servers, the following command returns a list of all mssql-specific installed packages, together with their version numbers:

apt-get list --installed | --grep mssql

The version number of the mssql-server-extensibility package version is the SQL Server version of the Machine Learning Services feature.

The version number of the mssql-mlservices-packages-r or mssql-mlservices-packages-py refers to each language package file. For more information, see Install SQL Server Machine Learning Services on Linux (Offline installation).

Frequently asked questions

Q1: How do you determine the version of SQL Server when SQL Server is not running?

A1: You can determine the version of SQL Server by using either Method 2 or Method 5 (for SQL Server 2008 and later versions) in the Determine which version and edition of SQL Server Database Engine is running] section of this article.

Q2: How do I map the product versions to product names?

A2: You can use the following table as a reference.

Frequently used terms and acronyms

Cumulative update (CU): A roll-up update that contains all previous critical on-demand hotfixes to date. Additionally, a CU contains fixes for issues that meet the hotfix acceptance criteria. These criteria may include the availability of a workaround, the effect on the customer, the reproducibility of the problem, the complexity of the code that must be changed, and other topics.

Hotfix: A single, cumulative package that includes one or more files that are used to address a problem in a product and are cumulative at the binary and file level. A hotfix addresses a specific customer situation and may not be distributed outside the customer's organization.

RTM: Usually means "release to manufacturing". In the context of a product such as SQL Server, it indicates that no service packs or hotfixes were applied to the product.

RTW: Usually means "release to web". It indicates a package that was released to the web and made available to customers for downloading.

Service pack: A tested, cumulative set of all hotfixes, security updates, critical updates, and updates. Service packs may also contain additional fixes for problems that are found internally since the release of the product and a limited number of customer-requested design changes or features.

For more information, go to the following websites:

• Announcing the Modern Servicing Model for SQL Server

• Naming schema and Fix area descriptions for SQL Server software update packages

• Description of the standard terminology that is used to describe Microsoft software updates

• Description of the standard terminology that is used to describe Microsoft software updates

• An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

• Software release life cycle

• SQL Server documentation

• SQL Server product information center

• SQL Server release blogs

SQL Server complete version list tables

SQL Server 2019

SQL Server 2017

SQL Server 2016

SQL Server 2014

SQL Server 2012

SQL Server 2008 R2

SQL Server 2008

SQL Server 2005

SQL Server 2000

Older versions of SQL Server

SQL Server 7.0

Use the version number in the following table to identify the product or service pack level.

SQL Server 6.5

Use the version number in the following table to identify the product or service pack level.