Why Get-SPDatabase does not return SharePoint Configuration Database?

Question

After migrating all the SharePoint 2013 databases to a new SQL server (2012) as per Microsoft documentation at how to change database in farms, I can't retrieve the SharePoint configuration database using Get-SPDatabase command even though other databases like content databases and service databases are returning in the result.

I have tried with the ServerInstance, but no success. It seems that the default database (webpplication.parent.DefaultDatabase) is not set to the configuration database. But not sure how to set it now. Any ideas? The Admin account and farm account have required permissions to the SQL server roles and databases.

Also, config db is not returning with the exact database name and SQL SQL alias

    Get-SPDatabase -ServerInstance SQLAliasDEV | ? {$_.Name -eq 'SharePoint_Config_SearchFarm'}  

Another symptom is that browsing certain page in the central admin such as "Manage servers in this farm (FarmServers.aspx)" is throwing Object reference not set error. The underline error is pointing to connection string.

System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SharePoint.ApplicationPages.FarmServersPage.get_ConfigurationDatabaseServer() at ASP._admin_farmservers_aspx.__Render__control6(HtmlTextWriter __w, Control parameterContainer) at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) at Microsoft.SharePoint.WebControls.AjaxDelta.RenderChildren(HtmlTextWriter output) at System.Web.UI.WebControls.WebControl.RenderContents(HtmlTextWriter writer) at System.Web.UI.WebControls.WebControl.Render(HtmlTe...

I've verified the web config files as well as the registry key for the Configuration database, but all look good. What other places the connection string might be stored at?

Answer 1

I’ve ultimately contacted Microsoft Support to find out the root cause of this issue.

We’ve found out that the SharePoint Configuration Database Object, as described below, was missing in the Objects table in the Configuration Database that has recently been migrated to a new SQL Server as described in my original post.

Missing object: SharePoint Configuration Database

Class Id: E6591A38-008A-4F23-A88F-36A181C7D4EB
Full Name: Microsoft.SharePoint.Administration.SPConfigurationDatabase…
Select * from objects where ClassId = 'E6591A38-008A-4F23-A88F-36A181C7D4EB'

However, the Object was found in the old database. I have no idea, nor does the MS Support Engineer as to how the object got deleted. She suggested that we “Rebuild the Farm” because, as we all know, MS doesn’t support direct database update.

Since the issue encountered in our DEV environment, I wanted anyway to give it a try to import the Object from the old database to the same configuration database on the new SQL server.

Because the items in the Objects table are in hierarchal nature, the challenge was to find out the parent and children of this object. Looking into Object tables in the yet-to-be-migrated SharePoint Configuration Databases in our other environments, I found out that the following Object is the parent of the missing object, and there are no children of this object. That should be easy enough! But, I’ve to admit that I needed to research a little bit more to understand the impact and other dependencies. An old post about Configuration Cache by Trevor Seward helped me do exactly that.

Parent of the missing object: SharePoint Database Service Instance

Class Id: 3112E92F-B97D-481E-8CEB-03FDE15ED1A7
Full Name: Microsoft.SharePoint.Administration.SPDatabaseServiceInstance..
select * from objects where ClassId ='3112E92F-B97D-481E-8CEB-03FDE15ED1A7'

Solution – import the object from the old database (not supported by Microsoft)

I have noticed that Id of the parent object (SPDatabaseServiceInstance) in the migrated Configuration Database is different from what is in the old database. So, in the INSERT statement, I had to replace the “ParentId” value with the new Id to link the two.

--1 Insert the new record to object table
INSERT INTO [dbo].[Objects]
           ([Id]
           ,[ClassId]
           ,[ParentId]
           ,[Name]
           ,[Status]
           ,[Properties])
     VALUES
           ('78DAFC6A-AB81-46B8-80E6-731AF56399A4'
           ,'E6591A38-008A-4F23-A88F-36A181C7D4EB'
           ,'52E6B769-05F8-4456-BA85-09A7C7DD45EB'-- Matching ParentId 
           ,'SharePoint_Config_SearchFarm'
           ,0
           ,'[Properties content from the old SQL DB]')

GO

delete from Tombstones where id='78DAFC6A-AB81-46B8-80E6-731AF56399A4'

GO

I'm not sure what I'm doing wrong in migrating SharePoint databases to a new SQL Server by following the MS instruction described here. The article doesn't address what to do if verification fails on certain steps. Also, it does not ask for turning off crawling, timer service etc.

We're yet to migrate the databases for the production environment, so much appreciate if anyone can share their experience on this.

Thanks - Matiur Rahman

Answer 2

Wanted to share my experience:

If the current SharePoint farm doesn't use SQL-aliases, and you would like to migrate SharePoint databases to new SQL Server/Instances, I would recommend creating SQL-aliases with the same name as the Old SQL Server/Instances name. No additional SharePoint configuration is needed.

Example

• Current SQL Server/Instance:ContosoOLD\SharePointInst
• New SQL Server/Instance:ContosoNEW\SharePointInst
• Create SQL-Alias on SharePoint Servers to point to New SQL Server/Instance: ContosoOLD\SharePointInst