Q&A on SharePoint Architect from MetaVis Technologies

Below are answers (in my own words and some of my own commentary) I got from a Q&A meeting with MetaVis Technologies on their SharePoint migration tooling called Architect Suite. They recommend their Architect Suite and the questions are answered based on the assumption that is the tool you will be using. However, depending on your needs some of the individual products in that suite can be purchased instead.

Q: What are the limitations of this product

A: The following are not supported:

· Highly customized look and feels

· Copying of assemblies for web parts that are installed. This must be manually BEFORE the content is copied.

· Best to copy solutions to target before copy content.

· Many workflows are not supported, but no clear way to tell.

· Running workflows are not supported.

· Workflow history is not preserved.

· Can on preserve either Workflow Approval Status or Last Modified By, but not both.

· Users must be in both active directories, otherwise the current user will be used.

Q: Are OOTB workflows supported

A: It depends. If there are dependencies or customizations then no. We can try it and see if they work. The copying of workflows is not enabled by default. In most cases they recommend just recreating the workflows.

Q: What happens to workflows that are running?

A: Running workflows are not support. Workflows MUST be completed before they are copied.

Q: Is Workflow history kept?

A: No, no workflow history is kept.

Q: What is the copying of a workflow actually doing?

A: They are getting the xaml for the workflows and then replacing links in them.

Q: Where should I install the Metavis application?

A: The Metavis application should be installed close to the source or target system.

Q: What kind of performance can I expect from this application?

A: 250MB an hour is slow and up to 6GB is the fast. Multithreading is recommended. This varies greatly though based on structure, permissions, location, bandwidth, etc. For example, moving files is fast, but re-creating structure, permissions, etc takes longer.

Q: What is the recommended approach for migrating

A: Make sure all the users that you want to keep history for are in active directory in the source and target.

Make sure all workflows are completed or don't copy workflows.

Enable claims based authentication if needed for Office 354 SharePoint migration.

Use one of the three approaches for copying:

Use the UI provided by Metavis

This has the advantage of requiring no other tooling and is still fairly efficient. Its disadvantage is that you must be present each time new sites need to be selected.

1. Take advantage of multi-threading.
2. To do this create the top level sites on the target.
3. Select a few sites at a time and start the copying.
4. Repeat step 3 while taking care not to overwhelm any systems. Do this until all sites have been processed.

Script / Schedule the Migration

This has the advantage of being executed again easily and give you more control. Perhaps the biggest advantage of this is you can run with minimal interaction.

1. Use the Metavis UI to migrate a site, but instead of executing, just generate the script. This script can be used on the command line, windows scheduler, etc.
2. Copy the script, change the source and destination to match each site you want to migrate
3. Execute the scripts in a parallel manner while taking care not to overwhelm any systems. Do this until all site have been processed.

Single-Threaded

This is the easiest because you just select all the sites and let it navigate the tree one site at a time. The downside is that it will take a lot longer to finish a large number of sites because it is only doing it one site at a time instead of in parallel as the other two approaches.

Test workflows that you may have copied. If they don't work, then re-create them.

Once everything is copied to the target have users do UAT to make sure everything is working ok. At this point keep the source system as the production one until UAT has finished. UAT will take some time. Don't expect everything to move over flawlessly, there will be issues in the beginning. Once everything looks good, go back and do an incremental copy from the source to the target system. No you can decommission the source system.

It is NOT recommended that you promise users that the system will be perfect on a given date. Plan to work with end users in UAT to solve issues. Once they have been solved it will be up to the users to start using the new system. This means that if you have a lot of users and sites you will likely have some users on the source system and some on the target system.

Q: Do site groups get copied over and over again

A: No they only get copied once, but there is a performance penalty the first time they are copied.

Q: There are a lot of settings on copy screen, should I just check everything.

A: No, the defaults are typically the best choice. Only change them if you know what they do (use the help icon near each option to better understand).

Q: How can script the migration

A: Use the UI and pretend like you are doing a migration using the UI. Before you get to the Finish / Execute step a button to generate a script will appear. Use that to generate a script you can use as a template for the other sites. You typically just need to change the source and target.

Q: Why aren't alerts copied by default?

A: Alerts are not copied by default, but you can enable them. Be careful though. The reason is that if you copy them when you are copying the site, the copying of the content will trigger alerts (lots of them). You can go back and copy the alerts after everything has been copied or you can leave it up to the end users to sign up for alerts again. Beware of copying alerts before you are sure you will not be doing any more incremental copies. Metavis recommends the later.

