dotnetco.de

As written in my previous blog post, I’ve migrated a database from MS SQL Server to Azure SQL. This worked quite fine. But now I wondered why one of my stored procedures does not work anymore….

Problem with cross-database joins

I checked the stored procedure and disabled a join which used data from a separate database. The database is of course also in Azure SQL and on the same Azure SQL Server, nevertheless it’s not possible to query this….

Here is a short example: Let’s say we have a project database and a global user database. The project database of course only stores the userID and then checks the Users-Database for details like Username, Telephone or whatever. This could result in a query like this

Select
    P.*, U.Name FROM [dbo].[MyProjects] P INNER JOIN [Users].[dbo].[Usernames] U ON
    P.UserID = U.UserID

 

Of course this works brillant (as long as access rights are set correct) for years. But not in Azure SQL….

There is a ‘solution’

Fortunately, Microsoft has setup a Feedback Forum for Azure, asking for ideas and suggestions regarding Azure. Not surprisingly, Cross database reference has already been requested in 2009:

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.

Ah, it has been set to ‘Completed’ just 4 months ago, great. So why does it still not work? The ‘solution’ is:

We announced the public preview of elastic database query at \Build recently. Full details at http://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/

Hmm… public preview does not sound like a solution. Anyway, let’s have a look at the linked article. OK, it does not seem to work out-of-the-box for whatever reason. Would be too simple if you could use Azure SQL similar to MS SQL Server… But take a look at this note:

The dedicated elastic database query database must be a SQL DB v12 database and initially only the Premium tier is supported.

Hmmm… Premium tier? Let’s check the Azure Pricing Calculator what this means in $.

• One standard SQL DB is 15$ per month.
• One premium SQL DB is 465$ per month.

Paying an extra 450 $ per month just for cross database queries…. Well, no!

Workarounds

So what to do now? Currently these are the 2 options:

1. Migrate everything into 1 single database so you don’t need to query across multiple databases.
2. Do 2 separate queries in your code (e.g. vb.net or c#) so that you first retrieve the projects and afterwards the usernames in a separate call

Strange that Microsoft did not fix this problem yet. If you see the comments-section of the feedback page, several users already complaint because it’s quite a basic feature. If you have a general user catalog, why need to implement into each database? Or why slowing down the system by causing multiple calls instead of one JOIN directly in SQL?