This week I needed to move a Configuration Manager 2012 database from the local primary site to a SQL Server 2008 R2 environment that is clustered. A fairly simple action but you need to keep a couple of things in mind when moving a Configuration Manager 2012 database to a cluster. Let’s see what you need to keep in mind.
To be able to move the database to a Failover SQL Cluster you need to prepare the SQL Server cluster and keep the following in mind:
- Add the computer account of the site server to the local administrators group of each cluster node.
- Be sure to 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. In my example below I am using the default port 1433.
- Be sure to allow the ports 1433 and 4022 (SQL Broker) through the Windows firewall of each cluster node.
Set the fixed port
After preparing the SQL environment, you are ready to start moving the Configuration Manager 2012 database from the local site server to the cluster. This can be done by creating a backup of the Configuration Manager 2012 database and restore it into the clustered SQL Server Instance on the cluster. In the SQL Server Management Studio you can find the option to backup the Configuration Manager 2012 database to a file on the local disk.
After the backup is finished you need to copy the backup to the cluster node on which the clustered SQL Server instance is active. Once again, go into the SQL Server Management Studio and restore the Configuration Manager 2012 database in the SQL Instance on the cluster.
After the restore is completed successfully, you need to reconfigure the Configuration Manager 2012 Site server to connect to the database on the SQL Cluster. This can be done by starting the Configuration Manager Setup, which you can find in Start Menu -> All Programs -> Microsoft System Center 2012 -> Configuration Manager.
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.
Normally this should be the way to migrate the database to the cluster, but there is an issue with the SQL Server 2008 R2 Native Client in combination with, Configuration Manager 2012 and clustered databases, like explained in the blog of fellow ConfigMgr MVP Kenny Buntinx. Errors like “[SQL Server Native Client 10.0] SSL provider: The target principle is incorrect” and “[SQL Server Native Client 10.0] Client unable to establish connection.” will occur when trying to change the database configuration.
So removing the SQL Server 2008 R2 Native client and installing the SQL Server 2008 Native client will fix it and you will be able to change the database location to the SQL Cluster. After installing the client you will see in the logs that on each cluster node the SMS_SERVER_BOOTSTRAP_<server> service is installed.
One of the best and simple way of explaining this topic , thanks a ton!