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. 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 13) to properly configure the TARGET.
  12. 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 
  13. Sp_configure ‘clr enabled’, 1 RECONFIGUREALTER DATABASE cm_sitecode

    SET enable_broker

    ALTER DATABASE cm_sitecode

    SET trustworthy ON

    ALTER DATABASE cm_sitecode

    SET honor_broker_priority ON

  14. Update registry key for SUSDB (reference Move SUSDB link below)
  15. On target SQL, apply script migrate users/group security
  16. Run CM, site server reset process, SQL Maintenance, point to a TARGET
  17. 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.
  18. Restart Stopped services, or reboot server.
  19. Verify CM logs, replication, Software updates, etc.
  20. Check that Software Update synchronization task works (sync software updates, monitor logs)
  21. Check for (newly) expired updates.
  22. 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

Filed under: ConfigMgr, SQL Server

email

Written by , Posted .