Rajendra Gupta
different SQL Server editions

Upgrading to a new SQL Server edition

September 9, 2019 by

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.

different SQL Server editions

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

    SQL Server 2017 pricing difference

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.

Enabled 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

Edition Upgrade Wizard in SQL Server set up

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 🙂

Product key wizard

In the next page, you get the Microsoft software license terms. Accept the license terms and move to the next page.

Accept the license terms

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.

Edition upgrade rules

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

Select the instance from the drop-down

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.

Verify feature rules

Click on Warning and it displays the following message.

Warning 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.

Ready to upgrade editions

Once you click Upgrade, it greys out the upgrade option.

CLick on Upgrade to start edition upgrade

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.

Success message after edition upgrade

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.

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.

Verify the SQL Server edition

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
2,518 Views