Currently the project I’m working on is using Azure’s Infrastructure as a Service (IaaS) and we want to move to using Azure SQL Database on Platform as a Service (PaaS).  The one thing stopping us doing that was cross database scripting, known in Azure terms as an Elastic Query.

Before we agreed the move to PaaS I wrote a prototype elastic query which worked, if we need it, the capability is there.

I then started thinking about development.  My preferred method of development is using SQL Server on my laptop as a sandbox, then when I’ve finished developing and written all my unit tests I promote it to a test box using SQL Source Control.  My problem, how do you develop locally when using functionality that is only available on an Azure SQL Database? I spent an hour or two investigating how this could be possible and came up with a couple of potential solutions:

1. Using a version of Azure to develop, not ideal because of potential costs and because I work from home sometimes I didn’t want to be wholly reliant on an Azure connection.  We live in the middle of nowhere and get about 600KB on our broadband, and that’s on a good day!!
2. I create the tables locally so that I can test the stored procedures etc and then write a migration script that deletes these tables and re-creates them as external data sources.  I ruled this one out because it would mean that I wouldn’t be testing what I was deploying which was something I didn’t like.

So, I went back to the drawing board.  Generally cross database or elastic queries, whilst possible is not recommended so I decided I had to rule them out.  I then spent an hour working out how to change my schema and stored procedures and have come up with a viable solution to my problem.  I haven’t written the code to change it but I’m pretty happy with my solution and next week when I’m back at work, currently I’m enjoying a week at home with my husband doing gardening type things, I’m looking forward to putting my solution in place.

I suppose the reason I wanted to post this as to say that sometimes you have to stop and think about things and not be blinkered, just because something works doesn’t mean to say it’s the best solution.  My new solution I think will work far better and doesn’t have the issues that cross-database scripting would give us.