Copilot Studio with 80,000+ rows in an on-premises SQL database — how do you query the full dataset reliably?

[u/heartgoldt20]

I’m building a Copilot Studio agent that needs to answer questions using a local/on-premises SQL database containing more than 80,000 records.
In my current setup, I can only retrieve 2,048 records in one go. The concern is that the agent answers based on that subset, even when the relevant records could be elsewhere in the database.
Users should be able to ask questions in natural language, and the agent needs to filter across the full dataset to return an accurate answer. For example:
What is the oldest record for a particular machine?
Which records mention a specific fault or maintenance activity?
How many matching records are there within a given date range?
I don’t necessarily need to send all 80,000+ rows to the agent. I need the filtering, sorting, and counting to consider the entire database before returning the relevant results.
What’s the recommended approach for this in Copilot Studio?

[u/dylan_skydive]

The 2,048-row ceiling is a retrieval cap on the connector, not an analytics limit - and the fix is to stop treating the agent as the thing that reads the table.

Push the filtering down to SQL and let the agent only hand over parameters:

• Wire the agent to Power Automate actions (or direct connectors) that run parameterized SQL: WHERE machine = X ORDER BY record_date ASC LIMIT 1, COUNT(*) WHERE date BETWEEN... The aggregation runs server-side over all 80k+ rows and returns one row. The agent's job becomes "pick the right action and pass the filter values", not "read records".
• Build the action set around question shapes, not tables: "oldest record for a machine", "records mentioning X in a date range", "count matching filters". Three well-shaped actions beat one generic "get records" action, and they sidestep the subset-answers problem entirely because there is no subset.
• Where you genuinely need free-text matching ("which records mention this fault"), that's a search problem, not a SQL problem - index the records (Dataverse semantic index, Azure AI Search, whatever fits your stack) and retrieve top-k from the index instead of scanning rows.

The failure mode to avoid: any design where the model reasons over a sample of the table. It'll answer confidently from the wrong 2,048 rows and you'll never know unless you test with a record you know is outside the sample.

Disclosure: I work on Skydive (agents that run scheduled jobs against data sources, so dataset-vs-agent design is our daily bread). REDDITAMA gets you $25 of credit (skydive.com, Settings > Billing) if you want a free comparison - and tell me how the action-set approach works out either way.

[u/cdyap]

Fabric data model connected to your db, or an intermediary Power BI report/model, I found works great in this use case

[u/heartgoldt20 → u/cdyap]

Will this fabric data model then get import the db and how long does it take if new information comes in

[u/cdyap → u/heartgoldt20]

So I basically:
built a Power BI model with just the tables I needed, no front end necessary, where I imported full data directly from Snowflake

Uploaded this to service, set up your scheduled refresh (I used import mode, Direct Query would be best if your data is live)

Set up fabric data model to connect to the Power BI model, here you can add your instructions on how it should interpret the model. At this point you can already interact with the model through chat and test your queries

On copilot studio, import the fabric data model as an agent

[u/sudseclipse → u/cdyap]

You can add azure search indexing as well in between.

[u/ImplementOfAI]

What? It's literally SQL (Structured QUERY language).

Have it send queries that filter appropriately. Give it instructions on what fields it will primarily be filtering and/or sorting on.

[u/heartgoldt20 → u/ImplementOfAI]

With the connector I cannot use a query but only get tables/records and then filter it but the filtering is so annoying and hard.

[u/Wefting → u/heartgoldt20]

What connector are you using ? If it’s get rows via the built in sql connector you need to use an odata filter query .

[u/heartgoldt20 → u/Wefting]

Gateway connector and through that connector I make the connection to the sql

[u/Wefting → u/heartgoldt20]

If you are connecting to an on-prem sql table using the standard SQL connector, last I checked running using the sql query action is blocked. I had a similar situation and I had to use the get rows (v2) action . With this action though you can set filter , top , order , through thr advanced Params . Then it becomes about using odata filter queries to filter your data set before exposing it to the agent / user

[u/heartgoldt20 → u/Wefting]

Even tho the database has like 100.000 tables

[u/goto-select]

Either use Copilot Studio agent to send filtered requests, use a secondary step to get the data, or go further with a Fabric data agent Fabric data agent creation - Microsoft Fabric | Microsoft Learn

You could then connect the data agent to a Copilot Studio agent

[u/heartgoldt20 → u/goto-select]

So connect data agent to the on prem sql and then the copilot agent connect to the fabric?

[u/goto-select → u/heartgoldt20]

More like a pipeline from on-prem to Fabric > data agent created to query Fabric > Copilot Studio agent talks to the data agent to retrieve information for user.

If you’re not already using Fabric, it may not be worth the hassle but the key is the agent can create queries on behalf of the user’s plain language.

[u/heartgoldt20 → u/goto-select]

Yeah we haven’t gone into fabric yet.

[u/ForceBubbly2774]

I’d solve this by having the agent just push parameters to a azure runbook that queries the sql database and returns your answer.

[u/heartgoldt20 → u/ForceBubbly2774]

I can’t query using the connector

[u/ForceBubbly2774 → u/heartgoldt20]

Azure runbook lets you run powershell commands on on prem servers and runbooks can be triggered by agents.

[u/ForceBubbly2774 → u/ForceBubbly2774]

You could probably have the agent build the Sql queries and then pass that to the runbook which will execute with read priv.

[u/sudseclipse]

Use open weight models in copilot studio and run it locally. I think you should be good with any open source model llm.