Upgrading SQL 2008 R2 ConfigMgr Sites

Author by Matt Herman

The new quarterly release schedule for System Center Configuration Manager (ConfigMgr) and the upcoming release of SQL 2016 means the end is in sight for support for ConfigMgr databases on SQL 2008 R2.  In a previous video, I covered upgrading the OS to Server 2012 R2.  In this video, I cover how to do the SQL portion of the upgrade. 

SQL Server Upgrade for Configuration Manager Sites from Concurrency on Vimeo.

High Level Steps:

  1. Health Check on Site
  2. Disable Services
  3. Reboot
  4. Database Maintenance Script
  5. Copy SQL Install Locally
  6. Install SQL ScriptDom
    1. https://www.Microsoft.com/en-us/download/details.aspx?id=42295
  7. Install Upgrade Advisor
  8. Upgrade Wizard
  9. Bring DBs Online
  10. Change Compatibility
  11. Enable Services
  12. Reboot
  13. Health Check on Site
  14. Reinstall Reporting Services Role

 

SQL Maintenance Script Developed with Jes Borland

Note: This is a different script than I used in the video.  I've added the commands to change the compatbility level of the databases.

 

/* Run Before Upgrading SQL */
 
----------------------------------------------------
/* Source */
/* Set DB single user */
ALTER DATABASE CM_CM0
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
/* Back up DB */
BACKUP DATABASE CM_CM0
TO DISK = 'D:\SQLBackup\CM_CM0.bak'
WITH COMPRESSION;
 
/* Set DB offline */
ALTER DATABASE CM_CM0
SET OFFLINE;
 

----------------------------------------------------
/* Back up DB  */
BACKUP DATABASE ReportServer
TO DISK = 'D:\SQLBackup\ReportServer.bak'
WITH COMPRESSION;
 

----------------------------------------------------
/* Back up DB to  */
BACKUP DATABASE ReportServerTempDB
TO DISK = 'D:\SQLBackup\ReportServerTempDB.bak'
WITH COMPRESSION;
 

----------------------------------------------------
/* Source */
/* Set DB single user */
ALTER DATABASE SUSDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
/* Back up DB */
BACKUP DATABASE SUSDB
TO DISK = 'D:\SQLBackup\SUSDB.bak'
WITH COMPRESSION;
 
/* Set DB offline */
ALTER DATABASE SUSDB
SET OFFLINE;
 
/* End of before upgrade steps */
 
----------------------------------------------------------------
 
/* After Upgrade */
 
/* Set DB multi-user */
ALTER DATABASE CM_CM0
SET MULTI_USER;
 
/* Set DB Online */
ALTER DATABASE CM_CM0
SET ONLINE;
 
/* Upgrade compatibility level. */
-----------------------------------------------------------
ALTER DATABASE [CM_CM0] SET COMPATIBILITY_LEVEL = 120
GO
 
/* DBCC CHECKDB */
/* Always open a new query window and run this separately - it can take some time, depending on DB size */
DBCC CHECKDB ('CM_CM0');
 

/* Upgrade compatibility level. */
-----------------------------------------------------------
ALTER DATABASE [ReportServer] SET COMPATIBILITY_LEVEL = 120
GO
 
/* DBCC CHECKDB */
/* Always open a new query window and run this separately - it can take some time, depending on DB size */
DBCC CHECKDB ('ReportServer');
 

/* Upgrade compatibility level. */
-----------------------------------------------------------
ALTER DATABASE [ReportServerTempDB] SET COMPATIBILITY_LEVEL = 120
GO
 
/* DBCC CHECKDB */
/* Always open a new query window and run this separately - it can take some time, depending on DB size */
DBCC CHECKDB ('ReportServerTempDB');
 

/* Set DB multi-user */
ALTER DATABASE SUSDB
SET MULTI_USER;
 
/* Set DB Online */
ALTER DATABASE SUSDB
SET ONLINE;
 
/* Upgrade compatibility level. */
-----------------------------------------------------------
ALTER DATABASE [SUSDB] SET COMPATIBILITY_LEVEL = 120
GO
 
/* DBCC CHECKDB */
/* Always open a new query window and run this separately - it can take some time, depending on DB size */
DBCC CHECKDB ('SUSDB');
Author

Matt Herman

Technical Architect