SP Config DB still pointing to old DB server

Question

We changed the SharePoint 2016 SQL server recently. Once alias is set to new SQL server, sites were working fine. But when I checked in CA, Configuration Database still points to old DB server (under manage servers in the farm).

The content databases pointed to new sql server. Only the config database still points to old server.

I tried to run config wizard, it is pointing to the new sql alias in the initial screen, but execution failed with error saying "An update conflict has occurred, and you must re-try this action."

I stopped the SQL services in the old server to check if the connections now point to new server. But then the sites and CA did not open and showed run time error. So I restarted the SQL server and tried to run the config wizard, same error. I tried to detach the config db from old SQL server but then CA did not load. How do I change the sql server pointed by config db?

Answer 1

To change the SQL Server pointed to by the Configuration Database in SharePoint 2016, follow these steps:

1. Set SQL Alias:

   • Ensure the SQL alias for the new server is correctly set on all SharePoint servers using cliconfg.

2. Stop SharePoint Services:

   • Stop all SharePoint services to prevent write operations.

3. Detach and Attach Configuration Database:

   • Detach the Configuration Database from the old SQL Server:

     USE master;
     ALTER DATABASE [YourConfigDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
     EXEC sp_detach_db @dbname = 'YourConfigDatabaseName';
     

   • Copy .mdf and .ldf files to the new SQL Server.
   • Attach the Configuration Database to the new SQL Server:

     CREATE DATABASE [YourConfigDatabaseName] ON 
     (FILENAME = 'D:\SQLData\YourConfigDatabaseName.mdf'), 
     (FILENAME = 'D:\SQLLogs\YourConfigDatabaseName.ldf') 
     FOR ATTACH;
     

4. Run SharePoint Configuration Wizard:

   • Run the SharePoint Products Configuration Wizard on each server.
   • If you encounter an "update conflict" error:
     • Restart the SharePoint Timer Service (SPTimerV4).
     • Clear the SharePoint Configuration Cache:
       • Stop SPTimerV4.
       • Delete XML files in C:\ProgramData\Microsoft\SharePoint\Config\GUID.
       • Edit Cache.ini, set the number to 1, and save.
       • Restart SPTimerV4.

5. Verify and Start Services:

   • Start SharePoint services.
   • Verify that Central Administration and sites are working.

This should update the Configuration Database to point to the new SQL Server.