Collabshow.com

Date: 18th August 2011 – 12 noon EST

I’m doing a free webcast for lightning tools. I hope you can attend. I recently worked with our collaboration team on a big upgrade and I’m interested to share the lessons learned. You can get more information on the session on the Lightning tools webcasts page.

Now that SP1 is out what how does this impact your upgrade strategies.  What does SP1 offer over RTM?  The reality of upgrade is in the strategy of how you execute your binary upgrade, but don’t underestimate the strategy around your visual upgrade.  In this session I’ll give you recent examples of an enterprise upgrade of 1TB of data with 50 databases and share our lessons learned from custom workflows, site templates, and dealing with the harsh realities of the Fab 40.

https://www3.gotomeeting.com/register/172141918

You heard that SharePoint now supports super mega sized lists and massive 4TB databases and even higher. Make sure you read this before you start creating really large lists. You need to know what you’re walking into before creating one massive document library. If you’ve already got large lists, hopefully you’re aware of the features that can help you scale and have a better experience.

After an upgrade, or after time goes on with any SharePoint 2010 environment you’re bound to run into the great features of SharePoint 2010… list thresholds. Users may not think it so great. When we upgraded we found 83 lists over the default limit of 5000 in a list view. We did some of our own testing and found that we could support 15,000 items in a list view without the delay being too great and without impacting the server too much. We had to increase our default as we found very little to help out calendars (tip: in the default list make the default view based on date range), and especially things like workflow history (which could be truncated or at least trimmed), but we found a lot of archives, and even a few doc libraries on their way to greater growth.

Out of 50 lists over 7000 items here’s the breakdown: 21 archive type lists, 7 workflow history lists, 6 somewhat transactional lists, 7 _catalogs/users lists, 4 data import lists, 3 doc libraries, 2 calendars

Required Reading for Devs and Admins, Power Users may still gain value out of reading this. It is *really* good info: Working with Large Lists in SharePoint 2010: List throttling by Steve Peschka

Below is a screenshot where you can see where the list view threshold is set. During upgrade we had a temporary window of the week following upgrade where we’ve been supporting 50,000. With so many lists over the threshold it was still 24 lists over 50,000 that we needed to work with on a case by case basis. Sounds easy to just increase this default right? Well, in the beginning you may not see much impact, but then as the list usage gets higher than the threshold may matter a lot more. The Microsoft paper "Designing Large Lists and Maximizing List Performance" strongly recommends not changing the default. You are "strongly recommended not to change this default. If poor performing queries are used on lists with more than 5,000 items, overall throughput may significantly decrease when raising this limit." It goes on to say "Operations, such as creating an index on a list that is over this limit, are prevented because the operation affects more than 5,000 items. This limit prevents the queries that have a selectivity (items that can be efficiently filtered using filter criteria) more than 5,000 items. This limit also prevents queries that filter on columns that are not indexed. This is because a query that filters (and in some cases sorts) on a column that is not indexed must perform the filter on all items in the list in order to retrieve the correct data set and will operate on more items than the list view threshold. The default value for this limit is based on farm and list performance and how SQL Server manages locks."

The document as well covers what doesn’t work when you’ve exceeded threshold:

What Fails to work well when you’re over the threshold

• Add/Remove/Update a list column
• Add/Remove/Update a List Content Type
• Create/Remove an Index
• Manage files which have no checked in version
• Non-indexed recursive queries
• Cross list query – Note: Includes queries by the content query Web Part and follows the list view threshold setting for auditors and administrators, which by default is 20,000. If the operation involves more than 20,000 items, the query fails.
• Lookup columns that enforce relationship behavior
• Deleting a list
• Deleting a site
• Save List as Template with Data
• Showing Totals in List Views
• Enable/disable attachments in a list
• Explorer view (see more detail below)
• List and Folder Permissions

Below: SharePoint Administrators can change the list view threshold which is configurable per web application with a default of 5,000

Within the list the threshold is visible when you start to grow. This comparison of used vs. the max limit is similar to the storage manager feature where you can see how you compare.

Here’s a list of walk through videos and from the help and how to videos and a handy quick reference

