Upgrading and Moving Integrated Mode Reporting Services With SharePoint

Dec 192011
 

There is a plethora of instructions out there on upgrading from SharePoint 2007 to SharePoint 2010, but relatively little on doing the upgrade where Reporting Services has been set up in SharePoint integrated mode. Given that there are a few gotchas that you can run into when doing this, I decided to put together this step-by-step, complete with the gotchas.

The most common scenario that will be encountered, given the vintages of the products will be an RS upgrade from SQL Server 2005 to 2008 R2. In addition, the in place upgrade is relatively painless (in the short term….) so I’ll be walking through a DB attach upgrade, which is just as applicable to RS as it is to SharePoint. Finally as I’ve written about previously, in a small farm, it’s likely a better idea to add the Reporting Services bits to a SharePoint front end server, than to add the SQL server to the SharePoint farm, and that will also be a part of our scenario.

The first question to answer is “why bother”? One of the advantages to using RS in SharePoint Integrated mode is that unlike Native mode, the reports, data connections and models are stored directly in SharePoint. It is therefore possible to just create a new RS database, and move forward. However, since subscriptions, schedules, and cache profiles are still in the database, it’s likely worth it to do the upgrade.

Step 1 – Back Up The Asymmetric Key

Reporting Services itself uses 2 SQL databases. One of the databases is for temporary operations, but the other database stores a number of important, and sensitive items for this reason, all sensitive items in the database are encrypted with a key. If we want to get access to these items, we need the key. To do so, we need to back it up from the source server before we move ahead.

Run the Reporting Services Configuration Manager on the source RS server, and select “Encryption Keys”. Click the Backup button, select (and remember) a password, and then save the key to the file system. 

image

Once saved, copy the key file to the destination RS server (likely your SharePoint 2010 front end server).

Step 2 – Back Up the Reporting Services Databases

Run SQL Server Management Studio on the Server where the Reporting Services databases are located. Run full backups of the two RS databases. When complete, copy the backups to the destination SQL server (likely the server that will host the SharePoint 2010 databases).

image

Step 3 – Restore the Reporting Services Databases

Using SSMS, restore the two databases to the host SQL server. Once restored, it’s likely a good idea to set the recovery model to Simple, and the Compatibility level to SQL Server 2008. These steps aren’t required, but are recommended, unless you have a reason for not doing so.

image

Step 4 – Run the RS Configuration

If Reporting Services hasn’t yet been installed on the SharePoint server, do so, otherwise, proceed to configuration by running the Reporting Services Configuration Wizard on the destination RS Server. Configure the basic steps, and then when it comes to Database configuration, select the option to choose an existing database.

image

Select the server where you restored the files in step 3, and select the primary RS database (the one without the word “Temp” in it”).

image

Complete the configuration wizard.

Step 5 – Connect SharePoint to Reporting Services

From SharePoint Central Administration, select General Application Settings, and then Reporting Services Integration.

image

Complete  the integration configuration, and then select OK

image

So far so good – now we’re ready for some gotchas. If you now click on the “Set Server Defaults” link in the Reporting Services section, you likely get a rather nasty looking error. You’ll also experience this error if you access ewither of the two RS URLs defined in the RS configuration wizard. The error is:

The report server installation is not initialized. (rsReportServerNotActivated)

This error happens when the server can’t access configuration information, and the most common cause of that is that it can’t decrypt the content. In our case, it can’t because we haven’t yet restored our key.

Step 6 – Restore the Asymmetric Key

On our new RS server, we need to run the RS Configuration Manager, Select Encryption Keys, and then click the Restore button. You will be prompted for the file that you created and copied in Step 1, and this is where remembering the password comes in very handy.

image

Once this is done, we can close the configuration manager and return to Central Administration. However, now when we try to access access any aspect of RS we get a new error:

The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)

The reason for this error is that when we restored the key, it added an entry in the Keys table in the Reporting Services database, causing RS to think that we’re using multiple Reporting Services servers. This is what’s known as a scale-out deployment, and is only supported in the Enterprise version of Reporting Services. Obviously this isn’t a problem for anyone running Enterprise, but if not, it’s a showstopper.

The way to fix this is to remove the old server entry in the Keys table. Using SQL Server Management Studio, connect to the Reporting Services database, and open the dbo.Keys table. The old entry should be easy to spot as it will have the old server name. Simple delete the row.

image

