How do you upgrade your SQL Server that is hosting SharePoint databases?

A common scenario is a SharePoint 2010 farm using SQL 2008 R2 or SQL 2012. Their is requirement to upgrade to a newer version of SQL Server.

There are a few questions you need to answer before doing the upgrade:

• What SharePoint build are you running? (SharePoint Build Numbers)
• What version and edition of SQL are you currently running? (SQLserver Build Numbers)
• What build number is supported by the new SQL Server version?
• Are you running SSRS integrated mode?
• How big are your SharePoint databases?
• What name will the new server have?

The process for moving to a new SQL server has the following steps:

1. Install SharePoint cumulative updates
2. Install SQL Server
3. Make the SharePoint databases read only on your old server
4. Backup databases on the old server
5. Restore to the new SQL Server
6. Change the databases to writable on the new SQL server
7. Update the SQL Alias on the SharePoint servers (see below)
8. IIS Reset on all SharePoint hosts

Configuring a SQL Alias

On the SharePoint Servers running a Command prompt (CMD) as Administrator

Run CLICONFG.EXE

If there is an existing SQL Alias, simply change the “Server Name” in the connection parameters to the new server.

If no SQL Alias exists, the you can create an Alias using the Old SQL Servers name and point it to the new server.

If the existing SQL Server is going to remain online for a period of time, you can also add an entry into the Windows HOSTS file, with the new SQLserver IP address and the old SQLserver name (Windows uses the hosts file to resolve the name before checking DNS).

NOTE: Other services accessing SQLserver from the SharePoint servers will also use the Alias.