SQL Server Edition差異比較

 

SQL Server Enterprise與Standard 基本差異比較如下:

	SQL Server Enterprise Edition	SQL Server Standard Edition
可用性	AlwaysOn高可用性支援多個主機節點 最高可建置8台次要主機節點	AlwaysOn高可用性僅支援2台主機節點 Limit of two replicas No read access on secondary replica
可用性	AlwaysOn高可用性支援多個DB	AlwaysOn高可用性僅支援一個AG for 一個DB Basic Always On availability groups for a single database
計算能力-CPU	1.Enterprise Edition: Core-based Licensing Operating system maximum   2.Enterprise Edition with Server + Client Access License (CAL) licensing is limited to 20 cores per SQL Server instance.	Limited to lesser of 4 sockets or 24 cores
可用性	Hot add memory and CPU	不支援
計算能力-Memory	Operating System Maximum	128 GB
管理	線上索引作業	不支援線上索引作業 (可能影響資料庫的可用性)
管理	Resource governor (可限制帳號資源使用)	不支援Resource governor

 

 

Compute capacity limits by edition of SQL Server

https://docs.microsoft.com/en-us/sql/sql-server/compute-capacity-limits-by-edition-of-sql-server?view=sql-server-ver15

SQL Server edition	Maximum compute capacity for a single instance (SQL Server Database Engine)	Maximum compute capacity for a single instance (AS, RS)
Enterprise Edition: Core-based Licensing*	Operating system maximum	Operating system maximum
Developer	Operating system maximum	Operating system maximum
Standard	Limited to lesser of 4 sockets or 24 cores	Limited to lesser of 4 sockets or 24 cores
Express	Limited to lesser of 1 socket or 4 cores	Limited to lesser of 1 socket or 4 cores

*Enterprise Edition with Server + Client Access License (CAL) licensing is limited to 20 cores per SQL Server instance. (This licensing is not available for new agreements.) There are no limits under the Core-based Server Licensing model.

In a virtualized environment, the compute capacity limit is based on the number of logical processors, not cores. The reason is that the processor architecture is not visible to the guest applications.

For example, a server that has four sockets populated with quad-core processors and the ability to enable two hyperthreads per core contains 32 logical processors with hyperthreading enabled. But it contains only 16 logical processors with hyperthreading disabled. These logical processors can be mapped to virtual machines on the server. The virtual machines' compute load on that logical processor is mapped to a thread of execution on the physical processor in the host server.

Editions and supported features of SQL Server 2019 (15.x)

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15

Scale limits

SCALE LIMITS

Feature	Enterprise	Standard	Web	Express with Advanced Services	Express
Maximum compute capacity used by a single instance - SQL Server Database Engine1	Operating system maximum	Limited to lesser of 4 sockets or 24 cores	Limited to lesser of 4 sockets or 16 cores	Limited to lesser of 1 socket or 4 cores	Limited to lesser of 1 socket or 4 cores
Maximum compute capacity used by a single instance - Analysis Services or Reporting Services	Operating system maximum	Limited to lesser of 4 sockets or 24 cores	Limited to lesser of 4 sockets or 16 cores	Limited to lesser of 1 socket or 4 cores	Limited to lesser of 1 socket or 4 cores
Maximum memory for buffer pool per instance of SQL Server Database Engine	Operating System Maximum	128 GB	64 GB	1410 MB	1410 MB
Maximum memory for Columnstore segment cache per instance of SQL Server Database Engine	Unlimited memory	32 GB	16 GB	352 MB	352 MB
Maximum memory-optimized data size per database in SQL Server Database Engine	Unlimited memory	32 GB	16 GB	352 MB	352 MB
Maximum memory utilized per instance of Analysis Services	Operating System Maximum	16 GB2 64 GB3	N/A	N/A	N/A
Maximum memory utilized per instance of Reporting Services	Operating System Maximum	64 GB	64 GB	4 GB	N/A
Maximum relational database size	524 PB	524 PB	524 PB	10 GB	10 GB

1 Enterprise Edition with Server + Client Access License (CAL) based licensing (not available for new agreements) is limited to a maximum of 20 cores per SQL Server instance. There are no limits under the Core-based Server Licensing model.

4 On Enterprise Edition, the number of nodes is the operating system maximum. On Standard edition there is support for two nodes.

Basic Always On availability groups for a single database

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver15

Limitations

Basic availability groups use a subset of features compared to advanced availability groups on SQL Server 2016 Enterprise Edition. Basic availability groups include the following limitations:

• Limit of two replicas (primary and secondary). Basic Availability Groups for SQL Server 2017 on Linux support an additional configuration only replica.

• No read access on secondary replica.

• No backups on secondary replica.

• No integrity checks on secondary replicas.

• No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).

• Support for one availability database.

• Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition.

• Basic availability groups are only supported for Standard Edition servers.

• Basic availability groups cannot be part of a distributed availability group.

• You may have multiple Basic availability groups connected to a single instance of SQL Server.

Comparison of Standard and Datacenter editions of Windows Server 2019

https://docs.microsoft.com/en-us/windows-server/get-started/editions-comparison-windows-server-2019

Locks and Limits	Windows Server 2019 Standard	Windows Server 2019 Datacenter
Maximum number of 64-bit sockets	64	64
Maximum number of cores	unlimited	unlimited
Maximum RAM	24 TB	24 TB

SQL Server NUMA

SQL Server NUMA (Non-Uniform Memory Access)

資料庫連線暫時性問題(暫時性錯誤)與Retry機制

 資料庫連線暫時性問題(暫時性錯誤)與Retry機制

偶爾出現的問題(暫時性錯誤)，可能是網路短暫的通訊問題，建議在程式端先設計retry機制來自動解決，避免花費大量時間調查原因

1. 在程式端設計retry機制來自動解決此問題 (建議)

相較於在SQL query設計retry機制，在程式端設計retry比較好控制處理流程

 Retry寫法可以參考以下連結

針對 SQL Database 和 SQL 受控執行個體中的暫時性連接錯誤進行疑難排解

https://docs.microsoft.com/zh-tw/azure/azure-sql/database/troubleshoot-common-connectivity-issues

 

2. 在SQL query設計retry機制 (比較不建議)

開發改寫比較複雜，疑難排解也比較困難

TRY...CATCH (Transact-SQL)

https://docs.microsoft.com/zh-tw/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15

https://www.mssqltips.com/sqlservertip/3188/implementing-sql-server-transaction-retry-logic-for-failed-transactions/