Moving the ConfigMgr Current Branch database to another server (as in back to the primary site server)

For the tenth time or so this year, I was tasked with moving a ConfigMgr database away from a remote SQL server to where it’s supposed to be: At home! E.g. on the same virtual machine as the primary site server. Why you ask? Because it’s better 🙂

This article is based on a great blog post by Anurag Shukla (Microsoft): https://blogs.technet.microsoft.com/configurationmgr/2013/04/02/how-to-move-the-configmgr-2012-site-database-to-a-new-sql-server but I have added in workarounds/configurations for some real world issues you are likely to run into.

WARNING: Don’t expect this process to work fine if you have a CAS, I recommend this for standalone Primary Sites only. If you have a CAS you have problems enough already, fix them first, like migrating off the CAS hierarchy into a standalone Primary Site.

Scenario

In this guide I have a remote SQL Server named SQL01, it’s running SQL Server 2012 SP3, and it’s currently hosting a ConfigMgr Current Branch 1606 Site Database named CM_PS1. In this guide the database will be moved from SQL01 to the Primary Site Server which is CM01. The overview steps are the following:

• Stop the ConfigMgr services on CM01, and backup the database on SQL01
• Review the current database settings on SQL01
• Install SQL Server (same version) on CM01
• Restore the database on CM01
• Setup ConfigMgr to use the database on CM01

Step 1 – Stop ConfigMgr services on CM01, and backup the site server database on SQL01

In this step you backup the ConfigMgr Site Server database on SQL01, and verify some database settings.
1. On CM01, stop most ConfigMgr services by running preinst.exe /stopsite. You find the preinst.exe file in the ConfigMgr installation directory (E:\Program Files\Microsoft Configuration Manager\bin\X64\00000409 in my environment).

2. Using PowerShell, stop the remaining ConfigMgr services by running the following commands in an elevated PowerShell using the following commands:

Stop-Service -Name AI_UPDATE_SERVICE_POINT      
Stop-Service -Name CONFIGURATION_MANAGER_UPDATE       
Stop-Service -Name SMS_NOTIFICATION_SERVER

Note: The AI_UPDATE_SERVICE_POINT service is only available if you configured Asset Intelligence.

3. Then use the following commands to make sure they are stopped

Get-Service -Name AI_UPDATE_SERVICE_POINT      
Get-Service -Name CONFIGURATION_MANAGER_UPDATE      
Get-Service -Name SMS_NOTIFICATION_SERVER

4. On SQL01, using SQL Server Management Studio, make a full backup of the ConfigMgr database. In my case the database was named CM_PS1.

Note: If you want to go all in with PowerShell, you can also use the dbatools from Microsoft MVP Chrissy LeMaire (@cl). Here is the link: https://dbatools.io 

Review the current database settings on SQL01

At this point, it’s time to review the current SQL Server and Site Server database settings on SQL01.

SQL version can be verified by running the following command on an elevated PowerShell prompt on the SQL Server.

Invoke-Sqlcmd -Query "SELECT @@version" | Format-Table –Wrap -AutoSize

Server Collation can be verified by running the following command on an elevated PowerShell prompt on the SQL Server.

Invoke-Sqlcmd -Query "SELECT CONVERT (varchar, SERVERPROPERTY('collation'));"

CLR Integration can be verified by running the following command on an elevated PowerShell prompt on the SQL Server. A run_value of 1 means its enabled.

Invoke-Sqlcmd -Query "sp_configure 'clr enabled'"

You also need to verify the database configurations for the CM_PS1 database, and even to this absolutely can be done in PowerShell too via invoke-Sqlcmd, I ran this query in SQL Server Management Studio since it’s easier to view there.

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

Step 3 – Install SQL Server (same version) on CM01

Technically it is supposed to work restoring the database to a higher version, but for safety I’ve always restored to the same version, and once verifying everything works, then upgraded to a later version if needed. Also make sure you to install SQL with the same Server Collation.

1. Install the same version of SQL on CM01, in my example SQL Server 2012 SP3 with GDR update (version 11.0.6248.0).

