sharepoint, sharepoint 2013

Recursively Copy Document Libraries from SharePoint 2010 to SharePoint 2013

We’ve recently been looking at migrating quite a large amount of content from our legacy SharePoint 2010 Farm to a new clean SharePoint 2013 installation. For a variety of reasons we didn’t want to go the whole content database->site collection upgrade approach, not least of all because we really wanted a clean break from what has been quite a naughty SharePoint 2010 farm. The thing is creaking, it hates all forms of human life and is quite possibly haunted. Because of this we wanted to bring the binary file data across and essentially nothing else.

Migrating documents around would seem to be one of those things that an Enterprise class document management platform should have nailed. However it turns out, its not quite as straightforward as you might think.

Perhaps I’m asking too much, but there didn’t seem to be any simple way of saying, “recursively copy all that, and place it there”.

I came across scores of roll your own scripts, C# apps and seriously expensive bits of software that would charge me by the gigabyte. For various reasons most of the approaches I tried didn’t pan out. We either had to spend a ton of cash to get a pro piece of software to (presumably) do what we needed, or we could spend a lot of time writing our own recursive code via either Powershell or C# and attempt to work around the fact that there is no real recursive copy function in the SharePoint APIs – at least not one that I found.

The approach I ended up going with is undoubtedly brute force, but has been extremely effective. The process is essentially a powershell script that does the following:

  1. Loop through all the target document libraries at the source site
  2. Dynamically create a *mapped drive* to both the Source and Destination locations
  3. Robocopy the files between the two mapped folders – taking advantage of the fact that robocopy doesn’t care that this is sharepoint behind the scenes and handles recursive copy like a walk in the park
  4. Wonder why that wasn’t considerably easier

There is one caveat here – because we were rearranging our library structure, I had already pre-created the destination document libraries. You may well not be in this scenario, in which case you will need to tweak the script to potentially create a document library at the correct location as you go. This would probably be a one line change.

The following script will need to be tweaked to get your source and destination library locations lining up. It won’t work right off the bat, but I did want to provide as a sample to demonstrate that powershell + robocopy can be used to migrate a large amount of content as it took me waaaayy to long to get to this point (thanks MS)


cls

$currentSourceDriveMapping = ""
$currentDestinationDriveMapping = ""

$sourceWeb = Get-SPWeb "http://mysourcelocation"
$destinationWeb =  Get-SPWeb "http://mydestinationsite"

Write-Host "Connected to $sourceWeb"
Write-Host "Connected to $destinationWeb"

Write-Host "Releasing mapped drives"

$sourceLibraries = $sourceWeb.Lists

Write-Host $sourceLibraries.Count + " document libraries found at source location"

$listTemplate = [Microsoft.SharePoint.SPListTemplateType]::DocumentLibrary

$processedCounter = 1

foreach($currentSourceLibrary in $sourceLibraries){
	
	Write-Host "Mapping Source Library to S drive"
	
	$currentSourceDriveMapping = "http://mysourcelocation" + $currentSourceLibrary.RootFolder.ServerRelativeUrl
		
	Write-Host $currentSourceDriveMapping
	
	# net use will create a mapped drive using the sharepoint location provided 
	net use s: "$currentSourceDriveMapping"
	
	Write-Host "Mapping Source Library to T drive"
		
	# NOTE: Voodoo here that won't apply to you - this made sense in my environment due to restructuring that we were doing
	$currentDestinationDriveMapping = $destinationWeb.Url + "/" + $currentSourceLibrary.Description + "/Tech Notes"
	
	Write-Host $currentDestinationDriveMapping
	
	net use t: "$currentDestinationDriveMapping"
	
	Write-Host "Mapping Source Library to T drive"
	
	# Robocopy all folders apart from the sharepoint specific Forms folder at the root of the source library
	# Note that robocopy is ideal for this as it already implictly handles recursive copying
	robocopy S: T: /e /xd s:\Forms
	
	Write-Host "Releasing mapped drives"
	net use s: /delete
	net use t: /delete
	
	Write-Host $processedCounter " records libraries processed successfully"
	
	$processedCounter++

}

I’ve used this script to migrate about 25GB worth of content in 2 hours. It’s not lightning fast, but it got the job done.

There are likely numerous caveats that may apply to you but didn’t to me. Amongst them would be the fact that any relvant permissions, history, properties will almost certainly not be copied over.

If you just need a fairly straight recursive dump of content though this may give you some pointers

sharepoint

We’re having a problem opening this location in File Explorer

If you get the following on a SharePoint 2013 install when trying to use the “Open in Explorer” feature from the server:

