Advertisement

SCCM Clustered DB Migration from SQL 2005 to Single Instance SQL 2008

Through this post I’m trying to explain my experience related to SCCM ConfigMgr central Server SQL DB migration. We had migrated SCCM ConfigMgr 2007 central site DB from SQL 2005 to SQL 2008 R2. Along with this we changed the SQL DB server from a clustered instance to single instance. Also, ConfigMgr SCCM DB server is moving from physical box to virtual one. So, we don’t need clustering for a virtual box, the redundancy of SQL server is taken care by VMware VMotion.

Along with SCCM ConfigMgr 2007 central Database, we need to move WSUS database as well as SSRS. This adds the complexity of this movement. Moving SCCM 2007 DB is documented over here by Sudheesh N. Once migration is completed as per the document, you need to verify and confirm the sitecomp.log file whether the following entry is present or not. Once the following entry is there, you can restart the SCCM 2007 central site server and remote site system servers (Remote MPs, DPs or SUPs – if required).

Synchronization complete

SCCM SQL DB Migration

SCCM SQL DB Migration Process Completion Notification @ SiteComp.log

SCCM WSUS database (software update) move and remote SUP server reconfiguration has been completed by changing the following registry key in the remote SUP server.

Navigate to HKLM->Software->Microsoft->Update Services->Server->Setup ==> Edit SqlServerName Key and provide new SQL server and Instance name.

After following the documented process, I got stuck with SCCM console connectivity issues explained in the following blog post hereUnable to connect to site database after it’s moved to a failover cluster.  When I tried to launch SCCM ConfigMgr console after  migration, I was getting following errors in SmsAdminUI.log and Smsdbmon.log !!

SmsAdminUI.log

The ConfigMgr console could not connect to the ConfigMgr site database. Verify that this computer has network connectivity to the SMS Provider computer and that your user account has Remote Activation permissions on both the ConfigMgr site server and SMS Provider computers. For more information, see “How to Configure DCOM Permissions for Configuration Manager Console. The ConfigMgr Provider reported an error.

Smsdbmon.log

CTriggerManager::Init – unable to get SQL connection
*** [28000][18456][Microsoft][ODBC SQL Server Driver][SQL Server]login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’
*** Failed to connect to the SQL Server.

As mentioned in the above post these errors were pointing to SPN registration issue. My SQL 2008 Database SQL Server (MSSQLSERVER) service is running with domain service account. This SQL 2008 service account is already registered to Active Directory SPN (Service Principal Name) using following commands. So SQL service account was pointing to correct host name and FQDN along with 1433 port of SQL server.

SPN registration commands :-

setspn -A MSSQLSvc/ACNSQL2K8DB:1433 ConfigMgr\svc_SQLSERVICEACCOUNT
setspn -A MSSQLSvc/ACNSQL2K8DB.ConfigMgr.com:1433 ConfigMgr\svc_SQLSERVICEACCOUNT

So what is the issue now? Resolution :-

When I checked the old SCCM ConfigMgr SQL 2005 DB, it was running with a different SQL service account and that service account was part of local administrators group of central SCCM ConfigMgr 2007 site server and remote SUP server.  So the resolution for my problem here was just to add new SQL service account ConfigMgr\svc_SQLSERVICEACCOUNT to local administrators group of both remote SUP and SCCM ConfigMgr 2007 central site server.

There is no need to worry about SCCM ConfigMgr SQL clustered instance DB to single instance SQL DB. The SCCM repair or reconfiguration wizard will take care of this complexity. Need to just make sure to remove the old SQL DB site system roles (there must be two DB site systems roles because of clustered instance).

Simple resolution for complex problem 🙂 Hope this helps !!

About Author 

Anoop is Microsoft MVP and Veeam Vanguard ! He is a Solution Architect on enterprise client management with more than 13 years of experience (calculation done on the year 2014) in IT. He is Blogger, Speaker and Local User Group Community leader. His main focus is on Device Management technologies like SCCM 2012,Current Branch, Intune. He writes about the technologies like SCCM, SCOM, Windows 10, Azure AD, Microsoft Intune, RMS, Hyper-V etc...

    Find more about me on:
  • googleplus
  • twitter
  • facebook
  • linkedin
  • youtube
Posted in: ConfigMgr (SCCM), Configmgr2012, SCCM, SCCM 2007, SCCM 2012

Leave a Comment and Contact Anoop