2. After install, verify if CLR Integration is enabled by running the following command on an elevated PowerShell prompt.

Invoke-Sqlcmd -Query "sp_configure 'clr enabled'"

3. If CLR Integration is enabled (run_value is 1), continue to the next step, otherwise enable it by running the following query in SQL Server Management Studio. I had to do that in my environment.

sp_configure 'clr enabled', 1      
Reconfigure

Step 4 – Restore the ConfigMgr Site Database on CM01

Now it’s time to restore the database.

1. Copy the SQL backup from SQL01 to CM01.

2. Using SQL Server Management Studio, restore the previous backup.

3. After restoring the backup, review the database configuration by running the following query:

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

As you can, some of the CM_PS1 database settings were not restored, so they needed to enabled by running the following SQL commands:

USE master      
ALTER DATABASE CM_PS1 SET ENABLE_BROKER       
ALTER DATABASE CM_PS1 SET TRUSTWORTHY ON       
ALTER DATABASE CM_PS1 SET HONOR_BROKER_PRIORITY ON    

To verify the configuration, I once again ran the query:

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

Yay, now the database settings are what they are supposed to be.

Step 5 – Setup ConfigMgr to use the database on CM01

The final step is to setup ConfigMgr to use the database on CM01, and for that you run the ConfigMgr setup in maintenance mode.

For ConfigMgr Current Branch you no longer find the setup on the start menu, like you had for ConfigMgr 2012, but rather in the cd.latest folder in the installation directory (E:\Program Files\Microsoft Configuration Manager in my environment).

1. On CM01, navigate to the ConfigMgr installation directory, and in the cd.latest\smssetup\bin\x64 folder, run setup.exe.

2. On the Available Setup Option page, select the Perform site maintenance or reset this site option, and click Next.

3. On the Site Maintenance page, select the Modify SQL Server configuration option, and click Next.

4. On the Database Information page, type in the Site Server (also the SQL Server) name, CM01.corp.viamonstra.com in my environment, and click Next.

5. Complete the wizard.

Real world note: Don’t be surprised if it fails. ConfigMgr Current Branch Setup (and older versions too) seems extremely picky about the SQL Server Native Client, and will throw the following error if it doesn’t like it:

This primary site is corrupted. Setup cannot find a valid site control image in the database or in the site control file.

If you open the C:\ConfigMgrSetup.log you will find additional info like:

SQL Server error: [08S01][233][Microsoft][SQL Server Native Client 11.0]Shared Memory Provider: No process is on the other end of the pipe.
*** [08S01][233][Microsoft][SQL Server Native Client 11.0]Communication link failure
*** Failed to connect to the SQL Server.   

But no worries, there is a fix for that 🙂 (keep on reading)

The fix? Simply run the setup wizard one more time, with the same settings.

6. When setup is completed, take a coffee break for about ten minutes, and then reboot both the ConfigMgr site server and the SQL Server.

7. After the reboot of both servers, have another coffee break, and then check the Site Server properties. They should now show Site Server (CM01 in my case), as the SQL Server.

8. Leave both servers running for a day or so, and after verifying that ConfigMgr has removed the Component server role from SQL01, you can delete the SQL01 server from ConfigMgr (assuming it doesn’t have any other roles).  Wait another hour and then turn off the original SQL Server (SQL01).

9. Yay, you now have your Site Server using a local SQL. Very shiny!

Extra Resources

For info on how to migrate related database like the WSUS DB or Reporting Services (migrating reporting services is usually not needed, just migrate any custom reports to the new server). Check the below links for more info:

Migrating SQL Reporting Services to a new server by moving the Reporting Services databases
https://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases/

Migrate WSUS to a new server
https://social.technet.microsoft.com/Forums/systemcenter/en-US/cb39db7f-9e2e-4ae2-abfd-e660b9788c77/move-sccm-and-wsus-database-step-i-wsus

This post is also useful if you just want to change from WID to SQL

Migrating the WSUS Database from WID to SQL
https://docs.microsoft.com/en-us/windows-server/administration/windows-server-update-services/manage/wid-to-sql-migration

Written by Johan Arwidmark.