Azure SQL Database vs. SQL Server on an Azure Virtual Machine
I’m working on my first paid project using Azure. Previously, I’d set up test databases in Azure but nothing focuses the mind like having to get something working for a real client. I am hardly an Azure expert, but most of my clients are interested in or using some sort of cloud application, of which Azure SQL database is one. So, part of my motivation for writing this post is to get my thoughts clear.
Why Azure SQL Database Should Work for Me and My Clients
In the SQL server universe, I spend most of my time writing queries and doing integration development on SQL Server. I’m not a production DBA. I only learned about the back end to help various clients survive SOX and other financially related orders. So things like backups and redundancy are areas where neither my team nor I have in depth experience. Most of our clients have someone else (whether an ERP VAR or an in house IT group) who’s responsible for keeping the infrastructure up and running.
For me, this is the big selling point of Azure. If I don’t have to worry about the back end stuff, it makes it easier to create our solutions.
Also in Azure’s favor: developing on Azure SQL Database is much the same experience as any other database—whether on-premise or in a hosted environment like Rackspace. Indeed, we do the vast majority of our work remotely. So, while there are some small annoyances (as long as you stay within one database), it feels no different.
Why Azure SQL Database Doesn’t Work for Me or My Clients
There are two big reasons why Azure SQL database doesn’t work for me:
- Lack of SSRS (SQL Server Reporting Services)
- Cross database functionality.
Lack of SSRS
Azure SQL Database is well named. It is SQL Database, not SQL Server. SSRS is not part of the package. (Other parts of SQL Server aren’t included either, but we don’t use them as frequently).
Therefore, if you want to use SSRS (which we do. Always.), you’ll be spinning up another server. And this means you’ll end up responsible for much of the maintenance you’d hoped to avoid with Azure SQL Database. Granted, the scope of maintenance can be much less. But you still have to deal with it.
Cross Database Functionality
In standard SQL Server, accessing another database is as simple as changing your table reference from dbo.mytable to mydatabase.dbo.mytable.
In Azure SQL Database, it’s not nearly as simple. You can’t even switch databases in SSMS without opening a new query.
There are ways to access other databases, but they’re much more cumbersome. (I won’t describe the procedure here. But if you’re interested, I recommend these posts by Paris Polyzos and Torsten Grabs.)
In my business, I work across multiple databases all the time. It’s part and parcel of our commitment to provide “the reports you need from the software you already have.”
And I’ve written posts about how to manage issues of cross database code. (For example, this one on a Dynamics GP integration.)
Given the multi-database nature of our work, Azure SQL Database’s restrictions on accessing other databases would require a major rethink. This may be worth it. But not right now.
Azure SQL Database can make a strong case for some uses. But considering how my team and I spend most of our time, for now we’ll be sticking with SQL Server in a virtual machine.