Elastic query in Azure SQL single database

Question

Hi, I have a requirement to execute cross database 'SELECT' query in our Azure SQL single DB. I went through the MS documentation. It very clearly describes the way how we can implement this. My expectation was, when I'll map one external data source to a different DB within same server, it will automatically map the underlying tables as well. But seems I need to manually create those tables as external tables and populate. If this is the feature, why we should use elastic query? we can create those tables with different schema in the same db. Why should I execute extra steps to create external data source and tables. Please suggest. Regards, Partha

Answer 1

When using Elastic Query in Azure SQL Database, you have to create external data sources and external tables manually to query across databases. Elastic Query is beneficial in situations where you need to maintain data separation for security, compliance, or management reasons without moving or duplicating data. You could create tables with different schemas in the same database as an alternative solution if data separation requirements aren't a concern. However, there are advantages to using Elastic Query: nullnullnullnull

If your use case doesn't need the benefits offered by Elastic Query and combining tables within a single database is acceptable, you can create tables with different schemas in the same database, simplifying your architecture.

Answer 2

@Partha Das In Azure SQL Database different databases under the same logical server are not sharing the same SQL Server instance, so to execute cross-database queries you need to explicitly define external tables that point to that different database.

Creating tables with a different schema in the same database would be a different design choice that may or may not be applicable (i.e. one may not own that external database, or may want to keep the two databases isolated for performance or security reasons).

This doc can help:Query across cloud databases with different schema - Azure SQL Database | Microsoft Learn And the other way you can use a Data API builder and invoke external REST endpoint to do cross Azure SQL DB communication.

Put DAB on a table in one DB and expose your table via REST. Then use invoke external REST endpoint stored proc in the other database to call that newly created REST endpoint in the other DB to query the table.

I hope this information helps.

Regards Geetha