One of a unique advantage of freelancing is learning more and more. I get a chance to hear some of the real word problem and it gives me immense pleasure to provide a feasible solution to them. In this blog post we will discuss How to Downgrade SQL Server Edition?
As the subject says, I was asked by a client to provide steps to change the edition of SQL Server. By mistake their vendor has installed SQL Server Enterprise Edition with his own copy. During an internal audit, it was identified that they are not having license of Enterprise so they should change the edition to Standard.
Keep in mind, we are not talking about version change (i.e. SQL 2016 to SQL 2014 etc.), we are talking about edition (standard, developer, enterprise). So, changing Edition of SQL Server is possible, but the steps would depend on the source and destination edition.
- Upgrade path supported by SQL Server: (Supported Version and Edition Upgrades)
- Upgrade path not supported by SQL Server (downgrade): whatever doesn’t fall in an upgrade matrix on above link would come here.
Here are the simple steps which I suggested to my client. (Enterprise to Standard)
- Make sure you are not using any feature which is not there in destination edition. This can be done by using catalog view dm_db_persisted_sku_fearures. This should be run on each database to find features.
- Backup ALL the databases before uninstall. Stop the SQL services and backup your database files and stored it in a safer place.
- Note down the current configuration of the database server. Keep track of the installation folder and note down the exact install location. Also make a note of select @@version, sp_configure.
- Uninstall the SQL server enterprise edition and install the standard edition.
- Apply patches, if needed, to come to same @@version as early.
- Backup the databases of new instance. Stop the SQL service of the new instance and copy the database files for safety.
- Restore the database backups or stop the SQL server and replace the files from the old instance to the new instance.
- Start the SQL services to check if everything is fine.
Have you ever down similar downgrade? Did you face any issue when downgrading SQL Server?
Reference: Pinal Dave (https://blog.sqlauthority.com)
that is sys.dm_db_persisted_sku_features; not dm_db_persisted_sku_fearures
Are you saying backup of current databases (on sql 2016) and restore on down version (sql 2014)? Do you think it’s gonna work?