Q: How do I import data and metadata from other systems such as Lotus Notes, Alfresco, or even the file system?

A: In all cases, Metavis does not write custom connectors to different products. However, most products can be exported to the file system and the metadata put in CSV format. You can then drag and drop from the file system to the Metavis application. The application then allows you to map the metadata (including folder structure) to SharePoint. You can also map users, permissions, etc at this point.

Q: How can I keep history of users that are no longer with the company?

A: The users MUST be in the source and target active directory. If you are migrating to Office 365 SharePoint just be sure that these users are not assigned a license. You can mark them as disabled as well. Then you can map the usernames using the Metavis application.

Q: Can I expect 100% fidelity of my sites after a copy?

A: No, there are too many customizations that can be done to SharePoint to guarantee full fidelity copying. However, lists, calendars, master pages, permissions, users, groups, and most uncustomized functionality is copied without issue.

Q: Is there a way to compare two sites

A: Yes, the two provides a visual compare of two sites. Permissions can be ignored as well.

Q: In the trial version it is difficult to get my site to transfer all the files, how can I get the entire site copied.

A: The trial version will only copy the first 5 item in a list. You can go back and copy the ones that were missed. You can also use the compare features to see any differences. There are typically some hidden lists as well. If you right-click on lists you can choose to show hidden lists.

Q: Are there any tools to help migrate references in Outlook to SharePoint lists and calendars to the new locations after the migration?

A: Not that I am aware of, but a good place to start is Colligo.

Getting the Description Column from SQL Server

If you are storing valuable information such as comments, etc in the Description for a given column in your table you may want to export it to excel or in general just get to it via t-sql. It is actually pretty simple to get it. Below is a query you can run from the database you want to report on. I have exluded views, non-user tables, default tables, and system diagram tables, but you can change that by commenting out the appropriate statements in the where clause.

SELECT
[Schema] = ColumnInfo.TABLE_SCHEMA,
[Table Name] = ColumnInfo.TABLE_NAME,
[Column Name] = ColumnInfo.COLUMN_NAME,
[Position In Table] = ColumnInfo.ORDINAL_POSITION,
[Date Type] = ColumnInfo.[Data_Type],
[Description] = Properties.value
FROM
INFORMATION_SCHEMA.COLUMNS ColumnInfo
LEFT OUTER JOIN sys.extended_properties Properties
ON
Properties.major_id = OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME)
AND Properties.minor_id = ColumnInfo.ORDINAL_POSITION
AND Properties.name = 'MS_Description'
WHERE
-- exclude ones created when SQL Server was installed
OBJECTPROPERTY(OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME), 'IsMSShipped')=0
 
-- only get tables (no views)
and OBJECTPROPERTY(OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME), 'IsTable')=1
 
-- exclude ones created when SQL Server was installed
and OBJECTPROPERTY(OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME), 'IsUserTable')=1
 
-- exclude tables used for system diagrams
and ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME <> 'dbo.sysdiagrams'

--AND ColumnInfo.TABLE_NAME = 'table_name'
ORDER BY
ColumnInfo.TABLE_NAME, ColumnInfo.ORDINAL_POSITION

I got the basics for this from this post.

Creating a more flexible master-detail layout in SharePoint 2010

In an earlier entry I gave instructions on how to easily create a master-detail layout in SharePoint 2010 using SharePoint Designer 2010. I found one limitation of it is that the initially selected item is always the first item in the topics list. Another one is that there is no way to link to the page with anything other than the first topic selected. To get past those short comings, it requires a little bit more of tweaking. Below is the updated instructions. I have copied the previous entry and modified it.

This assumes you have two Lists. One list is the lookup table (we’ll called it FAQTopics) that you want to group by. The other list is a table (I called it FAQ List) that has the rest of the data you want to display. In this scenario I want my lookup table to show on the left side of the screen and when I click one of the items on this table I want the list on the right to update and only show the rows that are in that match. In this case, the master is the FAQTopics list and the detail is the Topics list.

• Create a new web part page
  1. Open SharePoint Designer 2010
  2. Go to Site Pages
  3. Click Web Part Page from the Pages ribbon and select the desired web part layout. Pick on that has at least a left and right web part area for our purposes since we want the list of topics on the left and the FAQs on the right.
  4. Rename the file as a desired. I called it FAQ.aspx
  5. Right-click new page and select Edit File in Advanced Mode.
