Is it possible to use cross database queries in both Azure and SQL?I need to add a linked server to a MS Azure SQL ServerInserting multiple rows in a single SQL query?What are the options for storing hierarchical data in a relational database?Import SQL dump into PostgreSQL databaseQuerying across databases with Azure SQLUsing Polybase in Azure SQL Data warehouse to access Hadoop (Azure IaaS) HDFS filesSQL Azure cross DB Query PrivilegesDatabase having same table name cross database queries In Azure SQLCross Database Query in Azure SQL Database PerformanceHow to Cross Database Queries in Azure SQL with Same Table name and structure on both databaseInstall and configure Polybase on Azure SQL Server - Could not find stored procedure 'sp_configure'
Are there nouns that change meaning based on gender?
Metal that glows when near pieces of itself
A second course in the representation theory
Why don't we use Cavea-B
Can my boyfriend, who lives in the UK and has a Polish passport, visit me in the USA?
Why don't politicians push for fossil fuel reduction by pointing out their scarcity?
Was this pillow joke on Friends intentional or a mistake?
Are required indicators necessary for radio buttons?
Don't understand MOSFET as amplifier
Why were movies shot on film shot at 24 frames per second?
Are thrust levers synchronized by default when pushed/pulled?
Can you feel passing through the sound barrier in an F-16?
What is "Wayfinder's Guide to Eberron"?
Most practical knots for hitching a line to an object while keeping the bitter end as tight as possible, without sag?
Is it insecure to have an ansible user with passwordless sudo?
Thread-safe, Convenient and Performant Random Number Generator
Why does my house heat up, even when it's cool outside?
Bug or undocumented behaviour in Intersection
Sleeping solo in a double sleeping bag
Church Booleans
Does Git delete empty folders?
To "hit home" in German
In an emergency, how do I find and share my position?
Can you grapple/shove with the Hunter Ranger's Whirlwind Attack?
Is it possible to use cross database queries in both Azure and SQL?
I need to add a linked server to a MS Azure SQL ServerInserting multiple rows in a single SQL query?What are the options for storing hierarchical data in a relational database?Import SQL dump into PostgreSQL databaseQuerying across databases with Azure SQLUsing Polybase in Azure SQL Data warehouse to access Hadoop (Azure IaaS) HDFS filesSQL Azure cross DB Query PrivilegesDatabase having same table name cross database queries In Azure SQLCross Database Query in Azure SQL Database PerformanceHow to Cross Database Queries in Azure SQL with Same Table name and structure on both databaseInstall and configure Polybase on Azure SQL Server - Could not find stored procedure 'sp_configure'
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I am working on a project designed to run on azure platform using Azure database and Locally using local sql server. We have stored procedure that contain cross database calls. However It doesn't works on Azure server.I need cross database queries that capable of working in both azure and local sql server.
We an use Elastic Query which allows us to query across Azure SQL Databases
https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/
We can setup external data source in azure server Using following code. In this case we can execute cross database calls likes joining tables in a single database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';
CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin
WITH IDENTITY = 'yourServeradminlogin',
SECRET = 'yourPassword';
CREATE EXTERNAL DATA SOURCE RefmyDemoDB2
WITH
(
TYPE=RDBMS,
LOCATION='testdbdemoserver.database.windows.net',
DATABASE_NAME='myDemoDB2',
CREDENTIAL= yourServeradminlogin
);
CREATE EXTERNAL TABLE [dbo].[Department](
[DeptId] [int] NOT NULL,
[Name] [varchar](50) NULL
)
WITH
(
DATA_SOURCE = RefmyDemoDB2
);
It doesn't seem possible to use above method on our local SQL server without adding a third party database engine.
Install something called PolyBase to do cross database query in local sql server version 2017 and above.It only supported with external data source of type such as HADOOP rather than referencing another database(or any data storage) within the sql server.
We can accomplish that using following code
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyP@ssword123secretword';
CREATE DATABASE SCOPED CREDENTIAL mycredential
WITH IDENTITY = 'credential', Secret = 'secretkey'
CREATE EXTERNAL DATA SOURCE mycustomers
WITH (
TYPE = HADOOP,
LOCATION = 'wasbs://azurestorage.blob.core.windows.net/',
CREDENTIAL = mycredential
);
CREATE EXTERNAL FILE FORMAT csvformat
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ','
)
);
CREATE EXTERNAL TABLE TableName
(
[did] [int] NOT NULL,
[Dname] [varchar] (50) NULL
)
WITH
(
LOCATION = '/',
DATA_SOURCE = mycustomers,
FILE_FORMAT = csvformat
)
Using HADOOP and polybase we can create an external data source in local sql server. But it create external data source of that external data storage. That is External data table located in that external storage.Exactly, my requirement is create an external data source of database within the sql server. So that i can use same corss data base query in both azure and local sql server
Is there any solution to solve this. or any solution to run cross database queries in both azure and local sql server?
sql database azure azure-sql-database polybase
add a comment |
I am working on a project designed to run on azure platform using Azure database and Locally using local sql server. We have stored procedure that contain cross database calls. However It doesn't works on Azure server.I need cross database queries that capable of working in both azure and local sql server.
We an use Elastic Query which allows us to query across Azure SQL Databases
https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/
We can setup external data source in azure server Using following code. In this case we can execute cross database calls likes joining tables in a single database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';
CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin
WITH IDENTITY = 'yourServeradminlogin',
SECRET = 'yourPassword';
CREATE EXTERNAL DATA SOURCE RefmyDemoDB2
WITH
(
TYPE=RDBMS,
LOCATION='testdbdemoserver.database.windows.net',
DATABASE_NAME='myDemoDB2',
CREDENTIAL= yourServeradminlogin
);
CREATE EXTERNAL TABLE [dbo].[Department](
[DeptId] [int] NOT NULL,
[Name] [varchar](50) NULL
)
WITH
(
DATA_SOURCE = RefmyDemoDB2
);
It doesn't seem possible to use above method on our local SQL server without adding a third party database engine.
Install something called PolyBase to do cross database query in local sql server version 2017 and above.It only supported with external data source of type such as HADOOP rather than referencing another database(or any data storage) within the sql server.
We can accomplish that using following code
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyP@ssword123secretword';
CREATE DATABASE SCOPED CREDENTIAL mycredential
WITH IDENTITY = 'credential', Secret = 'secretkey'
CREATE EXTERNAL DATA SOURCE mycustomers
WITH (
TYPE = HADOOP,
LOCATION = 'wasbs://azurestorage.blob.core.windows.net/',
CREDENTIAL = mycredential
);
CREATE EXTERNAL FILE FORMAT csvformat
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ','
)
);
CREATE EXTERNAL TABLE TableName
(
[did] [int] NOT NULL,
[Dname] [varchar] (50) NULL
)
WITH
(
LOCATION = '/',
DATA_SOURCE = mycustomers,
FILE_FORMAT = csvformat
)
Using HADOOP and polybase we can create an external data source in local sql server. But it create external data source of that external data storage. That is External data table located in that external storage.Exactly, my requirement is create an external data source of database within the sql server. So that i can use same corss data base query in both azure and local sql server
Is there any solution to solve this. or any solution to run cross database queries in both azure and local sql server?
sql database azure azure-sql-database polybase
There are bags of documents about this already published, here is just one - sqldusty.com/2017/05/30/…
– jimmy8ball
Mar 27 at 15:30
@jimmy8ball , Thanks for your response. The given link explains setting up of Cross Database Queries in Azure SQL Database. Actually I need to execute those queries in Local sql server. In fact Those type such as RDBMS are doesn't works on local sql server. I already tried to build up that using following link docs.microsoft.com/en-us/sql/relational-databases/polybase/… .
– Sumith Jose
Mar 27 at 16:14
add a comment |
I am working on a project designed to run on azure platform using Azure database and Locally using local sql server. We have stored procedure that contain cross database calls. However It doesn't works on Azure server.I need cross database queries that capable of working in both azure and local sql server.
We an use Elastic Query which allows us to query across Azure SQL Databases
https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/
We can setup external data source in azure server Using following code. In this case we can execute cross database calls likes joining tables in a single database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';
CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin
WITH IDENTITY = 'yourServeradminlogin',
SECRET = 'yourPassword';
CREATE EXTERNAL DATA SOURCE RefmyDemoDB2
WITH
(
TYPE=RDBMS,
LOCATION='testdbdemoserver.database.windows.net',
DATABASE_NAME='myDemoDB2',
CREDENTIAL= yourServeradminlogin
);
CREATE EXTERNAL TABLE [dbo].[Department](
[DeptId] [int] NOT NULL,
[Name] [varchar](50) NULL
)
WITH
(
DATA_SOURCE = RefmyDemoDB2
);
It doesn't seem possible to use above method on our local SQL server without adding a third party database engine.
Install something called PolyBase to do cross database query in local sql server version 2017 and above.It only supported with external data source of type such as HADOOP rather than referencing another database(or any data storage) within the sql server.
We can accomplish that using following code
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyP@ssword123secretword';
CREATE DATABASE SCOPED CREDENTIAL mycredential
WITH IDENTITY = 'credential', Secret = 'secretkey'
CREATE EXTERNAL DATA SOURCE mycustomers
WITH (
TYPE = HADOOP,
LOCATION = 'wasbs://azurestorage.blob.core.windows.net/',
CREDENTIAL = mycredential
);
CREATE EXTERNAL FILE FORMAT csvformat
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ','
)
);
CREATE EXTERNAL TABLE TableName
(
[did] [int] NOT NULL,
[Dname] [varchar] (50) NULL
)
WITH
(
LOCATION = '/',
DATA_SOURCE = mycustomers,
FILE_FORMAT = csvformat
)
Using HADOOP and polybase we can create an external data source in local sql server. But it create external data source of that external data storage. That is External data table located in that external storage.Exactly, my requirement is create an external data source of database within the sql server. So that i can use same corss data base query in both azure and local sql server
Is there any solution to solve this. or any solution to run cross database queries in both azure and local sql server?
sql database azure azure-sql-database polybase
I am working on a project designed to run on azure platform using Azure database and Locally using local sql server. We have stored procedure that contain cross database calls. However It doesn't works on Azure server.I need cross database queries that capable of working in both azure and local sql server.
We an use Elastic Query which allows us to query across Azure SQL Databases
https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/
We can setup external data source in azure server Using following code. In this case we can execute cross database calls likes joining tables in a single database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';
CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin
WITH IDENTITY = 'yourServeradminlogin',
SECRET = 'yourPassword';
CREATE EXTERNAL DATA SOURCE RefmyDemoDB2
WITH
(
TYPE=RDBMS,
LOCATION='testdbdemoserver.database.windows.net',
DATABASE_NAME='myDemoDB2',
CREDENTIAL= yourServeradminlogin
);
CREATE EXTERNAL TABLE [dbo].[Department](
[DeptId] [int] NOT NULL,
[Name] [varchar](50) NULL
)
WITH
(
DATA_SOURCE = RefmyDemoDB2
);
It doesn't seem possible to use above method on our local SQL server without adding a third party database engine.
Install something called PolyBase to do cross database query in local sql server version 2017 and above.It only supported with external data source of type such as HADOOP rather than referencing another database(or any data storage) within the sql server.
We can accomplish that using following code
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyP@ssword123secretword';
CREATE DATABASE SCOPED CREDENTIAL mycredential
WITH IDENTITY = 'credential', Secret = 'secretkey'
CREATE EXTERNAL DATA SOURCE mycustomers
WITH (
TYPE = HADOOP,
LOCATION = 'wasbs://azurestorage.blob.core.windows.net/',
CREDENTIAL = mycredential
);
CREATE EXTERNAL FILE FORMAT csvformat
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ','
)
);
CREATE EXTERNAL TABLE TableName
(
[did] [int] NOT NULL,
[Dname] [varchar] (50) NULL
)
WITH
(
LOCATION = '/',
DATA_SOURCE = mycustomers,
FILE_FORMAT = csvformat
)
Using HADOOP and polybase we can create an external data source in local sql server. But it create external data source of that external data storage. That is External data table located in that external storage.Exactly, my requirement is create an external data source of database within the sql server. So that i can use same corss data base query in both azure and local sql server
Is there any solution to solve this. or any solution to run cross database queries in both azure and local sql server?
sql database azure azure-sql-database polybase
sql database azure azure-sql-database polybase
edited Mar 27 at 16:09
Sumith Jose
asked Mar 27 at 13:17
Sumith JoseSumith Jose
198 bronze badges
198 bronze badges
There are bags of documents about this already published, here is just one - sqldusty.com/2017/05/30/…
– jimmy8ball
Mar 27 at 15:30
@jimmy8ball , Thanks for your response. The given link explains setting up of Cross Database Queries in Azure SQL Database. Actually I need to execute those queries in Local sql server. In fact Those type such as RDBMS are doesn't works on local sql server. I already tried to build up that using following link docs.microsoft.com/en-us/sql/relational-databases/polybase/… .
– Sumith Jose
Mar 27 at 16:14
add a comment |
There are bags of documents about this already published, here is just one - sqldusty.com/2017/05/30/…
– jimmy8ball
Mar 27 at 15:30
@jimmy8ball , Thanks for your response. The given link explains setting up of Cross Database Queries in Azure SQL Database. Actually I need to execute those queries in Local sql server. In fact Those type such as RDBMS are doesn't works on local sql server. I already tried to build up that using following link docs.microsoft.com/en-us/sql/relational-databases/polybase/… .
– Sumith Jose
Mar 27 at 16:14
There are bags of documents about this already published, here is just one - sqldusty.com/2017/05/30/…
– jimmy8ball
Mar 27 at 15:30
There are bags of documents about this already published, here is just one - sqldusty.com/2017/05/30/…
– jimmy8ball
Mar 27 at 15:30
@jimmy8ball , Thanks for your response. The given link explains setting up of Cross Database Queries in Azure SQL Database. Actually I need to execute those queries in Local sql server. In fact Those type such as RDBMS are doesn't works on local sql server. I already tried to build up that using following link docs.microsoft.com/en-us/sql/relational-databases/polybase/… .
– Sumith Jose
Mar 27 at 16:14
@jimmy8ball , Thanks for your response. The given link explains setting up of Cross Database Queries in Azure SQL Database. Actually I need to execute those queries in Local sql server. In fact Those type such as RDBMS are doesn't works on local sql server. I already tried to build up that using following link docs.microsoft.com/en-us/sql/relational-databases/polybase/… .
– Sumith Jose
Mar 27 at 16:14
add a comment |
1 Answer
1
active
oldest
votes
You should not assume that using external tables in SQL Azure is remotely similar to doing cross-database queries in SQL Server. They are not the same thing and they have very different performance profiles. External tables is closer to linked servers than cross-database queries in SQL Server.
In SQL Server, cross-database queries are:
- running in the same SQL instance
- run the same basic execution code path to read data vs. single-database SQL Azure
- you have some slightly different transactional semantics vs. single-database operaitons in SQL Server, but the experience is generally similar from your perspective.
In SQL Azure singletons ("traditional") SQL Azure, you generally do not have the databases on the same physical machines. So, you have to cross-server queries (which are exposed using a linked-server like mechanism called external tables which support sharding/fan-out scenarios mostly). Trying to use this feature to simulate cross-database queries may functionally work, but it is not really a great plan due to the performance differences. There are also no real transactional guarantees in this path at all (no DTC).
SQL Azure Managed Instance does support cross-database queries within a single SQL Server instance internally. So, this would be the path that would be most similar for you to use if you really want to use cross-database queries. If you want to use SQL Azure single databases, you generally would not want to do cross-db queries at all for a legacy workload and you would want to rewrite to avoid the dependency. (Otherwise it will just be an ongoing headache due to the performance differences)
I need little more clarification. Offcourse, Cross database query Azure Sql server is differ from traditional Sql server. In traditional Sql server we can use linked server concept or call like "[DatabaseName].[TableName]" . In azure sql server it doesn't support. Elastic Query is an Alternative way to accomplish this. Can't use Elastic query in traditional sql server to link two database within the server. But My aim is how to make a query that joins two tables in two database, and it must be able to use in both azure and traditional Sql server environment.
– Sumith Jose
Mar 28 at 5:06
There is no single way to do this today, unfortunately, unless you use SQL Azure Managed Instance. Then you can just do normal 3-part name queries like in SQL Server. Elastic Query is really about scale-out query in SQL Azure (although you can squint at it and it can maybe look like a point-to-point database solution, it is not the same as cross-db query). If you need to write code that works in both, then I suggest you #ifdef to have 2 paths as there is no T-SQL surface area to do what you are requesting.
– Conor Cunningham MSFT
Mar 28 at 11:06
Finally I got the solution !. There is no single way to do this. But using 'SYNONYM' in traditional sql server and using Elastic query(External table concept) in azure sql solve that problem. So that now I can use a query to link 2 tables in 2 database in both azure and traditional sql. There is no need to make changes in query depending on server. But one issue remaining. How to access Function of another db in azure sql server?. Locally 'SYNONYM' allows to access functions in another DB. (azure sql server doesn't support SYNONYM)
– Sumith Jose
Mar 28 at 13:09
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55378180%2fis-it-possible-to-use-cross-database-queries-in-both-azure-and-sql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You should not assume that using external tables in SQL Azure is remotely similar to doing cross-database queries in SQL Server. They are not the same thing and they have very different performance profiles. External tables is closer to linked servers than cross-database queries in SQL Server.
In SQL Server, cross-database queries are:
- running in the same SQL instance
- run the same basic execution code path to read data vs. single-database SQL Azure
- you have some slightly different transactional semantics vs. single-database operaitons in SQL Server, but the experience is generally similar from your perspective.
In SQL Azure singletons ("traditional") SQL Azure, you generally do not have the databases on the same physical machines. So, you have to cross-server queries (which are exposed using a linked-server like mechanism called external tables which support sharding/fan-out scenarios mostly). Trying to use this feature to simulate cross-database queries may functionally work, but it is not really a great plan due to the performance differences. There are also no real transactional guarantees in this path at all (no DTC).
SQL Azure Managed Instance does support cross-database queries within a single SQL Server instance internally. So, this would be the path that would be most similar for you to use if you really want to use cross-database queries. If you want to use SQL Azure single databases, you generally would not want to do cross-db queries at all for a legacy workload and you would want to rewrite to avoid the dependency. (Otherwise it will just be an ongoing headache due to the performance differences)
I need little more clarification. Offcourse, Cross database query Azure Sql server is differ from traditional Sql server. In traditional Sql server we can use linked server concept or call like "[DatabaseName].[TableName]" . In azure sql server it doesn't support. Elastic Query is an Alternative way to accomplish this. Can't use Elastic query in traditional sql server to link two database within the server. But My aim is how to make a query that joins two tables in two database, and it must be able to use in both azure and traditional Sql server environment.
– Sumith Jose
Mar 28 at 5:06
There is no single way to do this today, unfortunately, unless you use SQL Azure Managed Instance. Then you can just do normal 3-part name queries like in SQL Server. Elastic Query is really about scale-out query in SQL Azure (although you can squint at it and it can maybe look like a point-to-point database solution, it is not the same as cross-db query). If you need to write code that works in both, then I suggest you #ifdef to have 2 paths as there is no T-SQL surface area to do what you are requesting.
– Conor Cunningham MSFT
Mar 28 at 11:06
Finally I got the solution !. There is no single way to do this. But using 'SYNONYM' in traditional sql server and using Elastic query(External table concept) in azure sql solve that problem. So that now I can use a query to link 2 tables in 2 database in both azure and traditional sql. There is no need to make changes in query depending on server. But one issue remaining. How to access Function of another db in azure sql server?. Locally 'SYNONYM' allows to access functions in another DB. (azure sql server doesn't support SYNONYM)
– Sumith Jose
Mar 28 at 13:09
add a comment |
You should not assume that using external tables in SQL Azure is remotely similar to doing cross-database queries in SQL Server. They are not the same thing and they have very different performance profiles. External tables is closer to linked servers than cross-database queries in SQL Server.
In SQL Server, cross-database queries are:
- running in the same SQL instance
- run the same basic execution code path to read data vs. single-database SQL Azure
- you have some slightly different transactional semantics vs. single-database operaitons in SQL Server, but the experience is generally similar from your perspective.
In SQL Azure singletons ("traditional") SQL Azure, you generally do not have the databases on the same physical machines. So, you have to cross-server queries (which are exposed using a linked-server like mechanism called external tables which support sharding/fan-out scenarios mostly). Trying to use this feature to simulate cross-database queries may functionally work, but it is not really a great plan due to the performance differences. There are also no real transactional guarantees in this path at all (no DTC).
SQL Azure Managed Instance does support cross-database queries within a single SQL Server instance internally. So, this would be the path that would be most similar for you to use if you really want to use cross-database queries. If you want to use SQL Azure single databases, you generally would not want to do cross-db queries at all for a legacy workload and you would want to rewrite to avoid the dependency. (Otherwise it will just be an ongoing headache due to the performance differences)
I need little more clarification. Offcourse, Cross database query Azure Sql server is differ from traditional Sql server. In traditional Sql server we can use linked server concept or call like "[DatabaseName].[TableName]" . In azure sql server it doesn't support. Elastic Query is an Alternative way to accomplish this. Can't use Elastic query in traditional sql server to link two database within the server. But My aim is how to make a query that joins two tables in two database, and it must be able to use in both azure and traditional Sql server environment.
– Sumith Jose
Mar 28 at 5:06
There is no single way to do this today, unfortunately, unless you use SQL Azure Managed Instance. Then you can just do normal 3-part name queries like in SQL Server. Elastic Query is really about scale-out query in SQL Azure (although you can squint at it and it can maybe look like a point-to-point database solution, it is not the same as cross-db query). If you need to write code that works in both, then I suggest you #ifdef to have 2 paths as there is no T-SQL surface area to do what you are requesting.
– Conor Cunningham MSFT
Mar 28 at 11:06
Finally I got the solution !. There is no single way to do this. But using 'SYNONYM' in traditional sql server and using Elastic query(External table concept) in azure sql solve that problem. So that now I can use a query to link 2 tables in 2 database in both azure and traditional sql. There is no need to make changes in query depending on server. But one issue remaining. How to access Function of another db in azure sql server?. Locally 'SYNONYM' allows to access functions in another DB. (azure sql server doesn't support SYNONYM)
– Sumith Jose
Mar 28 at 13:09
add a comment |
You should not assume that using external tables in SQL Azure is remotely similar to doing cross-database queries in SQL Server. They are not the same thing and they have very different performance profiles. External tables is closer to linked servers than cross-database queries in SQL Server.
In SQL Server, cross-database queries are:
- running in the same SQL instance
- run the same basic execution code path to read data vs. single-database SQL Azure
- you have some slightly different transactional semantics vs. single-database operaitons in SQL Server, but the experience is generally similar from your perspective.
In SQL Azure singletons ("traditional") SQL Azure, you generally do not have the databases on the same physical machines. So, you have to cross-server queries (which are exposed using a linked-server like mechanism called external tables which support sharding/fan-out scenarios mostly). Trying to use this feature to simulate cross-database queries may functionally work, but it is not really a great plan due to the performance differences. There are also no real transactional guarantees in this path at all (no DTC).
SQL Azure Managed Instance does support cross-database queries within a single SQL Server instance internally. So, this would be the path that would be most similar for you to use if you really want to use cross-database queries. If you want to use SQL Azure single databases, you generally would not want to do cross-db queries at all for a legacy workload and you would want to rewrite to avoid the dependency. (Otherwise it will just be an ongoing headache due to the performance differences)
You should not assume that using external tables in SQL Azure is remotely similar to doing cross-database queries in SQL Server. They are not the same thing and they have very different performance profiles. External tables is closer to linked servers than cross-database queries in SQL Server.
In SQL Server, cross-database queries are:
- running in the same SQL instance
- run the same basic execution code path to read data vs. single-database SQL Azure
- you have some slightly different transactional semantics vs. single-database operaitons in SQL Server, but the experience is generally similar from your perspective.
In SQL Azure singletons ("traditional") SQL Azure, you generally do not have the databases on the same physical machines. So, you have to cross-server queries (which are exposed using a linked-server like mechanism called external tables which support sharding/fan-out scenarios mostly). Trying to use this feature to simulate cross-database queries may functionally work, but it is not really a great plan due to the performance differences. There are also no real transactional guarantees in this path at all (no DTC).
SQL Azure Managed Instance does support cross-database queries within a single SQL Server instance internally. So, this would be the path that would be most similar for you to use if you really want to use cross-database queries. If you want to use SQL Azure single databases, you generally would not want to do cross-db queries at all for a legacy workload and you would want to rewrite to avoid the dependency. (Otherwise it will just be an ongoing headache due to the performance differences)
answered Mar 27 at 20:20
Conor Cunningham MSFTConor Cunningham MSFT
1,7231 gold badge6 silver badges11 bronze badges
1,7231 gold badge6 silver badges11 bronze badges
I need little more clarification. Offcourse, Cross database query Azure Sql server is differ from traditional Sql server. In traditional Sql server we can use linked server concept or call like "[DatabaseName].[TableName]" . In azure sql server it doesn't support. Elastic Query is an Alternative way to accomplish this. Can't use Elastic query in traditional sql server to link two database within the server. But My aim is how to make a query that joins two tables in two database, and it must be able to use in both azure and traditional Sql server environment.
– Sumith Jose
Mar 28 at 5:06
There is no single way to do this today, unfortunately, unless you use SQL Azure Managed Instance. Then you can just do normal 3-part name queries like in SQL Server. Elastic Query is really about scale-out query in SQL Azure (although you can squint at it and it can maybe look like a point-to-point database solution, it is not the same as cross-db query). If you need to write code that works in both, then I suggest you #ifdef to have 2 paths as there is no T-SQL surface area to do what you are requesting.
– Conor Cunningham MSFT
Mar 28 at 11:06
Finally I got the solution !. There is no single way to do this. But using 'SYNONYM' in traditional sql server and using Elastic query(External table concept) in azure sql solve that problem. So that now I can use a query to link 2 tables in 2 database in both azure and traditional sql. There is no need to make changes in query depending on server. But one issue remaining. How to access Function of another db in azure sql server?. Locally 'SYNONYM' allows to access functions in another DB. (azure sql server doesn't support SYNONYM)
– Sumith Jose
Mar 28 at 13:09
add a comment |
I need little more clarification. Offcourse, Cross database query Azure Sql server is differ from traditional Sql server. In traditional Sql server we can use linked server concept or call like "[DatabaseName].[TableName]" . In azure sql server it doesn't support. Elastic Query is an Alternative way to accomplish this. Can't use Elastic query in traditional sql server to link two database within the server. But My aim is how to make a query that joins two tables in two database, and it must be able to use in both azure and traditional Sql server environment.
– Sumith Jose
Mar 28 at 5:06
There is no single way to do this today, unfortunately, unless you use SQL Azure Managed Instance. Then you can just do normal 3-part name queries like in SQL Server. Elastic Query is really about scale-out query in SQL Azure (although you can squint at it and it can maybe look like a point-to-point database solution, it is not the same as cross-db query). If you need to write code that works in both, then I suggest you #ifdef to have 2 paths as there is no T-SQL surface area to do what you are requesting.
– Conor Cunningham MSFT
Mar 28 at 11:06
Finally I got the solution !. There is no single way to do this. But using 'SYNONYM' in traditional sql server and using Elastic query(External table concept) in azure sql solve that problem. So that now I can use a query to link 2 tables in 2 database in both azure and traditional sql. There is no need to make changes in query depending on server. But one issue remaining. How to access Function of another db in azure sql server?. Locally 'SYNONYM' allows to access functions in another DB. (azure sql server doesn't support SYNONYM)
– Sumith Jose
Mar 28 at 13:09
I need little more clarification. Offcourse, Cross database query Azure Sql server is differ from traditional Sql server. In traditional Sql server we can use linked server concept or call like "[DatabaseName].[TableName]" . In azure sql server it doesn't support. Elastic Query is an Alternative way to accomplish this. Can't use Elastic query in traditional sql server to link two database within the server. But My aim is how to make a query that joins two tables in two database, and it must be able to use in both azure and traditional Sql server environment.
– Sumith Jose
Mar 28 at 5:06
I need little more clarification. Offcourse, Cross database query Azure Sql server is differ from traditional Sql server. In traditional Sql server we can use linked server concept or call like "[DatabaseName].[TableName]" . In azure sql server it doesn't support. Elastic Query is an Alternative way to accomplish this. Can't use Elastic query in traditional sql server to link two database within the server. But My aim is how to make a query that joins two tables in two database, and it must be able to use in both azure and traditional Sql server environment.
– Sumith Jose
Mar 28 at 5:06
There is no single way to do this today, unfortunately, unless you use SQL Azure Managed Instance. Then you can just do normal 3-part name queries like in SQL Server. Elastic Query is really about scale-out query in SQL Azure (although you can squint at it and it can maybe look like a point-to-point database solution, it is not the same as cross-db query). If you need to write code that works in both, then I suggest you #ifdef to have 2 paths as there is no T-SQL surface area to do what you are requesting.
– Conor Cunningham MSFT
Mar 28 at 11:06
There is no single way to do this today, unfortunately, unless you use SQL Azure Managed Instance. Then you can just do normal 3-part name queries like in SQL Server. Elastic Query is really about scale-out query in SQL Azure (although you can squint at it and it can maybe look like a point-to-point database solution, it is not the same as cross-db query). If you need to write code that works in both, then I suggest you #ifdef to have 2 paths as there is no T-SQL surface area to do what you are requesting.
– Conor Cunningham MSFT
Mar 28 at 11:06
Finally I got the solution !. There is no single way to do this. But using 'SYNONYM' in traditional sql server and using Elastic query(External table concept) in azure sql solve that problem. So that now I can use a query to link 2 tables in 2 database in both azure and traditional sql. There is no need to make changes in query depending on server. But one issue remaining. How to access Function of another db in azure sql server?. Locally 'SYNONYM' allows to access functions in another DB. (azure sql server doesn't support SYNONYM)
– Sumith Jose
Mar 28 at 13:09
Finally I got the solution !. There is no single way to do this. But using 'SYNONYM' in traditional sql server and using Elastic query(External table concept) in azure sql solve that problem. So that now I can use a query to link 2 tables in 2 database in both azure and traditional sql. There is no need to make changes in query depending on server. But one issue remaining. How to access Function of another db in azure sql server?. Locally 'SYNONYM' allows to access functions in another DB. (azure sql server doesn't support SYNONYM)
– Sumith Jose
Mar 28 at 13:09
add a comment |
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55378180%2fis-it-possible-to-use-cross-database-queries-in-both-azure-and-sql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
There are bags of documents about this already published, here is just one - sqldusty.com/2017/05/30/…
– jimmy8ball
Mar 27 at 15:30
@jimmy8ball , Thanks for your response. The given link explains setting up of Cross Database Queries in Azure SQL Database. Actually I need to execute those queries in Local sql server. In fact Those type such as RDBMS are doesn't works on local sql server. I already tried to build up that using following link docs.microsoft.com/en-us/sql/relational-databases/polybase/… .
– Sumith Jose
Mar 27 at 16:14