Cross Database Queries in Azure SQL databases

Written by ppolyzos

Software engineer based in Athens, Greece

7 Comments

  1. Cobus

    Hi,

    Could you please advise if it possible to do elastic queries between Azure and on prem SQL.
    Company works from Azure, there is external data needed from a 3rd party company with on prem SQL2016.

    1. ppolyzos

      Short answer is, yes, you can have your on-premise database as external data source and your on-premise table as your external table.

      Although I am not a database specialist, you have to consider the performance impact that a connection between multiple db servers in different environments may have.
      Security is another important factor to consider in this case, as you should pay extra attention on connecting your dbs.

      1. Cobus

        Thank you.

        I’ve got everything set up, but I get this error:
        Error retrieving data from one or more shards. The underlying error message received was: ‘A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)’.

        When I select a top 0 * the query works, shows all the columns. select top 1 * gives the error.

        I’ll keep that in mind, we are doing a proof of concept for now.
        Most articles / forum posts I find work from querying on querying azure from on prem…

        1. ppolyzos

          This error usually occurs either when your firewall is not setup properly or remote connections aren’t allowed to your database.
          This post might help you.

          What pricing tier are using for your Azure SQL database?

    1. ppolyzos

      No you don’t have to convert your database to an elastic azure database. Elastic uery is supported on the Standard and Premium performance tier, howeber the first elastic query can take up to a few minutes on the Standard performance tier, in order to load the elastic query functionality.

Leave a Comment

Your email address will not be published. Required fields are marked *