SharePoint lists: Techniques for managing large lists

• Creating and Using an Index for Organizing a Large list – An index will allow you to perform not only faster results in a large list, but also allow you to support filtered views up to the maximum number allowed. Often it will only display the newest without an index on the column you are filtering on.

Above: The list may not be displaying all items if during a filter operation it says "displaying only the newest results below." If you add an index you can display up to the maximum items allowed in the list view.

Why do I need an index? When the container (folder or list) contains more items than the list the view threshold, it does a full scan against the entire folder/list because there is no index and will fail to return the items.

How to create an index

1. Go to the list that you want to set an index for.

2. On the List tab, click List Settings.

3. Under Columns, click Indexed columns.

4. Click Create a new index.

5. Under Primary column for this index, select a column that is frequently filtered with.

6. Click Create.

Note: If you get an error preventing you from creating the index, you may need your administrator to create the index for you. Or, you may need to create the index during the Daily Time Window for Large Queries.

• Using Folders to Organize a Large List – While I see this as an old school way of attempting to address this, but please pay attention to this warning. Warning: "Open with Explorer" does not show any items if a container has more items than the list view threshold (excluding items in sub folders). If a folder has 8,000 items total, but it has a sub folder that contains 4,000 items and only 4,000 items in the root, then Open with Explorer will work. If the root of a list contains more items than the list view threshold then Open with Explorer will not show anything. To use Open with Explorer the list must have items organized into folders in amounts less than the list view threshold in the root of any container.
• Datasheet View – If you’re using Access Services datasheet view (Access Web Datasheet) gets bumped up to 50,100 items. Requires Office 2003 32bit or later and Internet Explorer 32bit to support the ActiveX control. Note: The 64bit version of IE or Office will prevent it from working properly. You may find the list won’t display in the default datasheet view if the list is larger than the default list view size.

If you’re having issues with Datasheet view not working I suggest this great thread on the forums which explains some of the compatibility issues with clients.

• Daily Time Window for Large Queries – If you have a large list and you want to add an index or a column or a number of other changes like key filters, you’ll need the administrator to make those changes, or if there’s a time window established then you can make changes that otherwise would be restricted during that window. Note: This setting is performed by a SharePoint Server Administrator, not a Site Administrator.
• Using Key Filters for rapid filtering – There are certain columns and data types that you can setup as key filters. This feature is a great one for quickly being able to look up specific ranges of data from a large list.

How to configure key filters

1. Go to the list that you want to set a key filter for.

2. On the List tab, click List Settings.

3. Click Metadata navigation settings.

4. Under Available Key Filter Fields, select the column you want to filter, and then click Add.

You may need to create the key filter during the Daily Time Window for Large Queries or have your administrator set it up if you get an error not allowing you to set it up due to the list view threshold size.

5. Click OK.

• Using SharePoint Workspace for working with your large list – You can sync to SharePoint Workspace up to a maximum of 30,000 items. When you go past that even if you were already syncing prior the updates will fail. Note: You can’t sync calendars and some other special list types. The more common ones are able to be synced. After the initial sync, the data transferred is only on the items changed. So it’s very fast.
• Using Excel to work with your large list – (Starts around 5min) you can sync up to a maximum 52,000 items. You will be warned if it’s larger than that. All the work you do in filtering is client side, so you won’t see the same server restrictions. Note you can’t add items via Excel to be added. The sync only goes one way.
• Using Access to work with your large list – (Starts around 11min) the great thing about Access is the ability to update from the client and create custom queries and views.

http://office.microsoft.com/serverhelp/helppreview14.aspx?AssetId=HA010378155&lcid=1033&products=WSSENDUSER&respos=0&CTT=1&queryid=b98749789184406e9837c125756290a4

As well you may get errors related to setting fine grained permissions. When a list contains more items than the list view threshold and you try to setup granular permissions on folders it will fail because it affects too many rows. You can still set fine-grained permissions on child items, such as documents, in a large list, although you cannot set the permissions on the list itself or on folders that contain more items than the list view threshold.

