Collabshow.com

The more I dig into PreUpgradeCheck, the more I’m finding everyday value.  As well, the more I use Test-SpContentDatabase the windows powershell commandlet designed for testing databases prior to attaching them, the more I see differences between these tools.  I see a big mistake being made by customers who see these tools as the same, or see them as providing the same data.  It is a mistake.  Let me show you how.

First if we look at the default preupgrade check you’ll see it’s focus on features and site definitions.  There is no mention of missing references to themes, XSL, Images, or web part packages.  This is the richness I’m seeing in the powershell test-SpContentDatabase.  This difference is huge, but the output is overwhelming… Windows Powershell to the rescue!!!

What you don’t see in the examples on the Windows Powershell is the enhancements to the output.  It’s the output that’s overwhelming.  First let’s look at default output then I’ll show you some tips.

PS > get-help test-spcontentdatabase

NAME
    Test-SPContentDatabase

SYNOPSIS
    Tests a content database.

SYNTAX
    Test-SPContentDatabase [-Identity] <SPContentDatabasePipeBind> [-AssignmentCollection <SPAssignmentCollection>] [-D
    atabaseCredentials <PSCredential>] [-ServerInstance <SPDatabaseServiceInstancePipeBind>] [-ShowRowCounts <SwitchPar
    ameter>] [<CommonParameters>]

    Test-SPContentDatabase -Name <String> -WebApplication <SPWebApplicationPipeBind> [-AssignmentCollection <SPAssignme
    ntCollection>] [-DatabaseCredentials <PSCredential>] [-ServerInstance <SPDatabaseServiceInstancePipeBind>] [-ShowRo
    wCounts <SwitchParameter>] [<CommonParameters>]

