SQL Server 2012 – Downgrading Enterprise to Standard Edition

sql-server-2012

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.

Best Answer

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.

Related Question