How to move the ConfigMgr 2012 site database to a new SQL server

How to move the ConfigMgr 2012 site database to a new SQL server

  • Comments 8
  • Likes

Anurag Shukla | Support Engineer | Manageability - Microsoft Corporation

imageHello everyone, I wrote a blog post some time back that explained how to move the System Center Configuration Manager 2007 (ConfigMgr 2007) site database from a SQL 2005 to SQL 2008 (link), and here’s how to do the same with System Center 2012 Configuration Manager (ConfigMgr 2012).

This information is ‘AS IS’ and is provided for guidance to move the ConfigMgr Site Database to a new SQL Server. Note that this may not be the only way to successfully migrate the DB but it’s a method I’ve used successfully in the past. Please use this as a guide for getting the understanding of what all steps are involved in moving the Site database. Please use it at your own risk.

Since the inception of ConfigMgr 2012, a lot has changed from the SQL standpoint as we are now heavily relying on the SQL Broker Service and change tracking. Because of this, our approach also needs to change when we are planning to move the Site database to a new system.

LAB SETUP

clip_image001

Site Server: CM12PRI.CM12.LOCAL

SQL Server: CM12PRI.CM12.LOCAL

SMS Provider: CM12PRI.CM12.LOCAL

clip_image002

Also note that CM12PRI has a named instance where I am hosting my Site database.

Backing up the SQL database and preparing the new SQL server

We will use some SQL queries to identify the SQL server versions, configurations, service packs and configurations.

-- CHECK SQL SERVER VERSIONS

Select @@version

-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2806.0 (X64)   Feb 14 2012 18:18:40   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

This shows the SQL Server version, edition and version we are running. In my case, our existing server is running on version 10.50.2806.0. This version number will vary based on the edition you are using and on which SQL Cumulative Update your server is running. The following query can also show you the same details about the SQL Server.

--- Verify the Product version, Product Level, Editions

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The last query we will use identifies some core features that are required for ConfigMgr 2012 (e.g. whether SQL Broker is enabled or if the database is marked as trustworthy, etc.).

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

clip_image003

Here we can see various properties of our Site database server. It has SQL Broker enabled, plus the database is marked as trustworthy and is honoring the SQL Broker priority.

Step 1:

Before we move our ConfigMgr 2012 database to a different site system, we should create a current backup of the database.  Before you start the SQL backup, ensure that you run PREINST /STOPSITE to stop the Site Components. Here is what you will see, when you’ll run that command:

clip_image004

Step 2:

Now we will use the SQL Management studio to back up the database. Using the context menu on the Database, click Backup and you’ll see a page like the one below. Choose Full as the Backup type. You can place it on a network share, however in my case I will go-ahead and keep it local under c:\bkp\cm_pr1bkp.

image

 

Now it’s time to prepare our new SQL Server. In my lab this is named WINDOWS-S2TH386 CM12.LOCAL.

The new SQL Server should be running the same or higher version of the previous SQL Server. It’s possible to have a SQL Server that was running the Cumulative Update 4 (source database) and now you’re moving the database to a server that is running Cumulative Update 6, however if you are moving to the same version of the SQL server, please try to keep them alike to avoid any unwanted results.  

STEP 3:

Before you restore the ConfigMgr database on the new SQL Server computer, please verify Server Collation setting by doing the following:

a. Open Microsoft SQL Server Management Studio.

b. Choose Connect.

c. Right-click on your Server Name and choose properties.

d. Check for the following:

clip_image006

e. The server collation settings should match those on our old SQL Server.

Make sure that the CLR integration is enabled.  To check that run the following stored procedure:

sp_configure 'clr enabled'

Look for the RUN_VALUE - if that is marked as 1 then it means CLR is enabled. Once you have verified these settings, let’s move on to the next step.

Restoring the database on the new SQL Server

In my lab, the new SQL Server is named WINDOWS-S2TH386 CM12.LOCAL

1. Click on Restore Database under the database node in SQL Management Studio as shown below:

clip_image007

2. On the restore database page, choose the option “From device”:

clip_image008

3. Provide the backup file that was created in the previous step. I had already copied the backup file to the WINDOWS-S2TH386 CM12.LOCAL server at c:\bkp\cm_pr1bkp. I will pick the cm_pr1bkp file and then restore the database on the new server as shown below.

clip_image009

Once you click OK on the dialog box as shown above it will present the following screen. Please make sure that you put a check box next to the backup as can be seen below and click OK. 

clip_image010

This should finish restoring your database on to the new SQL Server. Once complete, you will see the ConfigMgr database listed under the new server. As shown below: 

clip_image011

4. Verify the new SQL Server configuration on the new server. I used the same query which was used in the first step above:

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

clip_image012

Here you can see that my database didn’t retain the is_trustworthy_on and is_broker_enabled settings. Use the following queries to enable them:

--- Enable the SQL Broker on the Site database

USE master;
GO
ALTER DATABASE CM_PR1 SET ENABLE_BROKER
GO

 

--- SET the Site Database as trustworthy

USE master;
GO
ALTER DATABASE CM_PR1 SET TRUSTWORTHY ON
GO

 

--- SET the Database to honor the HONOR_BROKER_PRIORITY

USE master;
GO
ALTER DATABASE CM_PR1 SET HONOR_BROKER_PRIORITY ON;
GO

Now, run the following query to check the settings again

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 where name = 'CM_PR1'

clip_image013

Now we can see that my database is ready for site maintenance.

5. Run Site maintenance.

Run the ConfigMgr setup from the start menu:

clip_image014

Then click Next on the “Before you Begin” page. Click Perform site maintenance or reset this Site on the Setup Wizard page as shown below:

clip_image015

Select Modify SQL Server configuration on the Configuration Manager Setup Wizard Site Maintenance page:

clip_image016

That will show you the old information about the SQL server. We will change that to our new SQL Server.

clip_image017

Now just let the ConfigMgr setup complete. After setup finishes, reboot the ConfigMgr site server and the SQL Server.  After the reboot, check the site settings in the ConfigMgr console and verify that it shows the new SQL Server listed. You might see a prompt stating that the site is in a Read Only mode; this can be ignored for now as we are running the re-synch.

Anurag Shukla | Support Engineer | Manageability - Microsoft Corporation

Get the latest System Center news on Facebook and Twitter:

clip_image001 clip_image002

System Center All Up: http://blogs.technet.com/b/systemcenter/
System Center – Configuration Manager Support Team blog: http://blogs.technet.com/configurationmgr/
System Center – Data Protection Manager Team blog: http://blogs.technet.com/dpm/
System Center – Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/
System Center – Operations Manager Team blog: http://blogs.technet.com/momteam/
System Center – Service Manager Team blog: http://blogs.technet.com/b/servicemanager
System Center – Virtual Machine Manager Team blog: http://blogs.technet.com/scvmm

Windows Intune: http://blogs.technet.com/b/windowsintune/
WSUS Support Team blog: http://blogs.technet.com/sus/
The AD RMS blog: http://blogs.technet.com/b/rmssupp/

App-V Team blog: http://blogs.technet.com/appv/
MED-V Team blog: http://blogs.technet.com/medv/
Server App-V Team blog: http://blogs.technet.com/b/serverappv

The Forefront Endpoint Protection blog : http://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: http://blogs.technet.com/b/isablog/
The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Thanks.. I worked just fine.. How do I remove the old sql from the sccm?

  • I am not sure if you referring to removing it from the ConfigMg.if so then using the configmgr setup should take care of this.

  • i'm seeing this in the logs when i perform the site reset:

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.~~  $$<Configuration Manager Setup><08-21-2013 12:55:22.716+300><thread=3208 (0xC88)>

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection  $$<Configuration Manager Setup><08-21-2013 12:55:22.723+300><thread=3208 (0xC88)>

    *** Failed to connect to the SQL Server, connection type: SMS ACCESS.  $$<Configuration Manager Setup><08-21-2013 12:55:22.730+300><thread=3208 (0xC88)>

    INFO: SQL Connection failed. Connection: SMS ACCESS, Type: Secure  $$<Configuration Manager Setup><08-21-2013 12:55:22.737+300><thread=3208 (0xC88)>

  • I have a solution on the Certificate / Trusted problem.

    go to the SCCM server open run and typ "MMC"

    in that console open file and add snappin.

    ADD certificate. in that wizard:

    "service account" next

    Local computer Next

    find SMS_SITE_COMPONENT_SERVER finish

    go to STS_SITE_COMPONENT_MANAGER\trusted pepole

    and take the domain certificate there an right click. all task> Export.

    copy the CRT file to the new SQL server and import that to local computer > trusted pepole.

  • it is 100% working, Many thanks and great efforts.

  • Great article, but, this manual missed an important thing – you MUST create & configure SQL SSL certificate for the new SCCM DB server!
    http://www.spanders.com/sql-encryption/
    Just create custom certificate for the new SQL server, import to the Personal Computer Certificates.
    And - on the certificate itself, right-click on the cert name, and select All Tasks -> “Manage Private Keys…”, then give the user the SQLSERVER service runs as Read permission in the security tab.
    After that select certificate from the SQL Server Network Configuration and click OK.


  • Problem in you system???
    Contact for FREE ANTIVIRUS TECH SUPPORT and get Expert Advise
    Contact on 1-800-935-0537
    http://www.computervirusremoval.us.com/

  • Hello and thanks for your article. Question: is it really neccesary to restart the SQL server at the end? We need to move the DB From a local SCCM to a high available SQL (cluster) that holds another important production Databases. Thanks in advance for your response.