Archive SharePoint Data in SQL / Maintain deleted list items in SQL tables

We have added a setting that enables you to maintain deleted list items in the replicated SQL table. This can be useful it you want archive SharePoint list items to SQL server.

To disable the removal of deleted list items from the corresponding SQL table, simply select the "Keep Deleted items" column in the list selection when configuring a replication:



If a list has the "Keep deleted items" option selected, when an item is deleted it will not be removed by the SQL table, but only marked as deleted in the new column named SQList.IsDeleted:






Note:
  • this feature is available to SQList Professional edition only;
  • only items that are deleted from the time the feature is enabled are kept in the SQL table, items deleted previously are not exported;
  • if you drop the SQL table, items marked as deleted will not be recovered;
  • if an item actually does need to be deleted from the SharePoint list and SQL table, it has to be done manually for the SQL table;
  • if an item previously exported to the SQL table is updated and then deleted while SQList is not running (or the replication is failing), the item in teh SQL table will be marked as deleted, but will contain the data as they were before the last update;
  • this feature must not be confused with versioning.

Added Windows authentication to individual SQL connections

We have added Windows Authentication as an option in each SQL database connection. With previous versions, windows authentication was only possible using Trusted Authentication, which only allows the use of one set of Windows credentials (those used to run the SQList service) against all SQL databases. Each database connection can now have its own set of windows credentials.

To use Windows authentication to connect to a SQL database, choose "Windows authentication" from the Authentication mode drop-down, when setting up a database connection in SQList Manager.



Two new columns have been added to the SQL tables (they will be automatically added to existing tables ):
  • SQList.Username: this column contains the name of the user, from the credentials used to connect to the SQL Server, that last updated/inserted the row;
  • SQList.UTCUpdated: this column contains the UTC timestamp of the last update to the row.

Changed handling of non-standard column types

When SQList exports a list column into a SQL table, it assign the SQL type that most closely match the list column type (e.g. a "Text" with a maximum length or 30 will be exported as a NVARCHAR(30)).

If the type of the list column is not recognised (e.g. a custom column type), SQList will try to determine its type from the physical column name; if it still cannot determine the type, it will export it as a NVARCHAR(MAX).

Note: previous versions of SQList exports not recognised types as NVARCHAR(255) so, if you are upgrading be aware that the column in the SQL tables will me changed to the new type. 

You can set the default length for these columns i the "Settings" tab, by changing the "Default length for NVARCHAR columns" value.


It is possible to map custom column types to specific SQL types by setting the "SPDB.CustomColumnMappings" value in the config file. This is an advanced setting, please contact us if you need to use it.

Increased precision for numbers "Displayed as percentage"

When SQList exports a "Number" column, it sets the number of decimals of the SQL column to the same length as it is defined in the SharePoint column.

However, when a SharePoint column is configured to be "Displayed as percentage", SharePoint stores the value divided by 100. This was causing a loss of precision under certain circumstances - e.g. a SharePoint column defined as a Number with 4 decimals, displayed as percentage, would store the number 1.2345 as 0.012345 in SharePoint and  as 0.0123 in the SQL table (loss of two decimals) requiring the column to be defined with 6 decimals to correct it.

We have now changed this behaviour so that 2 additional decimal positions are added to the SQL column for percentages so that no loss of precision occurs.





Note: 
  • existing SQL columns defined to "Display as percentage" will be altered to add the two new decimals 

Added SiteID to UNION view

The UNION view, created when exporting sub-sites, contained the name of the original table as one of its columns, to identify which table the row belongs. Using the name of the table did not prove ideal when joining UNION views, so we have removed that column (TableName) and replaced with the actual GUID of the site to which the item belongs.
  • a new column has been added to all tables, named WebID;
  • SQL joins between UNION views should now be done using the WebID and ID columns;
  • a new table is now created in the database, named SQList.Sites, which contains the GUIDs and Names of the exported sites;
  • the TableName column has been removed 

Custom name to table prefix

It is now possible to define a custom prefix to the tables generated by SQList.



In previous versions the name assigned to the site definition was also used at the prefix for the tables; this did not allow two different SharePoint sites to be exported to tables that have the same name.

While this worked well most of the times, there are some situations where you may want to use the same prefix for two different sites. For example, you may have separate replications configured for two versions of a SharePoint site, one development and one production. You may want to export these to separate SQL Server databases and generate tables with the same names.

New setting: "Abort all replications in one fails"

In previous versions, when one replication failed, SQList aborted all replications until the failing one was corrected. By default SQList now continues with the other replications. The old behaviour can still be resumed by selecting this new option.


All SharePoint system lists are now available

All lists in a SharePoint site are now available; in previous version SQList was hiding some of the system lists.
Note: in you have existing replications that by default exports all lists, these new system lists will be exported 

Added chat support directly in SQList Manager

If you are setting up a replication, and experience any difficulties, you can now open a chat window and contact us for assistance. If we are not online, you can leave a message and we will get in touch as soon as possible.


Minor changes

  • added some additional checks and warning when setting up a replication;
  • added an audit feature that, if enabled, sends us anonymous usage statistics that will help us understand how you use our software and how we can make it ever better;
  • fixed some minor bugs...thank you to those of you who pointed them out!