3

We are trying to create a cross-database query using Azure's preview Elastic Query. So we will be creating an External Table to make these queries happen.

Unfortunately, I have some apprehension about how the queries will be executed. I don't want a query or stored procedure to fail at run-time because the database connection fails. I just don't understand how the External Tables work.

Azure's External Table docs have good information on how to query and create the table. I just can't find information that specifically spells out how the data exists.

Oracle's version of external tables is just flat files that are referenced. SQL*Loader loads data from external files into tables of an Oracle database. I couldn't find any documentation about Azure doing the same. (Is it implied that they are the same? Is that a stupid question?)

If it is this way (external flat files), when the external table gets updated, does SQL Server update the flat files so our external table stays up to date? Or will I have to delete/create the link again every time I want to run the query for up to date information?

Improve this question
1
  • In regards to potential transient errors, if we were querying between two databases on the same server, what are some of the potential failure scenarios if both databases are available? We are trying to gauge the risk incurred by leveraging this feature and if queries could potentially fail because of the underlying connection to the source database. Commented Apr 23, 2019 at 13:33

1 Answer 1

Reset to default
2

Per Microsoft Support: Elastic queries basically works as remote queries which means the data is not stored locally but is pulled from the source database every time you run a query. When you execute a query on an external table, it makes a connection to the source database and gets the data. With that being said, you do not have to delete/create the links. Once you have performed these steps, you can access the horizontally partitioned table “mytable” as though it were a local table. Azure SQL Database automatically opens multiple parallel connections to the remote databases where the tables are physically stored, processes the requests on the remote databases, and returns the results.

There is no specific risk associated with using this feature but it is simply like opening connections to the source database so it can pull data. Besides this you can expect some slowness when executing a remote query but nothing that will cause any other issues with the database. In case any of the database becomes unavailable, queries that are using the affected DB as source or target will experience query cancellations or timeouts.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

1) you're my hero for self-documenting like this. 2) what about query caching? I find that the first query is slow-ish but if I immediately run it again the results come back instantaneously.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.