Cross Database Queries in Azure SQL Database

Question

Cross Database Queries in Azure SQL Database

Steve Jones - SSC Editor

SSC Guru

Points: 736478
More actions
July 25, 2019 at 12:00 am

#3662210

Comments posted to this topic are about the item Cross Database Queries in Azure SQL Database

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

shannonry Newbie Points: 5 More actions · Answer 1

This is very exciting to read! Thanks so much for the content.At my work we are fully cloud integrated on Azure and are trying to move from shared users and passwords to using Azure Active Directory for credentialing. Is it possible to setup a user to do this strictly using AAD?

Thank you for the article! I plan on trying it out today.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736478

July 25, 2019 at 1:36 pm

#3666064

You should be able to do this with AAD logins.

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 3

Can I query a different database and join multiple tables together in one query, combining with data in the source database? That seems rather straight-forward, but maybe not.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

The article is awesome!

This feature is like an online version of the linked server minus updates.

Regards,Yelena Varsha

shannonry Newbie Points: 5 More actions · Answer 5

jonathan.crawford wrote:

Can I query a different database and join multiple tables together in one query, combining with data in the source database? That seems rather straight-forward, but maybe not.

Yes, this is possible. I just performed what you were asking about. What it seems like you're essentially doing is creating a template for the information to render from the remote table into the source database/table call.

What I'm curious about is if this is like an indexed view, or a regular view in that if the attributes change on the remote then will the source external table freak out and throw an error. I plan on testing some of these possibilities later, but I'll have to do that a little later I guess.

Also, I notice that the external table name must match the target table name, which kinda defeats the purpose of my experiment with this, but maybe I can find a workaround for it. I'm glad they're doing work on this though, I really hate having to run the same query through 27 different databases.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 6

Shanonry,

You can define a multi server query (registered servers) and run the query only once.

Regards,Yelena Varsha

shannonry Newbie Points: 5 More actions · Answer 7

Yelena Varshal wrote:

Shanonry, You can define a multi server query (registered servers) and run the query only once.

I'll look into doing that, but do you have any BOL that I can reference? This is something I may explore tomorrow morning since I have deliverables for the remainder of the day.

Otherwise, I'll look that up through Google searches later tonight.

I appreciate the direction, regardless!

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 8

Shannonry, I don't have a BOL link, but in Management Studio do:

View - Registered Servers

Right-click on local server groups and select New Server Group, give it a name

Right-click on the new group name after it is created and select New Registered Server

In there on General tab specify the server name, connection and on the Connection Properties tab specify your first database. Complete the wizard. Then do the same with the second database. etc so you will need separate registered servers for all your azure databases. You do it only once. It is good to give the each connection the the name that will tell you what database you are connecting to.

After you do that once, each time you need to run a query on all databases update queries included) you just right -click the group name and select New Query. It will take time to connect to all databases and in the query window it will show server name as Multiple. The query results will additionally include the server name for each result.

You can avoid working with the UI for 27 servers by looking up where the registered servers XML file is located and editing it. Create a first connection in the Management Studio, then edit the file.

Regards,Yelena Varsha

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 9

jonathan.crawford wrote:

Can I query a different database and join multiple tables together in one query, combining with data in the source database? That seems rather straight-forward, but maybe not.

Yes

2019-07-26 16_05_20-SQLQuery3.sql - dkranchapps.database.windows.net.Speakers (sjones (121))_ - Micr

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 10

'Should' being the key word. Can you post a link to some examples of an AAD connected query? Thanks.

This reply was modified 5 years, 1 month ago by Dan Guzman - Not the MVP. Reason: more detail

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 11

Found this at MS Docs 🙁

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview

Important

Authentication using Azure Active Directory with elastic queries is not currently supported.

This reply was modified 5 years, 1 month ago by Dan Guzman - Not the MVP.

Attachments:

You must be logged in to view attached files.