Moving ConfigMgr 2012 databases between servers

Our CAS was split between two servers; a site server and a dedicated SQL Server. We decided to consolidate the ConfigMgr database to the site server; since the server running SQL Server was underutilized, and it freed up a server that could be used to run a copy of the CAS.

What made this a little tricky, the database server included the primary WSUS; SUSDB database that was used for the entire hierarchy. Moving the SUSDB steps are included, and an excellent post from a colleague of mine are in the references section, below.

Prior to the database move, based on research, and testing in a lab, I’ve compiled a check list of sorts to help guide you through the process. I highly recommend testing this in a lab first.

Note: Server name ‘SOURCE’ is the original SQL database, ‘TARGET’ is the new server.

  1. Install copy of SQL Server 2008 R2 on TARGET, with same SP & CU as SOURCE. (note: we will migrate to SQL 2012 at a future point in time. The key here; the SQL collation and version (SP & CU) on the TARGET server must match the SOURCE server.
  2. Set SQL Server properties; memory, tempdb size, autogrowth and drive location.
  3. Verify that SQL Server client utility (CLICONFG) have Named Pipes & TCP/IP enabled
  4. Get recent list of DB and file location list
  5. On source SQL, Script migrate user LOGIN information between servers, save output for subsequent step. Reference: http://support.microsoft.com/kb/918992
  6. Run CMD as Administrator Stop CM (PREINST /STOPSITE ) to stop the Site Components
  7. Stop WSUS & IIS Admin services
  8. Use native SQL Server to back up databases from SOURCE, use backup compression to reduce size of backup (optionally, use detach database, copy db files, then reattach database)
  9. Copy CM & SUSDB DB backups from SOURCE to TARGET
  10. Restore databases to TARGET
  11. For SUSDB database, verify that database option ‘Recurse Triggers’ = True
  12. Verify SQL configuration – for CM DB, the “is_*” fields for following query should all be on (value 1). I found for our site, I needed to run the commands after this select (step 14) to properly configure the TARGET.
  13. 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 
  14. Sp_configure ‘clr enabled’, 1 RECONFIGURE

    ALTER DATABASE cm_sitecode

    SET enable_broker

    ALTER DATABASE cm_sitecode

    SET trustworthy ON

    ALTER DATABASE cm_sitecode

    SET honor_broker_priority ON 

  15. Update registry key for SUSDB (reference Move SUSDB link below)
  16. On target SQL, apply script migrate users/group security
  17. Run CM, site server reset process, SQL Maintenance, point to a TARGET
  18. Change Servers and Site System Roles – CM console change, site properties, select reporting point role, verify that the server name is correct. If not, change the reporting point to TARGET. Update any custom SSRS DSNs to point to new server.
  19. Restart Stopped services, or reboot server.
  20. Verify CM logs, replication, Software updates, etc.
  21. Check that Software Update synchronization task works (sync software updates, monitor logs)
  22. Check for (newly) expired updates.
  23. Script any required SQL Server Agent Jobs (select Job, right click, Script Job as > CREATE to > File ) to create, then transfer/execute job creation on TARGET. Create schedules as needed.

References:

http://blogs.technet.com/b/configurationmgr/archive/2013/04/02/how-to-move-the-configmgr-2012-site-database-to-a-new-sql-server.aspx

http://blogs.technet.com/b/mwiles/archive/2011/06/17/how-to-move-the-wsus-database.aspx

http://support.microsoft.com/kb/2709082

Advertisements
This entry was posted in ConfigMgr, SQL Server. Bookmark the permalink.

4 Responses to Moving ConfigMgr 2012 databases between servers

  1. Hello Steve!

    Such a great post! I just have a question. In the case that I need to move the database from one instance to another, could this be done with similar steps as the ones you posted?
    The case is that a Primary Site is running in instance SQLcluster\instance1 and we need to move the database to SQLcluster\instance2.

    Thank you again for a great post with easy steps to follow.

  2. Kim says:

    Hi Steve,

    Can the above steps be used if i like to migrate the databases to the higher version than the source database? I like to migrate from SQL Server 2008 R2 SP2 to SQL Server 2014 SP2. Would this be possible using the steps you have provided about?

    Please advise!

    Thanks,
    Kim

    • ConfigMgrMVP says:

      Assume you are talking about migrating SCCM databases? The short answer, is it should work as long as you are using a supported version of SCCM with SQL 2014 Sp2. As always test, before a production deployment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s