Attachment Archive and Expire

Overview

Several QBO modules leverage the Attachment module, including qbo.Import, qbo.Contact, qbo.Debt and qbo.Score. Over the course of time, it the number of attachment accumulated by these modules can cause the Attachment module's performance to decrease, and incur unwanted storage expenses. To manage such issues, we can leverage archiving and expiration.

Attachment Templates offer fields that determine when to automatically archived (moved to cheap storage) or expired (deleted), based on the age of the attachment. Modules that use attachments "under the hood" can be configured to automatically use configured attachment templates to drive archiving and expiration. To configure:
  1. From Design > Documents, create a template configured with the archive and expiration fields set to whatever values you deem appropriate
    • in the examples below, the template created is called "SystemAttachmentTemplate"
  2. From Design > Configuration > Queuing, create a maintenance queue to ensure archiving and expiration jobs don't interfere with other processing
  3. Consider updating existing Attachment rows to populate the AttachmentTemplateID, ExpirationDate and ArchiveDate fields
  4. From Design > Configuration > {Module} > Settings, set the Default*AttachmentTemplate values to the template configured in step 1
    • Modules that leverage such settings include: ImportFile, ServiceRequest, ProfessionalLicense, CreditReport, Bucket, BucketItem, Score
Settings related to attachment templates:
  • qbo.Import
    • DefaultPreTransformAttachmentTemplate
    • DefaultPostTransformAttachmentTemplate
    • DefaultServiceRequestAttachmentTemplate
    • DefaultImportResultAttachmentTemplate
    • DefaultBucketAttachmentTemplate
    • DefaultBucketItemAttachmentTemplate
  • qbo.Contact
    • DefaultProfessionalLicenseAttachmentTemplate
  • qbo.Credit
    • DefaultCreditReportAttachmentTemplate
  • qbo.Score
    • DefaultScoreAttachmentTemplate

Empty Folder Cleanup

A few new settings have been introduced to the UNC File Object:
  • DeleteParentDirectory - bool (defaults to false). If set to true and the folder this attachment is in is empty, the folder will be deleted (Assuming it's not the file object assembly's root folder and not a protected folder name)
  • DeleteParentDirectoryDepth - int (defaults to 1). This will move up the directory tree deleting all empty folders until it finds a non-empty, non-protected, non-root folder.
  • DeleteParentProtectedFolderNames - string (defaults to empty). Forces protection of any folders with these names from ever being deleted. These are | delimited. E.G. "IN|OUT"
If you use the suggested FileNameFormat (/{Year}/{Month}/{Day}/{Object}/{ObjectID}/{Guid}.xml), it is recommended to have a DeleteParentDirectoryDepth of 6.

Sample File Object:

<ConfigurationEntryItem>
    <ConfigurationEntry>Standard BLOB Storage</ConfigurationEntry>
    <Source>FileObject.config</Source>
    <ConfigurationType>qbo.Attachment.Configuration.FileObjectCollection</ConfigurationType>
    <ConfigurationKey>Standard BLOB Storage</ConfigurationKey>
    <SubscriberID>qbs.quandis.net-FileObject-Standard BLOB Storage</SubscriberID>
    <ConfigurationXml>
      <FileObject Name="Standard BLOB Storage" 
                  Type="qbo.Attachment.FileObjects.UNCFile, qbo.Attachment"      
                  Uri="//HDCShared/UATFileStore/devqbs.quandis.net/" 
                  Settings="DeleteParentDirectory=true&amp;DeleteParentDirectoryDepth=6&amp;DeleteParentProtectedFolderNames=IN|OUT"
                  Compression="false"/>
    </ConfigurationXml>
 </ConfigurationEntryItem>

Data Listeners

The following sample XML will create jobs (data listeners) to archive and expire attachments based on the template settings. These data listeners are part of source-controlled Attachment.config, so this is for illustration purposes or overrides only.

Archiving

<ConfigurationEntryItem>
  <ConfigurationEntry>Attachment/ArchiveAttachments</ConfigurationEntry>
  <Source>Attachment.config</Source>
  <ConfigurationType>qbo.Application.Configuration.DataListenerCollection</ConfigurationType>
  <ConfigurationKey>ArchiveAttachments</ConfigurationKey>
  <ConfigurationXml>
    <Listener Name="ArchiveAttachments" Statement="ArchiveFileSearch" Class="Attachment" Operation="Archive" Parameters="AttachmentID={AttachmentID}&amp;UpdatedDate={UpdatedDate}" Async="False" StopOnError="False" Status="" />
  </ConfigurationXml>
</ConfigurationEntryItem>

Expiration

<ConfigurationEntryItem>
  <ConfigurationEntry>Attachment/ExpireAttachments</ConfigurationEntry>
  <Source>Attachment.config</Source>
  <ConfigurationType>qbo.Application.Configuration.DataListenerCollection</ConfigurationType>
  <ConfigurationKey>ExpireAttachments</ConfigurationKey>
  <ConfigurationXml>
    <Listener Name="ExpireAttachments" Statement="ExpireFileSearch" Class="Attachment" Operation="Expire" Parameters="AttachmentID={AttachmentID}&amp;UpdatedDate={UpdatedDate}" Async="False" StopOnError="False" Status="" />
  </ConfigurationXml>
</ConfigurationEntryItem>

Queue

The following sample XML will create a system maintenance queue to run the jobs above against.