We’re having a problem opening this location in File Explorer. Add this web site to your Trusted Sites list and try again

You may need to add the “Desktop Experience” feature as this is not installed on a server OS by default.

You can follow the instructions below:

http://blogs.technet.com/b/rmilne/archive/2013/07/11/install-desktop-experience-on-windows-server-2012.aspx

It should go without saying that you probably don’t want to do this on a production server, but you may need/want to on a development box

sharepoint

Hang whilst configuring farm in SharePoint 2013

I’ve recently been getting a new production SharePoint 2013 farm set up at work. The following cost me a couple of hours so I thought I’d share in case it saves anyone else some time.

Symptoms:

  • Provisioning a new Central Admin Site either via Powershell or the SharePoint Configuration Wizard seems to hang. The SharePoint Configuration Wizard will tend to hang at Step 3
  • There are no obvious error messages but nothing seems to be happening – low CPU/Mem/Disk usage on all servers
  • You may find the following placed in the SharePoint logs over and over again:
    • “Not running in high contrast, so we will paint the background with our trademarked image”

This was actually being caused by the Behavior Monitoring feature in our Sophos Antivirus. We had followed the MS guidance on excluding various directories from the virus scans and thought we had it covered. Not so – Sophos was silently crippling performance through its behavior analysis module.

I’m not certain but there was some anecdotal evidence from watching some SQL Server activity that Sophos was limiting the nuymber of simultaneous connections the SharePoint config wizard was being allowed to open at one time.

With Sophos on, only 2 -3 queries would complete per second on the SQL Server. With Sophos uninstalled performance jumped to 10s/100s queries per second. If you are finding similar behaviour where query throughput during large portions of the install is in the toilet, have a look into any antivirus or threat prevention tools you might be running

We havent yet experimented with just disabling the behaviour analysis feature rather than fully uninstalling Sophos so far

Disaster Recovery, High Availability, SQL Server, SQL Server 2016

Temporal Tables Coming in SQL Server 2016

A while back I wrote a piece about how Oracle Flashback was the only Oracle feature remaining that I would love for SQL Server. This after many years early in my career where I was frequently hit with occasional bouts of database envy.

You can see the full details of why I hanker for this feature here, https://rule30.wordpress.com/2014/04/23/the-only-oracle-feature-i-actually-still-want/ – but in short its because it lets you do this:

SELECT * FROM Orders
AS OF TIMESTAMP -- Say Wwwwhat!!
TO_TIMESTAMP('2014-04-23 09:30:00', 'YYYY-MM-DD HH:MI:SS')

The great news is that Satya Nadella has clearly been checking out the blog here and “flashback” queries are going to be included in SQL Server 2016 under the name Temporal Tables or System Versioned tables. Full details here:

Temporal Tables in SQL Server 2016

The key syntax is shown below:

-- To the Delorian!
SELECT * FROM Orders 
FOR SYSTEM_TIME AS OF '2014-04-23 09:30:00' 

Fantastic! The key use case I have currently is in providing a recovery option for managing VLDBs however there are very obvious applications for this in BI and Auditing considering the feature has been implemented as a simple first class concept in standard T-SQL.

A couple of observations:

  • As of CTP 2 Management Studio tooling doesn’t seem to have any support for enabling temporal/system versioned tables. Opting to create a System Versioned table throws you out to a standard coding window where you’ll have to get your DDL on. I’m guessing the tooling will catch up before release
  • I’m really liking the simplicity of the implementation. Enabling and managing Flashback in Oracle seemed like a bit of an ordeal to me so MS are really playing to their strengths. Once created in SQL Server a temporal table is really just a standard table with a secondary hidden table being maintained for you behind the scenes – its that simple
  • I’m not clear which versions of SQL Server will come with temporal tables. It “feels like” an Enterprise only feature given its high availability/big data applications but I haven’t seen anything on that yet. Even at SQL Server Enterprise pricing though its almost certainly going to be a bargain compared to Oracle.
sharepoint 2013

SharePoint 2013: Invalid URI: The hostname could not be parsed

A quick tip that might help you if you are setting up a new SharePoint 2013 environment. This tip applies if you get the following symptoms when trying to browse a SharePoint hosted App via your development site collection:

  1. You get Invalid URI: The hostname could not be parsed errors in your event log
  2. Your app doesn’t render correctly with all CSS and images having been stripped away. Using fiddler you might be able to see an HTTP 500 error coming back on each resource request

In my case the issue was a little embarrassing. For reasons that elude me just now I had created the new SharePoint 2013 farm without creating a default/root site collection.