• Related Errors:
  

  Error in SharePoint Foundation where it won’t even try to display the list.

• 
  
• Other related errors to large lists:
  
• "Displaying only the newest results below. To view all results, narrow your query by adding a filter."
  
• "Cannot show the value of the filter. The field may not be filterable, or the number of items returned exceeds the list view threshold enforced by the administrator."
  
• "This view cannot be displayed because it exceeds the list view threshold (5,000 items) enforced by the administrator."
  
• "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."
  
• 
  

   

  Good resources for Large lists:

  Designing Large Lists and Maximizing List Performance
  

  Capacity planning for a large scale document repository
  

   

  Developers

  Programmatically Using the Object Model Override for the List View Threshold
  

  Handling Large lists
  

  An oldie but goodie whitepaper: Steve Peschka’s white paper: Working with Large Lists in Office SharePoint Server 2007.
  

  Admin
  

  Changing the List View Threshold and Other Resource Throttling Settings
  

  Another reference: Managing lists with thousands or millions of items
  

So, I had been wrestling with error after error from templates that were missing, features, event receivers, and web templates and web parts. Seems like most of our upgrade was time spent wrestling with these things. I had really no idea how they were being used. I just knew we were using 7 of the server admin templates. First thing I did was go and look at Khalil’s 2010 Fab 40 list. I figured as long as I could match them up I would be fixed by simply installing the applicable template on the 2010 side. This was not the case. While we had no problem adding the solutions from Khalil’s blog, we found that none of our errors were addressed by adding them. Apparently Khalil’s list is for people who are looking to have new working copies of the Fab 40. This is nearly the opposite of what I was looking for. I simply wanted my sites to upgrade, and to deprecate the FAB 40 features such that no one would be able to create new sites based on them. I’ve been thinking Khalil’s work would help with upgrade, not so…

So I sent out a message to Chris Mullendore, our assigned Microsoft PFE asking him his suggestion:

"If you’ve deployed the June CU, you can deploy the Fab 40 templates to the farm. This should let the upgrade run and may let you continue to use the sites, but you will not be able to provision new sites using the templates, they’re still not technically supported on 2010, doing so violates the "clean build" goal you mentioned on the phone, and you’re really just punting the problem because the next upgrade is still going to list them as issues without the "grace period" the PG is trying to help with now.

If you’re NOT planning on keeping the old environment running, I would say you should migrate the content to an OOTB site definition (If you don’t already have tools, Excel and Explorer View can be very helpful here) and delete the sites that are referencing those definitions. Then deactivate the solutions (by ID using STSADM -force) and retract the solutions. You should no longer have the references and be able to upgrade the DBs.

If you want to keep the existing sites working, your best option is a separate intermediate 2007 environment. Migrate the sites you want to keep in the old farm to DBs that won’t be upgraded. Copy the DBs you do want to upgrade to the intermediate farm and delete any remaining sites and force deactivation of the 3 features. You should then be able to move the DBs to the 2010 farm and upgrade them successfully.

I have found that occasionally features don’t like to deactivate completely when using the intermediate farm despite the –force command. If you experience this problem (they will still show up during the upgrade and the DB will fail), I’ve found this tool to be helpful: http://featureadmin.codeplex.com/

Though this tool didn’t do anything directly unsupported (no direct DB writes) when I last used it, I have to of course give the disclaimer that this isn’t a Microsoft tool, isn’t supported by us, and you should do a thorough review and be willing take responsibility for any issues created by this tool should there be any.

Let me know if you would like me to elaborate on any of this.

Thanks!"

Chris Mullendore

So I sent out a message to some trusted resources…

I am trying to avoid installing the FAB 40, but we have a few hundreds sites based on them, you can see the references below to the errors we’re getting. I’m trying to get info on how best to address the needs of the fab 40 on our farm. I’m considering a third party migration tool to remove the dependency and do that migration at a later date and focusing on the clean migration now.

Joel

Was great to hear from the Todd’s…

