Upgrading to SQL Server 2012 R2

 
This article will provide a step-by-step set of instructions with screenshots wherever possible of the upgrade of a non-clustered SQL 2008R2 server (with SSRS belonging to a SharePoint 2010 farm) to SQL Server 2012 R2. The entire SharePoint farm is on one server.
Hardware Prerequisites: Since this SQL server belongs to a SharePoint 2010 farm, I assume that it has all the required hardware.
Software Requirements: Requires SQL Server 2008R2 with SP1. Also requires the .Net 4.0 frame work. I will show screenshots of what happens if you do not have either installed. As mentioned before, since this is a part of a SharePoint 2010 farm, software will be 64-bit only. In any case you cannot upgrade a 32-bit SQL server to 64-bit.SQL server. A complete list of software and hardware requirements can be found here. There are some errors in this document which will be highlighted in this article.
1. Insert the SQL Server 2012 Installation CD or map a drive to the installation files and click on xxx. The following screen pops up.

 
2. Click on System Configuration Checker. A utility will start that checks the various components to see if there is anything that will prevent a successful upgrade.
One warning appears. Since this development  farm is running everything on a single server including AD, just press OK.
3. From the Installation Center screen that appears again select “Install Upgrade Advisor” and follow the prompts to install the product.

  4. Install Upgrade Advisor by accepting the defaults and clicking Next> and then Install as shown in the following two pictures.




ERROR 1. Here ere is where an error pops up – this should have been picked up by the System Configuration Checker that was run in step 1.

The Microsoft link shown above does not work. So Google (or Bing, or use your favorite search engine) and look for the KB. Download the missing component “Microsoft SQL Server 2012 Transact-SQL Scriptdom” Click OK aboveto cancel the Microsoft Sql Server 2012 Upgrade Advisor Setup and install the downloaded pre-req by clicking Next> on the install window




Error 2 pops up. After doing a lot of confused looking at files and permissions on my system and not finding the dll in question I finally found out that this error is caused because the .Net 4.0 framework has not been installed. If for some reason you have a feature on your SQL server that required the .Net 4.0 framework prior to the upgrade and you installed the framework this error should not come up.

Click on Cancel, install the ,Net 4.0 framework, reboot the server and run the install the Upgrade advisor again. On successful completion, click the Finish button.

5. Run the Upgrade Advisor

On the Upgrade Advisor Screen, click on the link “Launch the Upgrade Advisor Analysis Wizard


Click Next> on the Welcome Screen

Select the items you want checked and click Next>. You will notice that two items have not been checked. Go ahead and try checking them. You will be informed that Notification Services (or Data Transformation Services) have been removed in SQL 2012.


On the Connection Parameters screen, select the SQL instance you want to check and click Next>

The SQL server parameters are brought up as shown, Scroll down the database list and you will see all your SharePoint databases. click Next>



The Reporting Services Parameters will be brought up (only if reporting services is running on the same server. If it is running on a different server the Upgrade Advisor has to be run on that server). Click Next>

The Analysis Service Parameters are brought up. Click Next>

The SQL Server Integration Services Parameters dialog box displays which allows you to select the SSIS packages if they are kept at a location different from the default location and to provide a password if the SSIS packages are password protected. Make your selections and click on Next>

Finally the dialog box confirming what you are about to do pops up and you can look at all the settings and if they appear to be in order, click Run

The upgrade advisor runs and on completion shows the following screen where you notice a warning. Click on the Launch Report Button to look at the upgrade advisor report. [You could also click on close and launch the report by clicking on the second link that is shown in the 2nd picture at the start of this process (item #5)

Strangely, no warnings are shown in the Report. Lets look at the report in detail. Click on Open Report



The report shows the warnings. You are also informed that these changes have to be implemented after the upgrade.

Clicking on the link shown above brings a list of affected objects. Make a note of these objects and close all the windows.

6. Finally, Upgrade SQL Server. Click on the Installation option on the top left and then on the screen that shows up select the Upgrade option


The setup rules will run. If all the rules pass, click on OK

 

Enter the product key and click Next>


Accept the License terms and click Next>



Check Include SQL Server product updates. As you can see SP1 is out so when you click Next> only the changed binaries will be downloaded and installed.

Setup Rules are run. A warning is displayed as I have everything running on one server. Click on Next>


Choose the SQL instance you want to upgrade using the dropdown



You are presented with a dialog box where you cannot make any changes, just look to see that all the items you want are there and click on Next>

Click on OK to upgrade on the next screen and the upgrade runs. After completion the following window appears

Click Close. However you are not done yet. You have to run the upgrade for each instance of SQL server. In this case, the Report Server is also an instance that has to be upgraded. I am not going to go detail the whole process again just show a few screens.
When the screen to select an instance to upgrade appears, notice that they build numbers on SQL Server and Report Server and their respective shared services are different. That is because the Report Server (Power Pivot) has not yet been upgraded. Select Report Server from the drop down and click Next>



The upgrade rules run. When they complete, click Next> and then Upgrade to run the upgrade

When complete , click Close and you are done

Remember to go into SQL 2012 and make any additional changes that are required from the last screen shot in item #5. Since I did not require them I did not make the changes. I may make the changes in future in which case I will update this document.
Note: This upgrade was done with SharePoint Server in read only mode and the search services stopped so that the databases would not change during the upgrade.
____________________________________________________________________________