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
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
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.
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.
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'.
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.