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.
Add ability to reference data in a sql azure database from a stored procedure in another SQL database.
In our S+S scenario we definitely need to be able to create linked servers against a cloud DB.
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 email@example.com if you’d like to help us define how we move forward in this space.
We currently load data into a staging database from offline data sources for validation, before it is then loaded via overnight batch into the main database as part of a transaction.
I have come from an self-hosted environment where this is possible to Azure where it is not.
The only viable solution given my current deadline is to merge the databases, which is less than ideal.
Please can this feature be added.
Amazon's MSSQL offering does not have this limitation. I am currently using that with much success, maybe you need to look there.
What is the status of this feature? I'd like to know if we should abandon any hope of Microsoft support this and move off of SQL Database. We're currently still not happy with any alternatives to this problem.
I need cross database query (joins and transactions)
Gowtham Kunduru commented
it has been 4 years, but still the same. cross database query is very useful when you are dealing with multiple databases. please let us know if you are going to provide solution for this or not. if you can provide this so many customers are happy to use the feature.
Ed O'Brien commented
Looking for cross-database movement of data. The new backup/restore feature added last month is a great enhancement - would like the ability to update production database with subset of data from restored database.
Scott Butler commented
Major deal breaker. Many times our app uses other third-party frameworks, which have their own DB's and schema's. We like to keep things clean so we have our separate DB. Then we need to do some cross DB joins for special examples. Think about a Security framework with its own scheam in a Security DB, then I have my data in my separate DB. I want to keep them separate so I can easily upgrade the security framework when needed. However, I need to write some queries that join the two in many situations.
Graham Wager commented
Our current scenario would be greatly eased with this capability. We currently have an old and new system and are slowly migrating clients between the two. Being able to do this using just SQL would greatly reduce the time it would take.
Currently we're having to backup, download, restore then link from a local instance into Azure to perform the operation.
what is the alternate solution?
I dont understand why it is not supported in azure. I bet ms see some good in linked servers otherwise it shouldnt be present in classic sql server versions.
This should be an absolute no brainer that folks need this!
I have a large R/O database I want to cros query with our R/W database. It does not make sense to combine into one db.
"Linked Servers" would be what I'm interested in.
Andy Dillbeck commented
We want to use Azure as a back end for a load balancing server, which will sync to our server based sql server database. The local sql server has two databases, one to hold user data, and one to hold the main data. I set up two sync groups because when I tried to sync both databases to a single Azure database it gave me all kinds of errors. I'd really love to have the ability to look up data in one database from another.
Our company would also want to join to a table in a different database. The same case as the comment below, we have multiple client DBs and then 1 or 2 common DBs.
I also think not having support for this would prevent us from using Azure.
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.