A popular RDBMS (Relational Database Management System) by Microsoft, SQL is a full-featured data management system that supports ANSI SQL, the standard language of SQL and has many features including support for XML datatype, DMVs (dynamic management view), text-search capability and database mirroring.
It is used for creating and maintaining databases, analyzing data via SQL Server Analysis Services (SSAS), generating reports via SQL Server Reporting Services (SSRS) and performing ETL operations via SQL Server Integration Services (SSIS).
Which version of SQL editions organizations, Windows web hosting providers or individuals use, depend upon the requirements of the user. Based on that, there are various Microsoft SQL Server Editions available as per the user’s requirements:
Server Editions: SQL
|Enterprise:||The operational needs of a large-scale organization are complex and it requires good technical support for database management. Hence, the Enterprise edition of SQL is built to support web enabled databases and data warehousing. The features include:
|Standard:||This edition serves the small and medium sized organizations. It supports complete server needs of any small or growing business. It also includes support for data warehousing and e-commerce.|
|Web:||The Web editions of SQL server are designed specifically to meet the needs of Value Added Providers (VAPs) and web hosts. It is built to lower the total cost-of-ownership for such organizations. Thus, it provides scalable and affordable capabilities to large or small scale web properties.|
|Developer:||The 2016 Developer edition of SQL server gives a new capability to the developers. They can build or create any type of application on top of the SQL server. It includes the characteristics of an Enterprise Edition, but it is solely licensed for developers to be used as a test system.|
|Express:||One of the basic and limited editions of SQL, it is good for beginners with less needs, as it has minimal features and supports. The number of users and database size is limited, thus supporting only fixed parameters. Features include:
As an individual user, how do your check which SQL server you are using. We will try to answer it through this write-up.
How to check SQL server version?
To know, which SQL version you are using, you have to follow some methods.
#By using Object Explorer
The user should connect to the server with the help of Object Explorer. It is found in the SQL Server Management Studio. Once it gets connected, you can see the version detail in parentheses, along with the username which is used during connection to a specific instance.
Alternatively, the user can view the server version through the error log file. It, by default, is located at Program files > Microsoft SQL server > MSSQL.n > MSSQL > LOG > ERRORLOG and ERRORLOG.n.
It will show all the required details like server version, edition and OS version on which the SQL server is running.
#Connect to SQL instance
The user can connect to the SQL instance and run the query to determine SQL server version.
It will look like this:
#Run Query in SSMS
The user can run query in SQL Server Management Studio (SSMS):
SELECT SERVERPROPERTY (‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition ‘)
Please note that this query works with instances of SQL Server 2000 or later versions. By running this query, you will get the product version, level, and edition of the server.
# SQL Server Features Discovery report
You can view the server details through the SQL Server Features Discovery report which is installed on SQL server 2008 and later versions. The report will give you the complete information of the locally installed server.
How to determine the server versions of SQL components?
Here, we will be discussing about some of the major SQL components, and how users can find the servers’ versions connected to that particular component.
#Determining version of SQL server client tools
You can find the version of the client tools installed on your SQL server very easily. Just start Management Studio, click on the About section on the Help menu.
#Determining version of SQL server integration
To determine the Microsoft SQL server integration services, you can use the following methods.
Here, you first need to open the Server Management Studio. Click on “Integration Services” from the “Server Type” list in “Connect to Server” dialog box. Now you should fill the SSIS server name in the Server name box and click Connect. In the root node, you will find the version number.
It will appear in the following format:
Server_Name (Integration Services Version_No – Domain_Name\User_Name)
You can view the version number in the MsDtsSrvr.exe file. By following this path – %programfiles%\Microsoft SQL Server\90\DTS\Binn\ you will find the required file. By right-clicking on the Properties, you can select the Version tab and view the number.
Apart from the above two methods, you can also use “The registry” and “Application Log” to view the version number.
#Determining the Version of SQL server Analysis Services
Use one of the following routes to determine the version of the analysis service.
Connect to the server by using the Object Explorer found in the Management Studio of SQL server. You will find the version number in parentheses along with the user name used to connect to the server.
In the bin folder of the Analysis services, click on the Msmdsrv.exe file. Click on properties and select the version tab.
You can also use the registry keys to view the version detail.
By determining the SQL version of Windows dedicated server, the user can analyze the update level required for that version.
Our technical experts are always here to help you in need. Feel free to contact us through the comments section below, and our experts would be more than happy to answer your queries.
Priya an ambivert by nature, believes in giving shape to her ideas through her write ups. She is an intellectual person who loves exploring and researching about new things. In her free times she loves reading novels along with some soft music.