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.
6
-
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 stackoverflow.com/questions/28590832/…. This is called a 'federated query'. Federated queries are usually slow and inefficient.Nick.Mc– Nick.Mc2017-01-09 04:48:28 +00:00Commented Jan 9, 2017 at 4: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– Sam2017-01-09 05:22:33 +00:00Commented Jan 9, 2017 at 5:22
-
1So 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 messagesdNick.Mc– Nick.Mc2017-01-09 06:56:58 +00:00Commented Jan 9, 2017 at 6: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– Sam2017-01-11 07:33:18 +00:00Commented Jan 11, 2017 at 7: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– Nick.Mc2017-01-11 09:14:13 +00:00Commented Jan 11, 2017 at 9:14
|
Show 1 more comment
2 Answers
Querying Data Warehouse from SQL DB is currently unsupported.
1 Comment
Nick.Mc
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.
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
);
