Different behaviour between SQL database and SQL Analytics endpoint

[pmscorca]

Hi,

I've noticed that there are diffencies between SQL database view and SQL Analytics endpoint view in the explorer.

F.e. when I create a table with a datetime column I can do it successfully in the SQL database view while in the SQL Analytics endpoint view I need to use a datetime2 column.

Moreover, it could occur that a dropped table in the SQL database view remains in the SQL Analytics endpoint view.

The SQL Analytics endpoint view should correspond to SQL database view.

Thanks

[lbendlin]

The SQL Analytics endpoint view should correspond to SQL database view.

Not really. The SQL Endpoint is a pared down, read only version.  Cannot be used for transactions, only for consumption. Meta data synchronization is a one way street, and is not deterministic.

[pmscorca]

Hi, I don't understand the usefulness of the SQL analytics endpoint layer for a SQL database.

These two features don't align with each other, even over time.

[v-anbandari]

Hi @pmscorca ,

It may seem that the SQL Database and SQL Analytics endpoint should behave the same, but they are designed for different purposes in Microsoft Fabric.

The SQL Database is used for transactional operations like insert, update, and delete. The SQL Analytics endpoint is a read-only layer used for reporting and analytical queries. The purpose is not to make both the same, but to allow analytics on the same data without affecting transactional performance and without moving data.

Because the SQL Analytics endpoint uses a different engine, some differences are expected, such as requiring datetime2 instead of datetime and slight delays in reflecting changes.

Both are meant to work together, not to behave exactly the same.

Thank you.

[Aparnaa_MS]

Hi @pmscorca 

What your observing is majorly due to the architectural difference between the two.

The SQL analytics endpoint layer is a read-only layer which is mirrored over the data in OneLake, but it doesnt support transactional SQL operations due to which the support of different datatypes may vary, like datetime and datetime2 which you have mentiond and changes such as dropped tables will appear but sometimes it there maybe a delay you will just have to wait until the refreshed data/metadata catches up.

Additionally it is recommended to use the SQL analytics endpoint mainly for analytics/reporting i.e OLAP tasks, and the SQL database for any operational queries i.e OLTP operations.

Hope this helps !

Thanks
Aparnaa

[pmscorca]

Hi, thanks for your reply.

The SQL analytics endpoint is an experience also offered from a SQL database. The SQL database experience already allows to manage a database similar to Azure SQL or SQL Server.

Why should a Fabric SQL database also include a SQL analytics endpoint? Moreover, with some differencies between the two experiences in architectural terms?
It makes more sense to have the SQL analytics endpoint layer for a warehouse or a lakehouse.

Thanks

[Aparnaa_MS]

Hi @pmscorca 

thanks for the follow-up.

The reason for including the SQL analytics endpoint with Fabric SQL Database, wrt architecture, is mainly about separating OLTP and OLAP workloads, i.e to separate operational processing from analytical consumption.

While the SQL Database handles transactional (OLTP) operations, like CRUD support and reads/writes, but running heavy analytical queries on the same can impact performance.
So the SQL analytics endpoint will handle the analytical queries over the same data without the need for any data movement (like running pipelines, ETL... etc)

Also its not that a SQL Endpoint might make more sense in a warehouse as a warehouse is already optimized for heavy analytics, but there are limitations to the operational queries you can perform in warehouse than in SQL DB.

In short, even though a SQL analytics endpoint is also useful for lakehouses/warehouses, in the case of Fabric SQL Database it exists to provide a built-in analytics for the same database, without requiring any external setup or movment.

Hope this helps!

Thanks

Aparnaa

[pmscorca]

Hi,

for me it makes little sense to query an OLTP SQL database for analytical purposes without tranforming it in a data warehouse.

However, the two layers remain disaligned after some object creation or drop operations;  it's not a question of waiting time.

[v-anbandari]

Hi @pmscorca ,

The SQL Analytics endpoint is kept in sync with the SQL Database through a background metadata synchronization process.

This process runs automatically when the SQL Analytics endpoint is active and typically reflects changes within seconds to a minute. However, it is not strictly real-time or deterministic, and in some scenarios changes (such as table create/drop) may not appear immediately or consistently.

In such cases, Microsoft recommends triggering an on-demand metadata sync instead of waiting for the background process.

You can do this by:

1. Using the Refresh option in the SQL query editor, or

2. Triggering the SQL Endpoint Metadata Refresh API

This forces the SQL Analytics endpoint to update with the latest changes from the SQL Database.

If the issue still persists even after a manual refresh, it may need further investigation.

For more details, you can refer to the official documentation:

Items - Refresh Sql Endpoint Metadata - REST API (SQLEndpoint) | Microsoft Learn

Thank you.

[pmscorca]

Hi,

I already refreshed both the SQL Database Editor and the SQL Analytics Endpoint Editor some time ago, but the SQL Analytics Endpoint isn't aligned with the SQL Database Editor yet.
I don't understand how triggering the SQL Endpoint Metadata Refresh API.
Thanks

[v-anbandari]

Hi @pmscorca ,

Just checking in, could you please confirm whether the  provided  solution has resolved your query? Your update will be valuable to the community.

Please feel free to reach out if you need any further assistance.

Thank you.

[v-anbandari]

Hi @pmscorca , 

Could you please confirm if your issue has been resolved using the suggested approach? This will help other community members facing similar scenarios.
 

Thank you for being part of the Microsoft Fabric Community.

[v-anbandari · Accepted solution]

Hi @pmscorca ,

To align the SQL Analytics Endpoint with the SQL Database Editor, you need to explicitly call the Fabric REST API refreshMetadata operation. This forces the endpoint to sync its schema with the latest definitions.

You don’t use this API inside the SQL Database Editor or SQL Analytics Endpoint UI directly  it’s a REST API call that you trigger from your own automation or client code. In practice, you’d use it in one of these places:

1. Fabric Pipelines → Add a Web activity after your ingestion/transform steps to call the refreshMetadata API, so the SQL Analytics Endpoint is always aligned before downstream reporting.

2. Notebooks → Use Python (with requests or http libraries) to call the API as part of your workflow.

3. External scripts → PowerShell, Python, or any HTTP client can invoke the endpoint. This is common if you’re orchestrating refreshes outside Fabric.

4. CI/CD automation → Integrate the call into deployment pipelines so schema changes are automatically reflected in the SQL Analytics Endpoint.

For more details, you can refer to the official documentation:

Items - Refresh Sql Endpoint Metadata - REST API (SQLEndpoint) | Microsoft Learn

Thank you.