Monday, January 4, 2016

How to Move SharePoint Databases to a New SQL Server

Requirement: Move SharePoint databases to new SQL Server! You'll have to move SharePoint databases including all content databases, configuration databases and service application databases to an another SQL Server at least in a couple of circumstances:

  • Upgrading SQL Server to new version. E.g. Upgrading SQL  Server 2012 to SQL Server 2016. 
  • Upgrading Servers to new hardware, moving servers from data centers, etc.
  • Moving from single standalone server instances to highly available cluster servers - AlwaysOn 
Solution:
In a nutshell, Moving SharePoint database from one server to another can be achieved with following steps:
  • Step 1: Stop All SharePoint Services:
  • Step 2: Backup & Restore All SharePoint Databases to the new SQL Server
  • Step 3: Change the SQL Alias to Point to the new SQL Server
  • Step 4: Start All SharePoint Services
Step 1: Stop All SharePoint Services:
To start with moving SharePoint databases to new server, Stop all SharePoint related services on all SharePoint servers of the farm, So that no connection are made to SQL server during the migration. Use this PowerShell script to stop all SharePoint 2013 services: How to Stop all SharePoint 2013 Services using PowerShell

Step 2: Backup & Restore All SharePoint Databases to the new SQL Server
Use the PowerShell cmdlet "Get-SPDatabase" to get all SharePoint databases and make a note of them. Proceed with backup each database.

Backup SharePoint Databases in SQL Server: 
  • Go to SQL Server Management Studio >> Right click on the relevant SharePoint database >> Tasks > Back Up.
  • Provide necessary inputs to the backup dialog window and complete backup for all SharePoint databases.
Restore SharePoint Database backups to the New database server: 
You have to make sure your Farm access account & service accounts have access to the target SQL Server! so before restore, Transfer all Logins from old server to new server: https://support.microsoft.com/en-us/kb/918992. This is important as the user access mappings are within the backup we made and should be restored.
  • I'm assuming SQL Server is installed on your new SQL Server, Login to your new SQL Server Database Server, Transfer all Backup files from Old SQL Server to your new SQL Server.
  • Go to SQL Server management studio >> Right click on Databases node > Restore Database >> Pick the database backup. Repeat this step for all database backups.
Other than backup-restore, You can also use copy database wizard to copy databases from one SQL server to another. Right click the source database from SSMS >> Tasks >> Copy Database >> Run through the Wizard to copy database between SQL Servers. Alternatively, you can detach and attach to move SQL Databases between servers.
Its a best practice to use SQL Alias to connect SharePoint to SQL Server!

Step 3: Create/Change the SQL Alias to Point to the new SQL Server 
Once SharePoint databases are restored into the target SQL server, the next step is to create or change SQL Server Alias on All SharePoint Servers of the Farm. Basically, we will redirect from the original SQL server to the new SQL server.
  • Open the cliconfg.exe tool >> Under Alias tab, add a new alias or change existing alias for your SharePoint farm (If your SharePoint farm uses SQL Alias already).  
  • In my case: My old SQL Server is 'AzSvrV18' and I want to move all Databases to 'SP16-SQL'. After restoring all SharePoint Databases on SP16-SQL, I created an alias on all SharePoint servers to make 'AzSvrV18' point to 'SP16-SQL'.
    Move SharePoint database from one server to another
Here, we have created an alias for the new SQL instance and gave this alias the same name as the current instance of SQL Server that the SharePoint databases are hosted. Please note, alias can only be configured after the original SQL instance is stopped or Turned OFF to avoid any conflicts. Refer this article to configure SQL alias for SharePoint: How to Create SQL Server Alias for SharePoint

Step 4: Start All SharePoint Services 
Start all the services that we stopped. Use the article at Step-1 to start all SharePoint services.

That's it! We have moved SharePoint database to new server. Pretty straight forward, huh! Next time you want to change your Database server, Just update the Server name in SQL Alias! No need to run SharePoint configuration wizard and disconnect from farm and so on! You can use this approach for other requirements such as: move SharePoint database from express to SQL server, move SharePoint database from express to standard. Technet article on moving SharePoint 2013 databases: https://technet.microsoft.com/en-us/library/cc512725.aspx

Related post: Move SharePoint 2013 database to another drive



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

No comments :

Post a Comment

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...