Lately I had to test the best approach to migrate SharePoint databases including the configuration database to a brand new SQL Database Server, and make sure everything is back in order. The following expresses my approach and the steps involved in a safe and sound migration.

Although there are various blogs and articles available out there, Unfortunately it became very cumbersome to manage and my intention was to keep it as simple as possible..

Assets.

Small to Medium farm contains 1 Application server, 2 frontends and 1 SQL Server (Just to keep it simple).

Topology.

Requirement.

1. Commission a new SQL Database Server (DB02) and
2. De-Commission the current SQL Database Server (DB01)

Reason.

Any valid reason in relation to performance, Network Issues and growing too fast and too quickly etc etc. In my case moving up to failover SQL clusters from single server scenario..

Process.

- Backup the entire MOSS farm using STSADM command line tool or the central administration web interface. This step is just in case anything goes wrong.

Make sure the backup is validated and tested.

This backed up information is not used in the following steps

- Backup all SharePoint related Databases from source database server (DB01) using standard TSQL Statements.

Script Example:

BACKUP DATABASE [dbname] TO  DISK = N'T:\Backup\Dbs\dbname.Bak' WITH NOFORMAT, NOINIT,  NAME = N'dbname-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

- Generate the SQL Scripts for User creation ( domain user accounts and domain service accounts ). SQL Logins require additional operations that where not tested through this process. 

Script Example:

CREATE LOGIN [domain\user] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

- Restore SQL Database Backup files to the new Database server (DB02)

Script Example:

Restore DATABASE [dbname] FROM  DISK = N'D:\Backup\DBs\dbname.Bak' WITH  FILE = 1,  MOVE N'dbnameLN' TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbname.mdf',  MOVE N'dbnameLN_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\dbname_log.LDF',  NOUNLOAD,  STATS = 10

- Execute the account creation script on the new Database server (DB02) (script generated from earlier step)

- Stop all SharePoint Services on all the servers including the application servers.

Example: In my scenario I run the following commands

On the application server

Net Stop SPTimerV3
Net Stop SPSearch
Net Stop OSearch
Net Stop SPAdmin
Net Stop SPTrace

On the web front end server

Net Stop SPTimerV3
Net Stop SPAdmin
Net Stop SPTrace

- Stop IIS, including all dependency services. Its easier to stop the IISAdmin service which in turn stops all dependency services.

Example: On the application server and web front end server(s)

IISReset /stop

- Run the STSADM Command to Rename the Server. This command needs to be executed on all the servers (application and front end).

Example:

Set PATH=%PATH%;{12 hive}\bin;

STSADM.exe -o RenameServer -OldServerName DB01 -NewServerName DB02

Watch for any abnormal messages and errors. Mostly the message will guide you what to do.

- Finally either

1. restart the SharePoint web front end servers including the application servers or;

2. restart all the stopped services (SharePoint and IIS) manually in a orderly fashion based on the dependencies.

Example:

On the application server

IISReset /start

Net Start SPTrace
Net Start SPAdmin
Net Start SPSearch
Net Start OSearch
Net Start SPTimerV3

On the web front end server

IISReset /start

Net Start SPTrace
Net Start SPAdmin
Net Start SPTimerV3

- All done…Now you should have your farm pointing to the new database servers, Just to make sure there are no other side effects monitor the event logs.

Test.

Go to the Central admin UI > Operations > Servers in farm, you should now see the new server displayed.

References.

http://technet.microsoft.com/en-us/library/cc512725.aspx

Note.

This process will not work effectively on a single/standalone server scenario.