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.

Patch My PC

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.

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

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

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

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

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

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.

Migrate sccm db
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.

Migrate sccm db

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
Migrate sccm db

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.

Migrate sccm db
Migrate sccm db

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.

Migrate sccm db

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

Migrate sccm db

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
Migrate sccm db

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.
Migrate sccm db

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

Migrate sccm db

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

Migrate sccm db

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

Migrate sccm db

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

Migrate sccm db

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.

45 thoughts on “SCCM SQL Server Database Migration | Part 2 | ConfigMgr | Step by Step”

    • Once the SQL DB is moved, you need to install the reporting service role on new SQL Site System Server and export the custom reports from old server to import on the new one.

      Reply
  1. Hey,

    I’m trying to migrate a 2008 RE backup (10.00.1600) onto a 2017 version (14.00.1000), however, once I try to redote the backup I get the following error message:

    System.Data.SqlClient.SqlError: The backup of the system database on the device (FILEPATH)\master.bak cannot be restored because it was created by a different version of the server (10.00.1600) than this server (14.00.1000). (Microsoft.SqlServer.SmoExtended)

    Do you have any idea what’s going on? Thank you.

    Reply
  2. What would be your recommendation for the Stanalone SCCM infra?
    Our SCCM is is installed on the Windows 2012 R2 STD 64-bit and the DB in the same box SQL Server 2012 Standart Edition (SP4) which just entered the extended support.

    Recently we upgraded the ConfigMgr to CB 1806 and now planning to upgrade the ConfigMgr DB.

    What would be the recommended version to upgrade without moving the database to a new server?

    Many thanks in advance!

    Reply
    • So if you are asking for the SQL Server upgrade then you can go for SQL Server 2016 SP2. But again the OS on the server is still Server 2012 which has moved to the extended support. So better is to upgrade OS and SQL in a phased manner.

      Reply
  3. I have migrated the DB to a new SQL server, however, when i launch SCCM, i notice that no packages are available, no boot images, no driver packages, no task sequences and no operating system image… I then re-point the SCCM install back to the original SQL server and all of the missing packages etc are available

    Reply
  4. When we do the migration, we run into the error:
    INFO: Creating SQL Server machine certificate for Server []…
    INFO: ” is a valid FQDN.
    ERROR: Failed to create SQL Server certificate on server
    ERROR: Failed to create SQL Server [] certificate remotely.

    The user we’re using for the installation is sysadmin on the SQL cluster, Full Administrator in SCCM and member of the local admin group of the cluster nodes of the new SQL cluster, the old SQL cluster and on the SCCM server.
    Any ideas, what could be wrong?

    Reply
  5. Hello Rajul,

    Thanks for the great post first! Then I just wanted to know if and when you plan to write the 2 lasts parts of the collections?

    Thanks

    Reply
  6. thanks for you really usefull article!
    one question: do we need to recreate the endpoint used by service broker in the destination server? or it is created by the SCCM Setup Wizard?

    Reply
    • Setup wizard will take care of the endpoint login of the primary site. If you are having a CAS and secondary site connecting to the PS DB then, only the PS endpoint login will be recreated by the setup. Remaining will be take care by the login backup and import script.

      Reply
      • thanks Rajul! i meant the endpoint node : this must be scripted out and executed on the destination instance? thanky very much!
        CREATE ENDPOINT [ConfigMgrEndpoint]
        STATE=STARTED
        AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
        FOR SERVICE_BROKER (MESSAGE_FORWARDING = ENABLED
        , MESSAGE_FORWARD_SIZE = 5
        , AUTHENTICATION = CERTIFICATE [ConfigMgrEndpointCert]
        , ENCRYPTION = REQUIRED ALGORITHM AES)
        GO

  7. What are your thoughts on moving from Enterprise Edition to Standard Edition using this process? Specifically moving from SQL Server 2012 SP4 Enterprise Edition to SQL Server 2016 SP2 Standard Edition.

    I am far from expert in SQL, but the only issue I’ve been able to identify (With the help of input from Garth via Technet) is if the DB us currently using any Enterprise features, especially partitioning.
    “SELECT * FROM sys.dm_db_persisted_sku_features;” returns nothing, and it appears that 2016 now supports partitioning since SP1 anyway.
    Any other roadblocks to changing version like this when moving the database?

    Reply
  8. Thanks for your article.
    We have SCCM (Current Branch) and SSRS (on different servers) databases configured with 2 instances on SQL Fail over Cluster. Since we have some issue with backing up the FoC servers, we prepared SQL Always on Availability Groups. To migrate the DBs (SCCM & SSRS will use same instance), steps are same as you mentioned with your article? Please suggest

    Reply
  9. I’ve run into the same issue as Bobby in that everything appears to be well but the package content is missing.

    I’ve completed all the instructions as per the guide.

    Just to add, the new server collation is set to Latin_genral_Cl_AS. The collation on the SCCM database on that server is correctly set to SQL_Latin_General_CP1_Cl_AS. Would this collation setup cause the initial issue I face and any others moving forward?

    Reply
  10. Hi Rajul and Anop, Thank you for the documentation. Event though i followed up the same steps when i open console only administration tab is shown. I tried site reset which did not work. I also tried modify sql options that gave an error in configmgrsetup.log “failed to create sql alias on the server”. And also “”CTool::ConfigureSQLAlias() failed to connect to the Registry with error 53″” .Site server computer account is direct member of local administrators group on database server and it has sysadmin rights on the database server. Do you guys have any idea?

    Reply
    • This means to complete 10.a, 10.b, and 10.c before proceeding to 11.

      a. Enable the SQL Broker on the Site database
      b.  SET the Site Database as trustworthy
      c. SET the Database to honor the HONOR_BROKER_PRIORITY

      Reply
  11. Do we need to have the old SQL server online when we migrate ?

    We seeing issues with the SCCM installer ConfigManager setup wizard trying to contact the old SQL server ?

    Reply
  12. Hi Raju,

    Please can you verify on your step when we migrate the SCCM SQL server from one server to another, on the site server do we need to Remove the Old SQL role and add the new SQL Role.

    Secondly as Bobby, Matt higlighted the same issue after migration was “no packages are available, no boot images, no driver packages, no task sequences and no operating system image…” Is this because on the Site server we have missed out step to remove Old SQL role and add the new SQL server role on the site server?

    Thanks

    Reply
  13. hi, im getting SQL error. ERROR cannot create certificate on remote server. Any ideas. Im moving SCCM Db from old sql server to a sql cluster

    Reply
  14. Also getting an error about not being able to create the certificate when trying to migrate from old standalone SQL server to SQL Cluster

    Both the Site Server and the User account are local admins on both nodes, and also have SysAdmin privs on the Cluster.

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.