DESCRIPTION
    Use the Test-SPContentDatabase cmdlet to test a content database against a Web application to verify all customizations referenced within the content database are also installed in the web application. This cmdlet can be issued against a content database currently attached to the farm, or a content database that is not connected to the farm. It can be used to test content databases from SharePoint 2010 Products and from SharePoint Products and Technologies
    .

    The Test-SPContentDatabase cmdlet does not change any of the data or structure of the content database, but can cause load on the database while the checks are in progress, which could temporarily block use of the content database
    . This cmdlet should only be used against a content database that is currently under low or no usage.

    For permissions and the most current information about Windows PowerShell for SharePoint Products, see the online documentation (http://go.microsoft.com/fwlink/?LinkId=163185).

RELATED LINKS
    Get-SPContentDatabase
    New-SPContentDatabase
    Set-SPContentDatabase
    Remove-SPContentDatabase
    Mount-SPContentDatabase
    Upgrade-SPContentDatabase

REMARKS
    To see the examples, type: "get-help Test-SPContentDatabase -examples".
    For more information, type: "get-help Test-SPContentDatabase -detailed".
    For technical information, type: "get-help Test-SPContentDatabase -full".

PS >get-help Test-SPContentDatabase -examples

——————-

C:PS>Test-SPContentDatabase -name WSS_Content_DB -webapplication http://sitename

This example tests the WSS_Content_DB content database against the sitename Web application, and returns a list of
issues.

—————

Here’s what I get when I run the default command.

test-spcontentdatabase –name Databasename –webapplication http://webapp –serverinstance SQLServerName

It scrolls and scrolls and scrolls with hundreds of lines of output. You’ll notice the  already different output than PreUpgradeCheck which clearly puts the features all together and never mentions missing XSL.

After watching Dan Holme’s powershell I was noticing the output options for formatting the output from Windows Powershell.  I got some great help from Nickholas who gave me one of his new awesome SharePoint books on Powershell.  I simply pipe | (with the pipe character the one with the two verticle lines that looks like a colon) the output to ConvertTo-Html and pipe that to and Output file by using Out-file then specifying the location of the file, then opening that in explorer.

Output to HTML

PS > Test-SpContentDatabase -name WSS_Content_SP2 –webapplication http://webapp -serverinstance SQLServer | ConvertTo-Html | Out-file c:filesTestSPContent_db2.htm

It’s incredible how much easier it is to read the output in nice columns.  Now in HTML it can easily be opened in SharePoint designer where you could add formatting to make it even easier to read.

I’m also a fan of the CSV and XML formats

Output to CSV

PS > test-spcontentdatabase -name WSS_Content_SP2 -webapplication http://spjoel -serverinstance SQLServer | ConvertTo-CSV | Out-file c:TestSPContentdb2.csv

Open up the file in Notepad and you have each line of the output in a CSV that is much much easier to read.  Throw the CSV into Excel and you can manipulate the content to your hearts content by formatting the data with default formatting and adding an extra column for how critical it is then format the rows in green yellow and red.

This might be easier to illustrate what I’m seeing, but with wrapping turned off it’s even easier to see.

#TYPE Microsoft.SharePoint.Upgrade.SPContentDatabaseTestResult
"Category","Error","UpgradeBlocking","Message","Remedy"
"MissingFeature","True","False","Database [WSS_Content_SP2] has reference(s) to a missing feature: Id = [bbe9def7-2fe9-a0b1-d712-aa128c837ebe].","The feature with Id bbe9def7-2fe9-a0b1-d712-aa128c837ebe is referenced in the database [WSS_Content_SP2], but is not installed on the current farm. The missing feature may cause upgrade to fail. Please install any solution which contains the feature and restart upgrade if necessary."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanAdminLinks.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanCategories.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanComments.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanCurrentDate.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanHead.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."
"MissingSetupFile","True","False","File [FeaturesCKS EBE ThemesThemesCleanimagesdelicious.png] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.","One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files."

Output to XML

PS C:Usersjoleson> test-spcontentdatabase -name ContentDatabase -webapplication http://WebApp -serverinstance SQLServer | Export-CLIXML c:filesTestSPContentdb.xml

Note the XML output pipes to Export-CLIXML and then an output filename is provided.

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>Microsoft.SharePoint.Upgrade.SPContentDatabaseTestResult</T>
      <T>System.Object</T>
    </TN>
    <ToString>Microsoft.SharePoint.Upgrade.SPContentDatabaseTestResult</ToString>
    <Props>
      <S N="Category">MissingFeature</S>
      <B N="Error">true</B>
      <B N="UpgradeBlocking">false</B>
      <S N="Message">Database [WSS_Content_SP2] has reference(s) to a missing feature: Id = [bbe9def7-2fe9-a0b1-d712-aa128c837ebe].</S>
      <S N="Remedy">The feature with Id bbe9def7-2fe9-a0b1-d712-aa128c837ebe is referenced in the database [WSS_Content_SP2], but is not installed on the current farm. The missing feature may cause upgrade to fail. Please install any solution which contains the feature and restart upgrade if necessary.</S>
    </Props>
  </Obj>
  <Obj RefId="1">
    <TNRef RefId="0" />
    <ToString>Microsoft.SharePoint.Upgrade.SPContentDatabaseTestResult</ToString>
    <Props>
      <S N="Category">MissingSetupFile</S>
      <B N="Error">true</B>
      <B N="UpgradeBlocking">false</B>
      <S N="Message">File [FeaturesCKS EBE ThemesThemesCleanAdminLinks.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.</S>
      <S N="Remedy">One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files.</S>
    </Props>
  </Obj>
  <Obj RefId="2">
    <TNRef RefId="0" />
    <ToString>Microsoft.SharePoint.Upgrade.SPContentDatabaseTestResult</ToString>
    <Props>
      <S N="Category">MissingSetupFile</S>
      <B N="Error">true</B>
      <B N="UpgradeBlocking">false</B>
      <S N="Message">File [FeaturesCKS EBE ThemesThemesCleanCategories.xsl] is referenced [1] times in the database [WSS_Content_SP2], but is not installed on the current farm. Please install any feature/solution which contains this file.</S>
      <S N="Remedy">One or more setup files are referenced in the database [WSS_Content_SP2], but are not installed on the current farm. Please install any feature or solution which contains these files.</S>
    </Props>
  </Obj>

Summary

Don’t just look to PreUpgradeCheck for checking your data.  Use Test-SpContentDatabase as well.  Don’t get overwhelmed with the output.  Decide what format is easiest to read and use for verifying and fixing.  By default YOU WILL be overwhelmed.  Use Output or Export to CSV, XML, or HTML and open it up in your browser or in Excel so you can much more easily consume, track and fix these issues.  In the example there are tons of lines of output, but ultimately by getting it into a format that’s readable I found most of these issues relate to a single feature that’s missing a number of themes that were referenced.  Looking at it on the command prompt it would be nearly impossible to make the same conclusion.  One last myth… Test-SpContentDatabase isn’t only for Database attach.  Use it with InPlace upgrade as well.  Setup a single install box and use the commandlet to check the source databases before any upgrade.  It will *Really* simplify your life.

I really appreciate the help of Niklas Goude who I ran into in the speaker room at SEF SharePoint Exchange Forum.  He handed me a his book which had great insights into using powershell with SharePoint.  Little about the Book: http://sharepointandpowershell.com/ Check out his blog www.powershell.nu and the co-author’s Mattias Karlsson’s Blog http://mysharepointofview.com/.  It’s hot off the shelves.

It’s already becoming commonplace.  The red bar at the top of the central administration is quickly becoming a part of what you expect to see when you login to CA.  I’m telling you now… that feature was designed to give you a heads up on issues and problems relating to the health of your farm… Already you’re ignoring it. Don’t deny it. Stop it!

First off “view these issues…”

One by one go through these issues and evaluate the criticality, but fix and clean it up…

1. Do you care?  If no, then edit the rule and uncheck enabled. You may need to reanalyze in some cases.  Take for instance your accounts.  Maybe you are using built-in for some of your services.  If you have decided that’s how you’re going to run things, then make the health rules reflect what you know and plan.  Obviously some of these are best practices and you should take them seriously, but after you’ve decided one way or another, it’s time to clean up the health so you can be notified when there are issues, and you don’t simply ignore the red bar.

2. Maybe you do care, but the frequency is too often, so change it from Daily to Weekly and so on.  I am a fan of onDemand for rules that I may check periodically on my own review schedule.

3. If the rule is tough to understand you can edit the title, maybe add why it is critical, and even change the status level of rules you care about.

4. Haven’t fixed it, but plan to? Add it into your tasks and get it fixed, but don’t live with the red bar.  You’ll learn to ignore important issues!!!

This patch Tuesday marks a couple of significant things to note.  One it’s a record for patch Tuesday with 16 bulletins across 49 vulnerabilities.  The previous record was 14 bulletins.  A few are critical which include Windows Server and IE, and even SharePoint 2007 and SharePoint Foundation 2010 are included… “MS10-072: Vulnerabilities in Microsoft SharePoint could allow information disclosure.”  Essentially the vulnerability in SharePoint is quite specific as it could allow information disclosure if an attacker submits specially crafted script to a target site using SafeHTML. 

There’s no need to freak out.  Just add this to your regularly scheduled patch process.  If you are following the cumulative updates, this will likely be included in the next month update if it’s not already included. 

IT Pro information on the “Important” SharePoint Patch MS10-072

MS10-072/KB2412048 – Important (SharePoint Services 3, SharePoint Foundation 2010, Office Web Apps, Office SharePoint Server 2007, Groove Server 2010): Issues with “SafeHTML” can allow attackers to have access to information that they should not on a variety of Microsoft collaboration platforms. It’s an important patch, but only if you use these tools. 12.0MB – 21.MB

Depending on what flavor of SharePoint you are running there are different KBs

More information on the other patches included in Patch Tuesday October 2010

• Microsoft Security Bulletin Advance Notification for October 2010 (microsoft.com
• Advance Notification Service for October 2010 Patch Tuesday including Webcast
• More information on all the patches

Some people are saying.  I’m having a problem upgrading the SSP with Database Attach upgrade.  Looks like I’m getting forced to use In-Place upgrade.

While There are some simple ways to upgrade search and single sign on with powershell scripts, the profile and audiences are more difficult.

I’ve been mentioning for some time that the way to upgrade the SSP database in a database attach method of upgrade is by using mount-spcontentdatabase.  Someone mentioned they had tried that and failed.

Ali Mazaheri has some good examples of doing this in his blog: Upgrading MOSS 2007 SSP User Profiles and My Site Content using Db Attach

There are some key pre-requisites before upgrading your SSP database:

1. Create a web app

2. Ensure the service app – managed metadata service is enabled (YES, this is a DEPENDENCY)

3. Ensure the Profile Service is up and running – and you should upgrade this database first with the special powershell cmdlet

If you are creating it fresh, you can use:

New-SPProfileServiceApplication -applicationpool <ApplicationPoolName> -Name <ServiceApplicationName> -Profiledbname <DatabaseName> [-Profiledbserver <ServerName>]

4. Create a new relationship/proxy for the User Profile Service App and ensure it is in the default proxy group.  You can do this in central admin or in powershell.

New-SPProfileServiceApplicationProxy -ServiceApplication <ProfileServiceApplicationID> -Name <ProxyName> -DefaultProxyGroup

5. Ensure the profile sync service is running

Note: The Profile Sync Service app Service Account for the profile sync job must be server admin, this is a FIM requirement.  Michael Noel uses a dedicated server for profile sync if the customer is concerned about the security/permissions.  Note the service

6. Upgrade the mysites before the SSP is mounted

Ultimately I think upgrading the services by doing an in place upgrade on the SSP is a decent way of upgrading the configuration.  I’m becoming a big fan of hybrid upgrade.

Webcast: Get to SharePoint 2010 – Strategies for Effective Upgrades and Migrations

Question and Answer Log:

I like to capture the questions from a good webcast.  Got a bunch of good ones today…
If you missed the presentation you can get a recording and the slides here.

Paul Asked: Are there major downsides to using SQL 2005 with SharePoint 2010?

The downsides are the features in SQL 2008 that SharePoint 2010 takes advantage of.  Starting with Mirroring, Remote Blob Storage, Backup Compression, Transparent Database Encryption, PowerPivot (see more below), Optimized Engine for performance, SQL Governor, etc…

Dutch McGehee Asked: Can I get a copy of the powerpoint slides?

Sure. http://www.quest.com/events/ListDetails.aspx?ContentID=12156

Question: What is the best non-destructive (to existing MOSS environment)
way to do upgrade if I’m upgrading to a new environment, but using the existing
production SQL?

Non Destructive?  Need to drill into that.  I’m guessing setting the current 2007 databases to read only as you migrate the databases with database attach to a new environment would be most non destructive.

Question: When will your book be available?

It’s available now in Rough Cuts on Safari. The other physical publish date is up to the publisher.

Travis Asked: Does PowerPivot require SQL 2008 R2? or will it work on 2008 and
2005 SQL?

It requires SQL 2008 R2.

“PowerPivot is essentially a way of making an analysis services cube using excel as the design tool.  When you use the PowerPivot for Excel add-in, then there no backend dependency, but if you want to save a PowerPivot to SharePoint 2010, there has to be a special installation of SQL Server 2008 R2 analysis services associated with the SharePoint farm.” Andrew Fryer in PowerPivot is Analysis Services sort of.

Question: Missed beginning…Is SQL 2008 required for SP 2010?

No, you can run SQL 2005 x64 with SP3 CU5 and later.

Jose Asked: can you use this method from 32bit to 64 bit?

Yes, you can use database attach to move from 32bit to 64bit SharePoint Servers, or you can upgrade the SQL environment first with In-Place Upgrade.

Steve Asked: Does Add Solutions include InfoPath forms in forms
services that have code-behind?

Some InfoPath forms will need to be redeployed especially in cases where these are server solutions with database attach.  In place upgrade would keep your solutions in place.  Some code behind may need to be tested and redeployed.  In all these cases I highly recommend testing.  Workflow is one area where I’ve found visual studio solutions do need to be updated.

Question: does the visual upgrade upgrades all sites in site collection or can it be done by each site?

It can be done granularly or the entire site collection.  You can also hide the preview option or upgrade it all at once.

Kyle Asked: Any experience or recommendations in using the AAM URL Redirection as part of the upgrade process

I recommend against using it if you don’t need it.  It is complex and adds an unnecessary complexity in most upgrades.  Some may think they need it if the upgrade will be over weeks and read only isn’t good enough.

Michael Asked: When should MySites/Profiles be migrated – before other site types? after?

You’ll want to make sure that your profiles and my site host is in place first.  I recommend getting the services in place, especially dependent services… before you start migrating content.

Question: Does Quest Migration Manager support migration of SP 2007/MOSS environment from on-premise to BPOS-D (SharePoint Online 2010)?

It’s in development.  Also note that SharePoint Online 2010 isn’t yet available for the general public.

Jeremy Asked: Does Migration manager work with WSS 3.0

Yes.  WSS 2.0, WSS 3.0 to SharePoint Foundation

or WSS 2.0, WSS 3.0 MOSS 2007 to SharePoint 2010

Question: In the Fab 40 page, you had mentioned http://bit.ly/dhQUjd . Is
this a valid site? I just checked and it showed error.

The link works for me.  It should redirect to: http://www.sharepointjoel.com/Lists/Posts/Post.aspx?ID=374 which has links to the blog post from the technical documentation team and links to Khalil’s upgraded SharePoint 2010 Fab 40.

Don Bruce Asked: Throughout the presentation you have mentioned a lot of things
done through PowerShell. We have typically thought of PowerShell as a
Developer-lite tool. How important is it that a SharePoint Administrator have
PowerShell skills?

Great question.  For SharePoint Administrators there are a lot of commandlets that act similar to STSADM and are very similar in their construction, but they give you the power of a scripting language.  I recommend reading my post on Powershell and SharePoint.

Practical Windows PowerShell for SharePoint 2010 – SharePoint Joel

Upgrading to SharePoint 2010 with Powershell

Sudip Shrestha Asked: I have a custom top navigation bar based on a xml datasource in a 2007 farm.. Does 2010 support 2007 type of navigation bar? How will 2010 handle this custom navigation bar?

I can’t answer specifically.  I do recommend testing.  I think you’ll find most things do upgrade quiet nicely.  Backward compatibility is quite good.  You will notice that binary upgrade will leave your nav bars, and visual upgrade will replace them.  You’ll need to add them back in after visual upgrade or add them to your masterpage.

Grace Sigal Asked: Very slow… i can’t see the demo

http://www.quest.com/events/ListDetails.aspx?ContentID=7836

Tim Chilva Asked: Does the "migration Manager handle placing "Content Type" into the "Master" META database, when a SP ’07 site is migrated?

This is a great feature request.

Pat Breitenbeck Asked: does quest have a doc that tells what Migration Manager
can do that PowerShell or Stsadm commands cannot do?

Mark Mascolino Asked: We have a large number of site collections in a single web
application. We expect that the upgrade timelline to require having two farms
2007 & 2010 in parallel for a few months. How can we use the db attach upgrade
approach to a new farm while maintaining a single hostname in both farms?
(sites.company.com) e.g. we need redirector from 2007 upgrade process

If you can do it a web application at a time over a weekend or use readonly mode for the source you should be ok.  If not you should look at the AAM Redirection or third party like Migration manager for post migration sync.  Alex has some good info on it.

Question: Will Migration Manager convert the links within content to the destination?

SharePoint Designer or STSADM –o import/export or powershell import/export will try to do link fixup, but only in lists, not within documents. 

Gerald Asked: can multiple 2007 publishing web applications be re-structured
under a single new 2010 web application

Yes with Migration manager you can consolidate sites during the migration.  With normal upgrade you’d need to either use import/export while on 2007 or while on 2010, not during the upgrade.

Archana Mordekar Asked: What needs to be done to move a SharePoint application
(site Structure, features, backend code) from one box to another? The
configuration on both boxes is the same – the app needs to be brought up on
another box

Backup/restore, and redeploy the solutions/features.

Question: If I’m running Quest WebParts for Sharepoint on a Sharepoint 2007 environment, will I have to upgrade to Quest WebParts for Sharepoint 2010, or the 2007 version of the Webparts work on 2010?

You can install the 2010 Quest webparts on a new farm with database attach.  With in-place you can upgrade the webparts after the upgrade.  I think you’ll find this consistent with most customizations.  They need to be in place on the source before the migration, or they need to be upgraded after, in the case of in-place upgrade.

Question To: Joel, what is your blog URL address?

http://www.sharepointjoel.com