The following migration method for move database server to new SQL Server
Method 1:shutdown the farm and Detach / Attach SQL server database to new server with same name
Method 2: step by step procedure for moving database.
- Configuration database
- Central Administration content database
- Content databases
- Service application databases
- Nintex databases(need to check the documentation with Nintex)
1. / Stop all SharePoint and IIS Related Services
- SharePoint 2010 Administration
- SharePoint 2010 Timer
- SharePoint 2010 Tracing
- SharePoint 2010 User Code Host
- SharePoint 2010 VSS Writer
- SharePoint Foundation Search V4
- World Wide Web Publishing Service
- SharePoint Server Search 14
- Web Analytics Data Processing Service
- Web Analytics Web Service
2. / Stop IIS
iisreset /stop
3. / Detach all related SQL Server databases
- Configuration database
- Central Administration content database
- Content databases
- Service application databases
- Nintex databases
4. / Move all database files (.mdf, .ldf, and .ndf) to the new server.
Place it in the data folder of the SQL Server instance of the new host.
- In SQL Server, ensure that all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for the databases from the source server have also been configured correctly on the destination server.
- To set up permissions on the new server
- On the destination database server, open SQL Server Management Studio and transfer All logins and permissions from the original instance to the destination instance
5. / Attach your databases to the new SQL Server
6. / Verify what ports your New SQL is using
7. / Go to your SharePoint server and create\Edit your SQL Server Alias
- 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
- Use SQL Server connection aliases to point to the new database server, and then use the connection alias to update all Web servers. A connection alias is a defined alternate name that can be used to connect to an instance of SQL Server. You have to configure the alias on all Web servers and application servers in the farm.
8. / Test the SQL Server client alias
9. / Start all your SharePoint services, now you have successfully migrated your database.
- SharePoint 2010 Administration
- SharePoint 2010 Timer
- SharePoint 2010 Tracing
- SharePoint 2010 User Code Host
- SharePoint 2010 VSS Writer
- SharePoint Foundation Search V4
- World Wide Web Publishing Service
- SharePoint Server Search 14
- Web Analytics Data Processing Service
- Web Analytics Web Service
10. / Stop IIS
iisreset /stop
Check all site and workflow, Services…