Migrate SQL 2008R2 SP2 to SQL 2012 SP1 in SharePoint 2013

I have created three tier architecture of SharePoint 2013 with the following details (WFE1 + APP1 + SQL1) 

where 

SQL1 - Win 2008R2 + SQL 2008R2 Enterprise edition

WFE1 - SharePoint 2013 Web front end

APP1 - SharePoint 2013 Application Server (with extra service applications like State service, Word automatin service etc).

Tested with the following scenarios

Test 1: -- Failed

1. Created a VM of SQL 2 with SQL 2012 SP1 on Win 2008 r2

2. Disconnected the Farm on APP1 and WFE1 using SharePoint product configuration wizard.

3. Stopped all the services, detach and attach all the respective databases from SQL1 to SQL2.

4. Tried to attach the config database from the new server SQL 2, Failed. Doesn't allow to proceed at all.. 

Tried lots of workarounds, but really failed, then noticed the reason that while the configuration database is created, SharePoint hardcodes the SQL server details with the versions on the database records and in the internal registry of the application server.

Test2: -- Failed

1. Reverted to the fresh snapshot of SQL2 with fresh SQL 2012 SP1 install & WFE1, APP1 servers to the normal state.

2. Stopped all the services on the APP1 and WFE1

3. Take all the respective databases offline in SQL1, then detach and attach the databases to SQL2.

4. Create Client SQL alias in APP1 and WFE1 servers to point to SQL 2., iisreset

5. This test also failed, as it throwed the same errors like the above ( as expected, just wanted to try it out)

 

Test 3: Sucessful

1. Ensure to have passphrase and get all the APP, WFE server configuration details using powershell script/also hidden information like port details etc.

2. Disconnect the APP1 and WFE1 from the farm from SQL1

3. Then Create a new farm on the SQL2 server using psconfig. 

The above will create fresh SharePoint config, Admin content for central Administration databases.

4. Then move all the contents databases and service application databases and properly configure to respective web application and service applications.

That's the reason, why we need all the information to ensure everything is configured correctly.

Test 4: -- Successful

1. Recreated the same server of SQL 1 to SQL3 with the same fresh installs.

2. Stops all the services on the SharePoint APP1 and WFE1

3. Then detach and attach all the databases from the SQL1 to SQL3.

4. Created Client SQL alias in APP1 and WFE1 server to point from SQL1 to SQL3 server.

5. Then iisreset, Ensure everything works with the connections with SQL3.

6. Then stop all the services on the APP1 and WFE1.

7. Upgrade the SQL3 to SQL 2012 using upgrade wizard.

8. After installation completes,irestart all the servers

9. Ensure it works successfull. Tested, works fine

Although SharePoint 2013 is compatible with SQL 2008 R2 SP2 and SQl Server 2012, it doesn't like to detach and attach method for the configuration databases within the sql versions. So make a wiser choice of selecting the newest SQl version.