Moving / Migrating Microsoft SharePoint Server Databases from one server to another might be difficult as you need to reconfigure all the related DB(s) to point at the new server. This might be acceptable if we are talking about the SharePoint Content Databases, but how about the below scenarios:
Scenario # 1:
Moving the SharePoint Configuration DB(s) or the SharePoint Service Application DB(s)?
Scenario # 2:
Lets consider another scenario, where you want to move the SharePoint DB(s) from a Single Standalone SQL Server to a Highly Available Cluster Server, or maybe even configure it to use on of the new features such as SQL Server AlwaysOn.
The answer to the above scenarios might not be as straight forward as it seems, as you might need to reconfigure each DB & Service Separately, so how about suggesting the use of SQL Server Aliases as it could be consider a good starting point for both of the above scenario.
To start take a backup of the SharePoint Server DB(s) that you want to move, this can be easily done using Microsoft SQL Server Management Studio. After the backup is complete restore the Database(s) on the new server.
- More information about SQL Server DB Backup & Restore can be found here.
- This approach will not migrate SQL Server other components such as SSRS or SSAS ... etc. however it could be considered a good start to the migration process.
- After Defining the SQL Sever Alias the Database on the Source Server will not be accessible and can be removed.
After successfully restoring the SharePoint DB(s) we need to start configuring the SQL Server Alias. To achieve this goal we need to do the following steps on All the SharePoint Servers in the Farm; such as WFE, APPS ...etc.:
Go to Start >> Run >> Type CliConfg.exe and the below screen will appear:
Select the Alias Tab and click Add:
Select TCP/IP from the Network Libraries and Enter the following Values:
Server Alias: This is the Old SQL Server Network Name
Server Name: This is the New SQL Server Name Network Name
- In Case of SQL Server FCI then this could be the cluster name
- In Case of SQL Server AlwaysOn AG this could be the SQL Listener Name
Port Number (if needed)
And that's its, you will see the new SQL Server Alias added as shown and you are ready to start using the new Database.
Don't forget to apply these changes to all the SharePoint Servers in the farm. hope you found it useful
Below are some useful references that provides more information about the items mentioned in the above post: