Upgrading SQL Server 2012 RC0 to RTM for Sharepoint 2010

After attending Rod Colledge’s presentation where he touched on steps in setting up Sharepoint 2010 Business Intelligence with SQL Server 2012 at SQL Saturday 138 in Sydney, I’ve decided that to be brave and tinker with Sharepoint installation and configuration a little bit more.

Recently I have this requirement to upgrade the SQL Server 2012 RC0 installation on a Sharepoint server to SQL Server 2012 RTM version. I decided to do a bit of research and typing in “upgrade sql server 2012 rc0 to rtm sharepoint” on Bing returned a few promising results. One of them was Jen Underwood’s comment posted at http://social.msdn.microsoft.com/Forums/en-US/sqldensetup/thread/cd7287b6-f587-4a87-9676-75ddc9b09800.

The worst case scneario that I could think of is that I would need to rebuild the Sharepoint server. That’s OK because luckily we’re still in an eval mode and not in live production yet. So I did the upgrade. For a non Sharepoint savvy person, the whole process took me about 1.5 hours including copying the SQL Server 2012 iso file across the network.

The good news is, it was successful! (Gee, I never thought that it would be this easy!)

Before we go ahead, I’d like to point out a few things:

  1. At the end of the upgrade, the Sharepoint machine will have 2 installation paths of SQL Server for each instance, i.e. the existing SQL Server 2012 RC0 folder and SQL Server 2012 RTM folder. I am pedantic and try to avoid this as much as possible, but for now, it’s good enough; perhaps we may even be able to clean the folders up later. SQL Server 2012 Directories after Upgrade RC0 to RTM in Sharepoint 2010
  2. The target Sharepoint environment only has SQL Server installation specific for Sharepoint – i.e. SQL Server instances on the Sharepoint machine are not used by any other Applications (if they do, then make sure that they’re no live connection to the SQL Server instances).
  3. The Sharepoint server is not an Active Directory server.
  4. Test this upgrade method in a Test machine first before rolling out to Production.

 

Let’s get into it now.

[Warning: The below steps should be used as guidelines and should be tested in a Test machine first. If you are using a virtual machine for your Sharepoint server, don't forget to create a backup prior to upgrading]

 

Prerequisites

  1. Administrator previlege on the machine as well as Sharepoint.
  2. SQL Server 2012 RTM version – and a product key for the Business Intelligence or the Enterprise Edition.

Steps

  1.  Go to Sharepoint Central Administration > Manage Services on Server (under the System Settings heading). Stop the following services: SQL Server Analysis Services, SQL Server PowerPivot System Service, SQL Server Reporting Services Service.Stop Sharepoint 2010 Services related to the SQL Server 2012 items to be upgraded
  2. Run setup.exe of SQL Server 2012 RC0 and choose Maintenance.
  3. Select the “Edition Upgrade” option (although it only lists SQL Server 2005, SQL Server 2008 and SQL Server 2008R2 upgrade) which will show you the list of instances that can be upgraded.
  4. Select “POWERPIVOT” instance to be upgraded.SQLServer2012 Upgrade RC0 to RTM Select POWERPIVOT Instance
  5. Click Next to move on to the “Select Features” which will grey out all the features installed under POWERPIVOT.
  6. Click Next to “Reporting Services Sharepoint Mode” which presents a message that Continuing with this upgrade may put your SharePoint farm in an inconsistent state. Tick on the “Continue with the upgrade.” option.SQL Server 2012 Reporting Services Sharepoint Mode upgrade message
  7. Click Next to go to “Instance Configuration” which you will need to give a new name, e.g. POWERPIVOT2100

    SQL Server 2012 Upgrade RC0 to RTM Instance Configuration

  8. Click Next until you reach “Upgrade Rules”. An error may come up stating that ‘Rule “SQL Server Analysis Services Upgrade Service Funcitonal Check” failed. The current instance of the SQL Server Analysis Services service cannot be upgraded because the Analysis Services service is disabled or not online. Please start the service and then run the upgrade rules check again‘. If this happens, go to SQL Server Configuration Manager and you will see that the Analysis Server is Stopped. Change this from Disabled to Automatic. (This may be because I stopped the SQL Server Analysis Services Service from Sharepoint earlier on).

    SQL Server 2012 Upgrade Rules Error related to Analysis Services
    SQL Server 2012 SSAS POWERPIVOT Start Mode changed to Automatic

  9. Click OK on the error dialog box and click Re-run on the Upgrade Rules window. It should rerun the upgrade checks and progress on to “Update Progress”.
  10. In the middle of the upgrade of SQL Server Analysis Services Service, a similar error will come up. “The following error has occurred: The service cannot be started, because it is disabled or because it has no enabled devices associated to it“. Again, go to SQL Server Configuration Manager and change the start mode from Disabled to Automatic.
    SQL Server 2012 Upgrade RC0 to RTM Upgrade Progress Error
  11. Click retry and the installation should resume normally to completion.
  12. Once this is done, you can verify the servers installed on the Sharepoint Server by launching SQL Server Management Studio and connecting to different parts of POWERPIVOT SQL Server instances.
    SQL Server 2012 SSMS Instances

After the upgrade, don’t forget to reboot the Sharepoint server.

Post upgrade verifications

  1. Verify that your PowerPivot gallery or Default PowerPivot Service Application is running properly, by going to Sharepoint Central Administration > Manage Service Applications (under Application Management) > Default PowerPivot Service Application
  2. Verify that Microsoft.AnalysisServices.SharePoint.Integration.dll in Windows Assemby is referring to 11.0.2100.60 (right click on the dll, choose Properties and click on Version tab). More detailed info http://msdn.microsoft.com/en-us/library/ee210646(SQL.110).aspx#checkver
  3. Verify that msmdsrv.exe version is 11.0.21.00.60 in \Program Files\Microsoft SQL Server\MSAS11_00.PowerPivot\OLAP\bin (right click on the file, choose Properties and click on the Details tab). More detailed info http://msdn.microsoft.com/en-us/library/ee210646(SQL.110).aspx#checkver

 

And that is it! OK, I bet you can do this in less than 1.5 hours!

Hope you find this instruction useful. Please let me know if you have any suggestions on the steps that I may have missed out from the above.

 

Upcoming DirectQuery vs Vertipaq Presentation

How exciting is it for us SQL Professionals to have so many SQL Server events since the beginning of the year? We’ve had 12 Hours of SQL, 24 Hours of PASS, 24 Hours of PASS in Russian edition, SQL Server 2012 Virtual Launch, SQL Saturday (ANZ tour is currently running) and plenty other PASS virtual chapter sessions.
 
I’m quite honoured that I’ve been selected to present at SQL Server User Group in Sydney, 24 Hours of PASS, and SQL Saturday #138 as well as being picked by the Community to present at SQL Rally Dallas this year.

I’d like to focus a bit more on my upcoming SQL Saturday #138 session and SQL Rally Dallas session. The title is “DirectQuery vs Vertipaq Mode in SSAS Tabular Model”. This session will take you to a second step to see what’s beyond the default option (In-Memory / Vertipaq). 

Since the time I wrote the abstract for SQL Rally, Microsoft has rebranded Vertipaq to “xVelocity in-memory analytics engine (VertiPaq)”. Some of the project settings / options have also been changed to refer to “In-Memory”, instead of Vertipaq; while some remain as Vertipaq such as in the Tabular Model Analysis Server Properties. Despite the name changes, they mean the same thing in SSAS. 

The DirectQuery vs Vertipaq Mode in SSAS Tabular Model session brings a tiny step beyond your first leap to deciding/considering In-Memory Tabular mode. It concentrates on introducing DirectQuery and how different it is to In-Memory. The demo will also show how the two modes differ in query execution, design and maintenance aspects; giving you enough information to make an informed decision on which mode to use based on your business case. 

If you are new to Tabular Model or PowerPivot and would like to know more about it, there are quite a number of great resources to get you up to speed with it, as listed at the end of this post. Hopefully by then, you’d be comfortable in learning more in my upcoming “DirectQuery vs Vertipaq Mode in SSAS Tabular Model ” session. Having said that, if you’re completely new to Tabular Model but want to know what’s the fuss is about, come to the session – and have your first leap to learning Tabular Model with me. 

I have been having a great joy using Tabular Model in my current work, and am continuously extending my knowledge by preparing for this presentation. So I do hope that you can attend SQL Saturday #138 in Sydney or SQL Rally in Dallas and join me at the session. 

 

Reading/Watching List

Welcome to Tabular Projects

http://blogs.msdn.com/b/analysisservices/archive/2011/07/13/welcome-to-tabular-projects.aspx

 

Building your first Analysis Services Tabular BI Semantic model with SQL Server 2012

Speaker: Frederik Vandeputte

http://technet.microsoft.com/en-us/edge/building-your-first-analysis-services-tabular-bi-semantic-model-with-sql-server-2012

 

Building the Perfect BI Semantic Tabular Models for Power View

Speaker: Kasper De Jonge

http://technet.microsoft.com/en-us/edge/building-the-perfect-bi-semantic-tabular-models-for-power-view

 

Developing and Managing a Business Intelligence Semantic Model (BISM) in SQL Server Code Name “Denali” Analysis Services [BIA-316-M]

Speaker: Cathy Dumas

http://www.sqlpass.org/summit/2011/Speakers/CallForSpeakers/SessionDetail.aspx?sid=1964

Note: if you have the PASS Summit 2011 DVDs, pull this session out and start watching it. Cathy was superb in this presentation and I would consider this as an energetic presentation where I “ooo… aaaa…”-ed a few times!

SQLRally Dallas 2012 Website

 

 

Undo Bad Tabular Mode Analysis Server Properties

Warning: This post contains some advice that may not be ideal for Production / critical environment. Please read thoroughly and proceed any changes on your environment with caution.

Late last week, I decided to change a few settings on my development Tabular Model Server. The first change was related to the VertipaqPagingPolicy value in an attempt to understand the setting a little better. The second was related to changing the DataDir path.  Changing values of the Analysis Server properties can have adverse effects. It is best done only if you understand the implication AND if you have thoroughly tested it in a dev environment prior to making the changes in Production.

The changes I’m outlining below were done through the Analysis Server Properties dialog box retrieved by right clicking on the Tabular Model Server and choosing Properties as shown below.

 

Accessing Analysis Server Properties

This will give you a nice dialog box to edit the Analysis Server Properties:

Tabular Model Analysis Server Properties

Changing Analysis Server Properties usually require a server restart. Fingers crossed, the Analysis Server should restart properly if the configuration is OK.

Let’s talk about the changes I’ve made, why and how I got stuck!

Vertipaq Paging Policy

I have hit a number of errors in relation to Memory Alocation Failure on my dev machine. So, I thought I’d start playing around with the VertipaqPagingPolicy values and see if it could help solving the problem.

The exact error message on my dev box was:

The following system error occurred:  Insufficient quota to complete the requested service.

Memory error: Allocation failure. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

The current operation was cancelled because another operation in the transaction failed.

 

MSDN Books online describes three different values acceptable for VertipaqPagingPolicy.

http://msdn.microsoft.com/en-us/library/ms174514(v=SQL.110).aspx

  • Zero (0) is the default. No paging is allowed. If memory is insufficient, processing fails with an out-of-memory error.
  • 1 enables paging to disk using the operating system page file (pagefile.sys).
  • 2 enables paging to disk using memory-mapped files.

 

As BOL mentioned, when VertipaqPagingPolicy is set to 1 or 2, processing is less likely to fail due to memory constraints because the server will try to page to disk using the method specified.  So I decided to change VertipaqPagingPolicy to 1 then restarted the server. Performing Process Full on the partitions one at a time worked perfectly without error with VertipaqPagingPolicy = 1.

Setting VertipaqPagingPolicy to 1

Marco Russo has published more information on VertipaqPagingPolicy in his article titled “Memory Settings in Tabular Instances of Analysis Services“. I strongly recommend reading this article - may be even a few times.

DataDir

By default the DataDir location for Tabular Model server is located at:

C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data

This folder typically is a home to the files / folders that I consider as “system” oriented, and it may also have data files and folders of users’ Tabular Model databases. The “system” files/folder that you typically find in DataDir folder are:

  1. ExcelMDX.0.asm.xml
  2. EXCELXLTINTERNAL.0.asm.xml
  3. master.vmp
  4. VBAMDX.0.asm.xml
  5. VBAMDXINTERNAL.0.asm.xml
  6. ExcelMDX.0.asm
  7. EXCELXLTINTERNAL.0.asm
  8. VBAMDX.0.asm
  9. VBAMDXINTERNAL.0.asm
  10. CryptKey.bin

If you have accidentally changed the DataDir location by mistake and restarted the Analysis Services (Tabular Mode) server, it would come up with an error.

Error when Starting Analysis Server

As long as you know what the correct DataDir path and that the files are still in tact, don’t panic. The fix to this is fairly simple.

In the Config folder of the server (by default installed on C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config\), there is a file called “msmdsrv.ini”. This file contains the settings that will be read and used when the Analysis Server is started. So when the server doesn’t start due to incorrect settings, the easy approach is to correct the configuration settings in msmdsrv.ini file. After the settings are corrected accordingly in this file, the server can be restarted via SQL Server Configuration Manager and it should be able to start properly.

There are two options to correct the msmdsrv.ini file.

Option 1 – Manually fixing it, if you know what you’re doing.

In regards to my earlier case with accidental DataDir change, I changed the <DataDir> element on the second line of “msmdsrv.ini” back to the previous value, i.e.

<ConfigurationSettings>
  <DataDir>C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data</DataDir>
  <LogDir>...

 

As previously stated, I would not recommend changing settings without understanding the implications and prior testing. I would also like to suggest making a copy / backup of the msmdsrv.ini file BEFORE making the changes manually.

Another tip, when you try opening the “msmdsrv.ini” file and it may come up with the following error message.

Access Is Denied

This is fine and you can treat this as if it was a reminder to create a back up file of the file that you were about to change. So copy the file and call the new file as “msmdsrv_MessedUp.txt” and rename the original file to “msmdsrv.txt” then make the required correction. Then rename the “msmdsrv.txt” back to “msmdsrv.ini”.  Restart the server – and voila! - the Analysis Server should start if the new settings are good.

 

Option 2 – Restoring the “msmdsrv.ini” file from the “msmdsrv.bak” file.

In the same folder as “msmdsrv.ini”, there is also “msmdsrv.bak”. This bak file is a backup copy of the last known good configuration file to the Analysis Services server. So, you may be able to use this bak file to restore everything back to a previous good configuration.

 

Wrap up

As I’ve warned earlier, changes to msmdsrv.ini can result in an unstable state and/or non-startable Analysis Server. So, please explore this with caution. I am a firm believer of giving things a go and exploring options that are available, but always with caution.

Changing VertipaqPagingPolicy has helped me to work around the Memory Allocation Error issue on my dev machine temporarily (next step would be to acquire more powerful machine!). When mistakes are made in configuring the Analysis Server properties, it may cause the Analysis Server to be non-startable. In this case, the solution to it, may simply be reverting the “msmdsrv.ini” file back to its previous working state.

 

 

SQL Saturday #138 in Sydney

Finally! SQL Saturday will be in Sydney for the first time on 21 April 2012 from 08:15 to 17:15 at

Epping Boys High School (EBHS), 213 Vimiera Road, Eastwood, NSW, 2122, Australia.

 

There will be 4 DBA/Development Stream as well as 5 BI Streams. Keynote and Locknote will be presented by Grant Paisley, the president of SQL Server Usergroup Sydney.

 

The DBA/Dev sessions are:

09:30 Database Development & Agile: Strange Bedfellows by Daniel Nolan

13:00 The Dark Art of Performance Tuning by Peter Ward

15:15 Better Together: Merge, Partitioning, ColumnStore by Greg Low

15:45 Keeping the light’s on with SQL 2012 Always On by Warwick Rudd

 

The BI sessions are:

09:30 BI Architecture with SQL 2012 & SharePoint 2010 by Rod Colledge

11:00 Killer Real-World PowerPivot Examples II  by Grant Paisley

13:00 Accelerated BI Growth with Power View & PowerPivot  by Bhavik Merchant

15:15 Analytic T-SQL Functions in 2012 by Rob Farley

15:45 DirectQuery vs Vertipaq mode in SSAS Tabular Model  by Julie Koesmarno

 

As you can see, six of the sessions are related to the new SQL Server 2012 to follow up the recent RTM announcement in March 2012.

After registering for SQL Saturday #138, you can then build your schedule for the day by going to Schedule > Schedule Builder from the menu.

I will be presenting on the last session of the BI Stream, “DirectQuery vs Vertipaq mode in SSAS Tabular Model“. This session will explain the different modes of Tabular Model and how they work. The aim of the session is so that you are equipped with enough information to decide which mode of Tabular Model is suitable for your requirements and to confidently explore these choices.

Well, what are you waiting for? Sign up for SQL Saturday now and join me for SQL learn-athon!

SQL Saturday #138 in Sydney