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?