Move SharePoint Database to another Database server

Move SharePoint Database to another Database server
6 votes, 4.67 avg. rating (92% score)

Scenario:

I have Standalone SharePoint Server both web application and database in single server. Now, I need to move this database to new database cluster.

Whole idea behind it is, I want to setup new farm and later bring another web front end server with load balancer. For this to happen, first I need to move the current SharePoint database to new one and let the web front end server point the new database server

image

Scenario’s you consider reading this post

> Migrate or Moving SharePoint Database(including configuration database) to another DB server

> How to perform unplanned SharePoint Database Migration

> How to configure Sql server client aliases in SharePoint farm

> How to move all databases from SharePoint server to another Server

> Changing SharePoint DB server

Below are the steps to do on high level (I will explain in detail, but this is enough to

  1. Stop SharePoint Services in WFE
  2. Take backup of all the SharePoint Databases (including SharePoint Config)
  3. Stop Sql services in WFE
  4. Restore all the database to new DB Server
  5. Create alias in DB server on the name of the old sql server
  6. Create the alias in WFE with cliconfg.exe
  7. Start SharePoint Services in WFE

You are done. Open SharePoint central admin and it is up and running. Its cool, sql server in the WFE is stopped and it is now connected to the new DB cluster.

The whole process is simple and sleek. Do not fall in the trap of configuration wizard just by reading some articles.

Step1 – Stop SharePoint Services in WFE

Stop all the SharePoint related services. I was using SharePoint Foundation so below are the services. If you are using SharePoint Server, then yes you might look for more services to stop. But, these are the core services to stop. What ever the service you stopped, you need to start in the last step.

image

Step2 – Take backup of all SharePoint Databases

Go to Management Studio, select the database> right click> Tasks > Back Up.

Save the database to physical file.

image

Simple script I use rather than from GUI

backup database SharePoint_AdminContent_58dda40c-ed12-4e32-b4ef-31841103e81a To DISK = 'D:\Adi\DBBackup\SharePoint_Admin.bak' WITH CHECKSUM GO

SharePoint_AdminContent_XXXX – is the actual database name

SharePoint_Admin.bak is the backup file name. If you are looking more on how to backup to tapes and powershell script on backup

https://msdn.microsoft.com/en-us/library/ms187510.aspx – Create Full backup of database.

 

One more handy script is to copy all the permissions of the current user

https://support.microsoft.com/en-us/kb/918992 – Transfer logins between sql servers

 

Some times you don’t have Sql Server management studio and if you are using Sql server express then,  go the physical folder where the mdf, log files are saved for the SharePoint. Take the backup of these files

Files are typically located at

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Data\MSSQL10.SHAREPOINT\MSSQL\DATA

 

Step3 – Stop Sql Services in WFE

WFE should not access the DB in the Server any more. Go to Run>Services.msc

stop the SQL Server service, it also stop’s SQL Server Agent service

image

Step4 – Restore all database to new DB server

Go to the new DB Server

Go to management studio > right click  Databases> Click Restore Database

 

image

 

>In the popup select ‘From Device’

>Click elipses as in below diagram and add the backup file that is copied in the Step 3

image

 

Restore all the databases that are copied from old DB server. Make sure you set the permission to the copied database. If you have run transfer login script, it is quite quicker to give permission. But, it’s up to you

Step5 – Create alias in the new DB server

This is very crucial step to do

Old DB Server: MyDomain/DBServerOld

New DB Server: MyDomain/DBServerNew

We have to create alias so that, whenever application access ‘MyDomain/DBServerOld’ the new server ‘MyDomain/DBServerNew’ will be responding

How to do it

In the new DB Server, go to Sql Server Configuration Manager

 

image

 

Under SQL native client configuration, right click ‘Aliases’ and click ‘New Alias’

For Alias Name -  give  old server name

For Port No

(you can see what is the port number by selecting Network Configuration> Protocols for new server> IP Address Tab> IP All > TCP/IP. If empty give some custom port number)

 

Protocol – TCP/IP

Server – give new server name

image

So what does this means. Here onwards, if any server calls the name ‘MyDomain/OldServer/, then ‘MyDomain/NewServer’ responds. Isn’t it cool.

http://smallcitydesign.com/configure-sql-client-aliases/

Step6 – Create the alias in WFE with cliconfg

Now our DB is ready with SharePoint databases. We have to update the WFE to use the new DB server. One step away to enjoy the fresh coffee

In WFE server, go to Run>Cliconfg

It will open ‘SQL Server Client Network Utility’ window

Go to Alias tab

Select TCP/IP as in the screen shot

for Server alias Give ‘MyDomain/OldServer’

for Server name Give ‘MyDomain/NewServer’

give Port number (same as the one we have given in Step 5)

 

Once again what Server alias do –

If any server calls the name ‘MyDomain/Oldserver’, then ‘MyDomain/NewServer’ will respond.

Next time you want to change the DB server, you will just update Server name. This is the standard that needs to be followed especially for SharePoint.

image

I generally use UDL file to test my sql alias is working or not.

http://adicodes.com/basics-test-db-connection-with-udl/

 

Step7 – Start all the services

Start all the services that were stopped in Step1. We are ready and open the SharePoint central admin or any web application. It is loaded and running.

Nothing we have touched in SharePoint but just done what we have to do for moving database.

Conclusion

Well it took me 20 minutes to set up whole thing in Production. It took 1 hour 20 minutes to write this article :)

Once again, do not fall into trap of running configuration wizard when migrating SharePoint database in this case. There are lot of articles mentioning to run wizard and disconnect from farm which is not necessary at all. Happy SharePointing and hope this article helps you

 

Some handy links which are having quality info

http://smallcitydesign.com/configure-sql-client-aliases/ – how to setup client alias in SharePoint

http://adicodes.com/basics-test-db-connection-with-udl/ – how to test database connection / alias

http://www.codeproject.com/Articles/664029/Migrate-SharePoint-s-SQL-Server-to-another-SQL-Ser

– has good info but not suggested to follow all the steps as is, some unwanted steps are there

https://support.microsoft.com/en-us/kb/918992 – transfer logins and passwords between instances of SQL Server

https://technet.microsoft.com/en-us/library/cc512723(v=office.14).aspx – move all database msdn link. Not fully descriptive but it is good one

https://msdn.microsoft.com/en-us/library/ms187510.aspx – how to take full database backup

http://blogs.msdn.com/b/sowmyancs/archive/2012/08/06/install-amp-configure-sharepoint-2013-with-sql-client-alias.aspx – Install and configure sharepoint 2013 with client conifig. This is good one explains the importance of client config. Has details how to test the connection

https://technet.microsoft.com/en-us/library/ee748624(v=office.14).aspx – backup SharePoint,

some mitigation plan you have to do before touching the Production environment.

April 28, 2015 · Adi · One Comment
Tags: ,  · Posted in: migration, Sharepoint 2010

One Response

  1. sathish kasinathan - July 26, 2016

    Hi,

    I read your article its nice, As you mentioned here, don’t disconnect the server from farm and don’t run the configuration wizard, My question is if i change from oldSQLserver to newSQLserver In my central admin–>Manage servers in Farm is showing oldSQLserver only and all my Conent DB’s are showing connected with OLD DB… So how to check it in Central Admin

Leave a Reply

What is 13 + 5 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)