Is it possible to create a view on one SQL Azure database that pulls data from another SQL Azure database? If so, is it possible to do this across SQL Azure accounts?
Our implementation will be deployed multiple times to each client and the thought was that we keep all global currency, region, location data in a global database somehow accessible to all other SQL Azure instances.
This is a great question. The only way to do this today would be to use SSIS or BCP and some job setup on a timer to pull the data over from the global DB to the regional dbs. We are working on some features that will make this easier but they won't be out until later in the year.
There are a couple of features we have released since this thread started (2 years ago!).
- DB Copy feature (a TSQL command part of the Create command, i.e. Create Database Foo as copy of Bar)
This allows you to make a copy of a database within a sub-region (i.e. within a cluster). So if you want to have multiple copies of your database to scale out read queries you can use this.
- Import/Export Service
We also have the Import/Export service. This allows you to make a logical copy of hte database by exporting the data and schema out of the clustser to a flat file. Then you can import the database to any SQL Azure cluster or an on-prem SQL database.
This can be used in a wider range of scenarios.
One could use just an SQL Server Express instance. The article states that Linked Servers are only available in Enterprise Edition but this isn't correct, I've used Linked Servers in Express edition before.
Might not work for the original posters instance, but it does in mine and thought this might be useful for someone coming across this thread (and I realize this wasn't possible when this thread was originally started).