You are here:   Research
  |  Login

Welcome to my blog, quickest way to find articles is usually to search for them.

Minimize
Search in All Title Contents
 
     

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

Jul 12 2017

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.

CM01

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).

image
Stopping most of the site components and services.

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 

image
Backing up the database using SQL Server Management Studio,

 

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

 

image
Checking SQL version.

 

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'));"

 

image
Checking Server 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'"

 

image
Checking if CLR Integration is 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

image
Verifying the configuration of the CM_PS1 database.

 

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

image   
Verifying configuration of the CM_PS1 database.

 

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.

image
The updated settings for the CM_PS1 database.

 

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.

image
The Available Setup Options page.

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

image
The Site Maintenance page.

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.

image
The Database Information page.

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)

 

image
ConfigMgr Setup being unhappy about the SQL Server Native Client.

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.

image
Site Server properties, showing CM01 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!

image
The ConfigMgr console now showing only CM01.

 

Written by Johan Arwidmark.








Happy deployment, and thanks for reading!


What our lawyers makes us say:

This information is provided "AS IS" with no warranties, confers no rights and is not supported by the authors or Deployment Artist.

Copyright © 2017 by Deployment Artist (the company behind deployment research). All rights reserved. No part of the information on this web site may be reproduced or posted in any form or by any means without the prior written permission of the publisher.

Shorthand: Don't pass off our work as yours, it's not nice.

Blog Archive

Minimize