Once the offending entry is deleted, RS should be good to go.

7. Fix up the content type names

I have posted about this already, but often, an upgrade will break the Content Type names for the Reporting Services content types. Just follow the steps in this post to clean them up.

8. Reconnect Reports with Data Sources And/Or Republish

In addition, moving connection files and reports around in SharePoint can cause them to be disconnected from each other, or for the connection files to be disabled. It’s a good idea to navigate to all of your reports to make sure that they are connected, or better yet, to republish from the source if you had previously used BIDS to publish reports.

SharePoint 2010 Upgrade breaks Microsoft Access Client Application

Sep 162011
 

I have been doing quite a few 2007-2010 upgrades lately, and suffering the appropriate slings and arrows. A recent upgrade resulted in a few issues, the strangest one was that Microsoft Access could no longer open a SharePoint list.

For quite some time now, Microsoft has been able to read and write data from SharePoint lists as if they were active Access tables. This is distinct from Access Services, which ships with the Enterprise version of SharePoint Server. Access Services lets you “convert” your entire Access application to a SharePoint site at which point the Access client is no longer required (for a user).

Our situation was much simpler. We were dealing with a Power user that was good with Access, and had leveraged the list read/write capability quite heavily with 2007. However, after the upgrade, Access 2007 couldn’t open some of the lists that it could previously. Compounding this problem was that Access 2010 didn’t have this problem on the lists in question. The browser could also open these lists just fine.

The answer to this one came from what appeared to be a different problem. Some of the other lists in the site couldn’t be opened by the browser. Instead, the user received the message “The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator”:

image

SharePoint has a bad reputation for “Unknown Error” messages, but this one is really quite good. This one pointed squarely at the list throttling features available in SharePoint 2010 that of course weren’t there in 2007. Basically, 2010 allows an administrator to throttle, or prevent poorly performing functions from slowing down the system for everyone. One such expensive operation is performing lookups, and the default limit is set to 8.

Dina Ayoub has a good post here on the throttling features if you would like to learn more, but the important thing to note here is that this setting affects not just lookup fields, but Person/Group and Workflow Status fields as well, so if you have 8 or more of them, the list will simply stop working.

This setting is scoped to the application level, so if it is changed, you affect all site collections in that application. (It also means that you can’t change it at all in Office 365.) You set it through the Resource Throttling settings in Central Administration. Once in CA, click on Application Management, highlight the application to be changed, and in the General Settings dropdown, pick Resource Throttling.

image

Scroll down to the section titled “List View Lookup Threshold”:

image

Here, you can simply increase its value to where you need it.

Changing the values should be done with considerable care. These throttling features were implemented for very good reasons, and changing them risks overloading your SQL server. A much better approach would be to go back and rethink the design of your list, if that’s an option. If it isn’t then this is a decent plan B. You can always buy more hardware…….

So this fixed our post upgrade list issue in the browser, how does this relate to our Access problem? Well, it turns out that they were one and the same, just manifesting differently. It seems that Access does something when it opens a list that adds a few more lookup type items to the Query, or at least it behaves that way. It also appears that Access 2010 and Access 2007 behave differently in this regard. In the end, increasing this value sufficiently solved the Access problems.

I haven’t found anything definitive out there, but anecdotally at least, you should be aware that when you use Access to open up a SharePoint list, you pay a “”List View Lookup Threshold” penalty.

SharePoint Upgrade Failure – Orphaned Documents not Orphaned Lists

Aug 312011
 

Upgrading to SharePoint 2010 from 2007 is well worthwhile, and is significantly easier than the upgrade from 2003 to 2007 was. With that said, there are a number of things to look out for, and a number of bumps along the way. Running the stsadm –o preupgradecheck on your SharePoint 2007 farm identifies a number of the potential pitfalls and alerts you to their dangers.

Recently, I was at a client site doing a database attach upgrade, and we ran into a warning about orphaned objects. Orphaned objects will cause the upgrade to fail, so it needs to be remedied prior to the upgrade.

Orphaned objects are items that exist in the database, but aren’t properly connected to anything in the site collection. Orphaned objects are typically sites or lists. Joel Oleson has a good article on deleting orphaned sites, Orphaned lists are trickier with much of the guidance pointing to removing and then re-adding the content database. Also there is an stsadm command that should repair orphaned objects. The syntax is:

stsadm –o databaserepair –url yoururl –databasename contentDB –deletecorruption

