Data Connection

Question

Hello Microsoft Support,

I am reaching out regarding a data architecture challenge we are facing with our SharePoint Online environment and would like guidance on the best permanent solution.

Our Situation:

We have a SharePoint Online list used for internal operational assessments, currently at 3,271 items and growing rapidly. We are approaching the 5,000-item List View Threshold and need a permanent solution before our PowerApps form submissions and Power BI dashboards are impacted.

What We Have Already Done:

• Created indexed columns on primary date fields
• Built filtered views to keep end user queries under the threshold

Our Questions:

1. What is the recommended permanent solution for a high-volume PowerApps form currently writing to SharePoint Online — should we migrate to Azure SQL, Dataverse, or another Microsoft-supported data source?
2. What is the recommended process for migrating existing SharePoint list data into SQL Server or Dataverse without data loss?
3. Can PowerApps connect directly to an existing on-premises SQL Server, and if so what connector and licensing is required?
4. How do we ensure existing Power BI dashboards remain intact and connected during and after the migration?
5. Is there a Microsoft FastTrack or technical specialist resource that can assist with this type of migration?
6. Is there anyway to save the sharepoint list since team leaders go in there sometimes?

Environment Details:

• SharePoint Online (Microsoft 365)
• PowerApps canvas app
• Power BI dashboards connected to the SharePoint list
• Existing on-premises SQL Server SSMS

We want to proceed correctly and in alignment with Microsoft best practices. Any guidance or escalation to a technical specialist would be greatly appreciated.

Moved from: Microsoft 365 and Office | SharePoint | Development

Answer 1

Dear @Omidy,
Thank you for posting your question in the Microsoft Q&A forum. From what you shared, you have a SharePoint Online list used for operational assessments that is growing quickly (3,271 items and increasing). You are concerned that when it gets close to 5,000 items, your PowerApps form submissions and Power BI dashboards may be affected. You already created indexed columns and filtered views to keep queries under the limit, and now you want a permanent, Microsoft‑recommended solution.

Typically, in SharePoint Online, the 5,000 limit is a view/query threshold (not a storage limit). Lists can grow far beyond 5,000 items, but views, filters, sorting, grouping, and certain operations can be blocked or slowed if a query touches too many items at once.

To keep SharePoint stable, I recommend as below:

• Ensure the columns used for filtering (for example: Created/Primary Date/Status) are indexed.
• Use filtered views that always return fewer than 5,000 items (for example: “Current year” or “Last 90 days”) and avoid heavy grouping/sorting in the default view.
• In Power Apps, ensure app formulas are delegable where possible (so the app can query the full dataset reliably).

For reference: Overview of large lists and libraries - Microsoft Support

For the broader questions about migrating to Dataverse/Azure SQL/SQL Server, Power Apps connector behavior/delegation, SQL premium licensing, and Power BI report repointing, those are Power Platform design topics. Power Apps also has query/delegation limits that can affect large datasets. Connecting Power Apps to SQL Server (including on‑prem via the on‑premises data gateway) uses premium connectivity and requires appropriate licensing.

To get the best architecture guidance, please post those Power Platform questions to the Find Answers | Microsoft Power Platform Community

This forum is dedicated to Power Platform and is supported by a knowledgeable community, including Microsoft experts and partners. By posting there, you'll have access to more specialized resources and insights, which can help resolve your issue more effectively.

I suggest this route because our resources on Power Platform are limited, and we want to avoid providing incomplete or inaccurate information. The Power Platform Community is the best place to get targeted help and also allows others to learn from your experience. 

Note: We appreciate your understanding regarding the scope of support available through this forum. As moderators, our role is to provide general guidance and assist with common troubleshooting steps. Apologies for redirecting you to a different community as the members of the posted category focus on users with Microsoft 365 concern and have limited knowledge about Power Automate, so to get a quick and better assistance, we redirect you in the correct way. 

I hope the information provided proves useful. Please proceed with the outlined steps and let me know whether they resolve the issue. If not, I’ll be glad to continue working with you to find a solution.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

1. Recommended Permanent Solution: For high-volume PowerApps forms currently writing to SharePoint Online, migrating to Dataverse is often recommended. Dataverse provides a more scalable solution for handling large datasets compared to SharePoint lists, and it integrates well with PowerApps and Power BI.
2. Migrating Existing SharePoint List Data: To migrate existing SharePoint list data into SQL Server or Dataverse without data loss, consider using tools like Power Query or Dataflows in Power BI to extract data from SharePoint and load it into the target database. Ensure to back up your data before migration and validate the data after the migration process.
3. PowerApps Connection to On-Premises SQL Server: Yes, PowerApps can connect directly to an existing on-premises SQL Server using the On-Premises Data Gateway. You will need to set up the gateway and ensure you have the appropriate licensing, which typically involves having a PowerApps Plan 1 or Plan 2 license.
4. Ensuring Power BI Dashboards Remain Intact: To ensure existing Power BI dashboards remain connected during and after the migration, maintain the same data structure and naming conventions in the new data source. Update the data source settings in Power BI to point to the new SQL Server or Dataverse after migration.
5. Microsoft FastTrack or Technical Specialist Resources: Yes, Microsoft FastTrack can assist with migrations and provide guidance on best practices. You can reach out to your Microsoft account representative to inquire about engaging a technical specialist for your migration.
6. Saving the SharePoint List: You can keep the SharePoint list accessible for team leaders by maintaining it as a read-only archive after migration. This way, they can still access historical data while new submissions are directed to the new data source.

By following these recommendations, you can ensure a smooth transition while adhering to Microsoft best practices.