"You have 3 types of errors below, the assembly errors are due to missing event receivers and you can remove with OM code. The missing feature errors are too able to be cleaned up with OM code or the tool which Chris mentions. The third I suspect are due to missing site definitions which you probably want to copy over the 12 hive to allow support for the webs which have been provisioned. If you don’t copy of the 12 hive funny things such as creating a new web part page will fail with very strange errors, basically due to not finding the onet.xml file."

Todd Carter

"I installed appcore, ProjectTracking and KnowledgeBase on an RTM farm this morning to fix some webs that were broken after a database attach upgrade.

If the customer can afford it, I always try to get them to use 3rd party migration software to clean these things up. Doing that also gives you the flexibility to split up site collections, combine site collections, populate metadata, and spur the economy some more."

Todd Klindt

Based on the feedback from this group… We first tried just copying over the 12 Hive, but couldn’t get any differences with results in test-spcontentdatabase. We then decided to install the seven Fab 40 solutions downloaded from the web. All 7 of them were server dependencies from the 2007 Fab 40. After testing them in our engineering environment, and changing the wsp’s feature.xml to hide each of the features so new people wouldn’t accidentally activate, and also include preventing new subsite creation from those templates. So far of the 2300+ site collections we upgraded, we only have a handful that have had issues, so overall our ability to support the Fab 40 that someone had installed in the past continue to persist for good or for worse, we are upgraded and have moved forward.

I’ve put the 7 Fab 40 Hidden Features on skydrive that we worked on to make hidden so people wouldn’t see them if they tried to activate the features on other sites or sites that are upgraded that didn’t have Fab 40 dependencies. Our goal was to reduce or eliminate dependencies.

• FAB40-AbsenceVacationSchedule.wsp
  

  6 minutes ago
  

  Joel Oleson
  

  Everyone (public)
  

  19 KB
  

  
  

• FAB40-ApplicationTemplateCore.wsp
  

  6 minutes ago
  

  Joel Oleson
  

  Everyone (public)
  

  1,212 KB
  

  
  

• FAB40-BugDatabase.wsp
  

  6 minutes ago
  

  Joel Oleson
  

  Everyone (public)
  

  16 KB
  

  
  

• FAB40-EventPlanning.wsp
  

  6 minutes ago
  

  Joel Oleson
  

  Everyone (public)
  

  22 KB
  

  
  

• FAB40-HelpDesk.wsp
  

  6 minutes ago
  

  Joel Oleson
  

  Everyone (public)
  

  19 KB
  

  
  

• FAB40-KnowledgeBase.wsp
  

  6 minutes ago
  

  Joel Oleson
  

  Everyone (public)
  

  7 KB
  

  
  

• FAB40-PhysicalAssetTracking.wsp
  

  6 minutes ago
  

  Joel Oleson
  

  Everyone (public)
  

  17 KB
  

  
  

• FAB40-RoomEquipmentReservations.wsp
  

  6 minutes ago
  

  Joel Oleson
  

  Everyone (public)
  

  33 KB
  

  
  

  • FAB40-AbsenceVacationSchedule.wsp
    
  • FAB40-ApplicationTemplateCore.wsp
  • FAB40-BugDatabase.wsp
  • FAB40-EventPlanning.wsp
  • FAB40-HelpDesk.wsp
  • FAB40-KnowledgeBase.wsp
  • FAB40-PhysicalAssetTracking.wsp
  • FAB40-RoomEquipmentReservations.wsp

We also used the codeplex Feature Admin tool allowed us to clean up a missing CKS Tag cloud, and a custom wow submit button that was deployed across 4 features. Most of those were showing up in 2007 as missing in preupgrade check. Using this codeplex tool we would navigate to a site that was known to have the feature using results from the stsadm -o enumallwebs –includefeatures > enumallwebswfeatures.txt. We may take this to 2010 so we can clean up features on this side as well. It’s been a very useful yet very powerful tool.

Another FAB 40 issue we’ve been experiencing that actually hit us with SP1 and June CU update upgrade was Resources Publishing content type duplication conflict. Our awesome PFE who was right with us during the final stages of upgrade was Chris Mullendore. He wrote us a powershell script to re-write all the Fab 40 "Resource" content types to "FAB40-Resource" content types, then we re-ran the upgrade with the powershell resume script upgrade-spcontentdatabase. Our databases upgrades at that point were nearly flawless…. After tracking down every feature, every assembly, every site template, site/web definition, and after ignoring a few web part errors including the common excel web part that you can ignore.

