专用计算机上的Azure SQL数据库与MS SQL Server [英] Azure SQL Database vs. MS SQL Server on Dedicated Machine
问题描述
我目前正在专用计算机上运行MS SQL Server 2014(12.1.4100.1)的实例，我租用的专用计算机月租费为270美元，具有以下规格:
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:
- 英特尔至强E5-1660处理器(六个3.3GHz物理内核+ 超线程+ turbo-> 3.9ghz)
- 64 GB已注册的DDR3 ECC内存
- 240GB Intel SSD
- 45000 GB的带宽传输
- 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
我一直在研究Azure SQL数据库，并且一直在考虑切换到其平台的想法.我使用他们的P2 Premium定价层在V12服务器上启动了Azure SQL数据库(仅用于测试)，并从专用计算机加载了现有数据库的副本.
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).
我并排运行了几组查询，一组针对专用计算机上的数据库，一组针对P2 Azure SQL数据库.结果令人震惊:我的专用计算机每次(在执行时间方面)都大大超过了Azure数据库.通常，专用数据库实例的完成时间不到执行Azure数据库所需时间的1/2至1/3.
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.
现在，我了解Azure平台的许多好处.它是在专用计算机上的托管与非托管设置的对比，它们的时间点还原效果比我的还好，防火墙易于配置，具有地理复制等，但是我有一个数据库数百个表，每个表中有数以千万计的记录，有时需要跨多个联接进行查询等，因此在执行时间方面的性能确实很重要.我感到震惊的是，每月约930美元的服务与每月270美元的专用机器租赁相比差强人意.从整体上来说，我对SQL还是很陌生的，对服务器/等也很陌生，但这对其他人不是总有加吗?有人可能会对我在这里缺少的东西有一些了解，还是应该由Azure SQL数据库的其他托管"功能弥补价格上的差异?
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?
最重要的是，我什至已经超出了专用计算机的功能，而且我确实一直希望Azure的SQL数据库是一个不错的下一个踏脚石，但是除非我没有丢失任何东西，否则不是.我的业务规模仍然很小，无法外出并在其他平台上花费数十万美元.
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.
任何人都对我是否缺少某些东西有任何建议，或者我所看到的性能是否符合您的期望?我是否还有其他选择可以比目前正在运行的专用计算机产生更好的性能，但不花费数万美元/月呢?我可以为Azure SQL数据库做一些事情(配置/设置)，这会增加执行时间吗?再次感谢您的帮助.
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.
让我修改我的问题，以使其更清楚一些:我所期望的是纯粹的执行时间性能，每月$ 270的专用服务器的性能远远优于Microsoft的Azure SQL DB P2层@每月$ 930?忽略托管和非托管等其他特权"，忽略诸如Azure用于生产的预期用途等.我只需要知道Azure SQL DB是否缺少某些东西，或者是否真的应该使它变得更好在一台专用机器上获得出色的性能.
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.
推荐答案
Microsoft可以替代Azure SQL DB:
There is an alternative from Microsoft to Azure SQL DB:
在Azure中配置SQL Server虚拟机"
"Provision a SQL Server virtual machine in Azure"
https://azure.microsoft .com/en-us/documentation/articles/virtual-machines-provision-sql-server/
详细介绍两种产品之间的区别:了解Azure VM中的Azure SQL数据库和SQL Server"
A detailed explanation of the differences between the two offerings: "Understanding Azure SQL Database and SQL Server in Azure VMs"
独立SQL Server与Azure SQL DB之间的一个重要区别是，使用SQL DB需支付高级别的可用性，这是通过在不同的计算机上运行多个实例来实现的.这就像租用4台专用计算机并在AlwaysOn可用性组中运行它们，这将同时改变成本和性能.但是，正如您从未提到过可用性一样，我想这不是您所关心的问题. VM中的SQL Server可能会更好地满足您的需求.
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.
这篇关于专用计算机上的Azure SQL数据库与MS SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
