Postgres vs SQL Server: Standard, Enterprise, or Neither

-- Inventory for a SQL Server licensing decision.
-- Prints the inputs the two published licensing models actually consume.

-- (a) Edition, build and HADR state of the instance being priced.
SELECT
    SERVERPROPERTY('Edition')            AS edition,
    SERVERPROPERTY('ProductVersion')     AS product_version,
    SERVERPROPERTY('ProductLevel')       AS product_level,
    SERVERPROPERTY('IsHadrEnabled')      AS hadr_enabled;

-- (b) Sockets and cores AS SEEN BY THE SYSTEM THIS INSTANCE RUNS ON.
--     REQUIRES SQL SERVER 2016 SP2 OR LATER: socket_count, cores_per_socket
--     and numa_node_count are all documented "Applies to: SQL Server 2016
--     (13.x) SP2 and later versions". On 2016 RTM/SP1 or earlier this part
--     fails with Invalid column name 'socket_count'. The fallback is
--     cpu_count with hyperthread_ratio from the same DMV -- "the ratio of the
--     number of logical or physical cores that are exposed by one physical
--     processor package" -- or a hardware/hypervisor inventory. Neither is a
--     substitute for a physical-core count; both are inputs to estimating it.
--     sys.dm_os_sys_info documents socket_count as "the number of processor
--     sockets available on the system" -- the system being the OS the instance
--     runs in. On bare metal that product IS the figure the physical-server
--     rule licenses, and the figure the four-licences-per-physical-processor
--     floor applies to. Inside a guest VM it is the GUEST's virtual topology:
--     the host's physical sockets and cores cannot be read from this DMV at
--     all and must come from the hypervisor or a hardware inventory. The
--     documentation says only "the system"; the guest reading is an inference
--     from that word, and it is the caveat that most often breaks a model.
--     Do NOT substitute the logical count below for either.
SELECT
    socket_count                         AS sockets_visible_to_this_instance,
    cores_per_socket,
    socket_count * cores_per_socket      AS cores_visible_to_this_instance,
-- (c) LOGICAL processors / schedulers. Relevant only under per-VM licensing,
--     where every hardware thread a virtual core maps to must also be
--     licensed -- and where Standard's 32-core limit means 32 hardware
--     threads. Per-VM licensing itself requires SA or a subscription.
    cpu_count                            AS logical_processors_per_vm_model,
    scheduler_count
FROM sys.dm_os_sys_info;

-- (d) Configured memory against Standard's 256 GB buffer-pool ceiling. The
--     documented limit is "Maximum memory for buffer pool per instance", and
--     max server memory bounds more than the buffer pool -- so this is the
--     knob a DBA checks, not literally the same object as the limit.
SELECT
    name,
    value_in_use                         AS configured_mb,
    256 * 1024                           AS standard_buffer_pool_ceiling_mb
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)');

-- (e) Memory-optimized data against Standard's 32 GB per-database cap.
--     UNLIKE (a)-(d), THIS PART IS NOT INSTANCE-WIDE.
--     sys.dm_db_xtp_memory_consumers reports only the database you are
--     connected to, and the cap is per DATABASE -- so run part (e) in every
--     user database, not once in master. A zero from master says nothing
--     about a user database on the same instance that is over the cap.
--     A database with no memory-optimized objects returns no rows here, so
--     the bare SUM would be NULL, not 0 -- ISNULL makes that read as zero
--     usage rather than as a broken query.
SELECT
    DB_NAME()                                     AS database_name,
    ISNULL(SUM(allocated_bytes) / 1024 / 1024, 0) AS in_memory_oltp_allocated_mb,
    32 * 1024                                     AS standard_per_database_cap_mb
FROM sys.dm_db_xtp_memory_consumers;

-- Sweep every database instead of trusting one connection. sp_MSforeachdb is
-- undocumented; a cursor over sys.databases where state = 0 is the supported
-- equivalent if you would rather not depend on it.
-- EXEC sp_MSforeachdb N'USE [?];
--     SELECT DB_NAME() AS database_name,
--            ISNULL(SUM(allocated_bytes) / 1024 / 1024, 0) AS in_memory_oltp_allocated_mb
--     FROM sys.dm_db_xtp_memory_consumers;';

