Monday, October 10, 2016

Upgrading from SQL 2008 R2 to SQL 2012 in SharePoint 2010 farm

We have recently upgraded to SQL 2012 in SharePoint 2010 farm for our client.
Below are the recommended steps for upgrading-

1. we can't directly upgrade from SQL 2008 to SQL 2012. we first need to install SP1 of SQL 2008 R2 and then upgrade to SQL 2012.
2.Side by side upgrade is recommended approach wherein we create a new DB server with upgraded version and then replace the new server with the old DB server.
3. Disconnect the existing DB server from the farm by running the SharePoint config wizard. Ensure to retrieve the pass-phrase before disconnecting the farm.
4. Connect to new DB server by re-running the config wizard. Here config wizard may fail on third step and may give below error (as happened in my case)-
"Configuration wizard cannot connect to configuration database. Object reference not set to an instance of an object."
To resolve this issue we shall use SQL alias on SharePoint server before connecting with new DB server. below are the steps-
  • run cliconfig.exe by double clicking it under C:\windows\system32 (C:\windows\sysWOW64 for 64 bit system)
  • On General tab -> Enable TCP/IP
  • On Alias tab > Click Add
  • Choose TCP/IP
  • On Server Alias > Give your Old SQL Server Name
  • Server Name > Give your New Server Name
  • Uncheck Dynamic > Choose the default Port Number 1433
  • Now run the config wizard to connect with new DB server
The config wizard should run successfully.
5. On Central admin site, navigate to servers on the farm page. This page might still be showing old DB server. The reason being, SharePoint retains reference of old server in its config DB. To resolve this issue, follow below steps in the config DB of the new server.



  •  select * from objects where name = ‘Sharepoint_Config’
    1. - You might get two rows. Check if the parent ID of both the entries are same.
    2. - One of the row will also have the parent ID equals to the ID of that row.
    3. - Note down the ID the other row where parent ID is not equal to the it’s own ID (ID1)
  •  Get ID of the new sql server (ID2)
    1.  - Select ID from objects where name =’newsqlservername’
  • Select ID from objects where parentid= ID2 (note down the ID = ID3)
  • Update objects set parentid=ID3 where ID=ID1. Try visiting Server in the farm page (FarmServers.aspx). you might still see the old server name.
  • Now restart your server and open the FarmServers.aspx page and it should now display the new server name. Refer this- http://www.sharepointtechs.com/moss-2007-showing-old-server-as-configuration-database-server  
6. Possibly the central admin content DB or may be your web application content DB might also be showing the old DB server. To resolve this, detach the content DB using PowerShell and then attach again with same content DB from new server.
Thats all. it should now be okay and the upgrade should work correctly.

Note- It should be noted that upgrading to SSRS 2012 will take away the Reporting services configuration manager and instead the configuration will be done by SharePoint service application for SSRS which automatically gets created once we upgrade to SSRS 2012.

2 comments:

  1. Hi Sir--Steps in the blog are at high level. If you could please have any detailed drafted steps please forward it to me. We are planning to perform this activity.

    ReplyDelete
  2. Please let me know where you need detailed steps? Once you will start upgrading and will have the context, you might get better idea. Try executing it. It should work. :)

    ReplyDelete