Once you create your root site collection the issue should go away and is probably caused by the development site collection referencing core files in the layouts folder at the root of the site.

Hope that helps someone as it was doing my head in for about an hour!

Corruption, SQL Server

Handling real world corruption of a SharePoint Content Database

You never want to come in to work to find that one of your critical SQL Server databases is corrupt but that’s exactly what happened today due to a SAN issue. I’ve got a fair amount of SQL Server experience but not so much so that a failed SQL Server corruption check doesn’t instinctively cause me to wonder out loud how up to date my CV is  😉

That said we did manage to repair the corruption with no downtime and no data loss so I thought I’d do a quick blog to describe what I did to resolve the issue in case it helps anyone who has a similar issue.

Its important to understand that there are a huge variety of corruption scenarios that can occur in the real world and the lessons here may well not apply to you. Our DB failed in a very specific way that lent itself to a relatively quick repair with no downtime. You may be able to take some of the ideas here and run with them though so I thought it worth a share.

What did SQL Server think was wrong?

We picked up on the problem when one of our custom maintenance jobs emailed me the results of a failed DBCC CHECKDB command. These corruption checks are crucial as they tell you about problems whilst you still have enough backups in rotation to be able to do something about them. I’ve actually gone as far as running them nightly using some hand crafted scripts that will email step by step guidance to the IT team on how to handle the problem in case I’m on holiday. If you aren’t doing frequent DBCC  CHECKDBs, you really need to start now.

The first thing we want to do is find out what SQL Server thinks is wrong. The key thing to do here is not panic. The SharePoint system in question was clattering a long quite happily at this point so there is plenty of time to gather diagnostics.

The first thing we want to do is to run a full CHECKDB. This will give us SQL Server’s assessment of what’s wrong and would likely be the first thing MS would ask for if you called them:

DBCC CHECKDB ('<database.') with no_infomsgs

This gave us the following output, giving us our initial handle on how severe the problem was (apologies for the formatting):

Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373408), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373409), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373410), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373411), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373412), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373413), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373414), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373415), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594094747648 (type In-row data): Errors found in off-row data with ID 1311506432 owned by data record identified by RID = (1:2277271:5)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594094747648 (type In-row data): Errors found in off-row data with ID 333774848 owned by data record identified by RID = (1:2282679:0)
CHECKDB found 0 allocation errors and 10 consistency errors in table 'AllDocStreams' (object ID 373576369).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'WSS_Content_CustomerDocs'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (WSS_Content_CustomerDocs).

Seems fairly scary but I actually started to relax a little once I read this, for reasons that will become apparent.

The first thing to understand about this database is that it is a large sharepoint content database.

This is actually great news because it means that the *vast* majority of the data in the db is not actually *row* data, it’s going to be BLOB data that represents the various files in SharePoint. I say good because in a 100GB content database, sheer probability suggests that if we did get a small corruption it would very likely fall within the BLOB data of a single file rather than row data or the intrinsic data structures of SQL Server. Actual corruption to rows or intrinsic data structures are far more problematic and I’d gladly write off a word document or powerpoint presentation if it meant not having to deal with that 😉

Reading through the output we can see that SQL Server is having an issue reconciling the row data stored on a single 8K page with the binary LOB data it stores “off row”. This tells me the following:

  • The root issue is with a single 8K page
  • The broader issue with that 8K page is that some pointers to the “off row” data seems a bit messed up.
  • Given the number of “off row” pages involved (8), the corruption seems to be very limited in scope – probably about 64KB given an 8K page size. It’s quite likely in fact that if we could pin it down, we might only be dealing with one or two corrupt files

In the grand scheme of things, this isn’t actually that bad.

Finding out what’s actually damaged

We now have a fairly good assessment of the scale of the corruption but we don’t actually have a great understanding of what that corruption corresponds to in the real world. Given that we’re talking about a SharePoint content database here that binary data could correspond to a system critical .aspx page or the sales guys sharing a humorous gif:

1385331909_dancing_baby

The key areas of the error message I’m now interested are the two lines near the end which say something along the lines of:

Errors found in off-row data with ID 1311506432 owned by data record identified by RID = (1:2277271:5)

We’re also told that the only errors were found in the AllDocStreams table.

The RID referenced above is crucial because if I could determine what actual row RID (1:2277271:5) corresponds to, I have a very good chance of determining what the real world file is.

Using the undocumented sys.fn_PhysLocFormatter function we can do just that – take a low level RID and use it to identify the human readable row. The full (Sharepoint specific) query I used is as follows:

