Part of: AI and search over SharePoint data

One SQL copy of SharePoint for every agent you run

What a cross-list question costs an AI agent through Graph, worked for three real-sized lists, and the same question as one SQL join on a replica.

Relational databasesAIDesktopCommand line

The ticket usually reads something like “give the facilities agent access to the Work Orders, Assets and Contractors lists”. Somebody senior has already said “just use the Graph connector”, and on a test site with a few dozen items it works the first time. You are the developer who has to make it work against the real lists, which hold 18,000 items, 2,400 items and 600 items, and against the question the facilities desk actually asks: which contractors have open work orders on assets that are already past their service date?

The first version of a tool like this calls Graph from inside the tool, and it tends to get rewritten within a month, because the cost of one question is far higher than it looks in the demo. This post works through that cost with the published numbers, then shows the same question against a SQL copy of the three lists.

What a cross-list question costs an agent through Graph

Graph has no join. To relate a work order to its asset and its contractor, the tool has to fetch items from all three lists and match the lookup ids itself. The listItem API returns results in pages, and Microsoft’s paging guidance says each API has its own default and maximum page size without giving one for list items. The older SharePoint REST API is specific: a GET on a list’s items returns the first 100 items unless you ask for more.

Say the tool asks for 200 items a page with $top. That puts the full read of the three lists at 90 pages, 12 pages and 3 pages, which is 105 requests for one question. At the REST default it would be 210 requests. Either way the agent is holding 21,000 rows in the tool’s memory before it can answer, and none of that work is cached for the next person who asks.

Microsoft prices Graph requests to SharePoint in resource units. A multi-item query costs 2 resource units, so those 105 requests cost 210 resource units. For a company with 2,600 licences, the per-app limit is 2,500 resource units a minute, which allows about eleven questions like this a minute before SharePoint starts answering with 429. That sounds like plenty until you watch how an agent uses a tool.

Why lookup columns and unindexed filters break agent queries

The obvious fix is to stop reading everything and filter on the server. The facilities agent only cares about open work orders, so the tool sends $filter=fields/Status ne 'Closed'. On a list of 18,000 items that request fails if Status is not an indexed column. The list view threshold sits at 5,000 items by default, and a filter on an unindexed column has to scan past it. The listItem documentation puts it mildly as “filtering works best on indexed columns”. You can index Status, which needs someone with rights on the list to do it, and the next question filters on Priority.

Lookups are the second wall. Expanding fields on a work order means resolving every lookup and person column, and SharePoint Online allows at most 12 join operations per query (the list view lookup threshold, which Microsoft’s Power Query documentation quotes). Microsoft’s SharePoint Server documentation counts lookup, person and workflow status columns as joins. Give the Work Orders list 14 lookup and person columns, which is what happens when every team that uses a list adds one, and a query that asks for all fields is refused. The tool then has to name its columns, and every new question means editing the tool.

Then the model has to do the matching. If the tool hands 21,000 rows back to the model, the context window fills and the arithmetic goes wrong. If the tool does the join in code, you are writing a small query engine in Python, with its own bugs, for each kind of question.

SharePoint throttling when several agents share a tenant

The facilities agent is rarely alone for long. In this company finance wants an agent over Contractors and Work Orders for invoice checks, and the service desk wants a Copilot Studio agent over Assets. Each agent reads its lists through its own connection, so Work Orders alone is paged in full by two agents, often within the same hour.

Microsoft’s throttling guidance is precise about what happens next. SharePoint returns 429 or 503 with a Retry-After header, and throttled requests count towards the limits, so a tool that retries without waiting makes the throttling longer. An application that keeps going can be blocked outright. On top of the per-app limit, there is a tenant-level limit of 37,500 resource units every 5 minutes shared by every application on the tenant. Your agents share that budget with the intranet and every scheduled Power BI refresh in the company. When a model decides to call a tool four times to answer one question, it spends its share four times. The detail is in our throttling post.

The same question as one SQL join

SPSync reads the three lists through Graph and keeps a copy in Postgres or SQL Server, current on a schedule. The first run is a full load. After that each run asks Graph only for what changed, using delta queries, and a delta request with a token costs 1 resource unit. Three lists every five minutes comes to 864 resource units a day, which is about four agent questions’ worth of the live approach, spent once for all of them.

In the database each list is a table. The Asset lookup on a work order becomes asset_id, which joins to sp_item_id on the assets table, with asset_value beside it holding the display text. The Contractor lookup becomes contractor_id in the same way. Lookup count stops mattering, because a database join has no lookup threshold. The facilities question is one statement:

SELECT c.title                  AS contractor,
       count(*)                 AS open_orders,
       min(a.next_service_due)  AS most_overdue_since,
       max(w.sp_synced_at)      AS synced_at
FROM sharepoint.facilities_work_orders w
JOIN sharepoint.facilities_assets      a ON a.sp_item_id = w.asset_id
JOIN sharepoint.facilities_contractors c ON c.sp_item_id = w.contractor_id
WHERE w.status <> 'Closed'
  AND a.next_service_due < current_date
  AND w.sp_deleted_at IS NULL
  AND a.sp_deleted_at IS NULL
GROUP BY c.title
ORDER BY open_orders DESC;

The database scans the rows, does the date comparison and returns one row per contractor. The model gets a short result set and writes the answer, quoting synced_at so the person reading it knows how old the data is. No request goes to SharePoint while the question is answered, so the finance agent and the service desk agent can ask whatever they like without touching anyone’s budget.

What tools does an AI agent need over a SQL replica?

For reading, an agent on a replica gets by with three tools:

You can get these tools from a maintained database MCP server, or from spsync mcp serve, SPSync’s own read-only server over the replica. DBHub, from Bytebase, refuses anything but reads when its SQL tool is set to read-only, caps the rows a query returns, and serves Postgres and SQL Server from the same process. Point one at the replica, connected as a role that holds SELECT on a few views, and register it with the agent framework. The views matter more than the server. They rename columns into the words the facilities desk uses and leave out anything the agent has no business seeing, including soft-deleted rows.

Each agent gets its own role and its own views over the same tables. The finance agent sees contractor rates and the service desk agent does not. SPSync does not copy SharePoint item permissions into the database, so these grants, plus row-level security where an agent should see one region only, are how you bound what each agent reads. The MCP server walkthrough has the full role and view script for Postgres.

Staging agent write proposals for a person to approve

Sooner or later someone asks for the agent to close a work order or reassign a contractor. The replica cannot do that. SPSync reads SharePoint and never writes to it, and nothing in the product sends a change from the database back to a list. We think that is the right starting point for an agent, though it means writes need a design of their own.

The pattern we recommend is a proposals table that lives outside the schema SPSync manages. It holds the list name, the item id, the column, the proposed value, the agent’s reason and a status. A fourth tool lets the agent insert one row there, through a second database role that can insert into that table and do nothing else, so the query role stays read-only. A person reviews the open proposals in a simple Power Apps screen or a saved query and makes the approved changes in SharePoint, by hand or through a Power Automate flow they own. When the next sync brings the new value into the replica, a scheduled query marks the proposal as applied by comparing the two.

The person in the loop is slower than an agent writing directly, and some teams find that frustrating at first. In exchange the agent never holds a SharePoint write permission, and every change it suggested has a row showing who approved it.

How fresh is a SharePoint replica for an agent?

On the default schedule the replica runs every five minutes, and you can set the interval as low as 30 seconds where the lists change quickly and the tenant has budget to spare. For the facilities desk, five minutes was never the problem. Nobody books a contractor on the strength of a work order that changed two minutes ago.

Reconcile runs weekly by default and its schedule is configurable. It compares every item in each list with the replica and corrects any difference, which gives you a regular, documented check that the copy matches SharePoint. It is also how you recover after your own database has been offline for a while, because the next reconcile brings everything back into line without a reload by hand.

When a live Graph call is still the right tool

Keep one Graph call in reserve for the moment before an agent acts on a single record. If the facilities agent is about to propose dispatching a contractor to work order 48213, it should read that one item from Graph first and check the status has not changed since the last sync. A single item query costs 1 resource unit, and it is the cheapest insurance you can buy. The agent’s app registration can be limited with Sites.Selected to the one site, with read access only.

Graph is also the right route when the question is about what is inside a document, since SPSync replicates library metadata and not file contents. For a one-person pilot on a list of a few hundred items, a Graph tool is quicker to try and the arithmetic above barely registers. Once several agents read lists in the thousands, and a security team wants to know what each identity can do, one copy that every agent queries is cheaper to run and far easier to approve.

The setup starts with the destination you already run: SharePoint to PostgreSQL or SharePoint to SQL Server. An agent data layer for SharePoint covers the grants, row-level security and audit that keep each agent scoped.

Compared with

Book a call

Book a call to talk through your use case, see further walkthroughs or demos, and set up a trial in your own environment.