One of the challenges as an SCCM Architect is to fix environments and bring them in line with best practices. This week’s challenges was the migration of the SCCM site database from a shared instance to an instance of its own. The group this was being delivered for was seeing performance issues with their SCCM applications as competing applications were using resources on the same SQL instance.

From time to time you will realize that with legacy environments, certain best practices were usually put aside to save cost but need to be implemented to improve stability and performance of the application. In our situation we had our SCCM database on a shared instance with other databases on our organization. This led to degradation of performance of the SCCM application and even stalls in the console when other databases were executing high memory SQL jobs. Below you will find the steps to migrate the SCCM SQL database to a new site server. From the inception of ConfigMgr 2012, a lot has changed from the SQL standpoint and it heavily depends on the SQL Broker Service and change tracking.

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.

This shows the SQL Server version, edition and version we are running. In our 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.

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

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.

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 to stop the Site Components.

The command should produce the following output: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 or a local share.

image

Now it’s time to prepare our new SQL Server.

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:

  1. Open Microsoft SQL Server Management Studio.
  2. Choose Connect.
  3. Right-click on your Server Name and choose properties.
  4. Check for the following:clip_image006
  5. 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:

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

  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. clip_image009
  4. 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.
  5. clip_image010
  6. 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
  7. Verify the new SQL Server configuration on the new server. I used the same query which was used in the first step above:

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

Now, run the following query to check the settings again

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