31

I'm currently running an instance of MS SQL Server 2014 (12.1.4100.1) on a dedicated machine I rent for $270/month with the following specs:

  • Intel Xeon E5-1660 processor (six physical 3.3ghz cores + hyperthreading + turbo->3.9ghz)
  • 64 GB registered DDR3 ECC memory
  • 240GB Intel SSD
  • 45000 GB of bandwidth transfer

I've been toying around with Azure SQL Database for a bit now, and have been entertaining the idea of switching over to their platform. I fired up an Azure SQL Database using their P2 Premium pricing tier on a V12 server (just to test things out), and loaded a copy of my existing database (from the dedicated machine).

I ran several sets of queries side-by-side, one against the database on the dedicated machine, and one against the P2 Azure SQL Database. The results were sort of shocking: my dedicated machine outperformed (in terms of execution time) the Azure db by a huge margin each time. Typically, the dedicated db instance would finish in under 1/2 to 1/3 of the time that it took the Azure db to execute.

Now, I understand the many benefits of the Azure platform. It's managed vs. my non-managed setup on the dedicated machine, they have point-in-time restore better than what I have, the firewall is easily configured, there's geo-replication, etc., etc. But I have a database with hundreds of tables with tens to hundreds of millions of records in each table, and sometimes need to query across multiple joins, etc., so performance in terms of execution time really matters. I just find it shocking that a ~$930/month service performs that poorly next to a $270/month dedicated machine rental. I'm still pretty new to SQL as a whole, and very new to servers/etc., but does this not add up to anyone else? Does anyone perhaps have some insight into something I'm missing here, or are those other, "managed" features of Azure SQL Database supposed to make up the difference in price?

Bottom line is I'm beginning to outgrow even my dedicated machine's capabilities, and I had really been hoping that Azure's SQL Database would be a nice, next stepping stone, but unless I'm missing something, it's not. I'm too small of a business still to go out and spend hundreds of thousands on some other platform.

Anyone have any advice on if I'm missing something, or is the performance I'm seeing in line with what you would expect? Do I have any other options that can produce better performance than the dedicated machine I'm running currently, but don't cost in the tens of thousand/month? Is there something I can do (configuration/setting) for my Azure SQL Database that would boost execution time? Again, any help is appreciated.

EDIT: Let me revise my question to maybe make it a little more clear: is what I'm seeing in terms of sheer execution time performance to be expected, where a dedicated server @ $270/month is well outperforming Microsoft's Azure SQL DB P2 tier @ $930/month? Ignore the other "perks" like managed vs. unmanaged, ignore intended use like Azure being meant for production, etc. I just need to know if I'm missing something with Azure SQL DB, or if I really am supposed to get MUCH better performance out of a single dedicated machine.

Daniel A. Burke
  • 544
  • 1
  • 5
  • 11
  • I'm considering the same move from local installation to Azure. Regarding performance, after the move some post-migration steps seems necessary. Among those index defragmentation and to update stats, see link below. Both these will have impact on performance. By the way, did you move to Azure and did you come to grips the performance difference? [http://rocksolid.gibraltarsoftware.com/loupe/loupe-service-migrating-from-sql-server-to-sql-azure] – John P Apr 28 '16 at 03:08

4 Answers4

15

(Disclaimer: I work for Microsoft, though not on Azure or SQL Server).

"Azure SQL" isn't equivalent to "SQL Server" - and I personally wish that we did offer a kind of "hosted SQL Server" instead of Azure SQL.

On the surface the two are the same: they're both relational database systems with the power of T-SQL to query them (well, they both, under-the-hood use the same DBMS).

Azure SQL is different in that the idea is that you have two databases: a development database using a local SQL Server (ideally 2012 or later) and a production database on Azure SQL. You (should) never modify the Azure SQL database directly, and indeed you'll find that SSMS does not offer design tools (Table Designer, View Designer, etc) for Azure SQL. Instead, you design and work with your local SQL Server database and create "DACPAC" files (or special "change" XML files, which can be generated by SSDT) which then modify your Azure DB such that it copies your dev DB, a kind of "design replication" system.

Otherwise, as you noticed, Azure SQL offers built-in resiliency, backups, simplified administration, etc.

As for performance, is it possible you were missing indexes or other optimizations? You also might notice slightly higher latency with Azure SQL compared to a local SQL Server, I've seen ping times (from an Azure VM to an Azure SQL host) around 5-10ms, which means you should design your application to be less-chatty or to parallelise data retrieval operations in order to reduce page load times (assuming this is a web-application you're building).