#!/usr/bin/env bash
# SQL Server 2025 list-price model. NOT A QUOTE.
#
# The Per Core and server prices here are Microsoft's published open no-level
# estimated retail prices from the SQL Server 2025 pricing sheet, whose
# footnote on those rows reads: "Pricing represents open no-level (NL)
# estimated retail price. For your specific pricing, contact your Microsoft
# reseller." The two subscription figures come from that sheet's separate
# subscription and add-on pricing table. Negotiated EA/MPSA pricing is
# normally lower. This models the SHAPE of the decision, not your invoice.
set -euo pipefail

# --- Inputs -----------------------------------------------------------------
# CORES: which number belongs here depends on which model you are pricing.
#   Licensing a PHYSICAL server -> pass PHYSICAL cores. The four-licence floor
#     below applies to this figure.
#   Licensing a VIRTUAL MACHINE -> pass virtual cores, PLUS every hardware
#     thread each virtual core is mapped to. Per-VM licensing requires
#     Software Assurance or a subscription and has its own four-licence
#     minimum per virtual OSE. The two models are not interchangeable.
CORES=${CORES:-16}
SOCKETS=${SOCKETS:-2}

# USERS_DEVICES: EVERY user and device that accesses the instance, including
# indirect access through an application tier, and including devices nobody
# attends. Multiplexing or pooling does not reduce the count. This is not your
# visible seat count, and the break-even below is only valid against the
# honest total.
USERS_DEVICES=${USERS_DEVICES:-200}

# --- Published open no-level prices -----------------------------------------
ENT_PACK=15123      # Enterprise, 2-core pack, perpetual
STD_PACK=3945       # Standard - per core, 2-core pack, perpetual
# Subscription is a separate licensing model, not a per-year Per Core rate.
ENT_PACK_YR=5434    # Enterprise, 2-core pack, subscription
STD_PACK_YR=1418    # Standard, 2-core pack, subscription
STD_SERVER=989      # Standard - server (Server + CAL model)
STD_CAL=230         # Standard - CAL

# --- Core licence count -----------------------------------------------------
# Minimum four licences per physical processor, then rounded up to whole
# two-core packs.
floor=$(( SOCKETS * 4 ))
licences=$(( CORES > floor ? CORES : floor ))
packs=$(( (licences + 1) / 2 ))

std_percore=$(( packs * STD_PACK ))
ent_percore=$(( packs * ENT_PACK ))
std_year=$(( packs * STD_PACK_YR ))
ent_year=$(( packs * ENT_PACK_YR ))
std_servercal=$(( STD_SERVER + USERS_DEVICES * STD_CAL ))
breakeven=$(( (std_percore - STD_SERVER) / STD_CAL ))

printf '%d cores / %d sockets -> %d core licences -> %d two-core packs\n\n' \
  "$CORES" "$SOCKETS" "$licences" "$packs"
printf '  Standard   Per Core, perpetual : $%d\n'  "$std_percore"
printf '  Enterprise Per Core, perpetual : $%d\n'  "$ent_percore"
printf '  Standard   Subscription, 1 year: $%d\n'  "$std_year"
printf '  Enterprise Subscription, 1 year: $%d\n'  "$ent_year"
printf '  Standard   Server + CAL (%d)    : $%d\n' "$USERS_DEVICES" "$std_servercal"
printf '\n  Server + CAL is cheaper ONLY at or below %d users AND devices,\n' "$breakeven"
printf '  counted with indirect access and unattended devices included.\n'
printf '  Enterprise Server + CAL is not available for new agreements.\n'

# --- The high-availability line, never folded into the single-node total ----
# Microsoft: "licenses without SA or non-subscription licenses do not include
# disaster recovery or high availability rights, including use of fail-over
# clusters", and passive fail-over replicas "may be implemented in a variety
# of ways, including via availability groups and fail-over cluster instances."
# The SA benefit is capped: one passive fail-over replica for high
# availability per licensed OSE, and its licence count "may not exceed the
# number of licenses needed to cover your primary" OSE.
# READ THE SECOND LINE BELOW AS A MODEL, NOT A PRICE: Microsoft states that
# those rights are not INCLUDED; it nowhere instructs you to license the
# passive node in full. Two fully licensed OSEs simply need no passive
# fail-over benefit, which is the standard reading -- but it is a reading.
printf '\nTwo-node Standard failover cluster instance:\n'
printf '  with SA or a subscription : $%d  (ONE passive HA replica,\n' "$std_percore"
printf '                                     no larger than the primary)\n'
printf '  without                   : $%d  (passive node licensed in full)\n' "$(( std_percore * 2 ))"
printf '\n  NOTE: the SA or subscription cost that unlocks the first line is\n'
printf '  NOT in that figure. Microsoft publishes no SA price on the 2025\n'
printf '  sheet; the published alternative is the per-year subscription\n'
printf '  above. Passive-node coverage is conditional, not free.\n'

