Wednesday, August 22, 2012

Migrating Sccm Database From Local Site Server To Remote Sql Cluster

Infrastructure Requirements


SQL Version:

·         SQL version                                        : SQL Server 2008 R2 Enterprise Edition

·         Collation                                                : SQL_Latin1_General_CP1_CI_AS

·         Type                                                     : Active / Passive


Disk Details of the Clustered SQL Server:

·         Separate drive for SQL Db: 150 GB for a 15,000 client environment with all features enabled

·         Separate Drive for Logs : 50GB

·         Separate Quorum Drive : 5-10 GB should be good

·         SQL Data and SQL Logs must be setup on different drives.

·         Data on the disk array can be backed up for HA


SQL Features that need to be clustered:


SQL Services Required for SCCM
Services that need to be clustered
·         Database Engine Service
·         Analysis Service
Not required
·         Reporting Service
Cannot be clustered. Will be setup on a physical server
·         Integration Service
Not Required
·         Management Tools – Basic
·         Microsoft Sync Framework


Microsoft confirms that we may observe performance degradation while using a clustered SQL database to host the Configuration Manager 2012 site database.


Database Backup:

SQL Server database mirroring is not supported for the Configuration Manager site database therefore the database backups will be managed and maintained by the SCCM Console itself.


Plan to Migrate SCCM Database from local site server to remote SQL Cluster



  • SQL Servers should be provisioned and clustered as per requirements
  • At least 2 SQL Cluster nodes should be available
  • Provision access to the SCCM Production Environment and SQL Cluster Servers


SQL Clustering Plan:

Tasks for CAS SQL Migration to Cluster Node

Hours for completion of each activity are mentioned with sufficient amount of buffer.
Duration (Hours)
1.       Prepare SQL Environment
    1. Add the computer account of the site server to the local administrators group of each cluster node.
    1. Configure TCP/IP Protocol of the SQL Instance in the SQL Server Network Configuration to have a fixed port. Configure this on each cluster node. E.g. you can use the default port 1433.
    1. Allow the ports 1433 and 4022 (SQL Broker) through the Windows firewall of each cluster node
  1. Migrate Database
    1. Open SQL Management Studio on the CAS and back up the CAS site database on a network drive
    1. Copy the backed up files and folders to one of the clustered SQL nodes
    1. Open SQL Management Studio on the SQL Cluster and restore the backed up CAS database on the database in the SQL Instance on the cluster
  1. Post Migration Configuration
    1. Reconfigure the Configuration Manager 2012 Site server to connect to the database on the SQL Cluster
    1. Choose the “Modify SQL Server Configuration” in the “Perform site maintenance or reset this site” setup option. Supply the new SQL cluster information and go through the wizard
    1. Remove the SQL Server 2008 R2 Native client and install the SQL Server 2008 Native client
    1. Change the SQL Backup options in SCCM to point to the SQL Cluster from the CAS server
    1. Troubleshoot in case of any issues
    1. Monitor the SCCM and SQL logs to ensure the migration is successful with no errors for normal operations



  1. Fantastic article ! You havemade some very astute statements and I appreciate the the effort you have put into your writing. Its clear that you know what you are writing about. I am excited to read more of your sites content.

  2. The blog is absolutely fantastic. Lots of great information and inspiration, both of which we all need. Thanks
    IT Service Support in Singapore | Data Governance Solution

  3. This comment has been removed by the author.

  4. Nice information sharing about Migrating sccm Database information..

    sccm migration
    windows migrations

  5. I am very happy when read this blog post because blog post written in good manner and write on good topic. Thanks for sharing valuable information.
    Bangalore Web Designing Company, Web Development Company Bangalore

  6. The blog is absolutely truly incredible. Lots of large information and inspiration, both of which we all need.
    UI Designing Companies in Bangalore, Web Application Development Services in Bangalore

  7. This is more helpful for programmer who is converting database from local site to remote site.

    Visual FoxPro to .Net
    VB.Net Migration
    VB6 Migration
    SQL Server Migration