What is AxioWorks SQList
AxioWorks SQList is designed to synchronise data from SharePoint lists onto SQL Server tables, and keep the SQL tables up-to-date as close to real time as possible. Note that SQList does not work the other way around, it does not update the SharePoint lists with changes made to the SQL tables.
SQList is made of two components, the AxioWorks SQList Service and SQList Manager; the former is the Windows service that perform the synchronisation, the latter is the UI used to configure the replications. both are installed by the SQList installation MSI package.
SQList reads SharePoint data using the standard SharePoint web services via HTTP or HTTPS, so no changes to the SharePoint farm or site collection are required. The only requirement being that web services are enabled on the site you want to export. Likewise, SQList uses ADO.NET TCP connections to connect to the SQL Server database, therefore no changes are required to the SQL Server configuration either.
The only changes that may be required to your infrastructure, is to allow SQList through any firewall rules that might stop it from communicating with the SharePoint site or the SQL server (by default, ports 80/443 for the former, and 1433 for the latter).
SQList support the following authentication mechanisms:
To the SharePoint site:
- Windows authentication
- SharePoint OnLine (including ADFS 2 and ADFS 3)
- Forms authentication
- Trusted authentication
To the SQL Server:
- SQL authentication
- Windows authentication
- Trusted authentication
SQList can be configured to export multiple SharePoint sites to a multiple databases in a very versatile manner, for example:
- each site to individual database
- multiple sites to the same database
- one site to multiple databases
How does AxioWorks SQList work
SQList runs in the background as a Windows service.
Once the replications are configured and the service started, SQList connects to all the sites that are configured to be replicated and check their lists.
Once it's worked out the structure of the sites and lists it has to export, it checks the destination databases and creates or update the SQL tables where necessary (the SQL table structure mirrors the structure of the SharePoint list - for more details see
this article)
At this point, SQList polls each list selected for synchronisation and checks whether three are items that need updating onto the corresponding SQL table and, if it finds any, updates them in the SQL database. Depending on the license level, lists are processed sequentially or in parallel; the latter reducing dramatically the time it takes for a change in SharePoint to be replicated to the SQL table.
After all lists have been processed, SQList "sleeps" for a period (10 seconds by default), then goes and do it all over again. For more details about SQList cycles, please see
this article.
Because of its design, SQList is very reliable and fault proof in case of issues with connections or infrastructure. If any of the systems involved goes down and updates are not completed, SQList will catch up the next time everything is back up.
It is important to understand that SQList does not synchronise the SQL tables in real time; in other words, when a list item is updated, the change is not immediately replicated to the SQL table, as that depends on which point in a cycle SQList is when the list item is updated, and by how many sites and lists are being replicated. However, when properly tuned, SQList replicates changes in matters of seconds in most scenarios.
SQList can be installed anywhere
SQList connects to the SharePoint sites via its web services, therefore though HTTP port 80 or HTTPS port 443. To connect to the SQL Server databases, SQList uses the standard SQL Server TCP port 1433 (by default).
Because it uses standard network connections, SQList does not have to be installed on the same machine where SharePoint or SQL server are running, but can instead be installed on any machine that can access them via TCP, the only infrastructure requirements being that the firewalls (if present) don't stop the connections, and that the SharePoint web services are enabled.
This characteristic of SQList makes it an extremely versatile tool that, combined with its low resource requirements, can be installed even on a laptop.
If you have any questions about SQList, please do not hesitate to contact us at
support@axioworks.com