SCCM SQL Server Database Migration | Part 2 | ConfigMgr | Step by Step

The site database server is one of the critical roles of SCCM architecture. SCCM site database is a server that runs a supported version of Microsoft SQL Server. SQL Server DB is the database used to store information for SCCM sites and clients. Let’s see SCCM SQL Server Database Migration.

Related Post – New ConfigMgr Primary Server Installation Step-by-Step Guide | SCCM

Each SCCM site in an SCCM hierarchy contains a site database and a server that is assigned the site database server role. To know more about the supported versions of SQL Server for SCCM, you may check here.

Introduction

I will be covering end-to-end SCCM DB migration topics in this post. Detailed steps on how to install a SQL Server 2017 and the industry best practices are described in my previous post.

This is a continuation of my series of the post The Complete Guide for SCCM Server Migration.

1. The Complete Guide for SCCM Server Migration Part 1 – SQL 2017  -SCCM SQL Server 2017 Installation and best practices
2. The Complete Guide for SCCM Server Migration Part 2 – Database Migration

1. The Complete Guide for SCCM Server Migration Part 3 – WSUS Server Migration
2. The Complete Guide for SCCM Server Migration Part 4 – Primary Server Migration – SCCM Site Restore without SCCM Backup

Content of this post

- Pre-requisite required for migrating the SCCM DB from old server to new SQL Server 2017
- Steps involved in Migrating the database
- Post SCCM DB Migration checks

Pre-requisite for Migrating SCCM Database

1. Reboot the Primary Site and new/old SQL Server to avoid any pending reboot instances.
2. Capture the logins from the old SQL server by running the script and storing the output in a notepad. The user should be having the appropriate permission on the SQL to perform these actions(sysadmin preferred).