-- Postgres parity layer for a SQL Server migration.
--
-- THIS SCRIPT SPANS TWO CONNECTIONS, and the split is not cosmetic: pg_cron
-- "may only be installed to one database in a cluster" -- by convention the
-- maintenance database -- while the tables being audited live in the
-- application database. Run Part 1 in one and Part 2 in the other; neither
-- half completes from the other's connection.
--
-- PREREQUISITE NEITHER PART CAN SATISFY ITSELF: pgaudit and pg_cron must be
-- listed in shared_preload_libraries and the server RESTARTED before the
-- pg_cron and pgaudit CREATE EXTENSION statements below succeed (see the
-- postgresql.conf settings further down). A reload is not enough.

-- ===========================================================================
-- PART 1 -- connect to the MAINTENANCE database:  psql -d postgres
-- ===========================================================================

CREATE EXTENSION IF NOT EXISTS pg_cron;        -- one database per cluster

-- Cluster-wide, so they belong on exactly one connection: roles are
-- cluster-level objects, and ALTER SYSTEM writes postgresql.auto.conf for the
-- whole instance. pgaudit implements object-level auditing through the roles
-- system -- it logs statements touching objects the role named in
-- pgaudit.role has been granted rights on, so that grant list IS the evidence
-- scope you hand an assessor. The grants themselves are in Part 2, because
-- they have to be made where the tables are.
CREATE ROLE auditor NOLOGIN;
ALTER SYSTEM SET pgaudit.role = 'auditor';
ALTER SYSTEM SET pgaudit.log  = 'ddl, role, write';
SELECT pg_reload_conf();

-- The SQL Server Agent equivalent. schedule_in_database() is how a job reaches
-- a database other than the one pg_cron is installed in.
SELECT cron.schedule_in_database(
    job_name => 'nightly-vacuum-analyze',
    schedule => '0 2 * * *',
    command  => 'VACUUM (ANALYZE) public.ledger_entries',
    database => 'ledger'
);

-- The cron schema exists only in the database pg_cron was installed into, so
-- this readback belongs here and nowhere else.
SELECT jobid, jobname, schedule, database, active FROM cron.job ORDER BY jobid;

-- ===========================================================================
-- PART 2 -- reconnect to the APPLICATION database:  psql -d ledger
-- ===========================================================================
-- Extensions are per-database objects, so these two are created again here,
-- in the database holding the tables being audited and the vectors being
-- searched. Do NOT add CREATE EXTENSION pg_cron to this part -- it is already
-- installed in the one database per cluster that may have it.

CREATE EXTENSION IF NOT EXISTS pgaudit;
CREATE EXTENSION IF NOT EXISTS vector;         -- the pgvector project

-- The evidence scope, granted where the relations actually exist. Run these
-- against the maintenance database instead and both fail with "relation does
-- not exist".
GRANT SELECT, INSERT, UPDATE, DELETE ON public.ledger_entries  TO auditor;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.customer_tokens TO auditor;

# postgresql.conf -- EXCERPT. Only the settings this article argues about.
# ...

# Both pgaudit and pg_cron are preload libraries. Neither CREATE EXTENSION
# succeeds until this line is present and the server has been restarted --
# a reload is not enough.
shared_preload_libraries = 'pgaudit,pg_cron'

# ...

# The commit guarantee. ANY 1 of two listed standbys must acknowledge before
# the primary commits -- the analogue of requiring one synchronised secondary
# on an availability group.
synchronous_commit         = on
synchronous_standby_names  = 'ANY 1 (standby_a, standby_b)'

# ...

