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.

Answer 2

You can use elastic queries to query tables across different databases in different logical servers and even when they belong to different subscriptions. You can find and example here.

You can also run transactions across databases on different logical servers as announced in this article.