none
Moving all SharePoint databases to a new server without using alias RRS feed

  • Question

  • Looking to move all SharePoint databases to a different SQL server. The content and service app databases are pretty straightforward. It's the SharePoint configuration databases I haven't figured out yet. There's some registry entries associated with the config database location. There also appears to be references to the original SQL database within the configuration database. Microsoft documentation seems to point to the ability to change the location of all databases using changeDatabaseInstance (towards bottom of page):

    https://technet.microsoft.com/en-us/library/cc512725(v=office.16).aspx

    However, I'm unclear how that will work considering once you've updated the config, it will point to the config database on the new SQL server which won't have the updated reference to the new SQL server.

    I prefer not to use the SQL client alias option as it will be more clear which SQL server is being used if the references in SharePoint match the actual server name. I am going to use a CNAME for the new SQL server, but that won't help with the migration at this point.

    Thanks for any info you can pass along.


    • Edited by SJNBham Wednesday, November 15, 2017 5:14 PM
    Wednesday, November 15, 2017 5:11 PM

Answers

  • Unfortunately, after additional testing, my original solution didn't work. I opened a support case with Microsoft and the only fully documented and supported method is to use SQL alias or rebuild the farm. 
    • Marked as answer by SJNBham Wednesday, November 22, 2017 12:02 AM
    Wednesday, November 22, 2017 12:02 AM

All replies

  • Thanks David for the links. I had seen all of those. The solution ended up being:

    - Ensure you move the SharePoint Config database first before any other databases

    - Disable the SharePoint Timer service on the web front end

    - Back up the config database and restore it to the new location

    - DO NOT mark either the old or new config database as read-only

    - Run the following PowerShell commands on the web front end

    #Show all databases and their IDs
    get-spdatabase
    
    #Get the SharePoint_Config database using it's ID from the previous command
    $db = get-spdatabase -Identity databaseID
    
    #Verify the correct database is selected 
    write-host "DB Name = "$db.Name
    write-host "DB current connection string = "$db.DatabaseConnectionString
    
    #Update the database instance
    $db.ChangeDatabaseInstance("NEWSERVER\SharePointInstance")
    $db.update()
    
    #Verify the connection has been updated
    write-host "DB new connection string = "$db.DatabaseConnectionString

    Once you've run the above commands, you should be able to mark the old config database as read only and still make changes in Central Admin to system settings without errors. 

    You should also see the value of the following registry key on the web front end server change to the new server\instance:

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\16.0\Secure\ConfigDB]DSN

    You can  now delete the old SharePoint_Config database.

    • Marked as answer by SJNBham Thursday, November 16, 2017 4:35 PM
    • Unmarked as answer by SJNBham Wednesday, November 22, 2017 12:00 AM
    Thursday, November 16, 2017 4:35 PM
  • Hi SJNBham,

    Thanks for your sharing.

    Best regards,

    Allen Bai


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, November 17, 2017 5:55 AM
  • Unfortunately, after additional testing, my original solution didn't work. I opened a support case with Microsoft and the only fully documented and supported method is to use SQL alias or rebuild the farm. 
    • Marked as answer by SJNBham Wednesday, November 22, 2017 12:02 AM
    Wednesday, November 22, 2017 12:02 AM