Here are the errors we got from the upgrade due to FAB 40 conflict, and when we tried to upgrade our Farm with SharePoint 2010 SP1 and June CU.

[SPSiteWssSequence2] [ERROR] [7/11/2011 10:50:51 PM]: Feature upgrade incomplete for Feature ‘PublishingResources’ (Id: ‘aebc918d-b20f-4a11-a1db-9ed84d79c87e’) in Site ‘https://blah/blah’. Exception: An error occurred binding content type ‘0x0101000F1C8B9E0EB4BE489F09807B2C53288F0054AD6EF48B9F7B45A142F8173F171BD1’ to list ‘/pci/_catalogs/masterpage’ on ‘https://blah/blah’. Exception ‘A duplicate content type Publishing Master Page was found.’ (Inner Exception: A duplicate content type Publishing Master Page was found.)

[SPSiteWssSequence2] [ERROR] [7/11/2011 10:51:15 PM]: Feature upgrade incomplete for Feature ‘CTypes’ (Id: ‘695b6570-a48b-4a8e-8ea5-26ea7fc1d162’) in Site ‘https://blah/blah’. Exception: Feature ’75a0fea7-3b2a-4838-8a0c-57ba864feed3′ for list template ‘107’ is not installed in this farm. The operation could not be completed.

If you’re having that issue, I have the permission to give you the script and you have full liability. It’s a sample, and shouldn’t be considered ready for prime time. Use at your own RISK!!!

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.  THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.  We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

SPContentTypeRename.zip

In all we successfully got them all not giving us the errors we were getting prior and the upgrade proceeded. On some of them we did have to use –force to get the feature added and deployed. Following the directions on TechNet to get them installed still worked to remove the errors, but the knowledge base as an example while it has the content doesn’t allow you to add new KBs. We’ve moved its functionality to an enterprise wiki and moved existing articles to PDFs and copy pasted to wiki pages. Fab 40 aren’t perfect, and I’m not happy to say we have to support them through the next version, but I am happy to say, we are now on 2010 across the board.

We are upgraded! Yeah!!

If you have a custom Master Page, you won’t see visual upgrade in the drop down.

There are two ways of getting there otherwise…

1. Site Actions | Site Settings, then click Title, description, and icon under Look and Feel.
2. Another option is to simply append /_layouts/prjsetng.aspx to the end of your site collection or site URL. You can also go into site settings

What if I a user performed the visual upgrade skipped the preview and chose Update the user interface and it broke a bunch of things, and now I don’t see the link, because it’s gone!

For these scripts you can save them as whatever.ps1 and you can execute them by adding adding the . to execute. Such as " .whatever.ps1" For some reason it brings me back to my Unix days.

Powershell Script to Undo the Visual Upgrade:

$Web=Get-SPWeb http://<site&gt;

$Web.UIVersion=3

$Web.UIVersionConfigurationEnabled=$true

$Web.update()

Powershell Script to Display sites upgraded:

Now you want to know which sites have been visually upgraded. Thanks to Michael Doyle for sharing this script (replace <site> with your web app URL):

$web = Get-SPWeb "http://<site>&quot;

foreach($subweb in $web.GetSubwebsForCurrentUser())

{

write-host "Site:" $subweb.Title " Version:" $subweb.UIVersion

foreach($subweb2 in $subweb.GetSubwebsForCurrentUser())

{

write-host " Site:" $subweb2.Title " Version:" $subweb2.UIVersion

foreach($subweb3 in $subweb2.GetSubwebsForCurrentUser())

{

write-host " Site:" $subweb3.Title " Version:" $subweb3.UIVersion

foreach($subweb4 in $subweb3.GetSubwebsForCurrentUser())

{

write-host " Site:" $subweb4.Title " Version:" $subweb4.UIVersion

}

}

}

}