4

I'm working on a project that is designed to run on Microsoft's Azure platform. At present we have a few developers working locally on their own machines with the intent of being able to publish the solution to Azure when we go live. We have been using dynamic stored procedures (sprocs that are essentially quoted strings with the database name replaced) so that we can make cross-database calls, however it turns out that these don't work on Azure.

One solution seems to be to use elastic queries, using the SQL methodology below:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'encryptionpassword';  

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential    
WITH IDENTITY = 'username',  
SECRET = 'password';  

CREATE EXTERNAL DATA SOURCE mydb WITH   
    (TYPE = RDBMS,   
    LOCATION = 'path-to-database.database.windows.net',   
    DATABASE_NAME = 'dbname',   
    CREDENTIAL = SQL_Credential   
) ;  

CREATE EXTERNAL TABLE [dbo].[tc_City](
    [CityID] [uniqueidentifier] NOT NULL,
    [CityLabel] [varchar](50) NOT NULL,
    [County] [varchar](50) NOT NULL,
    [InsertionDate] [datetime] NOT NULL
) WITH ( DATA_SOURCE = mydb) 

select * from dbo.tc_City

This solution seems the most elegant, as it removes the need for dynamic stored procedures, which can be horrible to debug and modify. There is one major caveat: it doesn't seem possible to use the elastic query methodology on our local SQL server 2016 installs without adding a third party database engine. We upgraded from 2012 recently because we thought it would be possible to use the same technique, but it turns out that we have to install something called PolyBase to do elastic queries, and that is only supported with external databases of different types, such as Hadoop, rather than being capable of referencing another database within the SQL 2016 engine.

PolyBase methodology:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass';  

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential    
WITH IDENTITY = 'user',  
SECRET = 'pass';  

CREATE EXTERNAL DATA SOURCE Central WITH   
    (TYPE = HADOOP,   
    LOCATION = 'localhost',   
    CREDENTIAL = SQL_Credential   
);  

CREATE EXTERNAL TABLE [dbo].[tc_City](
    [CityID] [uniqueidentifier] NOT NULL,
    [CityLabel] [varchar](50) NOT NULL,
    [County] [varchar](50) NOT NULL,
    [InsertionDate] [datetime] NOT NULL
) WITH ( DATA_SOURCE = Central) 

select * from dbo.tc_City

As you can see, the code for accessing an external database via PolyBase ends up being mostly the same, with only the data source setting being different - which would mean we could essentially use one core set of SQL scripts/stored procedures and just run the relevant changes when publishing to Azure. Having to install Hadoop servers on our local machines just so that we can access another database within the same server without having to rewrite masses of SQL seems somewhat excessive and unnecessarily complicated.

Is there any way we can use the same methodology to access another database running on the same SQL server 2016 engine without having to write different SQL for the stored procedures running locally and those running in Azure? e.g. we don't want to have to write the "create external table ..." on Azure and reference the data in that table via dbo.external_tablename, whilst having to locally refer to databasename.dbo.external_tablename - as that would mean writing two different sets of stored procedures.

Can the external data source be referenced by file instead, if that allows all of the subsequent SQL to be the same?

  • Curious what you came up with, since this is a year old. My answer in 2017 would still be "no", and that I'd wait for Managed Instances. Until then, PolyBase is your answer, and it's not what you want (huge and unnecessary overhead, IMHO). Stating the obvious, SQL Server is not Azure SQL Database, so you'd need two sets of stored procedures because they're fundamentally different beasts. Managed instances may change that. – Randolph West Nov 21 '17 at 20:02
  • 1
    @RandolphWest I was never able to find a satisfactory workaround. Cross database calls were horrendously slow because of the size of the data that needed to be joined, so we opted to put both databases as separate schema in the same database for the time being and rely on the ability to assign better hardware. – Rexx Magnus Nov 23 '17 at 9:51
  • Managed instance seems to be ideal for your scenario. – Alberto Morillo Dec 7 '18 at 5:10
0

The 'New Polybase connectors' added in SQL Server 2019 CTP 2.0 give you what you're looking for. These are new in 2019, and are not in the 2016 and 2017 releases.

See 'New PolyBase connectors' here:

https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sqlallproducts-allversions#databaseengine

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.