This article gives an overview of different editions in SQL Server and also explains the process to upgrade SQL Server editions.
Introduction
It is a usual activity for DBA to install SQL Server. You should collect the requirement and plan accordingly build new SQL Server instance. Suppose we want to install a standalone SQL Server instance. We require the following essential information.
- SQL Server version, Service pack and cumulative packs (2012/2014/2016/2017)
- SQL Server edition ( Enterprise/Standard/Developer/Express)
- Default or named instance. In the case of named instance, name of the instance.
- Authentication method and Collation setting
- Administrators group users
- Required features to install
- Service account for SQL Server services
- Location for SQL Server binaries, user databases( data file and log file), tempdb location
Microsoft provides various editions for SQL Server. It is a vital aspect to choose which edition we should install.
Overview of different SQL Server editions
For example, you can choose the available SQL Server 2017 editions from Microsoft SQL Server 2017 Editions page.
Enterprise edition
Enterprise SQL Server Edition contains all supported feature, in particular, SQL Server version. It is suitable for mission-critical databases with advanced features. It is recommended to use in the production environment. We can use unlimited CPU and memory in this edition; however, it should be supported by underlying operating system.
Standard edition
This edition is a light version of the enterprise edition and suitable for not using the advanced feature of SQL Server. For example, we can use 24 cores with 128 GB max memory in a standard edition. We can easily upgrade SQL Server Standard to enterprise edition, and it does not require any code-level changes.
Express edition
It is the simplest version of SQL Server and suitable as an entry-level database. It only supports basic features of SQL Server and having very limited compute capabilities as well. We can use only 4 cores, 1410 MB RAM along with 10 GB maximum database size. It does not provide SQL Server functionalities such as SQL Server Agent, Database mail,
Developer edition
SQL Server developer edition provides all features available in the Enterprise edition of SQL Server. You can download developer edition for free and use it for build, test and use features of enterprise edition in a non-prod environment. We can also upgrade developer edition to enterprise edition.
Web edition
We can consider it edition in between the standard and express edition. It has very limited compute and feature availability and suitable for small web site hosting.
Factors in choosing edition in SQL Server
We should consider the following points while choosing the required SQL Server Edition.
- Environment (Production, development, UAT, Test)
- Features required. You should know what features you required to use in SQL Server and whether these features are available in the required edition or not
-
Pricing: You should review Microsoft SQL Server Licencing and pricing. In a big organization, usually DB architect plays this role. However, you should be aware of these. In the following screenshot from Microsoft SQL Server 2017 pricing, you can see the considerable pricing difference between enterprise and standard editions
The following list shows all possible SQL Server Edition upgrade paths. You can refer Microsoft docs for a full list of supported edition upgrades.
- Evaluation Enterprise edtion to enterprise, standard and developer edition
- Standard edition to enterprise edition
- Developer edition to Enterprise, standard edition
- Express edition to enterprise, standard and developer edition
You should plan the required edition before installing SQL Server. Suppose you installed standard edition, but later due to organization license policy, you require enterprise edition. We can use the Edition Upgrade wizard to change SQL Server edition without any change in the T-SQL, features.
In the available SQL Server edition list, we can see that SQL Server development contains all features of SQL Server enterprise edition. Let’s say you installed developer edition and later you want to change it to the standard edition. You can do this change, but if you are using the features of enterprise edition that are not available in standard edition, you need to review all those features and capabilities.
You can use the dynamic management view sys.dm_db_persisted_sku_features to check the edition specific feature that is enabled on the connected instance. You need to run it on an individual database to check database level feature. For example, I executed the following query, and it returns the following value in another instance of SQL Server for me.
It shows ColumnStoreIndex feature is enabled on the database.
|
1 2 |
SELECT * FROM sys.dm_db_persisted_sku_features; |
In my developer SQL Server Editioninstance, it does not return any output row. It shows that we are not using any enterprise-level feature. We can change this developer edition to standard edition without any issues.
Edition Upgrade Wizard in SQL Server set up
Let’s try to change the edition from developer to standard edition.
- Open SQL Server installation media and launch the setup.exe. It opens the SQL Server installation centre, and you get options in the left-hand side such as Planning, installation, Maintenance, Tools, Resources
- Click on the Maintenance and in the top, and you can see Edition Upgrade. In the description, you can see that it launches a wizard to change your edition of SQL Server
In the upgrade edition wizard, you can specify the product key of SQL Server Standard edition. I greyed it out to hide my product key 🙂
In the next page, you get the Microsoft software license terms. Accept the license terms and move to the next page.
In the next page, it checks for edition upgrade rules. You can see the status of the rule as Passed or Failed. You should have rules status passed to proceed with edition upgrade.
Specify the instance of SQL Server from the drop-down. We want to upgrade the default instance, so it shows MSSQLSERVER.
You can see the edition is Developer and version is 13.1.4474.0
It checks for feature rules of upgrade edition wizard. In the following screenshot, we can see a warning that shows SQL Server 2016 edition downgrade.
Click on Warning and it displays the following message.
You get a message that selected edition downgrade path is not valid. We cannot change the developer edition to target standard edition, as it is not a supported path. We already checked in the Microsoft docs that it is a supported path.
As highlighted earlier, we cannot change SQL Server Edition from developer to standard edition if we are using an enterprise-level feature in connected SQL Server instance. We already verified that no such feature is in use for my instance. Click on Ok and move to next page.
We are now ready to upgrade edition. Verify the summary of SQL Server edition. We can also note down that configuration file path.
Click on Upgrade as highlighted in the following image.
Once you click Upgrade, it greys out the upgrade option.
We do not get any progress bar. It stays in the stage for a few minutes and later, you get the following successful edition upgrade message. You can also go to summary logfile from the link mentioned on the page.
Although it does not asks you to reboot Server hosting SQL Server instance. I would recommend you to do so.
Once a server is up, connect to SQL Server instance in SSMS and execute the following query. It uses SERVERPROPERTY function to get product version, edition and product level.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @Version NVARCHAR(128) SET @Version = CONVERT(NVARCHAR(128),SERVERPROPERTY ('ProductVersion')) SELECT CASE WHEN @Version like '11%' THEN 'SQL SERVER 2012' WHEN @Version like '12%' THEN 'SQL SERVER 2014' WHEN @Version like '13%' THEN 'SQL SERVER 2016' ELSE 'Unknown' END AS 'Sql Server Version Name', SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductLevel') AS ProductLevel |
You can see that SQL Server Edition changed from the developer to standard. You can also verify that product version is similar after the upgrade as well. We did not perform any service pack or Cumulative pack upgrade. Therefore, the product version is similar in developer edition as well.
You can follow the same method to perform other supported SQL Server Edition upgrade paths. If you are doing it in a production environment, I would recommend taking a backup of all databases, logins, jobs before doing it. The edition upgrade might not create any issues, but it is better to take precautions keeping production system in mind.
Conclusion
In this article, we explored different SQL Server editions and fundamental differences in these. We also used an edition upgrade wizard to upgrade SQL Server edition. We cannot use this wizard to downgrade SQL Server edition such as enterprise to standard.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023