Sharepoint Pilot Migration – Quickly create large lists

Introduction

There are various tools in the market from third party vendors to ease the migration experience. Each vendor claims their product is superior and when the options are plenty, its too hard for us to go by the analysis and choose the right tool that fits our business scenario. This blog focusses on how to quickly choose the best tool for a specific scenario such as a site collection having large lists.

To refine the number of options available for migration, let us focus only on migrating the Sharepoint content from an On-Premise Sharepoint to O365 in this blog. Again when migrating on-premise content to a Sharepoint Online tenant, the primary factor we need to call out at the initial stage is whether its a dedicated tenant or a multi-tenant target. As migration to SPOD (Sharepoint Online Dedicated) is supported by Microsoft, let us focus on the multi-tenant scenario. Following tools are available in the market irrespective of their rankings.

1) MetaLogix 2) Sharegate 3) Quest 4) Doc Ave and others

Let us consider a scenario of migrating a large list having around 5000 list items on the source site and evaluate each of the tools mentioned above for efficiency, when the target is an O365 tenant. Remember this is a pilot migration and we are evaluating the tools, so it is evident that we do not have the test data in hand. How do we quickly create a large list and populate 5000 list items on the source site. Below is a power shell script which exactly does that. This script has been verified to create more than 5000 list items but just be sure to set the list view threshold to the appropriate value you are targeting. Ofcourse, we have a logical question here to better design the lists in the target environment, however if the customer insists not to split the list, then it is always safe to be prepared with our evaluation for larger lists greater than or equal to 5000 list items. Please observe that the script not only focuses on the number of items but also on the number of columns thus making the list created out of this script really large.

#Start of Script

# set number of columns to create for various data types
$IntColumnsToCreate = 30               # max of 96
$BoolColumnsToCreate = 30              # max of 96
$ChoiceColumnsToCreate = 0             # max of 276
$SingleLineTextColumnsToCreate = 70    # max of 276
$MultiLineTextColumnsToCreate = 42     # max of 192
$DateTimeColumnsToCreate = 10          # max of 48
$CurrencyColumnsToCreate = 40          # max of 72

# set number of items to create
$ItemsToCreate = 5000

# URL of site that will contain the large list
$LargeListSiteURL = "http://MigEval/sites/demo"

if((Get-PSSnapin "Microsoft.SharePoint.PowerShell") -eq $null)
{
Add-PSSnapin Microsoft.SharePoint.PowerShell
}

Start-SPAssignment -Global

# get reference to the "large list" site
$web = Get-SPWeb -Identity $LargeListSiteURL

# if large list exists delete it
if($web.Lists["EvalList"] -ne $null)
{
$web.Lists["EvalList"].Delete()
}

# create list and get reference to list
$web.Lists.Add("EvalList", "Test Large List", [microsoft.sharepoint.splisttemplatetype]::GenericList)
$list = $web.Lists["EvalList"]

# add integer columns
for($count = 1; $count -le $IntColumnsToCreate; $count++)
{$list.Fields.Add("Int$count", [microsoft.sharepoint.SPFieldType]::Integer, $false)}

# add boolean columns
for($count = 1; $count -le $BoolColumnsToCreate; $count++)
{$list.Fields.Add("Bool$count", [microsoft.sharepoint.SPFieldType]::Boolean, $false)}

# add choice columns
for($count = 1; $count -le $ChoiceColumnsToCreate; $count++)
{$list.Fields.Add("Choice$count", [microsoft.sharepoint.SPFieldType]::Choice, $false)}

# add single line text columns
for($count = 1; $count -le $SingleLineTextColumnsToCreate; $count++)
{$list.Fields.Add("SingleLineText$count", [microsoft.sharepoint.SPFieldType]::Text, $false)}

# add multi line text columns
for($count = 1; $count -le $MultiLineTextColumnsToCreate; $count++)
{$list.Fields.Add("MultiLineText$count", [microsoft.sharepoint.SPFieldType]::Note, $false)}

# add date time columns
for($count = 1; $count -le $DateTimeColumnsToCreate; $count++)
{$list.Fields.Add("DateTime$count", [microsoft.sharepoint.SPFieldType]::DateTime, $false)}

# add currency columns
for($count = 1; $count -le $CurrencyColumnsToCreate; $count++)
{$list.Fields.Add("Currency$count", [microsoft.sharepoint.SPFieldType]::Currency, $false)}

# populate list with items
foreach($x in 1..$ItemsToCreate)
{
$item = $list.AddItem()
$item["Title"] = [char](97 + (Get-Random -Maximum 25))

    # assign values to integer columns
    for($count = 1; $count -le $IntColumnsToCreate; $count++)
    {$item["Int$count"] = Get-Random -Minimum -100000 -Maximum 100000}

    # assign values to boolean columns
    for($count = 1; $count -le $BoolColumnsToCreate; $count++)
    {$item["Bool$count"] = [bool](Get-Random -Minimum 0 -Maximum 2)}

    # assign values to choice columns
    $choices = "Cat1;Cat2;Cat3;Cat4" -split ";"

    for($count = 1; $count -le $ChoiceColumnsToCreate; $count++)
    {$item["Choice$count"] = $choices

    # assign values to single line text columns
    for($count = 1; $count -le $SingleLineTextColumnsToCreate; $count++)
    {$item["SingleLineText$count"] = "sampletext for test " * (Get-Random -Minimum 1 -Maximum 8)}

    # assign values to multi line text columns
    for($count = 1; $count -le $MultiLineTextColumnsToCreate; $count++)
    {$item["MultiLineText$count"] = "sampletext for test " * (Get-Random -Minimum 1 -Maximum 200)}

    # assign values to date time columns
    for($count = 1; $count -le $DateTimeColumnsToCreate; $count++)
    {$item["DateTime$count"] = (Get-Date).AddDays((Get-Random -Minimum -1000 -Maximum 1000))}

    # assign values to currency columns
    for($count = 1; $count -le $CurrencyColumnsToCreate; $count++)
    {$item["Currency$count"] = [system.decimal](Get-Random -Minimum -10000 -Maximum 10000)}

$item.Update()
}

#End of Script

We can now leverage this script when evaluating different tools for the large list scenario!!!

SecureInfra Team

Author