Cross Database Queries in Azure SQL Database

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716331

  • shannonry

    Newbie

    Points: 5

    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: 716331

    You should be able to do this with AAD logins.

  • jonathan.crawford

    SSCertifiable

    Points: 6363

    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: 34207

    The article is awesome!

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

    Regards,Yelena Varsha

  • shannonry

    Newbie

    Points: 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: 34207

    Shanonry,

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

    Regards,Yelena Varsha

  • shannonry

    Newbie

    Points: 5

    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: 34207

    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: 716331

    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: 3754

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

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3754

    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.

    Attachments:
    You must be logged in to view attached files.

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

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