Post
Ask Question

Archival Process for Modern and Classic SharePoint Lists - Part Two

Introduction 

 
Hi guys, whoever didn't get the chance to go through my previous blog, please click here. Today, let's talk more on overall high-level points and different approaches to take care of them in the Archival Process of Lists/Libraries with beyond Threshold and the entire site collections that hold them.
 
If the whole site collection needs to be archived, then simply create a migration using some tools like ShareGate, if Online to Online, SPMT, if from On-Prem to Online, Metallogix, if from OnPrem + SQL DB to Online etc. Furthermore, use PowerShell to lock them for READ Only after your complete migration of all site collections and related objects, such as:
  • Classic and Modern Lists
  • Classic and Modern Libraries 
  • Related Infopath Customizations
  • Related PowerApps Customizations
  • Related Workflows or Flows
  • Related Permissions
  • Related Navigations
  • Related Large Lists
  • Related Sub sites
Now let's talk about specific Lists/Libraries within a Site Collection. Here is the overall summary:
 
Business Requirement:
 
To provide the best suitable Archival Solutions for ABCM Corp, SharePoint Online Lists/Libraries both Modern/Classic Environment that hold records/items/documents beyond the threshold limit in each Site Collection.
 
SharePoint Online Site Collection List/Library Inventory Report Generation
 
Use the PowerShell For Each function to get all Lists/Libraries inside each Site collection with the Item Count > 5000 List View Threshold.
 
Challenges to be Addressed:
  • To Archive list items/documents/pictures based on user-defined data filters.
  • To Extract only the desired content based on various filter criteria (e.g., modified date, created by, stale libraries/lists, etc.), as SharePoint content is voluminous and structured.
  • To Export SharePoint content that has been recently modified since the previous export/archive session in order to avoid exporting the full content repeatedly.
  • The Exported content and metadata must be ready for automation using other tools/applications without the need for manual intervention, such as modifying or cleansing the metadata and/or the content.
  • To Maintain the same source folder structure and version history when exporting data from the SharePoint list/document library to manage and maintain older versions.
  • To Backup SharePoint content in file shares or file servers and migrate contents back to new SharePoint libraries/lists in a new server after de-duplication and metadata cleansing to avoid redundancy.
List/Libraries Archival Process High-level Points:
  • We have the below scenarios:

    • PowerApps Customized Modern Lists
    • Non-PowerApps Classic Customized Lists
    • Libraries with InfoPath Customizations
    • Modern Libraries

  • For the PowerApps Customized Modern Lists, the threshold limit is 2000 due to the Power App functions Delegate limit[Data row limit for non-delegable queries in PowerApps is 2000], while the other Lists/Libraries have a List View Threshold limit of 5000.
  • For both scenarios, we must come up with Archival Solutions. Here are the major high-level steps:

    • Create an Archival Site Collection[For Ex:- https://abcdefgcorp.sharepoint.com/sites/archive ].
    • Create an exact mirror image/replica of the given required Source Lists in the Target/Archive Site Collection with the below attributes:

      • List Columns,
      • Content Types,
      • Look Ups,
      • InfoPath Forms,
      • PowerApps functionalities if available [Migrating PowerApp form],
      • MS Flows,
      • SharePoint Designer WFs etc.

  • Set the Threshold Limit to the same as the source lists above.
  • Migration using 3rd Party Sharegate Tool OR Any other suitable Tool based on the complexity or based on the business requirements.
  • Notification to the Stakeholders once the Migration has completed.
  • Verification of all old Source List records in the Target Lists by the Stakeholders.
  • Approval from the Client to clean up old records on the Source List.

    • Clean up of Migrated Source List records programmatically using Scheduled Jobs through MS Flows,
    • PowerShell scripting.

  • Set the Created Target SP List to READ ONLY once the above process is finished.
  • Search Configuration in the Archive Site Collection with implementing Indexing on the Archived content.
Note
*Active Workflow-related List Items should be carefully ignored for the archival process.
  •  The above Archival Process a be repeated if the Archival List/Lib reached the Threshold limit. For Ex: AR Reports Archive, Archive2, Archive3, etc.
Workflow History List Handling
 
We have 2 Scenarios:- Active working Workflow List & Status Completed Workflow Items List. 
 
For an Active working Workflow List
 
One good idea is to create an MS Flow that quickly Get the Item from the Source List to be Archived due to Threshold >> Create the Item on the Target Archival List >> Deletion of the Source List Item once Archived in the Target promoting a seamless, continuous Scheduled Microsoft Flow that could be run Weekly once.
 
For Status Completed Workflow Items List
 
If it's SharePoint Designer related WorkFlow History List Items it's already a READ Only List and can't be deleted through PowerShell scripting.
 
However, there is a concept called Purging. Here are a few important points on it,
  • When you delete the List Items from List, it goes to the first recycle bin and then it goes to the second one. So you may want to turn this off when deleting the items. Sometimes it is not practical to delete items, so think about the overhead. Delete the item from a list, then 1st recycle bin and then 2nd recycle bin.
  • Delete Items in a batch. Instead of deleting them as a big list, delete them in chunk. (for ex: start with 2000 items)
This is something not recommended, but can be tried in one-off case. Basically, you can read List Item ID from the database instead of reading it using Object Model.
  1. === === === === === === === using System;  
  2. using System.Collections.Generic;  
  3. using System.Text;  
  4. using Microsoft.SharePoint;  
  5. using System.Data.SqlClient;  
  6. namespace PurgeList {  
  7.     class Program {  
  8.         private static int iteration = 5;  
  9.         private static int Count = 2000;  
  10.         static void Main(string[] args) {  
  11.                 if (args.Length != 1) {  
  12.                     Console.WriteLine(“first argument: No of iteration”);  
  13.                     Console.WriteLine(“YourprgramName.exe 5”);  
  14.                     Console.WriteLine(“ ** ** ** Program terminated: Argument missing!Please give one argument ** ** ”);  
  15.                 } else {  
  16.                     try {  
  17.                         iteration = Convert.ToInt32(args[0]);  
  18.                     } catch (Exception ex) {  
  19.                         Console.WriteLine(“Error: Failed to convert arguments: {  
  20.                             0  
  21.                         }”, ex.Message);  
  22.                         return; // exit  
  23.                     }  
  24.                     Console.WriteLine(“Current System Time(Start): ”+DateTime.Now.ToString());  
  25.                     try {  
  26.                         for (int iterate = 0; iterate < iteration; iterate++) {  
  27.                             int start = GetMinID();  
  28.                             Console.WriteLine(“Start Index: ”+start.ToString());  
  29.                             using(SPSite site = new SPSite(“http: //Enter Site Name“))  
  30.                                     {  
  31.                                         using(SPWeb web = site.OpenWeb()) {  
  32.                                             SPList wflist = web.Lists[“Workflow History”]; //workflow history  
  33.                                             string wflistID = wflist.ID.ToString();  
  34.                                             Console.WriteLine(“No of items before deletion: ”+wflist.ItemCount);  
  35.                                             Console.WriteLine(“Building query…“);  
  36.                                             StringBuilder batchString = new StringBuilder();  
  37.                                             batchString.Append(“xml version = \”1.0\” encoding = \”UTF - 8\” ? > ”);  
  38.                                             int end = start + Count– 1;  
  39.                                             for (int i = start; i <= end; i++) {  
  40.                                                 batchString.Append(“ < Method > ”);  
  41.                                                 batchString.Append(“ < SetList Scope = \”Request\” > ”+wflistID + “”);  
  42.                                                 batchString.Append(“ < SetVar Name = \”ID\” > ”+Convert.ToString(i) + “”);  
  43.                                                 batchString.Append(“ < SetVar Name = \”Cmd\” > Delete”);  
  44.                                                 batchString.Append(“ < /Method>”);  
  45.                                                 }  
  46.                                                 batchString.Append(“ < /Batch>”);  
  47.                                                     //Console.WriteLine(batchString.ToString());  
  48.                                                     try {  
  49.                                                         web.AllowUnsafeUpdates = true;  
  50.                                                         Console.WriteLine(“Executing query…“);  
  51.                                                         Console.WriteLine(“Batch Execution(Start): ”+DateTime.Now.ToString());  
  52.                                                         string result = web.ProcessBatchData(batchString.ToString());  
  53.                                                         //Console.WriteLine(result);  
  54.                                                         web.AllowUnsafeUpdates = false;  
  55.                                                         Console.WriteLine(“Batch Execution(END): ”+DateTime.Now.ToString());  
  56.                                                     } catch (Exception ex) {  
  57.                                                         Console.WriteLine(“Process batch error: ”+ex.Message);  
  58.                                                     }  
  59.                                                     Console.WriteLine(“No of items before deletion: ”+wflist.ItemCount); using(SPSite site1 = new SPSite(“http: //Enter Site Name“))  
  60.                                                             {  
  61.                                                                 using(SPWeb web1 = site1.OpenWeb()) {  
  62.                                                                     Console.WriteLine(“No of item after deletion: ”+web1.Lists[“Workflow History”].ItemCount);  
  63.                                                                 }  
  64.                                                             }  
  65.                                                             Console.WriteLine(“————————————————–“);  
  66.                                                         }  
  67.                                                     }  
  68.                                                 }  
  69.                                                 Console.WriteLine(“Current System Time(End): ”+DateTime.Now.ToString());  
  70.                                                 Console.WriteLine(“———–Program Completed————“);  
  71.                                             } catch (Exception ex) {  
  72.                                                 Console.WriteLine(“Error: ”+ex.Message);  
  73.                                                 Console.WriteLine(“ ** ** ** Program terminated due to error ** ** “);  
  74.                                             }  
  75.                                         }  
  76.                                     }  
  77.                                     public static int GetMinID() {  
  78.                                         try {  
  79.                                             string connString = @” Data Source = .\SHAREPOINT;  
  80.                                             Initial Catalog = WSS_Content_c20feb22657e4e2ab82f7db433f3e4c7;  
  81.                                             Integrated Security = SSPI”;  
  82.                                             using(SqlConnection objConn = new SqlConnection(connString)) {  
  83.                                                 objConn.Open();  
  84.                                                 string sqlString = “Select min(tp_ID) as col1 from dbo.alluserdata where tp_listid = ‘6463 BECE - 3560 - 4 D15 - B965 - B245F3203BEE '”;// workflow list id  
  85.                                                 SqlCommand cmd = new SqlCommand(sqlString, objConn);  
  86.                                                 int id = Convert.ToInt32(cmd.ExecuteScalar());  
  87.                                                 return id;  
  88.                                             }  
  89.                                         } catch (Exception ex) {  
  90.                                             Console.WriteLine(“SQL ERROR: ”+ex.Message);  
  91.                                             throw ex;  
  92.                                         }  
  93.                                     }  
  94.                                 }  
  95.                             } === === === === === === === === === === === =  
Microsoft Strategy for Archival
  • https://techcommunity.microsoft.com/t5/sharepoint/sharepoint-online-backup-strategies-for-a-cloudy-day/m-p/225418
  • https://www.sharepointeurope.com/archiving-in-sharepoint/

3rd Party Tools available for Archival Solutions

 
AvePoint
 
AvePoint offers a robust platform for managing SharePoint Online. The backup and restore option offers a comprehensive suite of capabilities for SharePoint Online.
 
http://www.avepoint.com/products/office-365-online-services/management
 
Quick Overview on Technical Capabilities can be found here,
 
http://www.avepoint.com/assets/pdf/technical_overview/DocAve_Online_Technical_Overview.pdf
 
Metalogix
 
Metalogix offers a comprehensive solution to managing Office 365 including comprehensive backups for content across Office 365 servers including SharePoint Online and OneDrive for Business.
 
http://www.metalogix.com/Products/essentials-for-office-365/manage
 
Notes on feature capabilities can be found here,
 
http://www.metalogix.com/docs/default-source/product-collateral/metalogix-essentials-for-office-365-data-sheet.pdf
 
CloudAlly
 
CloudAlly is a solution Focal Point does not have experience with. It can meet the backup/archive needs based on information presented on this site.
 
http://www.cloudally.com/sharepoint-backup/
 
LEAP
 
LEAP, like CloudAlly, is a solution Focal Point does not have experience with. Backups are stored in Windows Azure Storage where only Motoman has access.
 
https://www.leaphq.com/
 
LISTMAN
 
A modern .NET app that uses CSOM and allows you to archive or export data from large lists without a 5000 view limit.
 
www.listman.io
 
Happy archiving with the above knowledge!! 
Next Recommended Reading Archival Process for Modern and Classic SharePoint Lists - Part One