<ConfigurationEntryItem>
  <ConfigurationEntry>SystemMaintenance</ConfigurationEntry>
  <Source>Queuing.config</Source>
  <ConfigurationType>qbo.Application.Configuration.QueueCollection</ConfigurationType>
  <ConfigurationKey>SystemMaintenanceQueue</ConfigurationKey>
  <ConfigurationXml>
    <Queue Name="SystemMaintenance"
           Type="qbo.Queue.MSSQL.ServiceBroker, qbo.Queue.MSSQL, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
           TypeLabel="qbo.Queue.MSSQL.ServiceBroker"
           Uri="SystemMaintenance"
           Threads="1"
           RetryCount="1"
           RetryIntervalType="None"
           ThrottleIntervalType="None"
           Schedule="Queue - Business Hours"
           Status="Started"
           SuccessLog="QueueLog"
           FailureLog="QueueLog"
           Description="System Maintenance Queue"
           ImpersonateType="QueuedUser" />
  </ConfigurationXml>
</ConfigurationEntryItem>

System Attachment Template

The following sample XML will create a template that can be used by the application settings in other modules. Note that you should review and alter the ArchiveFileObjectAssembly value to a valid entry for your system.

<AttachmentTemplateItem>
  <AttachmentTemplate>SystemAttachmentTemplate</AttachmentTemplate>
  <Repeatable>Many</Repeatable>
  <ExpirationScale>0</ExpirationScale>
  <Expiration>14</Expiration>
  <FileObjectAssembly>Standard BLOB Storage</FileObjectAssembly>
  <FileNameFormat>/{Year}/{Month}/{Day}/{Object}/{ObjectID}/{Guid}.xml</FileNameFormat>
  <ArchiveScale>2</ArchiveScale>
  <Archive>1</Archive>
  <ArchiveObjectAssembly>AmazonS3</ArchiveObjectAssembly>
  <ExtranetEnabled>None</ExtranetEnabled>
  <CustomStatusList>False</CustomStatusList>
  <CustomTypeList>False</CustomTypeList>
</AttachmentTemplateItem>

DB Statements

These statemnents are part of source-controlled Attachment.config, so this is for illustration purposes or overrides only.

<ConfigurationEntryItem>
  <ConfigurationEntry>Attachment/ArchiveFileSearch</ConfigurationEntry>
  <Source>Attachment.config</Source>
  <ConfigurationType>qbo.Application.Configuration.DbStatementCollection</ConfigurationType>
  <ConfigurationKey>ArchiveFileSearch</ConfigurationKey>
  <ConfigurationXml>
    <Statement Name="ArchiveFileSearch" ReturnType="DataReader" Permission="{Table}ArchiveFileSearch" Timeout="1200" AllowInheritance="true" Query="
                 SELECT TOP {Param.Batch} AttachmentID, Attachment.ArchiveScheduled UpdatedDate FROM Attachment
                  INNER JOIN AttachmentTemplate
                   ON Attachment.AttachmentTemplateID = AttachmentTemplate.AttachmentTemplateID
                  WHERE ArchiveScheduled &lt; GETDATE()
                   AND Attachment.FileObjectAssembly &lt;&gt; AttachmentTemplate.ArchiveObjectAssembly
                   AND ArchivalDate IS NULL
                   AND Attachment.ArchiveScheduled &gt;= '{Param.FromDate}'
                   AND ISNULL(Attachment.Status, '') &lt;&gt; 'Error'
                  ORDER BY Attachment.ArchiveScheduled
                 ">
      <Parameters>
        <Parameter Name="FromDate" DbType="DateTime" />
        <Parameter Name="Batch" DbType="Int32" Value="500" />
      </Parameters>
    </Statement>
  </ConfigurationXml>
</ConfigurationEntryItem>

<ConfigurationEntryItem>
  <ConfigurationEntry>Attachment/ExpireFileSearch</ConfigurationEntry>
  <Source>Attachment.config</Source>
  <ConfigurationType>qbo.Application.Configuration.DbStatementCollection</ConfigurationType>
  <ConfigurationKey>ExpireFileSearch</ConfigurationKey>
  <ConfigurationXml>
    <Statement Name="ExpireFileSearch" ReturnType="DataReader" Permission="{Table}ExpireFileSearch" Timeout="1200" AllowInheritance="true" Query="
                 SELECT TOP {Param.Batch} AttachmentID, Attachment.ExpirationScheduled UpdatedDate FROM Attachment
                  WHERE ExpirationScheduled &lt; GETDATE()
                   AND ExpirationDate IS NULL
                   AND Attachment.ExpirationScheduled &gt;= '{Param.FromDate}'
                   AND ISNULL(Attachment.Status, '') &lt;&gt; 'Error'
                  ORDER BY Attachment.ExpirationScheduled
                 ">
      <Parameters>
        <Parameter Name="FromDate" DbType="DateTime" />
        <Parameter Name="Batch" DbType="Int32" Value="500" />
      </Parameters>
    </Statement>
  </ConfigurationXml>
</ConfigurationEntryItem>

Schedule Your Jobs

Note the {ScheduleID} is the value of ScheduleName in the parameters (not the actual schedule name).

http://localhost/Attachment/Attachment.ashx/Schedule/Listen?Listeners=ArchiveAttachments&ScheduleName={ScheduleID}&QueueName=SystemMaintenance
http://localhost/Attachment/Attachment.ashx/Schedule/Listen?Listeners=ExpireAttachments&ScheduleName={ScheduleID}&QueueName=SystemMaintenance


Comments