-- Identify the corrupt rows by the physical location reported by DBCC CHECKDB
-- Exclude the content column as this will probably cause a crash
SELECT [Id]
,[SiteId]
,[InternalVersion]
, sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID]

FROM
[AllDocStreams]

WHERE
sys.fn_PhysLocFormatter (%%physloc%%) LIKE ('%(1:2282679:0)%') OR sys.fn_PhysLocFormatter (%%physloc%%) LIKE ('%(1:2277271:5)%')

The results returned 2 rows indicating that as expected, at most 2 document streams were involved.

We then did a query on the document IDs on sharepoint’s AllDocs table to get the actual locations and file names of the corrupt files.


-- Check the problem rows are readable from the corrupt table
SELECT 
	AllDocStreams.Id, AllDocStreams.SiteId
FROM AllDocStreams

WHERE Id = 'E55D0C17-239F-4F9A-8BBE-2D7B89A5FAAD' OR Id = '749E1AA8-A96F-48F8-8EEF-B40AF72477B9'


-- SharePoint Specific
-- Now we've identified the problem document *stream*, use the same ID to find out what the actual document name and location is in SharePoint
SELECT * 
FROM AllDocs

WHERE Id = 'E55D0C17-239F-4F9A-8BBE-2D7B89A5FAAD' OR Id = '749E1AA8-A96F-48F8-8EEF-B40AF72477B9'

The results can be seen below. I’ve had to obscure some of it but you can hopefully make out that the two problem files are effectively 2 Word documents.

Reverse engineering corrupt rows

So – we’ve managed to confirm that our relatively scary corruption scenario is actually just a problem with 2 files.

The structure of the database is fine and there is no nasty low level row data involvement that would lead us to be worried about row inconsistencies if we tried a repair.

With the two document locations in hand I then went into SharePoint and attempted to open the document in question. Sure enough the documents were corrupt, but crucially SharePoint didn’t have any issues attempting to open them or download them. This was a very strong indicator that the corruption was contained entirely in the files themselves and that both SharePoint and the SQL Server itself were either oblivious or unconcerned with the corruption within.

OK – so this next bit was a bit of a gamble as there was no real guarantee over what would happen. It was better than the alternatives I had on offer though so was worth a whirl.

After checking that the documents were non-critical or could be retrieved from secondary sources I simply deleted the documents from Sharepoint. I figured there was a chance that SharePoint would crash at this point but I had a strong hunch that we would get away with it. My strong feeling at this point is that the corruption is solely in the payload of the documents, and even SQL Server is unaware of the corruption at a runtime level. It is actually when you ask it to go looking via a CHECK DB that it reports the problem.

I had to delete the documents from both the document libraries, site recycle bin and the site collection recycle bin but once that was done all seemed well. I ran another quick DBCC CHECKDB and it passed without issue.

Corruption begone!

Conclusion

I’ve got to be honest, the sort of corruption that we had was at the more benign end of the spectrum. No row data or internal data structures were actually involved in the corruption. We also got lucky that a straight delete of the data from SharePoint even worked.

That said, if there is a lesson to be learned from the episode it would be – “don’t overreact”.

Check what the actual corruption is using DBCC CHECKDB and think logically about what is likely to be damaged given the sort of data your storing.

If the system is still online (which it often will be) don’t feel compelled to act – take your time, gather information and you may well find that you can do a complete repair without a moments downtime

cluster, sql server 2008

Windows Server 2003 Hotfix KB937444 is not installed

If you’re installing a Windows Server 2008 R2 cluster on Windows Server 2012 R2, you may be hit with the installer asking you to install a Server 2003 hotfix referenced in KB 937444. The problem being, there is no chance that 2003 hotfix is going to install on a 2012 OS.

The issue is resolved in SQL Server 2008 R2 SP1 but if you’re installing from the RTM media you will be in a chicken and egg situation. You can’t install SQL Server 2008 without SP1, and you can’t install SP1 without getting the main SQL Server 2008 install done.

The solution is fairly tedious – you can create your own 2008 SP1 slipstream with a bit of extracting ISO’s and copying files and folders around. Don’t worry – it can all be done on disk – you don’t need to burn a DVD or anything like that.

The full details of the process are here –

http://blogs.msdn.com/b/petersad/archive/2011/07/13/how-to-slipstream-sql-server-2008-r2-and-a-sql-server-2008-r2-service-pack-1-sp1.aspx

It took about 20 minutes of dicking about, but the cluster install sequence sailed through once using the slipstreamed installer.