We want to be able to query the Data Warehouse database from the SQL Server database both with the same server name in Azure. Select statements including the database name (e.g. select * from server.dbo.product) returns with error that a reference to the database and/or server name isn't supported in this version of SQL Server. There are articles on creating an external data source in the SQL Server database but we get error that the credentials don't have permission to perform this action.
1 Answers
1
votes
Azure SQL (as of Azure SQL V12 in late-2016) does not support cross-database queries the same way that they do with a normal on-premises SQL Server (e.g. using the DatabaseName.schemaName.TableName syntax).
Given that Azure SQL and Azure Data Warehouse databases each reside in different physical servers it wouldn't work anyway, as the above syntax is intended for databases that all reside in the same server.
Azure SQL does support cross-database calls via the EXTERNAL DATA SOURCE feature, which we have in normal on-premises SQL Server already. This is documented here: https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/
Note that performance won't be that great and you might be better-off doing the querying in your application code. For example complex JOINs which reduce data will run suboptimally.
