eRoom to Sharepoint 2010 migration permission mapping

From my previous blog, the key content migration challenge is eRoom database with attachments migration. We have listed all the eRoom objects to SharePoint objects mapping for content migration. The next challenge is how we migrate permissions that will include eRoom user privileges and access control list (ACLs). You may consider to reconstruct the permissions instead to migrate them.

Here is the object mapping for eRoom user privileges to SharePoint.

Here is the object mapping for eRoom access control list (ACLs) to SharePoint.

 

Please note we do not plan to migrate eRoom Community or Facility so we will skip the SharePoint hierarchy corresponding to eRoom hierarchy. We will also skip user privileges related to those hierarchy such as Site Adminstrator and Community Adminstrator. Since we would like to have good security control on all the migrated eRoom content, there is a good opportunity to restructure the SharePoint permissions after content migrated. You may consider to apply SharePoint permissions instead migrate the eRoom existing user privileges and ACLs.

The final decision will have to work with content owners. The eRoom Custom Groups and Custom Groups may also need to map to SharePoint Site Groups and Site Roles.

How to add clickable world map or office maps to SharePoint?

We have SharePoint users asking to have the clickable world map and office maps inside SharePoint sites. The links could redirect users to navigation to those site-specific pages or the office locations with Google map directions.  You could also add a label that your users will see on mouse-over as "screentip". In SharePoint 2010, this is quite simple as shown in the following procedure. We will use world map as example.

1. Find the best world map users like and you could find here
2. Upload the map image to SharePoint Picture Library of the site
3. Add a Content Editor Web Part to the page
4. In the Format Text ribbon section, click HTML / Edit HTML Source.  A new "HTML Source" window will open where you can paste the following HTML.  Click OK when you have pasted it.

<map name="FPMap0" id="FPMap0">

<area alt="China" coords="297, 56, 293, 46, 300, 43, 303, 36, 297, 28, 285, 31, 280, 40, 271, 41, 265, 40, 248, 34, 240, 33, 235, 43, 245, 51, 255, 57, 272, 62, 274, 69, 293, 77, 288, 69, 286, 64, 294, 63" shape="polygon" href="http://google.com">

</map><img src="../Pictures/world.bmp" usemap="#FPMap0">

The map is saved in Picture Library as world.bmp. Here is the map.

Now you have a clickable link pointing to Google URL around China area! Well, the questions you will ask are how can I generate the HTML with the coordinates for the areas we need?

The blog published by Sadalit Van Buren provides you very easy way to generate the HTML you need to configure the Content Editor Web Part. There are some tools listed here will help you get the coordinates easily. Here is the list of the tools you might use.

• Use an Image Map Editor
• Create an Image Map with Dreamweaver
• Create an Image Map With Microsoft FrontPage

If you need any rich tootip on the map, please refer the Bryian Tan's blog. There are some good ToolTip / RadToolTip with ImageMap from Telerik. We are in the process to generate the company building map with links to each building address and Google map directions. This will be very simple process for any developer.

Workaround to remove orphan components when site owners delete site templates or sandbox solutions from solution gallery without deactivating them

If you are using sandbox solution or saved site template on SharePoint 2010, you will noticed those solutions will be uploaded into solution gallery. If you have not upgraded to 2011 December CU and you are on load balanced multiple tier environment (example is two WFS and one application server), site owners are able to delete site templates or sandbox solutions from solution gallery without deactivating them through edit page. Those site templates or sandbox solutions will become orphan and users will still be able to see them in site creation template. Of cause, users will get error if they want to create ant site from these orphan templates. You could refer my previous blog for details.

In this blog, I will use save site template as example to explain how we could workaround and clean up those orphan sandbox solutions. Before we explain the clean up procedure, let us identify what will be added into content database after we activate the sandbox solution and what would be cleaned up automatically if we deactivate and then delete the solution. I’m using two saved templates ITDebug.wsp that saved form out of box team site for the testing.

First, we will identify what happened inside content database if we upload, activate, deactivate, and delete the solution ITDebug.wsp in single SharePoint server farm.

• Upload the ITDebug.wsp, there is no entry in dbo.Feature or dbc.Solutions tables.
• Activate the ITDebug.wsp, you will see ONE additional feature entry in dbo.Feature and ONE additional entry in dbc.Solutions table
• Deactivate ITDebug.wsp, there is no entry in dbo.Feature or dbc.Solutions tables for this saved template. Both are cleaned up
• Remove ITDebug.wsp, the file is removed from the solution gallery

 

