How to make a Azure SQL Exec or select another table/procedure in another Azure SQL

Question

I've two Azure SQL Servers, each one contains one database, I need to exec Selects or Stored procedures between them.

For example:

Server1.database.windows.net DB: local1 Server2.database.windows.net DB: remote1

in Server1: Select * from Server2.remote1.dbo.Orders and this query return a list of orders.

in Server2: Exec Server1.local1.dbo.ProcedureOrders and this execute a stored procedure in Server1

First of all, is this possible? If yes, how?

Answer 1

Yes, it's possible. Azure sql database doesn't support linked server, but like @Alberto Morillo mentioned that Elastic Query can help you query and execute stored procedure in other SQL Server.

For the remote table query, here are the steps:

1. Create database scoped master key and credentials:

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master_key_password'; CREATE DATABASE SCOPED CREDENTIAL <credential_name> WITH IDENTITY = '<username>', SECRET = '<password>' [;]

2. Create external data sources:

   <External_Data_Source> ::= CREATE EXTERNAL DATA SOURCE <data_source_name> WITH (TYPE = RDBMS, LOCATION = ’<fully_qualified_server_name>’, DATABASE_NAME = ‘<remote_database_name>’, CREDENTIAL = <credential_name> ) [;]

3. External Tables:

   CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name ( { <column_definition> } [ ,...n ]) { WITH ( <rdbms_external_table_options> ) } )[;]

   <rdbms_external_table_options> ::= DATA_SOURCE = <External_Data_Source>, [ SCHEMA_NAME = N'nonescaped_schema_name',] [ OBJECT_NAME = N'nonescaped_object_name',]

For execute remote stored procedure:

Elastic query also introduces a stored procedure that provides direct access to the remote database. The stored procedure is called sp_execute _remote and can be used to execute remote stored procedures or T-SQL code on the remote database.

Example:

EXEC sp_execute_remote
        N'MyExtSrc',
        N'select count(w_id) as foo from warehouse'

For more details, please reference:

1. Reporting across scaled-out cloud databases (preview)
2. Query across cloud databases with different schemas (preview)

Hope this helps.