Downgrading SQL from Enterprise Edition to Standard for ConfigMgr

No Gravatar

At a recent client engagement we discovered that Microsoft SQL Server Enterprise edition was installed on the ConfigMgr Primary Site Server.  Technically this is not a problem, but it is only needed if you expect to have more than 50,000 clients*.  As this environment wasn’t anywhere close to the limit so there was no need to pay the extra licensing cost of Enterprise edition (Standard edition comes with the ConfigMgr licenses).

 

These steps are largely based on the Jonathan Kehayias approach as documented by Brady Upton at MSSQLTips.

Steps for SQL Enterprise to Standard downgrade

  • On each database verify that no Enterprise features are utilized (SELECT * FROM sys.dm_db_persisted_sku_features)

select ‘Master’ as [Database], * from [master].[sys].[dm_db_persisted_sku_features]

select ‘Model’ as [Database], * from Model.[sys].[dm_db_persisted_sku_features]

select ‘msdb’ as [Database], * from msdb.[sys].[dm_db_persisted_sku_features]

select ‘tempdb’ as [Database], * from tempdb.[sys].[dm_db_persisted_sku_features]

select ‘CM_P01’ as [Database], * from CM_P01.[sys].[dm_db_persisted_sku_features]

select ‘SUSDB’ as [Database], * from SUSDB.[sys].[dm_db_persisted_sku_features]

select ‘ReportServer’ as [Database], * from ReportServer.[sys].[dm_db_persisted_sku_features]

select ‘ReportServerTempDB’ as [Database], * from ReportServerTempDB.[sys].[dm_db_persisted_sku_features]

  • Document databases, security, maintenance plans, and jobs
  • Verify the SQL version number and ensure install files are available (SELECT @@VERSION)
  • Stop and disable backup software
  • Stop ConfigMgr, IIS, and Windows Update services (set to disabled if desired)
  • Backup databases (system and user)
  • Stop SQL services
  • Copy the master, model and msdb database files (.mdf and .ldf) to another location
  • Uninstall SQL Enterprise instance (all features)
    • The Shared tools do not have to be uninstalled; however, if they are not then reporting the SQL edition in the future will be confusing
  • Reboot
  • Install new SQL Standard instance as required by ConfigMgr being sure to keep the same instance name and file/folder paths.
    • Review the Required and Optional configurations for SQL server (64-bit, SQL_Latin1_General_CP1_CI_AS, Database Engine, Windows Authentication, min/max Memory, nested triggers, CLR integration, static TCP ports, etc.)
    • If the original SQL ConfigurationFile.ini is still around, installing based on this file can make all of the configurations fool proof.
  • Patch SQL to the same version as before
  • Verify the SQL version and edition (SELECT @@VERSION)
  • Stop SQL Server and copy/restore the system databases
  • Configure Trace flags (see section below)
  • Start SQL server and verify databases, security, and jobs are as before
    • If login fails, use PSEXEC to start SQL Management Studio as the SYSTEM account, then recreate any SQL Logins needed
  • Enable common language runtime (CLR) integration (sp_configure ‘clr enabled’,1; reconfigure)
  • Enable and start IIS, and Windows Update services… verify WSUS is working
  • Enable and start ConfigMgr services
  • Verify event Viewer and ConfigMgr logs and monitoring to ensure ConfigMgr is healthy
  • Re-enable and start backup software

 

 

SQL Trace Logs

Using this method is simple and easy, but there is one additional thing to keep in mind… SQL Trace flags (thanks Allen for pointing this out).  When installing SQL, trace flags are not enabled / added by default; this is taken care of by the ConfigMgr installation.  Since we are not doing a ConfigMgr installation or a site reset, etc. these options need to be added manually.

  • Open SQL Server Configuration Manager
  • Navigate to SQL Server Services -> SQL Server… -> Properties
  • Add "-T8295"
  • Add "-T4199"
  • Apply, stand on one foot, OK, Close

image

Executing DBCC TRACEON (4199,-1) and DBCC TRACEON (8295,-1) in SQL Server Management Studio will enable these flags as seen by executing DBCC TRACESTATUS (-1).  However, this only affects the current session and they need to be added as startup flags.

 

SQL and ConfigMgr References

Additional / Related References

April 20, 2016

Posted In: Uncategorized