Using the –deletecorruption directive goes ahead and fixes the problem, without it, it just tells you where the problem lies.

Unfortunately, nothing worked in my case. Running databaserepair simply resulted in a return that looked like the following:

<OrphanedObjects Count="1">
  <Orphan Type="SPList" Id="{787A6375-ABA3-4475-AE64-230853EB4448}" SiteId="{13AB0F9E-386B-4128-916C-E70BFC6A45F3}" />
</OrphanedObjects>

This discussion (in which the response marked as an answer isn’t the answer) got me pointed in the right direction. I am loathe to do anything directly in the content database, but if I had an orphan, I could at least find it there. I therefore opened up the AllLists table in the content database and did a SQL search for my list, like:

select * from AllLists Where tp_ID=’787A6375-ABA3-4475-AE64-230853EB4448’

Unfortunately, I got no results. This was baffling.

I had the GUID of the offending list, but it didn’t exist in the list table. I started rooting around elsewhere. It wasn’t too long before I found a reference to it in the AllDocs table, in the ListId column. To me, that meant that I didn’t have an orphaned list, but an orphaned document that was referring to a non-existent list..

After determining that the document was in fact disposable, I deleted its record with some simple SQL

DELETE FROM AllDocs Where ListID = ‘787A6375-ABA3-4475-AE64-230853EB4448’

Once that was done, I ran the preupgrade check, and all was clear – no more orphaned objects. We could proceed to the next roadblock… (more on that later).

This solution worked in my case, but as it involves monkeying with the content database, use at your own risk, and whatever you do – have a backup of the database available!

Confusion When Upgrading from SharePoint 2007 to SharePoint 2010 In Place

Apr 302010
 

When You install SharePoint 2010 on a server that already has SharePoint 2007 installed on it, it will install all of the 2010 bits on that server in a side-by-side fashion with the 2007 bits. This is a good thing, for all sorts of reasons.  However, it can be the source of some confusion, and at least once already in my case, an opportunity for slip ups.

After you run the new SharePoint Products Configuration Wizard (note the new lack of “And Technologies” in the name), you will have converted your farm, which is all of your databases. However, you still have access to all of the code from the 2007 install. This includes the SharePoint Products and Technologies Configuration wizard from the 2007 install.

This can be a real problem, particularly if you have always used a shortcut to run it, and you expect the shortcut to be upgraded. It won’t be… Take care that you’re running the right tool. It’s now installed off the start menu in the “Microsoft SharePoint 2010 Products” folder, and its name is slightly different,“SharePoint 2010 Products Configuration Wizard”. The chrome around it is slightly different,but not enough to tell, but one visual clue that you’ll get while inside the wizard is that the prompt that warns you about services restarting has “v4” in most of the Service names.

Required SQL Server Versions and Patch Levels for SharePoint 2010

Apr 282010
 

Microsoft has been very clear about the requirements for installing SharePoint 2010. The biggest thing in this release is that it’s 64 bit only, not just on the Operating System side, but also in the SQL Server requirements. In addition, it’s also quite fussy about which versions it supports.

On the operating system side, a complete list can be found here. On the SQL Server side, it’s generally thought that it supports only SQL Server 2008 and above. However, this isn’t true – it supports SQL Server 2005 – provided that it’s 64 bit mode. However, the devil is in the details. It’s very specific about the patch level that you’re running. Glenn Berry has a list of the supported versions, with patch levels, and the SQL script for determining your precise version levels. There are only 3, so I’ll repeat them below:

  • SQL Server 2005 SP3 CU3 (Build 4220) or greater
  • SQL Server 2008 SP1 CU2 (Build 2714) or greater
  • SQL Server 2008 R2

The kicker is that neither the SP3 level of 2005, nor the SP1 level of 2008 will cut it,and you will have a failure if you do not conform to these versions. This failure will not appear until you run the SharePoint Products configuration wizard,which is pretty much past the point of commitment. In addition, the stsam.exe –o preupgradecheck command on a 2007 farm does not appear to detect this deficiency (at least it hasn’t yet in my experience).

Do yourself a favour, and patch up your SQL servers before you start your install/upgrade. The most recent cumulative update packages, as of this writing are:

I’ve used CU7 for 2008 on my installs thus far, and can attest that it works. I’m still waiting, hopefully not long now, for 2008 R2…

 Older Entries