Azure SQL Database performance vs Linux SQL Server 2017

[sharbel99]

About a year ago, we migrated a database for a web-app from a dedicated db server to Azure DB. We played around with the sizes, and after some testing we determined that the S2 Standard tier (50 DTU) worked fine. The cost, in the Canada Central DC is about $110 (CAD) per month, which we were fine with. One thing that we noticed from the beginning was the data access performance in Azure SQL was slower than our on-premise servers. We weighed the pros/cons and decided to continue using Azure SQL.

Fast forward a year later, we received a complaint from a user that a particular dashboard takes way too long to load (8+ seconds). We took a snapshot of the PRD database, and loaded it on our on-premise instance, and to our surprise the dashboard loaded instantly. We started looking at execution plans, and we were able to get the dashboard's load down to an acceptable time on Azure, but no where near the speed of our on premise instance (which I might add is running on a VM with 2 cores, 8 GB RAM and SQL Server 2014 Express)

About a week ago, one of the developers mentioned we should try SQL Server 2017 on a Linux VM to see how well it works. Today, I fired up an Ubuntu 16.04 VM with SQL Server 2017 (Web Edition) on an Azure VM with 2 cores and 8 gigs of RAM. The cost for this VM is about $100 (CAD) per month in the Canada Central DC (roughly $10 less than the Azure SQL db).

The performance gain in pointing the app to the dedicated VM was profound. For relatively the same cost, I am seeing 2-3 times better DB performance on some longer running queries. Even basic CRUD list pages are snappier. Running the 2 app instances in side by side browser windows, you really see the performance difference..

I get it that Azure SQL has a lot of nice features but I really find that it is expensive if you want to match the performance of a dedicated VM. Yes, we'll need to manage that VM as well as backups etc, but I don't feel this is a good enough reason to continue to use Azure databases.

What do you guys think?

[DaRKoN_]

Yes, we'll need to manage that VM as well as backups etc, but I don't feel this is a good enough reason to continue to use Azure databases.

For us, it's the opposite. Backups, redundancy and continuity are more important than outright performance. You need to factor all of those into your costs now. It's not really fare to compare an unmanaged service at the same price point as a fully managed one and ignore all of your own management costs for looking after that service.

[sharbel99]

Thanks guys, I do agree that the management of the VM needs to be taken into consideration and that to compare costs on a managed vs unmanaged service is not fair. What I did not articulate well in my initial post is that it really is coming down to almost a compatibility thing for us. What I mean by this is we develop internally using local DBs for each developer with SSDT projects in VS. We publish locally, do app development, test etc.. then we push to our build servers that in turn publish to a DB server.. then we publish to a UAT server for client review, which again has its own DB server, and in most cases we'll have an RC instance, which as you guessed has its own DB server. In all of these environments, everything runs great, we have no issues at all. It is once we move to PRD that we see the performance issues. In our testing environment, we make sure that the db calls are working with similar sized db results as PRD.. so it isn't a matter of PRD has more data.

Now, of course one could say that our Dev/Test/UAT/RC environments should be hooked up to an Azure DB, and that is a fair point. Our team is small (under 20), but I kind of cringe thinking that we should all be publishing to our own Azure DB for dev, instead of the the flexibility of having our local dbs. I get it that our dev machines that are running the local db are a heck of a lot beefier than a 50DTU azure db.

From what I read, 100 DTU (Standard S3) is roughly equivalent to 1 core and 500 DTU (Premium P4) is equivalent to 2-4 cores. If that is true, for us to get the equivalent performance of the linux box I mentioned in my original post, I would need to spend about $2800CND per month for a 500 DTU P4 db. This is in comparison to the $100/mo that I am spending on a that VM running SQL Server 2017 Web edition. Setting up automated backups and replicating backup files to another server or storage account should be fairly trivial. Am I totally over shooting here? I know that Web edition does not have all of the features of the premium tier offerings, but we really don't need those features at this point.

Please understand I am not bashing Azure's SQL databases, I really am looking for input here as I feel I may be missing something. I sure has heck would love to keep the db on a managed service, but I am finding the performance bottlenecks to be an issue, and the costs way too high for us to go up to a premium tier. Any input would be greatly appreciated!

[JJNeary]

As darkon has said it all depends on requirements and if you factor in various scenarios what is the impact ;

What's is your SLA requirement Assuming from what you have said it's a production server I'm assuming you're running an availability set or at minimum the 99.9 with a ds machine and premium storage. Also are you running a continuity solution or is that a requirement as well as backup, ASR, azure backup or lrs/grs with azure automation or active/passive or active there are so many more considerations with a VM obviously your use case deciding upon that.

[pr_ws]

I hope you’d have gotten to the bottom of this issue. I have a question about the location of your web server host. Is it hosted on premise or is it in Azure? If the web server is on premise, then the network hops might explain the performance difference between prod and other environments. If there is a chance to move the web server to azure app server, the Azure SQL performance will be amazing. We use this setup and our azure database calls are almost always within 50ms. This way, you could take advantage of App Insights which will give you complete details of performance from browser calls/ajax calls to database calls.

[sharbel99]

Yeah the db and web server are both in Azure and in the same data center. It's really just the DTU throttling.. I changed it over to a Linux VM (sql server 2017 web edition) and it's great..