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.
We are looking to better understand what customers are trying to do here. Are folks looking for cross DB query, or cross DB movement of data, or fan-out query. I’d love to hear the scenarios that you’re looking at. Please email me at firstname.lastname@example.org if you’d like to help us define how we move forward in this space.
Steve Hipwell commented
We wish to be able to join to a table in another database. Basically in our system each customer has their own database and then there are a couple of common databases. We want to use Azure but we don't feel that we can until there is the support for this.
Although is not a regular use, but maintenance services for example.
It is very useful for migration services, extra safety backup or non-timely data sync.
These are simple tasks in regular MS-SQL, but without this feature we have to use any other external tool making this task very unpractical.
Synonyms would be a great place to start!
Glenn Drake commented
I was thinking along the lines of synonyms.
Thanks Guy for getting back to us on this request.
We're specifically interested cross DB queries in order to prevent needing to replicate "common" data across multiple clusters/shards of our DBs. Due to performance, scalability, and security limitations within Azure we sometimes shard-out data for specific systems or entire customers to separate databases. This obviously complicates queries that need to "JOIN" data from a central "Core" DB with data in a different DB cluster or shard.
Here's a simple example. Let's say you have a "Core" DB which contains Customers and SalesReps and a "Customer5" DB which contains actual SalesData identified by a SalesRepID. Some systems and queries need the Customers and Reps to be stored in a central place. The SalesRep table may contain location and name information. Other requirements need the SalesData itself to be in a different DB. Currently it is not possible to run a query and "JOIN" the SalesRep information along with the SalesData across the 2 separate DBs.
In order to accomplish this you would have to replicate the SalesRep data into the other DBs. This is difficult to maintain when SalesRep data is constantly growing, updating, or restructuring. I'd love to know if if there are more elegant solutions to this scenario.
We investigated using Federations but there are too many limitations with how you can query the data which prevent us from using it. We were invited to Redmond a few years ago to speak to various Azure Dev teams and were originally excited to learn about the progress being made on Federations before it was even announced. Unfortunately it's not as flexible as we had hoped it would be.
Any update on this? Is there any timeline to have this feature available?
Any word on this? Only been 4 years.
James D. Schwarzmeier commented
This is a must-have. It would also seem to me that supporting this would also make it possible to run queries between the root and shards, or across shards in federated scenarios. Given the "scale-out" architecture that is encouraged with cloud apps, this ability seems very foundational in nature.
Jim Pelletier commented
Having to consolidate and join data from the different databases in memory on the client is a performance killer and also causes us a lot of extra and unnecessary work. Outside of Azure, it's an easy and fast job. Please add this feature.
Cris Valenzuela commented
This is a deal breaker. Without Cross Database joins my company cannot move any of its apps to SQL Azure.
Cross Database Joins!!! P L E A S E!!! Completely worthless otherwise.
We are working with big database, and we need CROSS DATABASE JOIN-s
It is necessary to have cross database calls ability, to have errors logging from database procedures, if your logs repository designed as separate database.
Mike Lord commented
Windows Azure is not ready for Prime Time for Enterprise developers without Cross-database Join capabilities!
Matthew Allen commented
This would be very useful for some of our systems.
Kameron Voller commented
Please add the cross database reference feature. If your goal is to get IT departments to move their databases to Azure then this is a must have
Jose Antonio Ocampo commented
Same problem we need to avoid merging databases.
Toby Page commented
Hugely important for our implementation. Left us with some horrid code-based solution (merging datatables) and thinking whether Azure is really right for us...
Sudheer kumar commented
This would be helpful for migrations.
While Migrating, this will be very usefull feature