Hi All;
I've recently has a situation where I need to query data across 2 databases in Azure;
Now both these Db's are on the same logical server with database A being auditing data from several applications and many sources including Google Analytics and SalesForce and database 2 being a reporting DB. This reporting data gets feeds via ADF for many other sources including on Prem.
Options considered
1: Build a new ADF pipeline to get the data from source into my reporting DB - This is in place for data that is not in database A but for the data in DB A, it would just be a waste of time so not a preferred option.
2: ADF the data from A to B - As option 1 duplication of work, duplication of data, waste of time & effort, extra operational overhead.
3: Join the data from A & B in an aggregation layer such as Power Bi or AAS - Fine for POC but as this is an evolving solution I don't want to build out a tabular model as yet.
4: Use External tables -- Preferred option.
So I have opted for option 4 and set this up which is great. Now my one concern is that for all the data loading pipelines, I am using ADF with managed identity however as far as I can see, Managed Identities cannot be used for external tables where it is SQL source and destination: Managed identities can be used for external tables in many circumstances but not in the situation I have.
Have I missed something? -- It looks like this may be possible in Synapse but I'd rather not go there as yet.
