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. 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
I will be covering end to end SCCM DB migration topic in this post. For detailed steps on how to install a SQL Server 2017 and the industry best practice is described in my previous post.
This is a continuation of my series of the post The Complete Guide for SCCM Server Migration.
- The Complete Guide for SCCM Server Migration Part 1 – SQL 2017 -SCCM SQL Server 2017 Installation and best practices
- The Complete Guide for SCCM Server Migration Part 2 – Database Migration
- The Complete Guide for SCCM Server Migration Part 3 – WSUS Server Migration
- 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
- Reboot the Primary Site and new/old SQL Server to avoid any pending reboot instances.
- Capture the logins from old SQL server by running the script and store 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 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 Back up.
b. Select the backup destination on Disk and add the backup the location to save the file. Mention the file name with .bak extension.
c. Select 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 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 Primary site server as a member of Local Admin group of new SQL Server. It should be a direct member and not a member of any security group. This is must otherwise, DB move will fail.
7. Restore Database on 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 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 old SQL server on the new one.
d. Select OK, to initiate 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
- 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
- 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.
- Verify the sitecomp.log for bootstrap operation post DB move. make sure you don’t reboot the server if the bootstrap operations are continuing.
- Verify the logs on site systems connecting to the database like MP, WSUS etc.
- Create a test package and distribute it to the test distribution points
- Plan to move the Reporting services from the old SQL server to new SQL Server along with the custom reports.
- Plan for moving the WSUS database from old server to new SQL server.