SQL Azure Cross-Database Views

Question

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.

Thanks,

Josh

---

Josh

Answer 1

Josh,

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.

Rick Negrin
SQL Azure

Answer 2

Is there any update on this Rick...

Thank You - Sid

---

Sidhartha Magu

Answer 3

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.

Answer 4

You wrote " (i.e. within a cluster)".

Even when the two databases are on the same cluster, we still get the error:

"Reference to database and/or server name in 'Global.dbo.DMS.Language' is not supported in this version of SQL Server."

The code I used is:

select * from DB2.dbo.[DB2Table]

Am I doing something wrong?

Thanks!

Answer 5

Frank:

You can copy an entire database from one server to another, but you can't do cross-database queries, even when they are on the same server.  I would really like this feature though.

Mark

Answer 6

I hear you Mark, and that is my understanding. But Rick specifically wrote "(i.e. within a cluster)". My point is that it seems to me that Rick is wrong (or I am missing something).

I really would like to have this feature too!

Best regards,

Answer 7

My comments referred to the copy command.  You can copy a database within the cluster.  However, you cannot access a database from inside (i.e. inside a stored procedure) of another database. 

Rick Negrin

Answer 8

Linked Servers from a local SQL Server instance might be an option too, depending on the situation (is running a local copy of SQL even if it hosts no data possible on your setup?)

I found this article that seems to talk about it at: http://blogs.msdn.com/b/sqlcat/archive/2011/03/08/linked-servers-to-sql-azure.aspx

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).

Thanks,

Dan