Change SQL Server of existing SharePoint 2013 Server

Below are the pre-requistes to change the SQL server of the existing SharePoint 2013 server. Please let me know in comments if required the detailed steps for following tasks.

1. Setup new SQL server and have administrative permissions on its instance.
2. Replicate all SharePoint databases to new SQL server. (Recommended - Stop all SharePoint sites to avoid loss of data during database replication).
3. Get TCP IP port number of new SQL server and name of old SQL server and new SQL server name.
4. Have SharePoint Powershell permissions to execute commands. 
5. Have appropriate command prompt permissions to start or stop services.

Steps for pointing SharePoint server to the new database server.

 

 1. Stop all SharePoint services using the following commands in command prompt with run as administrator.

 

   net stop w3svc
  net stop sptimerv4
  net stop spadminv4
  net stop sptracev4
  net stop osearch15
  net stop SPSearchHostController
  net stop SPUserCodeV4
  net stop SPWriterV4
  net stop W3SVC
  iisreset /stop 

2. Create SQL Server alias for pointing to new SQL database server on all your SharePoint servers.

If SQL Configuration Manager is installed just head over to the SQL Native Client 10.0 configuration then into Aliases. Create a new alias that points to your new SQL Server by right clicking on the Alias. You need to do this on both 32 and 64 bit instances.
 

• On the Alias name put your old SQL Server IP or Name
• On the Port the port you indicated on step 7, default is 1433.
• Protocol should be TCP/IP
• And on Server the new SQL Server IP or Name together with its instance name if there is any. In our case it’s SQL2012

if you don’t have SQL Configuration Manager in your SharePoint Server then run the CLICONFIG again on both 32 and 64 bit instances. For 32 bit go to run command and execute this “%SystemRoot%/SysWow64/CliConfg.exe” and for 64 just this “CLICONFG” all without quotes of course. Add the same info as above.

 

3. Rename the old sever name to new server from following command.

Rename-spserver -identity "OLDSQLSERVER.DOMAIN.COM" -name "NEWSQLSERVERNAME.DOMAIN.COM" 
 

4. Start all SharePoint services.

  net start w3svc
  net start sptimerv4
  net start spadminv4
  net start sptracev4
  net start osearch15
  net start SPSearchHostController
  net start SPUserCodeV4
  net start SPWriterV4
  net start W3SVC
  iisreset /Start

5. Check in the Central Administration site, whether old SQL server is renamed to the new SQL server name.

6. Stop all SharePoint services like in step 1.

7. Delete SQL alias which was created in step 2.

8. Start all SharePoint services similar in step 4.

9. Check whether all new database is connected to new database or old database.