Here is the solution gallery screen shot after ITDebug.wsp solution activated.

Here is the one entry inside table dbc.Solutions with name ITDebug.wsp.

Here is the screen shot for entry inside table dbo.Feature for ITDebug.wsp solution.

Here is the SQL script to get the entry. Please note the content database for testing site http://sbx08:3797/sites/Orphan is WSS_Content_TEST_ORPHAN. You could identify the database name from central admin when you view the site or from ‘stsadm -o enumsites’ command.

SELECT [WSS_Content_TEST_ORPHAN].[dbo].[Features].[SiteId]

      ,[WSS_Content_TEST_ORPHAN].[dbo].[Features].[FeatureId]

      ,[WSS_Content_TEST_ORPHAN].[dbo].[Features].[SolutionId]

      ,[WSS_Content_TEST_ORPHAN].[dbo].[Solutions].[Name]

  FROM [WSS_Content_TEST_ORPHAN].[dbo].[Features] INNER JOIN [WSS_Content_TEST_ORPHAN].[dbo].[Solutions]

  ON [WSS_Content_TEST_ORPHAN].[dbo].[Features].[SolutionId] = [WSS_Content_TEST_ORPHAN].[dbo].[Solutions].[SolutionId]

 

Second, we will identify what happened inside content database if we upload, activate, delete without deactivating the solution ITDebug.wsp in multiple SharePoint server farm.

• Upload the ITDebug.wsp, there is no entry in dbo.Feature or dbc.Solutions tables. This is same as before.
• Activate the ITDebug.wsp, you will see ONE additional feature entry in dbo.Feature and ONE additional entry in dbc.Solutions table. This is same as before.
• Delete ITDebug.wsp without deactivating it,  both entries inside dbo.Feature and dbc.Solutions table are still there!!!  These are orphan entries.

You could use the following SQL script to identify the orphan solutions. Please replace the content database name with yours.

SELECT [Content_DBNAME].[dbo].[Solutions].[Name]

  FROM [Content_DBNAME].[dbo].[Solutions] INNER JOIN [Content_DBNAME].[dbo].[Features]

  ON [Content_DBNAME].[dbo].[Features].[SolutionId] != [Content_DBNAME].[dbo].[Solutions].[SolutionId]

Now it's time to describe the workaround to cleanup those orphan components. As you see we need to clean up two entries, one inside feature table and another inside Solutions table. Here is the procedures.

1. Use powershell or stsadm command to disable to feature for the sanbox solution. You need to pass the feature ID you get from previous queries.

  

     Disable-SPFeature –Identity DA9016BA-93F2-44AD-99B0-1F80B968E7E7 –url http://projectsdev/sites/Harry/ –Confirm:$false -Force:$true

      2. Use database delete to remove the orphan entry inside Solution table for the solution. STOP! This is not supported by Microsoft. If you update the SharePoint tables, your farm will not be supported by Microsoft any more!

Well, I have decided to leave the solution entry at this time and will work with Microsoft to see anyway we could clean it up using Powershell or OM API. I have tried the following commands but there are not able to remove the solution.

 

    Uninstall-SPUserSolution –Identity emsdev.wsp  -Site http://yoursharepoint/sites/Harry/ -Confirm:$false –Force:$true

    Remove-SPUserSolution –Identity emsdev.wsp  -Site http://yoursharepoint/sites/Harry/ -Confirm:$false –Force:$true

If you have a better way to clean up the solution table without deleting the entry from the database table, please let me know. We are also in the process to automate process to identify orphan solutions and deactivate the feature.

eRoom to Sharepoint 2010 migration object mapping

EMC/Documentum, the supplier of eRoom software, is ending support for eRoom. New development of eRoom was stopped in 2006 and it is rapidly becoming obsolete. Even now, a number of eRoom components are no longer supported in the latest versions of Windows or SQL database. As a result our company is phasing out support for eRoom over the next 2 years. We are evaluating tool that could support eRoom to SharePoint 2010 migration.

The process accomplishes a migration of an eRoom environment that results in the following matrix when it comes to objects in eRoom as recreated in SharePoint. Though eRoom and SharePoint are by no means identical products, all data should be carried forward into the resultant environment. In this blog, we will focus on the eRoom object migration and mapping to SharePoint objects. We will discuss other migration components such as eRoom hierarchy, access control, and user privileges in later sessions.