# WAL retention. wal_keep_size is the floor kept for any standby;
# max_slot_wal_keep_size is the backstop that stops a dead replication slot
# pinning WAL until the primary's volume fills.
wal_keep_size              = 4GB
max_slot_wal_keep_size     = 64GB

# ...

# Object-level audit logging. pgaudit.role names the role whose grants define
# the evidence scope; the log classes are what an assessor reads.
pgaudit.log                = 'ddl, role, write'
pgaudit.log_relation       = on
pgaudit.log_parameter      = off   # keep bound values out of the audit trail

# ...

-- Basic availability group, SQL Server Standard, Linux, cluster type External.
--
-- THREE instances, not two. The third holds the AG configuration in master and
-- none of the user databases. Per CREATE AVAILABILITY GROUP's
-- CONFIGURATION_ONLY option, that replica "Can be hosted on any edition of SQL
-- Server, including Express Edition" -- an instance and an endpoint to
-- operate, not a licence to buy.
--
-- PREREQUISITES THIS STATEMENT CANNOT SATISFY ITSELF. Pasted on its own it
-- errors instead of producing the group above. Per Microsoft's "Configure an
-- availability group for high availability on Linux":
--   1. Always On enabled on EACH of the three instances, then a restart:
--        sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
--        sudo systemctl restart mssql-server
--   2. A certificate exchanged between the nodes and a DATABASE_MIRRORING
--      endpoint on every instance, on the port used below:
--        CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_PORT = 5022)
--            FOR DATABASE_MIRRORING (ROLE = ALL,
--                AUTHENTICATION = CERTIFICATE dbm_certificate,
--                ENCRYPTION = REQUIRED ALGORITHM AES);
--        ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
--      On the node hosting the configuration-only replica the role is WITNESS
--      -- on Express Edition it is the only valid value.
--   3. [ledger] in FULL recovery with a valid log backup, because this
--      statement names it in FOR DATABASE:
--        ALTER DATABASE [ledger] SET RECOVERY FULL;
--        BACKUP DATABASE [ledger] TO DISK = N'/var/opt/mssql/data/ledger.bak';
--      A DIVERGENCE TO KNOW ABOUT: FOR DATABASE is valid grammar and the shape
--      the Windows examples use, but Microsoft's Linux examples all create the
--      group with FOR REPLICA ON and no database, then ALTER AVAILABILITY
--      GROUP [ag_ledger] ADD DATABASE [ledger] once the secondaries have
--      joined. Naming it here is what pulls this prerequisite forward.
--
-- AND AFTERWARDS: SEEDING_MODE = AUTOMATIC seeds nothing until the group is
-- granted the right to create databases -- on the primary immediately after
-- CREATE, and on each secondary once it has joined (a configuration-only
-- replica needs the join alone):
--   ALTER AVAILABILITY GROUP [ag_ledger] GRANT CREATE ANY DATABASE;      -- primary
--   ALTER AVAILABILITY GROUP [ag_ledger] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
--   ALTER AVAILABILITY GROUP [ag_ledger] GRANT CREATE ANY DATABASE;      -- secondary
-- The Pacemaker resource is configured AFTER the group exists, and
-- FAILOVER_MODE = EXTERNAL does nothing until it is. That ordering -- install,
-- configure the AG, then configure the cluster resource manager -- is the
-- roadmap on a DIFFERENT page, "Configure a Pacemaker cluster for SQL Server
-- availability groups", and so is the fencing warning: that page's examples
-- "don't use fencing agents. They are for testing and validation only."
-- Production needs one.

