The server that hosts the SQL database for our SharePoint server is out of warranty so I needed to migrate the database to a new SQL server. I performed the following steps to migrate the database from SQL 2008 R2 to SQL 2012 R2.
- Stopped all SharePoint and web services on our SharePoint server.
- Performed a backup of all of the SharePoint databases on the old SQL server (I had 24 dbs total, ugh). I know there’s a way to script detaching (especially with the amount of dbs I have) but I figured it would take me just as long to figure out the script as it would to just right-click Detach each one of them.
- Ensured that all of the accounts used on my old SQL Server were present in the Security > Logins on the new SQL.
- Ensured that SharePoint admin and Farm account are in the local Administrators group on the server. Not all people may need these accounts present but in order to get certain things to work in my environment, I had those accounts on the old SQL server.
- Detached all databases on the old SQL server.
- Stopped SQL on the old server so no confusion of whether or not SP connected to new or old db.
- Copied .mdf and .ldf files to the new SQL server and attached them using the Attach feature in SQL Mgmt Studio.
- I received no errors when attaching. (yay!)
- Created a SQL alias on the SharePoint server by running CLIConfg.exe using TCP/IP, server alias oldsqlserver and connection parameter server name newsqlserver with port 1433 defined.
- Restarted the old SQL server for a clean startup of all SharePoint and web services.
- After the SharePoint server was running smooth I launched Central Admin and also our SharePoint home page… received the following error “Cannot connect to the configuration database”.
- I was happy that I at least saw that message and that it wasn’t the dreaded page cannot be displayed. Found quite a few web pages referencing this error. Used this one as guidance.
- First thing I tried was turning off the Windows Firewall. I refreshed the page and voila! it worked. So then I re-enabled the firewall and added a new rule to allow TDCP port 1433 inbound. Refreshed the page again and it seemed to work great but just for good measure I did an iisreset, closed and reopened the browser to our SharePoint site and it still worked. Yay!
I hope your migration goes just as smooth!