Dai
  • 141,631
  • 28
  • 261
  • 374
  • 1
    I do understand that the Azure DB is supposed to fulfill the production role, and I currently mimic this format, but by using two local SQL Server instances on my dedicated machine--one for dev and one for production. But the production one needs to execute the more complex queries faster. Some take over 5 minutes currently. Sure there are plenty of quick, transactional queries, but those are of little concern. I did check, and I successfully recreated all the same indices on the Azure DB that I had on my local db, but still it seems to run (execution time) a lot slower. – Daniel A. Burke Sep 06 '15 at 05:59
  • With this type of paradigm, how do you sync data between all the local databases, or add in test data? – TWilly May 04 '16 at 23:47
  • @TWilly As Azure is intended for Production-only data, you shouldn't have Test data, but if you do want to seed data then you can include seeding operations in your Post-Deploy SQL Scripts in your DACPAC. As for data sync, Azure SQL supports Data Replication with self-hosted SQL Server: https://azure.microsoft.com/en-us/blog/transactional-replication-to-azure-sql-db/ – Dai May 05 '16 at 02:04
  • I'd have thought that Azure SQL DB is becoming increasingly suited for dev/test databases. It doesn't require the DBA to manage locally installed instances. The tooling support is catching up. It scales to the needs of the developer... – David Atkinson Aug 11 '16 at 12:56
  • 1
    @DavidAtkinson The tooling is still far behind - SSMS has no designer support for Azure, and the latency from Azure SQL makes SSMS and SSDT still a painful experience as the UI still freezes during background operations. A local SQL Server Express installation still gives a much better user-experience - and is free! – Dai Feb 12 '17 at 05:26
  • @Dai - which operations in SSMS are slow when running against Azure? – David Atkinson Feb 13 '17 at 15:36
  • 2
    @DavidAtkinson UI things are the main issue for me - after right-clicking a table in Object Explorer it takes up to 2-3 seconds for the context-menu to appear - every time, whereas on a local or LAN server it's near-instant. There are many areas in SMSS where the UI thread blocks on network activity. Multiply the pain levels when dealing with Azure instances in datacenters in different continents. – Dai Feb 13 '17 at 18:42
15

Perf and availability aside, there are several other important factors to consider:

  • Total cost: your $270 rental cost is only one of many cost factors. Space, power and hvac are other physical costs. Then there's the cost of administration. Think work you have to do each patch Tuesday and when either Windows or SQL Server ships a service pack or cumulative update. Even if you don't test them before rolling out, it still takes time and effort. If you do test, then there's a second machine and duplicating the product instance and workload for test.
  • Security: there is a LOT written about how bad and dangerous and risky it is to store any data you care about in the cloud. Personally, I've seen way worse implementations and processes on security with local servers (even in banks and federal agencies) than I've seen with any of the major cloud providers (Microsoft, Amazon, Google). It's a lot of work getting things right then even more work keeping them right. Also, you can see and audit their security SLAs (See Azure's at http://azure.microsoft.com/en-us/support/trust-center/).
  • Scalability: not just raw scalability but the cost and effort to scale. Azure SQL DB recently released the huge P11 edition which has 7x the compute capacity of the P2 you tested with. Scaling up and down is not instantaneous but really easy and reasonably quick. Best part is (for me anyway), it can be bumped to some higher edition when I run large queries or reindex operations then back down again for "normal" loads. This is hard to do with a regular SQL Server on bare metal - either rent/buy a really big box that sits idle 90% of the time or take downtime to move. Slightly easier if in a VM; you can increase memory online but still need to bounce the instance to increase CPU; your Azure SQL DB stays online during scale up/down operations.
SQLmojoe
  • 1,924
  • 1
  • 11
  • 15
  • All totally true and relevant. My biggest issue was simply overcoming the fact that my $270 machine WAY outperformed in terms of execution time even Azure's P4 I recently tested with. I always knew of the extras of a managed solution like Azure over my own machine, I just a) didn't realize the scope of them and that they really could make up for that large price gap, and b) wanted to be absolutely certain I wasn't missing some configuration or tuning option to get more out of the Azure products. For now... it looks like I'm sticking with my own server because execution time is so critical. – Daniel A. Burke Sep 09 '15 at 03:47
  • 1
    I don't think you're missing anything major as far as configuration is concerned. Dedicated bare metal hosted databases will almost always outperform cloud service based databases. There is just more fixed overhead for the latter to deal with initially. The real value comes from other places as described by many here and when you need grow. If you have a simple to moderate workload and you're able to manage it yourself, the $270 rental is the right platform. You already know the various other issues that might come up so as long as you keep an eye on them, I think you'll be fine. – SQLmojoe Sep 09 '15 at 16:47
12

There is an alternative from Microsoft to Azure SQL DB:

“Provision a SQL Server virtual machine in Azure”

https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-provision-sql-server/

A detailed explanation of the differences between the two offerings: “Understanding Azure SQL Database and SQL Server in Azure VMs”

https://azure.microsoft.com/en-us/documentation/articles/data-management-azure-sql-database-and-sql-server-iaas/

One significant difference between your stand alone SQL Server and Azure SQL DB is that with SQL DB you are paying for high levels of availability, which is achieved by running multiple instances on different machines. This would be like renting 4 of your dedicated machines and running them in an AlwaysOn Availability Group, which would change both your cost and performance. However, as you never mentioned availability, I'm guessing this isn't a concern in your scenario. SQL Server in a VM may better match your needs.

Jack Richins
  • 548
  • 2
  • 5
  • Maybe that's what I was missing: the high availability. I just wish there were a tier of Azure SQL DB that matched or exceeded the execution time performance of my dedicated server, but not even the Premium P4 tier does that from a few test queries I ran. I was not able to match performance until I scaled up an Azure SQL Data Warehouse instance to 300 DWU, but I know relatively nothing about data warehouses, so.... – Daniel A. Burke Sep 07 '15 at 21:47
2

SQL DB has built in availability (which can impact performance), point in time restore capability and DR features. You have the option to scale up / down your DB based on your usage to reduce the cost. You can improve your query performance using Global query (shard data). SQl DB manages auto upgrades and patching and greatly improves the manageability story. You may need to pay a little premium for that. Application level caching / evenly distributing the load, downgrading when cold etc. may help improve your database performance and optimize the cost.

Sirisha Chamarthi
  • 1,283
  • 12
  • 16