• Add the FAQTopics to the page
1. Click on one of the left web part zones
2. Click the Insert ribbon and choose Empty Data View from the Data View button. This will create a DataFormWebPart. This will work best since it gives us access to the html directly and allows us to easily customize the selected row styles.
3. Click the Click here to select a data source link in the middle of the DataFormWebPart and select FAQTopics (you may have called it something different). The screen won’t change much, but open the Data Sources Details panel (if it is not all ready visible). Here you will see a list of columns from the FAQTopics.
4. Drag over the column you want to display in the FAQTopics list. In my case, I used Title. Please, note, this field should uniquely identify the row.
5. On the Options ribbon, set any filters or sorting you may need by clicking the Filter and Sort & Group buttons.
6. You can now test what we have done. Click the Home ribbon and then click the Preview in Browser button. This will open your page up in the browser. It should show a list of rows from your FAQTopics table.
• Add the FAQ List to the page
  1. Click on one of the right web part zones
  2. Click the Insert ribbon and choose FAQ List from the Data View button. This will create a XsltListViewWebPart. Note, we didn’t create an Empty Data View (like we did for the FAQTopics) and instead selected the FAQ List. This causes SharePoint Designer to use the default XsltListViewWebPart. You could use a DataFormWebPart like we did for the FAQTopics, but you may lose some functionality. For example, if you want to use the Rating stars to allow users to rate the FAQs, they don’t really work when you use the DataFormWebPart and instead you just get the number stored in the database. So, depending on your needs, pick the appropriate web part. The rest of the steps are the same regardless.
  3. You can now test what we have done. Click the Home ribbon and then click the Preview in Browser button. This will open your page up in the browser. It should show a list of rows from your FAQTopics table and also have the list of FAQs on the right side. They are independent of each other right now, but we’ll connect them next.
• Connect the two lists
  1. Right-click anywhere in the FAQTopics web part on the left and choose Add Connection.
  2. Choose Send Row of Data To (the default).
  3. Click the Next button.
  4. Click Connect to Web Part on this page (the default).
  5. Click the Next button.
  6. Choose FAQ List from the Target Web Part drop down list.
  7. Choose Get Filter Values From from the Target action drop down list.
  8. On the right set of columns (called Inputs to FAQ List) in the list of columns scroll down to the column you want to group by and will match the text in the FAQTopics. In my case the column is called Topic.
  9. Click the column (it shows as <none> initially) to the left of the Topic column. You will notice that <none> turns into a drop down list. Select the matching column from the list. In my case, I selected Title.
  10. Click the Next button.
  11. Select Title from the Create a hyperlink on drop down list.
  12. Check the Indicate current selection using checkbox.
  13. Click the Modify button and select the column that uniquely identifies the row. In my case, this is Title.
  14. Click the Next button.
  15. Click the Finish button.
  16. You can now test what we have done. Click the Home ribbon and then click the Preview in Browser button. This will open your page up in the browser. It should the two lists like before, except this time the first item in the FAQTopics (the one on the left) should be bold (the default style for the currently selected row). You can each row and the list on the right side (FAQ List) should update to show only the rows that match the selected FAQTopic.
• Add support for query string to Topics DataView
1. Right-click on one of the DataViews and then choose Manage Connections.
2. Remove the Connection we added earlier. We needed to add the connection in the first place so that it added support for current selected item.
3. Select the Topic DataView
4. Click the Parameters button and click the New Parameter button to create a new parameter called something like Topic.
5. Change the Parameter Source drop down list to Query String.
6. Enter Topic into the Query String textfield.
7. For Default value, enter the topic you would like to be selected when the page loads for the first time or there is no topic specified in the url. For example, if you topics are Topic 1, Topic 2, Topic 3 and you would like Topic 2 to be selected by default then enter Topic 2. NOTE: You can change this in the future by selecting the DataView and then Parameters again.
8. Make sure you have SharePoint Designer 2010 set to Split view so that you can see code and the Designer view at the same time.
9. Click on one of the rows in your DataView. This will show something like this:
   <td class="ms-vb">
        <xsl:value-of select="@Title"/>
   </td>
10. Replace or modify the snippet above to look like the following:
    <td class="ms-vb" nowrap="nowrap" style="height: 23px">
        <a target="_self">
        <xsl:attribute name="href">
            <xsl:text>?Topic=</xsl:text>
            <xsl:value-of select="ddwrt:UrlEncode(string(@Title))"/>
        </xsl:attribute>

    <xsl:attribute name="style">
        <xsl:if test="$CurrentRowKey = $Topic">font-weight: bold;</xsl:if>
    </xsl:attribute>
    <xsl:value-of select="@Title" />
    </a>
