How can we improve Azure SQL Database?

Cross database reference

The ability to query (read-only) data from a difference database or linked server. This will allow to reference (join) some tables from the source database and build views that uses two or more SQL Azure databases.

1,350 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    anonymousanonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    Sakthivel NachimuthuSakthivel Nachimuthu shared a merged idea: Enable Linked Server Service on Azure SQL Databases  ·   · 
    Chamil De ZoysaChamil De Zoysa shared a merged idea: Allow SQL azure to perform Cross Database Queries  ·   · 
    Anonymous shared a merged idea: Cross database querying not supported ...  ·   · 
    Anonymous shared a merged idea: Add ability to access data between SQL databases. This will make SQL databases more useful in datawarehousing and analytical applications.  ·   · 
    J RobsonJ Robson shared a merged idea: Allow cross database queries for azure databases  ·   · 
    EllisEllis shared a merged idea: allow interdatabase joins.  ·   · 
    TriciaTricia shared a merged idea: Add ability to reference data in a sql azure database from a stored procedure in another SQL database. Cross Query Azure databases.  ·   · 
    anonymousanonymous shared a merged idea: Linked Server  ·   · 
    anonymousanonymous shared a merged idea: Allow 3-part synonyms (database.schema.object) to use other databases in the server.  ·   · 

    76 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Kael DowdyKael Dowdy commented  ·   ·  Flag as inappropriate

        The idea of an elastic database query feature has its merits (particularly for interacting with 3rd party remote services). However, having to implement such an approach to execute a basic cross-database transaction (for which the user connection would need proper rights to the remote database, of course) is like having to resort to using a Series 4 De-Atomizer to get the job done when a simple slingshot would have sufficed. Quite the overkill here...

      • PetersPeters commented  ·   ·  Flag as inappropriate

        Try taking backup using "Cloudbacko software" i have experience of using CloudBacko software for my MYSQL database backup. Currently, i am using the same software because it gives me full security and protection to my data. Backup of a large 100GB MySQL Database can be finished overnight. Fast multi-thread MySQL Database backup. Block level incremental hot backup with zero downtime. MySQL Database backup pre-requisites checking guarantees restorability. Multi-destination concurrent backup. Directly restore from backup to original database. Service is very good so i would like to suggest you to have CloudBacko software for your backup plan.

      • JuanPabloJofreJuanPabloJofre commented  ·   ·  Flag as inappropriate

        Using SQL 2014, I was able to do a distributed query between a local SQL server and a SQL Azure.

        First, I created a Linked-Server:
        Linked Server (name): FOO
        Provider: Microsoft OLE DB Provider for SQL Server
        Product name: (blank)
        Data Source: <azure-db>.database.windows.net
        Provider string: (blank)
        Location: (blank)
        Catalog: <db-name>

        In security options: (*) Be made using this security context:
        Remote login: <azure-user-name>
        With password: <password>

        in SSMS entered the following test query:

        use [Local_DB]
        go

        select L.[Name]
        from [dbo].[T1] L
        join (
        Select Name
        from [FOO].[RemoteDB].[dbo].[Remote_T1]
        except
        select Name
        from [FOO].[RemoteDB].[dbo].[Remote_T1]
        group by Name having count(*) > 1
        ) R on L.[Name] = R.[Name]

        It worked beautifully !!

        I found non-duplicate matches between local and azure tables.

      • MarieMarie commented  ·   ·  Flag as inappropriate

        +1. I'd like to be able to do this with EF. Distributed transactions are a must in my case.

      • Chamil De ZoysaChamil De Zoysa commented  ·   ·  Flag as inappropriate

        Elastic Database provides to perform multi shard elastic queries through external tables.

        But this approach does not work for scenarios where you use cross db queries in your stored procedures and perform CRUD operations .across dbs as those external tables are read only.

        Hence, the elastic query feature is not helping the scenarios where you plan to migrate set of on premise relational databases in to sql azure without doing major a data layer change.

      • A. Glez. B.A. Glez. B. commented  ·   ·  Flag as inappropriate

        Elastic Database Query means creating one external table for each table we want to query? So instead of having a view selecting from 10 tables in a different db we get 10 more tables and then a view. Right?

      • Torsten GrabsTorsten Grabs commented  ·   ·  Flag as inappropriate

        Cross-database queries through Elastic Database Query are now in preview across all regions of Azure SQL DB. To get started, please refer to the following content:

        - Overview: https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/
        - Tutorial: https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-getting-started/

        Note that the current functionality requires an Elastic Database Tools shard map to establish the references to remote databases. We are currently working on a much simpler experience for non-sharded data tiers. We will make this part of the preview soon.

        Thanks,
        Torsten

      • Anonymous commented  ·   ·  Flag as inappropriate

        Cross database querying not supported ...It is the heart of the system.sepcially for the master database.

      • VinceVince commented  ·   ·  Flag as inappropriate

        Thank you. Seriously. Thank you. I would buy beers and send if I could.

      • David MulhollandDavid Mulholland commented  ·   ·  Flag as inappropriate

        We were seriously looking at migrating to Azure Db but not being able to query cross database is the sole reason we cannot. We are an enterprise organisation with data split across dozens of database and frequently query cross database e.g. we have a customer database and a sales database and we want to look at sales by customer. It is not feasible to consolidate as there are far too many different concerns to include in a single database (medical records, warehousing, finance, CRM, diary, contact centre functions, helpdesk, time keeping & payroll, HR etc. etc.) and even if we did the amount of data involved would exceed the maximum allowed for a single Db anyway. We need some function to allow data to be queried via joining to tables in separate databases in order for it to be feasible to move.

      • Rafi AsrafRafi Asraf commented  ·   ·  Flag as inappropriate

        Our product is a retail POS data warehouse solution, with integrated data from different chains and stores, made for consumer goods vendors.
        Each vendor is having a separate database, with only their data on it. We also maintain master data, and general public data, that is integrated and used in reports.

        We would like to upload these databases to the cloud, using SQL Azure Databases. Having the ability to perform a cross database reference would allow us to store the common and public data once, in a global database, and use it many times. For this we need to create a view in database A that is performing a select * from t1 from database b.

        Also, exposing repots to the end user, with only their DW logs, which are stored in a central database, will be easier with a view filtering only the relevant customers logs.

        The alternative is to replicate the same data multiple time, with the risk of it being not in sync, and the overhead of storage and syncing the same data 100s of time.
        A second alternative is to run it as a VM, but then we do not really get the advantages of using a cloud database, as we will need to administrate the server, and allocate resources, and disk space, and memory etc.

      • AlexanderAlexander commented  ·   ·  Flag as inappropriate

        A lot of real world (Microsoft-) SQL(-Server) solutions depend on cross- database queries. This is the #1 showstopper for moving our application/website to Azure.

      • Anonymous commented  ·   ·  Flag as inappropriate

        This is a common task for database developers and a MAJOR obstacle to using SQL Azure. Very simple requirement:

        SELECT * INTO [db2].dbo.[some table] FROM [db1].dbo.[some table]

        or

        INSERT INTO [db2].dbo.[some table]
        SELECT * FROM [db1].dbo.[some table]

        These and similar tasks are so common that I really will not be able to use SQL Azure without the ability to copy data between databases.

      • XxBladesxXXxBladesxX commented  ·   ·  Flag as inappropriate

        I want:
        SELECT *
        INTO [TargetDB].[dbo].[TargetTable]
        FROM [SourceDB].[dbo].[SourceTable]

      ← Previous 1 3 4

      Feedback and Knowledge Base