0

I'm being asked to downgrade some non critical database environments from Enterprise Edition 2012 to Standard Edition 2012. Due to ongoing licensing costs.

Can I do this? What are the drawbacks?

Is there a process similar to upgrading I can do? Can I just run the standard edition install as an upgrade?

We currently don't have development environment for this databases.

1
  • You Can downgrade but you shall not find out some enterprise feature like alwaysOn Availability group,replication etc. Before downgrade Or downgrade take the backup of all databases(user,system also). I upgradation again you have to take backup of databases and install the same version of sql server enterprise and restore over there. Commented Aug 15, 2016 at 13:38

2 Answers 2

1

You can not perform a downgrade in the same way SQL Server allows you to do an in-place upgrade, so you're stuck with having to manually move your database(s) between two installs. If you've got a named instance, you'll need to back it up, perform an uninstall of the Enterprise edition, and a fresh install of Standard - restoring the databases thereafter.

Of course that's a basic theory, and it's not always that simple.

Are you using any Enterprise specific features, such as partitioning, snapshots, some aspects of Always On Availability Groups, Resource / IO governance, compression...?

You can see the full feature list by edition here here

You can also run the following query to check for any version specific features you are currently using:

SELECT * FROM sys.dm_db_persisted_sku_features

I've tackled this in the past by doing the following:

First, before doing anything, get a quick configuration report. You'll want to put everything back 'as is' when you configure your new Standard installation. Here's a useful script I use to get some key configuration settings - feel free to amend:

SELECT sysDB.database_id
    ,sysDB.NAME AS 'Database Name'
    ,syslogin.NAME AS 'DB Owner'
    ,sysDB.*
FROM sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

SELECT *
FROM sys.dm_server_services

DECLARE @DefaultData NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'DefaultData'
    ,@DefaultData OUTPUT

DECLARE @DefaultLog NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'DefaultLog'
    ,@DefaultLog OUTPUT

DECLARE @DefaultBackup NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'BackupDirectory'
    ,@DefaultBackup OUTPUT

DECLARE @MasterData NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
    ,N'SqlArg0'
    ,@MasterData OUTPUT

SELECT @MasterData = substring(@MasterData, 3, 255)

SELECT @MasterData = substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))

DECLARE @MasterLog NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
    ,N'SqlArg2'
    ,@MasterLog OUTPUT

SELECT @MasterLog = substring(@MasterLog, 3, 255)

SELECT @MasterLog = substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))

SELECT isnull(@DefaultData, @MasterData) DefaultData
    ,isnull(@DefaultLog, @MasterLog) DefaultLog
    ,isnull(@DefaultBackup, @MasterLog) DefaultBackup

Next, backup all user databases.

Stop the services for the Enterprise edition instance.

Copy the master, model and msdb data and log files to a secure location. You'll need these later so you don't have to recreate all the jobs and logins.

Uninstall Enterprise Edition.

Install standard edition and apply the appropriate SP's and CU's.

Restore the user databases.

Stop the services.

Copy and replace the system database files you copied earlier.

Restart the services.

2

I do not believe there is a "downgrade path" that's officially supported for what you want to do. Nothing that is full proof or baked in, in other words. I would take full backups of all databases, then uninstall and reinstall (also reinstall required SPs). Microsoft's supported upgrade paths: https://msdn.microsoft.com/en-us/library/ms143393.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.