</td>
NOTE: If you are showing a field different than Title you will need to change @Title to the name of your field. Also, if you name your parameter something other than Topic, you will need to change the $Topic to the name you used. If you typed something else in the Query String textfield you will need to change ?Topic to that as well. I have bolded each of the items you may need to replace depending on the values you entered in previous steps.

• Add support for query string to FAQ DataView
1. Select the FAQ DataView
2. Click the Filter button on the Data View Tools | Options ribbon.
3. Select Topic from the Field Name drop down list.
4. Select Create a New Parameter… from the drop down list.
5. Change the Name of the parameter to Topic.
6. Change the Parameter Source drop down list to Query String.
7. Enter Topic into the Query String textfield.
8. For Default value, enter the topic you would like to be selected when the page loads for the first time or there is no topic specified in the url. For example, if you topics are Topic 1, Topic 2, Topic 3 and you would like Topic 2 to be selected by default then enter Topic 2. NOTE: You can change this in the future by selecting the DataView and then Parameters again.
   
• Tweak the UI (optional)
  1. This step is totally optional if you are happy with the default styles, etc of the topics on the left.
  2. Do a search for bold in FAQ4.aspx. You will find a tag like this:
     
     <xsl:attribute name="style">
          <xsl:if test="$CurrentRowKey = $Topic">font-weight: bold;</xsl:if>
     </xsl:attribute>
     This adds the style=”font-weight:bold:” to the link tag. You can modify this or if you prefer, you can add a css class reference by adding the following lines after the lines for the style attribute.
     
     <xsl:attribute name="class">
          <xsl:if test="$CurrentRowKey = $Topic">selected</xsl:if>
     </xsl:attribute>
     In this case if the row is selected we the link tag to have class=”selected” to it. Then in your stylesheet you create a .selected style. The name (selected) is not important, but it does need to match what you have in your stylesheet.

Get all SharePoint sites (webs) recursively in PowerShell

SharePoint has always had confusion between sites and webs. What we are really talking about hear is for a given site (not site collection, but really a web) such as a team site we want to get all the sites below it. An easy way to do this is to use PowerShell if you are using SharePoint 2010.

1. Open SharePoint 2010 Management Shell (As Administrator).
2. Paste the snippet of code below into the prompt. Note, the first line can be uncommented if you are just using a PowerShell prompt that doesn’t have the SharePoint PowerShell commands added already.

 

#Add-PSSnapin "Microsoft.SharePoint.PowerShell"
$url = "http://mysharepoint/SiteDirectory/site1"
$site = Get-SPWeb ($url)
foreach ($web in $site.Site.AllWebs)
{
    if ($web.Url.StartsWith($url))
    {
    Write-Host ($web.Name + "|" + $web.Url)
    }
}

In my example I am writing to the console the name of the site, the pipe character as a delimiter, and then the url.

Programmatically finding and using a person using Client Side Object Model

I have a custom table and one of the fields is a Person column column called Answered By. In the SharePoint UI the person can type in the persons username (same as email if you are using SharePoint Online) or use the Person Picker to find the person. In the code below we want to find them by username.

using (ClientContext clientContext = ClaimClientContext.GetAuthenticatedContext("https://myco.sharepoint.com/site1/site2"))
{
    var user = clientContext.Web.EnsureUser("me@myco.com");
}

Once you have the person you can then use it when setting the value for that field programmatically. In this example I am creating a new list item and setting the Answered By field using the person object that I find using the EnsureUser() method.

using (ClientContext clientContext = ClaimClientContext.GetAuthenticatedContext("https://myco.sharepoint.com/site1/site2"))
    {
        var destList = clientContext.Web.Lists.GetByTitle("FAQs");
        var itemCreateInfo = new ListItemCreationInformation();
        var newItem = destList.AddItem(itemCreateInfo);
        newItem["Title"] = "Some title here";
        var user = clientContext.Web.EnsureUser(brent.vermilion@nxp.com);
        newItem["Answered_x0020_By"] = user;
        newItem.Update();
        // actually create the records in the destination list
        clientContext.ExecuteQuery();
    }

Take note, I could have also put the integer instead of the user object, but I think this actually saves a trip to the database and is cleaner. I also found it interesting that spaces in column names in SharePoint have the space replaced by the space with a notation that uses the hex value for space (20).

To learn more about CRUD operation and other useful topics see this intro.