Claims data from SharePoint in Azure SQL or Managed Instance

Azure SQL Database and Azure SQL Managed Instance run the same engine, so SPSync writes the same tables to either. What differs is the network path, and that is where most of the setup time goes. This walkthrough replicates Meridian Insurance Group’s Policies, Claims Register and Underwriting Referrals lists into Azure SQL, covers the firewall and VNet steps for each target, and ends with a claims leakage report in Power BI.

The underwriting team built its first report straight on the lists, and it struggled. Refresh took minutes, each policy came back as an id, and once a list passed 5,000 items a query that filters or sorts on a non-indexed column could fail. A Power Automate flow broke whenever the Policy lookup or the multi-value Affected Coverages column changed, and a Data Factory pipeline needed its mapping updated by hand. Each month the numbers ended up in a spreadsheet.

One engine, two ways in

SPSync reads the lists in full once, then follows them with delta queries and writes the changes to Azure SQL on a schedule. Power BI, Excel and any report writer read the database and never touch SharePoint. The schema is built from the lists: lookups and people become key columns and multi-value columns become child tables. Because Database and Managed Instance share the SQL Server engine over the wire, everything in this post applies to both, apart from how you provision and reach the server.

Signing in and opening the network path

Install the desktop app, or SPSync in your Azure for a tenant-wide setup, and sign in with the account that already opens the three lists in SharePoint Online. The sign-in is delegated, so SPSync sees what that account sees, and the permissions wizard sets out what Sites.Selected grants before an admin approves it. SPSync never writes to SharePoint.

On the database side, SPSync uses SQL authentication: a username and password on an encrypted connection string. Microsoft Entra authentication is not supported, so create a SQL login on whichever target you use.

Azure SQL Database is a logical server with a public endpoint on port 1433, guarded by server-level firewall rules. Add the SPSync host to the IP allow-list, or use a private endpoint if the server is locked to a VNet. Managed Instance lives in a subnet of your own VNet and has no public endpoint unless you turn one on. SPSync needs a route into that VNet through peering, ExpressRoute or a VPN gateway, or you can enable the instance’s public endpoint, which listens on port 3342. Past that point the connection string, schema and sync behave identically. On the command line, sign-in is a device code and the token is kept in the OS keychain.

Policies, claims and coverages as tables

Every table is keyed on the SharePoint item id. The Policy lookup on Claims Register becomes policy_id, a key column pointing at sp_policies. Affected Coverages becomes its own child table, one row per coverage per claim, so a claim with three coverages is three rows and can be counted. Claims handlers and underwriters resolve to one shared users table, which makes “assigned to” and “handler” join the same way.

If a SharePoint column changes type, say a number becoming text, SPSync adds a column for the new type and leaves the old values alone. A column removed in SharePoint stays in the table, and a report built on it keeps working. SPSync does not add foreign key constraints, so load order cannot fail a sync. Add them yourself once you trust the schema.

Running the first sync

The first run is a full read; after that, each run reads the delta. When Graph asks clients to slow down, SPSync backs off, and the tenant’s other calls are unaffected. The events screen splits the totals by list, so an operator can see what ran without opening a log file.

Power BI with DirectQuery

Power BI connects to the replica with the SQL Server connector, using import for a scheduled model or DirectQuery for something close to live. DirectQuery pushes filtered SQL to Azure SQL, which the SharePoint List connector cannot do at any useful scale. The policy lookup is a column, so you define the relationship once in the model and the report joins claims to policies like any two tables. The leakage question, which line of business has the widest gap between reserve and payments, becomes a single grouped query.

Keeping the claims tables current

On a five-minute interval each run reads only the changes, so a report on the replica is a few minutes behind SharePoint at most. A full reconcile, every seven days by default, re-checks every item to confirm the two sides match; run one on demand as a QA check or to catch up after your own server or Microsoft 365 has had an outage. The desktop app and SPSync in your Azure have a schedule picker. From the command line, spsync run does one run, and cron, a scheduled task or a CI pipeline sets the interval.

Claims that are edited or withdrawn

The next delta run picks up an edit. A claim deleted in SharePoint is soft-deleted: it stays in the table with a flag, so a report can include or exclude it, and the reporting view SPSync can generate for each table leaves it out.

The tabs on each step above show the desktop app, SPSync in your Azure and the command line doing the same work. To replicate these lists anywhere else as well, add a replication per destination; the destinations page has the full list, and if the one you need is missing, tell us.