;WITH Logins AS (
SELECT prn.name PrincipalName
 ,prn.default_database_name
 ,rol.name RoleName
 FROM sys.server_principals prn
 LEFT OUTER JOIN sys.server_role_members mem
 ON prn.principal_id = mem.member_principal_id
 LEFT OUTER JOIN sys.server_principals rol
 ON mem.role_principal_id = rol.principal_id
 WHERE prn.is_disabled = 0
 AND prn.type IN ('U','G')
 AND prn.name NOT LIKE @@SERVERNAME+N'%'
)
SELECT N'USE [master];' AS CommandsToKeepAndRun
UNION ALL
-- Logins
SELECT N'IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = N'''+PrincipalName+N''')
CREATE LOGIN ['+PrincipalName+N'] FROM WINDOWS WITH DEFAULT_DATABASE = ['+ISNULL(default_database_name,N'master')+N'];
'
 FROM Logins
UNION ALL
-- Server Roles
SELECT N'IF (
SELECT ISNULL(rol.name,N''NULL'')
 FROM sys.server_principals prn
 LEFT OUTER JOIN sys.server_role_members mem
 ON prn.principal_id = mem.member_principal_id
 LEFT OUTER JOIN sys.server_principals rol
 ON mem.role_principal_id = rol.principal_id
 WHERE prn.name = N'''+PrincipalName+N'''
) != N'''+RoleName+N'''
ALTER SERVER ROLE '+RoleName+N' ADD MEMBER ['+PrincipalName+N'];
'
 FROM Logins
 WHERE RoleName IS NOT NULL
UNION ALL
-- Server Permissions Extended:
SELECT CASE prm.class
 WHEN 100 THEN prm.state_desc+' '+prm.permission_name+' TO ['+pal.name+'];' COLLATE SQL_Latin1_General_CP1_CI_AS
 WHEN 105 THEN prm.state_desc+' '+prm.permission_name+' ON ENDPOINT :: ['+(SELECT name FROM sys.endpoints WHERE endpoint_id = prm.major_id)+'] TO ['+pal.name+'];' COLLATE SQL_Latin1_General_CP1_CI_AS
 END
 FROM sys.server_permissions prm
 INNER JOIN sys.server_principals pal
 ON prm.grantee_principal_id = pal.principal_id
 AND pal.is_disabled = 0
 AND pal.name NOT LIKE @@SERVERNAME+N'%'
 AND pal.type IN ('U','G')
UNION ALL
SELECT N'GO';
--Courtesy: Benjamin Reynolds--

• 3. Stop the SCCM services on the Primary site and take the DB backup to avoid any data loss.
• 4. Backup SCCM DB

a. Initiate the backup by right-clicking the SCCM database and selecting the tasks and then Backing up.

b. Select the backup destination on Disk and add the backup location to save the file. Mention the file name with the .bak extension.

c. Select the media option on the left and select the checkboxes for Verify backup when finished and Perform checksum before writing to media.

d. Select OK to start the backup and wait for the backup to complete. You may check the backup progress on the backup GUI or else run the below query to see the detailed progress.

select command 
 ,CAST(total_elapsed_time/1000.0/60.0 AS NUMERIC(8,2)) AS [Elapsed Min] 
 ,CAST(estimated_completion_time/1000.0/60.0 AS NUMERIC(8,2)) AS [ETA Min] 
 ,CAST(estimated_completion_time/1000.0/60.0/60.0 AS NUMERIC(8,2)) AS [ETA Hours]
 ,CAST(percent_complete AS NUMERIC(8,2)) AS [Percent Complete] 
from sys.dm_exec_requests 
where session_id = (select max(spid) from master.dbo.sysprocesses with (nolock) where cmd in ('RESTORE DATABASE','BACKUP DATABASE'))

• 5. Once the backup is completed, stop the SQL Services on old SQL Server and copy the backup to the new server.

6. Add the Primary site server as a member of the Local Admin group of the new SQL Server. It should be a direct member and not a member of any security group. This is a must otherwise, the DB move will fail.

7. Restore the Database on the new SQL Server. I am using a SQL server 2017 on Windows Server 2016.

• a. Initiate restore by right-clicking the databases and selecting Restore

b. Select the backup location as a device and add the backup location by navigating to the location where you have copied the backup from the old server to the new server.

c. Select the files tab and here you can see the previous file location and new file locations. There is no restriction that you have to use the same drive layout of the old SQL server on the new one.

d. Select OK, to initiate the restore process. Check the restore progress on Restore GUI or the script provided above in 4.d.

8. Enable CLR

EXECUTE sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;

• 9. Restore the logins by running the script which was received as the output from step 2
• 10. Reconfigure the restored database where XXX is your site code.

a. Enable the SQL Broker on the Site database

USE master; 
GO 
ALTER DATABASE CM_XXX SET ENABLE_BROKER 
GO

b.  SET the Site Database as trustworthy

USE master; 
GO 
ALTER DATABASE CM_XXX SET TRUSTWORTHY ON 
GO

c. SET the Database to honor the HONOR_BROKER_PRIORITY

USE master; 
GO 
ALTER DATABASE CM_XXX SET HONOR_BROKER_PRIORITY ON; 
GO

11. Verify the SQL Server configuration on the new server. Use the below query to check the database settings. Make sure is_trustworthy_on and is_broker_enabled settings is on for the SCCM database

select name, collation_name, user_access_desc, is_read_only, state_desc, 
is_trustworthy_on, is_broker_enabled, is_honor_broker_priority_on from sys.databases

12. Start the SCCM Services on Primary site and we are ready to move the SCCM database now with all pre-requisite tasks completed.

 Move the SCCM Database

1. On the primary site open the SCCM Setup Wizard from the server and proceed to The Getting started page.

2. On The Getting Started page, select Perform Site Maintenance or reset this site and click next.

3. On the Site Maintenance window, select Modify SQL Server Configuration and select Next.

4. On Database Information Window, provide the new SQL Server FQDN and select Next.

5. On Configuration windows, you can view the setup progress and also the detailed information can be visible in ConfigMgrSetup.log.

Post SCCM DB move Checks

1. If you have moved the SQL server of a primary site in a multi-tiered hierarchy then the site will be in maintenance mode (Recovering Deltas). The site will re-initialize the replication groups which needs to be updated. So you need to monitor the site replication closely.
2. Verify the sitecomp.log for bootstrap operation post DB move. make sure you don’t reboot the server if the bootstrap operations are continuing.
3. Verify the logs on site systems connecting to the database like MP, WSUS etc.
4. Create a test package and distribute it to the test distribution points
5. Plan to move the Reporting services from the old SQL server to new SQL Server along with the custom reports.
6. Plan for moving the WSUS database from old server to new SQL server.