The major eRoom folder could be migrated to SharePoint Document Library or Folder. Files will be migrated as files within Document Libraries or Folders. Most of the eRoom objects could be mapped into SharePoint objects straight forward. However, the eRoom database with attachments from eRoom to SharePoint migration is the key differential for all different migration tools. Some tools could not handle this case and some tool can migrate them into different SharePoint objects. Here is the eRoom objects to SharePoint object migration mapping summary from one of the migration tools. 

From my discussion with end users, the SharePoint object mapping for eRoom database should not be just be SharePoint Custom List. It could be Document Library or other SharePoint objects depends on the eRoom database attachment type. For example, the eRoom Document Library database should be migrated to SharePoint Document Library instead of the Custom List. All the folder and files inside that eRoom Document Library database should be migrated as SharePoint folder and files.

Please note there are some objects in eRoom may not be migrated to SharePoint. One of the examples is the eRoom workflows. We would recommend eRoom users to complete the workflows before the migration. The new workflows have to be reconfigured or created in SharePoint. More discussions on permission migration is listed here.

Could not get Managed metadata or lookup field values through SharePoint 2010 REST web service

SharePoint 2010 provides a new RESTful web service to integrate with SharePoint data. When we use it to integrate with list data, we are not able to get managed metadata fields. After researching through different scenarios, we have concluded that REST interface could not get any lookup fields including user and group fields, normal lookup fields, or managed metadata fields. Indeed user fields and managedmetadata fields are look up fields.

We have created a customized list named “SOAP” with four columns. Title as string, Sponsor as user, ManagedMetadata as local term store fields, and descriptions as string. We have two list items added as in the following screenshot.

Please Sponsor is the looup fields as indicated in the following screenshot.

When we try to access this SOAP list through REST web service, we are not getting either Sponsor or ManagedMetadata values. Instead we are getting the lookup index values. Here are two ways either from REST returned xml or SharePoint Designer Data Source. Here are details.

1. Verify from REST ATOM returned xml data. Here are the steps to reproduce this.

• Configure to display REST data in xml format. For IE version 8 go to Tools –> Internet Options –> Content tab –> Feeds and web slices Settings.  In this dialog, clear the Turn on feed reading view check box as indicated in Randy's post.
• Invoke REST to display result http://sbx08/sites/Harry/_vti_bin/listdata.svc/SOA

 You will see result in xml in the following screenshot. The list item 1 Sponsor displayed as index "1" and Managedmetadata also displayed as index.

2. Verify from SharePoint designer Data Source values. Here are steps.

• Open site http://sbx08/sites/Harry with SharePoint designer
• Add REST Web service to the Data Sources similar to add SOAP web service
• Add a data view web part to the page using the data source 

Please note you need to enter user name and password in order to make REST data source work. The correct for my site is show in the screenshot.

I have inserted the REST data source into one page and here is the result from REST data source as in the screenshot. You will see it does not return Sponsor or Managedmetadata values. They are lookup index instead.

The same list will return both Sponsor or Managedmetadata values through SOAP web service interface as in the following screen shot using designer.

There are several attempts from different people try to resolve this through API or LINQ. However, it would be extremely difficult for end users to utilize REST web service if we could not get back managed metadata or any lookup fields.

 If you have any suggestions, please let me know.

Deep dive and workaround to resolve renamed Managed Metadata value not applied to list items issue

After upgraded to SharePoint 2010 in August 2010, we have started utilize managed metadata services to many sites. However, we identified many issues related the services. One of the issues is the list managedmetadata value NOT updated after managed metadata term store value changed. You could use site collection local term store to reproduce this easily if you have the issue. This was confirmed with other customers from different blogs. From Microsoft release notes, SP1 and June CU suppose to fix this issue. However after we applied the SP1 and June CU in August 2011, we still have this issue on productions. The strange behaviors is we are not able to reproduce the issue on all other non production environments including DEV, TEST, STAGE, and development boxes.  This is the reason I have to pick up this issue and would like to share the finding and workaround for this issue.

First let’s understand the managed metadata field - Taxonomy Fields. They are lookup columns as Wictor explained in his blog. Three types of data play critical role to synchronize taxonomy value with managed metadata term store. These three data types are listed below and the relationship is explained in my previous blog.

• Taxonomy Fields on your list
• TaxonomyHiddenList on your site
• Managed metadata term store tables from service application

