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.
- 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 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
GOb. 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.
So how do you move the Reporting services from the old SQL server to new SQL Server along with the custom reports?
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.
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.
How to move WSUS database from old server to new SQL server.
https://docs.microsoft.com/en-us/windows-server/administration/windows-server-update-services/manage/wid-to-sql-migration
i was able to do it with this document
Were Parts 3 & 4 ever published?
Rajul is preparing 3 and 4
Will be released soon
Were parts 3 & 4 ever published?
Very Soon as per Rajul 🙂
Were Parts 3 & 4 ever published?
when will WSUS migration be released?
Will be published soon.
When will WSUS migration – Part 3 be released?
Soon !!
Thanks Rajul for this guide! Part 3 will be very helpful to me.
Do the SQL Server versions need to be the same on both the old and new SQL servers? Same question for the OS.
It can be any supported version of SQL (I suppose)
Its always preferred to keep same version or higher version of SQL. Downgrading the SQL will trigger unforeseen issues.
Good point Rajul. I didn’t think someone will downgrade the SQL 🙂 I agree with Rajul, I don’t recommend doing a downgrading of SQL version with migration.
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!
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.
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
Did you run the site maintenance task to point the SQL to the new server after moving the database manually?
Yeah, confirmed it was pointing to the new SQL server
Yeah done that. Logs all appear fine.
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?
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
Hi Rajul ,
Hope you are doing well. Are you planning to publish the part -3 🙂
dissapointing that this series was not completed
Very much agree with others, would be great if the series would be completed, please!!!
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?
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.
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
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?
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
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?
Does WSUS migration post is released
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?
what do you mean by 10. Reconfigure the restored database where XXX is your site code.
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
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 ?
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
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
Do you have ready document for Reporting role migration post this DB Migration to new server ?