Is it posible to use SQL Server Session Context with Azure elastic queries

Question

I want to know if it's posible to share SQL Server SESSION CONTEXT variables between different Azure Sql databases using Elastic Queries. I searched in official documentation but i can't found any information about this feature is available or not.

Answer 1

SESSION CONTEXT exists locally to a single server instance in SQL Server. (It's tied to a session). SQL Azure is built using SQL Server but there are some parts of the mapping that are opaque to customers (they can change based on circumstances such as what Edition you use or what version of the internal software we are using to deliver the service).

Elastic Queries is a feature to let you query from one database (source) to one or more other databases (target(s)). In such a model, you have a SQL Server session to the source database, and the elastic query has a separate connection/session to each other database being touched.

I think the question you are asking is "can I set the session context on the source connection/session and have it flow through to all the target connections when running queries there?" (That's my best guess - let me know if it is different). The answer today is "no" - the session variables do not flow from source to target as part of the elastic query. Also, since today elastic query is read-only, you can't use elastic query to set the session context individually on each target database connection/session as part of the operation.

In the future, we'll consider whether there is something like this we can do, but right now we don't have a committed timeline for something like this.

I hope this explains how things work a bit under the convers. Sincerely, Conor Cunningham Architect, SQL