CREATE AVAILABILITY GROUP [ag_ledger]
    WITH (
        BASIC,
        CLUSTER_TYPE = EXTERNAL
    )
    FOR DATABASE [ledger]
    REPLICA ON
        N'sqlnode-a' WITH (
            ENDPOINT_URL      = N'tcp://sqlnode-a.db.example.com:5022',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            -- FAILOVER_MODE = EXTERNAL is documented in CREATE AVAILABILITY
            -- GROUP's add_replica_spec; ALTER AVAILABILITY GROUP's reference
            -- grammar lists FAILOVER_MODE = { AUTOMATIC | MANUAL } only. Set
            -- it here, at CREATE time -- do not rely on an ALTER to set it.
            FAILOVER_MODE     = EXTERNAL,
            SEEDING_MODE      = AUTOMATIC
        ),
        N'sqlnode-b' WITH (
            ENDPOINT_URL      = N'tcp://sqlnode-b.db.example.com:5022',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            FAILOVER_MODE     = EXTERNAL,
            SEEDING_MODE      = AUTOMATIC
        ),
        -- The arbitrating replica. No FAILOVER_MODE and no SEEDING_MODE
        -- clause: those options "aren't supported when availability_mode is
        -- set to configuration_only for a replica". Its mirroring endpoint
        -- must be of type WITNESS, and the availability mode can't be altered later.
        N'sqlnode-c' WITH (
            ENDPOINT_URL      = N'tcp://sqlnode-c.db.example.com:5022',
            AVAILABILITY_MODE = CONFIGURATION_ONLY
        );
GO

-- The commit guarantee. Postgres equivalent: synchronous_standby_names with
-- an ANY quorum count (see the next script).
--
-- CAVEAT, and it is the whole point of this comment: the value you set here is
-- not the value that persists. "For cluster type EXTERNAL the setting is
-- changed when the availability group is added to a cluster resource", and on
-- Linux "the value is configured automatically by the cluster on the AG
-- resource itself." Pacemaker owns this number. Read it back; do not assume it.
ALTER AVAILABILITY GROUP [ag_ledger]
    SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
GO

-- MODIFY REPLICA carries no FAILOVER_MODE clause here on purpose: these
-- replicas are EXTERNAL, and that value appears only in CREATE's documented
-- grammar. Leave the failover mode where CREATE put it.
-- Postgres equivalent of AVAILABILITY_MODE: synchronous_commit.
ALTER AVAILABILITY GROUP [ag_ledger]
    MODIFY REPLICA ON N'sqlnode-b'
    WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
GO

SELECT
    ag.name                                        AS availability_group,
    ag.basic_features,
    ag.cluster_type_desc,
    ag.required_synchronized_secondaries_to_commit AS required_sync_secondaries,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ar.secondary_role_allow_connections_desc       AS secondary_readable
FROM sys.availability_groups  AS ag
JOIN sys.availability_replicas AS ar
     ON ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name;
GO

-- Postgres half of the availability-group translation. Run as superuser.

-- Maps to REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1: commits wait until
-- the WAL is replicated to at least ONE of the two listed standbys. On a
-- SELF-MANAGED cluster the value lands in postgresql.auto.conf, which you own
-- and can diff -- and which overrides the same setting in the postgresql.conf
-- file shown earlier, so this statement is the one that wins. UNDER AN OPERATOR IT
-- DOES NOT: CloudNativePG's replication documentation states that "direct
-- configuration of the synchronous_standby_names option is not permitted",
-- and that the operator populates the option with the names of local pods,
-- customisable through the .spec.postgresql.synchronous stanza.
--   https://cloudnative-pg.io/docs/1.30/replication/
-- A separate default, documented on its PostgreSQL-configuration page and not
-- tied to this setting, disables ALTER SYSTEM on new clusters altogether:
-- every statement below needs .spec.postgresql.enableAlterSystem = true.
--   https://cloudnative-pg.io/docs/1.30/postgresql_conf/
-- Same question as Pacemaker owning the SQL Server value: find out which
-- layer writes the number, then read the number back from the engine.
ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (standby_a, standby_b)';

-- Maps to AVAILABILITY_MODE = SYNCHRONOUS_COMMIT.
ALTER SYSTEM SET synchronous_commit = 'on';

-- WAL retention for the standbys' replication slots. max_slot_wal_keep_size is
-- the backstop: a slot is allowed to pin this much WAL and no more.
ALTER SYSTEM SET wal_keep_size          = '4GB';
ALTER SYSTEM SET max_slot_wal_keep_size = '64GB';

SELECT pg_reload_conf();

-- Read the resulting guarantee back: sync_state tells you which standbys are
-- actually counted toward the quorum right now, not which ones are listed.
SELECT
    application_name,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication
ORDER BY sync_state, application_name;

-- And the slots, because an inactive slot is the failure mode, not the standby.
SELECT slot_name, active, wal_status, safe_wal_size
FROM pg_replication_slots
ORDER BY slot_name;