Azure SQL 数据库与专用计算机上的 MS SQL Server

Azure SQL Database vs. MS SQL Server on Dedicated Machine

提问by Daniel A. Burke

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:

我目前正在一台专用机器上运行 MS SQL Server 2014 (12.1.4100.1) 实例，我以 270 美元/月的价格租用以下规格：

• 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

• Intel Xeon E5-1660 处理器（六个物理 3.3ghz 内核 + 超线程 + turbo->3.9ghz）
• 64 GB 注册 DDR3 ECC 内存
• 240GB 英特尔固态硬盘
• 45000 GB 带宽传输

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).

我一直在玩弄 Azure SQL 数据库一段时间，并且一直在考虑切换到他们的平台的想法。我在 V12 服务器上使用他们的 P2 Premium 定价层启动了 Azure SQL 数据库（只是为了测试），并加载了我现有数据库的副本（从专用机器）。

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.

我并排运行了几组查询，一组针对专用计算机上的数据库，另一组针对 P2 Azure SQL 数据库。结果有点令人震惊：我的专用机器每次都大大超过（在执行时间方面）Azure 数据库。通常，专用数据库实例的完成时间不到 Azure 数据库执行时间的 1/2 到 1/3。

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 平台的诸多优势。它是在专用机器上托管与我的非托管设置，它们的时间点恢复比我拥有的要好，防火墙很容易配置，有异地复制等。但我有一个数据库数百个表，每个表中有数千万到数亿条记录，有时需要跨多个连接进行查询等，因此执行时间方面的性能确实很重要。我只是觉得令人震惊的是，每月 930 美元的服务与每月 270 美元的专用机器租赁相比表现如此糟糕。总的来说，我对 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.

任何人都对我是否遗漏了什么有任何建议，或者我看到的表现是否符合您的期望？我有没有其他选择可以产生比我目前运行的专用机器更好的性能，但成本不超过数万/月？我可以为我的 Azure SQL 数据库做些什么（配置/设置）来提高执行时间？再次，任何帮助表示赞赏。

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.

编辑：让我修改一下我的问题，让它更清楚一点：我所看到的纯粹的执行时间性能是预期的，其中专用服务器 @ 270 美元/月的性能远远优于 Microsoft 的 Azure SQL DB P2 层@ 930 美元/月？忽略其他“特权”，如托管与非托管，忽略预期用途，如 Azure 用于生产等。我只需要知道我是否遗漏了 Azure SQL DB 的某些内容，或者我是否真的应该变得更好单台专用机器的性能。

采纳答案by Hyman Richins

There is an alternative from Microsoft to Azure SQL DB:

从 Microsoft 到 Azure SQL DB 有一个替代方案：

“Provision a SQL Server virtual machine in Azure”

“在 Azure 中配置 SQL Server 虚拟机”

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

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”

两种产品之间差异的详细说明：“了解 Azure VM 中的 Azure SQL 数据库和 SQL Server”

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

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.

独立的 SQL Server 和 Azure SQL DB 之间的一个显着区别是，使用 SQL DB，您需要为高级别的可用性付费，这是通过在不同的计算机上运行多个实例来实现的。这就像租用 4 台专用计算机并在 AlwaysOn 可用性组中运行它们，这将改变您的成本和性能。但是，由于您从未提到可用性，我猜这在您的场景中不是问题。VM 中的 SQL Server 可能更符合您的需求。

回答by Dai

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

（免责声明：我为 Microsoft 工作，但不在 Azure 或 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.

“Azure SQL”不等同于“SQL Server”——我个人希望我们确实提供一种“托管 SQL Server”而不是 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).

从表面上看，两者是相同的：它们都是具有 T-SQL 查询能力的关系数据库系统（好吧，它们都在后台使用相同的 DBMS）。

Azure SQL is different in that the ideais 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.

SQL Azure的是，不同的想法是，你有两个数据库：使用本地SQL Server（最好2012或更高版本）和在Azure上的SQL生产数据库开发数据库。您（应该）永远不要直接修改 Azure SQL 数据库，实际上您会发现 SSMS 不为 Azure SQL 提供设计工具（表设计器、视图设计器等）。相反，您设计和使用本地 SQL Server 数据库并创建“DACPAC”文件（或特殊的“更改”XML 文件，可以由 SSDT 生成），然后修改您的 Azure DB 以便它复制您的开发 DB，一种“设计复制”系统。

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

否则，正如您所注意到的，Azure SQL 提供内置的弹性、备份、简化的管理等。

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).

至于性能，您是否可能缺少索引或其他优化？您可能还会注意到与本地 SQL Server 相比，Azure SQL 的延迟略高，我看到 ping 时间（从 Azure VM 到 Azure SQL 主机）大约为 5-10 毫秒，这意味着您应该将应用程序设计得更少-聊天或并行化数据检索操作以减少页面加载时间（假设这是您正在构建的 Web 应用程序）。

回答by SQLmojoe

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.

• 总成本：您 270 美元的租金只是众多成本因素之一。空间、电力和暖通空调是其他物理成本。然后是管理成本。想想你必须在星期二和 Windows 或 SQL Server 提供服务包或累积更新时完成每个补丁的工作。即使您在推出之前不对其进行测试，也仍然需要时间和精力。如果您进行测试，那么还有第二台机器并复制产品实例和工作负载以进行测试。
• 安全性：有很多关于将您关心的任何数据存储在云中的糟糕、危险和风险的文章。就我个人而言，我看到本地服务器（甚至在银行和联邦机构）的安全实施和流程比我在任何主要云提供商（微软、亚马逊、谷歌）上看到的都要糟糕。把事情做好需要做很多工作，然后再做更多的工作来保持正确。此外，您还可以查看和审核他们的安全 SLA（请参阅 Azure 的http://azure.microsoft.com/en-us/support/trust-center/）。
• 可扩展性：不仅仅是原始的可扩展性，还有扩展的成本和努力。Azure SQL DB 最近发布了巨大的 P11 版本，它的计算能力是你测试过的 P2 的 7 倍。放大和缩小不是即时的，而是非常简单且相当快速。最好的部分是（无论如何对我来说），当我运行大型查询或重新索引操作时，它可以被提升到更高的版本，然后再次返回以进行“正常”加载。使用裸机上的常规 SQL Server 很难做到这一点 - 要么租/买一个 90% 时间都闲置的非常大的盒子，要么需要停机才能移动。如果在虚拟机中，则稍微容易一些；您可以在线增加内存，但仍需要反弹实例以增加CPU；您的 Azure SQL DB 在扩展/缩减操作期间保持在线。

回答by Sirisha Chamarthi

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.

SQL DB 具有内置的可用性（这会影响性能）、时间点还原功能和 DR 功能。您可以选择根据您的使用情况扩大/缩小您的数据库以降低成本。您可以使用全局查询（分片数据）提高查询性能。SQl DB 管理自动升级和修补，并极大地改善了可管理性。您可能需要为此支付一点溢价。应用程序级缓存/平均分配负载、冷时降级等可能有助于提高数据库性能并优化成本。