There are several processes that will participate the pushes changes from the Term store into TaxonomyHiddenList so the list will look up to the changed value. You got the key point and here are the processes involve and their functions.

• Taxonomy Update Scheduler - OTB Sharepoint timer job which synchronizes changes from Term store into Sharepoint web site. This timer jobs pushes changes from the Term store into your list.
• ItemUpdating and ItemUpdated events – These events on your hidden list TaxonomyHiddenList will update the lookup values on your list
•  TaxonomyFieldAdded site collection feature - Responsible for creating the hidden list TaxonomyHiddenList as well as adding the item receivers
•  TaxonomyItemSynchronousAddedEventReceiver and TaxonomyItemUpdatingEventReceiver events– These event receivers are added to your list for  taxonomy changeson your list events

Second let’s summarize the process the process so we could understand how SharePoint update the list Taxonomy Fields.

• When Taxonomy Fields added to a list, TaxonomyFieldAdded site collection feature is activated to creates hidden list TaxonomyHiddenList as well as ItemUpdating and ItemUpdated events to the hidden list
• If managed metadata term store term changes, Taxonomy Update Scheduler will pick the changes and update the TaxonomyHiddenList. This update will then trigger the ItemUpdating and ItemUpdated events on hidden list TaxonomyHiddenList to update the lookup values on your list
• The lookup Taxonomy Field value of your list changes will trigger TaxonomyItemSynchronousAddedEventReceiver/TaxonomyItemUpdatingEventReceiver events of the list. You could write event receiver to track the call sequence. You may find yourself to have gun using API.

Now we understand all the data and processes involved to synchronize taxonomy value with managed metadata term store, we track what is missing after deploy the event receivers to track the sequence calls. In the SharePoint farm that Managed Metadata value not applied to list items, the ItemUpdating and ItemUpdated events on hidden list TaxonomyHiddenList never triggered!!!

Well you might have the workaround in your mind - if we verify related feature, event receiver, and dcheduled timer job listed above are working, we could update the TaxonomyHiddenListhiden list to reflect the term stor change to trigger event receiver and update the list lookup values!

Here are the steps ypou could refer. My site in the example is http://sbx08/sites/Harry  that has List1 with one Taxonomy Field named MM.

1. Verify Taxonomy Update Scheduler is scheduled on the webapp and run successfully as in the screenshot. You may need to change the frequency of the timer job if there is issue here.

     2.  Verify whether you have TaxonomyFieldAdded site collection feature enabled on the site. Use               command from Glyn Clough's Blog. You many need to enable the feature is it’s not enabled already.

      Get-SPFeature -Site http://sbx08/sites/Harry | Sort DisplayName | FT DisplayName,Id


           Result is:
           TaxonomyFieldAdded  73ef14b1-13a9-416b-a9b5-ececa2b0604c

    3. Verify whether you have event receivers added correctly to your lists with Taxonomy Fields. You could use the powershell provided by Mike Smith. I never find issue that these event receivers are missing but you should try to activate the TaxonomyFieldAdded site collection feature to add them if they are missing.

        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
        $site = New-Object Microsoft.SharePoint.SPSite("http://sbx08/sites/Harry")
        $web = $site.Rootweb
        $web.Lists | Where {$_.eventreceivers.count -gt 0} | Select title,eventreceivers

        Result is:
         …
        Title          : List1
        EventReceivers : {TaxonomyItemSynchronousAddedEventReceiver,    
        TaxonomyItemUpdatingEventReceiver}

At last, if you have changed all those listed above and you are still out of luck, you could use powershell provided Joerg Sinemus to update the TaxonomyHiddenList of the site you have Taxonomy Fields on your list. Here is the code snippet.

SPSite Site2Update = new SPSite(SiteUrl);

       TaxonomySession.SyncHiddenList(Site2Update);

       Site2Update.Dispose();

It works good for the one of our production list and we are converting the code to C# and deploy as customized timer job. The real issue is how we get the list of the site collections with Taxonomy Fields quickly with minutes so we could schedule the customized timer job within 15 minutes. Here are different ways I'm thinking to identify the site collections that are using managed metadata Taxonomy Fields.

1. Check any site collection has site collection feature TaxonomyFieldAdded enabled
2. Check any site collection has hidden list TaxonomyHiddenList 
3. Query managed metadata tables
   

 We will compare which one is the fastest way and then update the hidden list.

Hope this could help.