0

Is there any option to query the Azure SQL Data warehouse (Cloud) from the Azure SQL Server database (Cloud)? We have a central warehouse hosted in cloud with all the domains needed for the Application. It would be great if we can use those tables as external tables using the approach described at https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db.

Sam
  • 281
  • 5
  • 15
  • Are you saying you want to access data in azure from an on-premise SQL Server install? You should be able to use _linked servers_ but generally this is not a good idea. Try this guide http://stackoverflow.com/questions/28590832/i-need-to-add-a-linked-server-to-a-ms-azure-sql-server. This is called a 'federated query'. Federated queries are usually slow and inefficient. – Nick.Mc Jan 09 '17 at 04:48
  • It is actually a SQL Azure DataWareHouse in Cloud. Just for testing purposes I created a DW and SQL database using sample data. I can't access the data from the DW using Sp created in the SQL database. – Sam Jan 09 '17 at 05:22
  • 1
    So both databases are on the cloud but they are different products (i.e. one is SQL Azure Data Warehouse, the other is SQL Database). Can you please edit your question to make it clear what the products are (exactly as they are named in azure) and whether they are on premise, Azure VM or Azure products, and which way the communication needs to go. Can you also try the link I posted and post back any issues or error messagesd – Nick.Mc Jan 09 '17 at 06:56
  • Thanks. I edited my post and added the product names. As mentioned before, both DB are in cloud. Data needs to be accessible in SQL Azure DB from SQL Azure DW. – Sam Jan 11 '17 at 07:33
  • I suspect linked servers for Azure SQL DB is not supported. Does it need to be real time? Have you considered using data factory to transfer data on a scheduled basis? – Nick.Mc Jan 11 '17 at 09:14
  • Thanks for your reply. We need real-time data. I will look for other options and solve this. Thanks all for your help. – Sam Jan 16 '17 at 16:53

2 Answers2

3

Querying Data Warehouse from SQL DB is currently unsupported.

Josh G
  • 61
  • 1
  • Do you mean Azure Data Warehouse? It's just another SQL Server endpoint to connect to. If you can connect from an application you should be able to connect from a on premise SQL Server DB. – Nick.Mc Jan 11 '17 at 02:26
1

You can access Azure SQL Data Warehouse from Azure SQL using external tables Here's the article: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql

Example:

CREATE MASTER KEY
CREATE DATABASE SCOPED CREDENTIAL dwcredential WITH IDENTITY = '<username>', 
SECRET = '<password>';

CREATE EXTERNAL DATA SOURCE sqldw WITH
(
    TYPE = RDBMS,
    LOCATION = N'<dwserver>.database.windows.net',
    DATABASE_NAME = N'<dwdatabasename>',
    CREDENTIAL = dwcredential
)
GO

CREATE EXTERNAL TABLE [<azuresqltablename>]( 
    [col1] [int] NOT NULL,
    [col2] [int] NOT NULL
)    
WITH
(
    DATA_SOURCE = sqldw,
    SCHEMA_NAME = 'dbo', --schema name of remote table
    OBJECT_NAME = '<dwtablename>' --table name of remote table
);
R Kelly
  • 45
  • 1
  • 6