Anurag Shukla | Support Engineer | Manageability - Microsoft Corporation
Hello 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
Site Server: CM12PRI.CM12.LOCAL
SQL Server: CM12PRI.CM12.LOCAL
SMS Provider: CM12PRI.CM12.LOCAL
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
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:
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.
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:
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:
2. On the restore database page, choose the option “From device”:
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.
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
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'
Now we can see that my database is ready for site maintenance.
5. Run Site maintenance.
Run the ConfigMgr setup from the start menu:
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:
Select Modify SQL Server configuration on the Configuration Manager Setup Wizard Site Maintenance page:
That will show you the old information about the SQL server. We will change that to our new SQL Server.
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:
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/
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/