sqlsql-serverazure-sql-database

Join Tables from different databases in Azure SQL (SAAS)?


Having the older application that joins SQL tables from different databases like this:

SELECT a.value, b.value
  FROM databaseA.dbo.tableA AS a
       JOIN databaseB.dbo.tableB AS b
         ON a.thekey == b.thekey

Being 3rd party, we have to accept the decision of the main implementor for the customer to use the license Azure SQL SAAS (Software As A Service; I am new to that, sorry if some terms are incorrect).

Is it possible to have databaseA and databaseB (that part is possible, checked through SSMS) in the Azure SQL and do the same JOIN (that part is unknown to me)?


Solution

  • As you comment said: "I understand that it is different than working within one physical SQL Server. On the other hand, the different servers is probably related to possible replication. Is that correct? ", yes, you're right.

    For Azure SQL database(PSSA), we only can use the elastic query to achieve the cross database query:

    Note: Make sure the primary database have the permission to access the remote databases. Add the the client IP to their database firewall. The remote database can be in different Azure SQL Server.

    If you are using Azure SQL managed instance and Azure SQL Server on Azure VM, you can run this across query like on-premise SQL Server.