SQL Server in SaaS — difficulties and pitfalls

An incident without an identifiable change is almost always due to data growth (volume crossing a threshold: memory, size limit, different plan) or an expiration (token, password, certificate, application secret).

· Symptom: DirectQuery visuals freeze for a few minutes at night. Cause: TRUNCATE/DROP/ALTER or SWITCH takes a Sch-M lock, which all read queries wait on. Remedy: load into a staging table then do a short atomic SWITCH or rename; maintenance window; or disable DirectQuery during consolidation (Import model, or read replica).

· Symptom: CPU at 100% at 9 AM, drops around 10 AM. Cause: all users open the same DirectQuery page; no cache. Remedy: Import aggregations, fewer visuals per page, “reduce the number of queries” option (slicers applied via button), Premium/Fabric capacity query cache.

· Symptom: “the result set exceeds the row limit” (on the order of 1 million rows on shared capacity; adjustable in Premium depending on settings). Cause: unaggregated table/matrix visual. Remedy: aggregate, filter upstream, paginate with table visual limited detail.

· Symptom: slow consolidation while CPU is at 30%. Cause: transaction log write throughput is capped by the service tier (wait LOG_RATE_GOVERNOR). Remedy: reduce logged volume (batch loads, columnstore, INSERT ... SELECT into a new table), reduce indexes to maintain, or temporarily scale up.

⚠️ Pitfall 5 — A single huge UPDATE · Symptom: giant transaction, full log, very long rollbacks. Cause: updating tens of millions of rows in one go. Remedy: process in batches (50,000 to 500,000 rows) with checkpoints; prefer INSERT into a new table + SWITCH.

· Symptom: inconsistent performance with no code change. Cause: another database in the pool consumes the budget. Remedy: min/max limits per database, separate critical databases, or move the database out of the pool.

· Symptom: “optimization gained 40%” then disappears in acceptance testing. Cause: environment variability, warm cache. Remedy: protocol above; success criterion expressed in CPU and reads.

· Symptom: first morning refresh fails or takes longer. Cause: database resume (on the order of a minute); connection timeout exceeded. Remedy: disable pause for production (or long pause delay), “wake” the database with a lightweight query before refresh, retry tolerance in the pipeline.

· Symptom: connection drops for a few seconds. Cause: a service-tier change flips connections at the end of the operation. Remedy: outside peak hours; reconnect strategy with retry in pipelines; avoid changes during refresh.

· Symptom: Desktop OK, service KO (“client with IP address … is not allowed”). Cause: the user’s IP is allowed, not the service. Fix: allow “Azure services” (broad) or targeted ranges, or private endpoint + virtual network gateway; check the service tag PowerBI.

· Symptom: the server name resolves to a public IP from the corporate network, which is denied. Cause: private DNS zone privatelink.database.windows.net not linked to the network. Fix: link the zone to the VNet, or use conditional forwarders; test with nslookup from the gateway machine.

· Symptom: all refreshes for a domain fail at the same time. Cause: gateway on a single stopped VM, update, certificate issue. Fix: gateway cluster (≥ 2 nodes), monitoring, scheduled updates; or managed virtual network gateway.

· Symptom: Timeout expired on connection or execution. Cause: paused database, worker saturation, query too long. Fix: increase command timeout in the source if legitimate, but above all fix the root cause; distinguish connection timeout (network/pause) from command timeout (query).

· Symptom: gateway installed but “offline.” Cause: outbound ports (HTTPS, Service Bus) blocked by the corporate firewall. Fix: open the documented destinations (current list to check in the official gateway documentation).

· Symptom: refresh suddenly starts failing overnight (“invalid credentials”). Cause: SQL password renewed, service account disabled, Entra app secret expired. Fix: service account without surprise expiration or, better, service principal/managed identity with planned rotation and D-30 alerting.

· Symptom: Login failed for user '<token-identified principal>'. Cause: the database did not create the external user (CREATE USER [x] FROM EXTERNAL PROVIDER), or the Entra admin is not set on the server, or the token does not have the correct audience. Fix: set the Entra admin; create the user in every database; grant roles; verify the tenant.

· Symptom: all users see the same data, SQL RLS is not applied. Cause: the model source uses the owner’s identity instead of “Entra ID Single Sign-On for DirectQuery queries.” Fix: enable the option in source settings; test with two accounts with different permissions.

· Symptom: API calls (refresh, deployment) return 401/403. Cause: service principals were not authorized at the tenant level (admin setting) or do not have a role on the workspace. Fix: dedicated authorized security group in the admin portal, Member/Contributor role on the workspace.

· Symptom: refresh fails after multi-factor authentication is enabled. Cause: a “technical” user account became subject to conditional access. Fix: no more user accounts for automations; service identities with appropriate conditional access policies.

· Symptom: a datamart setup kit “succeeds” but did not create some objects. Cause: insufficient permissions (not database owner) or errors hidden by a tool that returns 0. Fix: grant db_owner to the deployment account on target databases, run the kit with stop-on-first-error and check logs, not just return code.

· Symptom: refreshes pending then failing, insufficient memory. Cause: N models scheduled at the same time on one capacity. Fix: stagger, limit parallelization, refresh by partitions; monitor the capacity metrics app.

· Symptom: users want “real time” while 8 refreshes/day is the maximum. Cause: poorly framed requirement. Fix: clarify the actually useful freshness; hybrid table, or targeted DirectQuery, or Direct Lake via mirroring.

· Symptom: same duration as a full refresh. Cause: Power Query step that breaks query folding (non-translatable functions, step order), or RangeStart/RangeEnd parameters applied incorrectly. Fix: check native query; do date filtering as the first step; use a SQL view.

· Symptom: incorrect historical numbers despite a fix in the database. Cause: incremental policy does not refresh older periods. Fix: targeted partition refresh (enhanced XMLA/API) or “change detection” on a last-modified column; documented historical recovery procedure.

· Symptom: memory error during refresh, even though the model “fits” for reading. Cause: temporary doubling of memory. Fix: incremental, remove high-cardinality columns (identifiers, second-level timestamps), split date/time, aggregations, or larger capacity.

· Symptom: coworkers cannot see the report. Cause: without dedicated capacity (or depending on Fabric/Premium license), the reader must have a Pro/Premium Per User license; with sufficient capacity, readers may be “free” (depending on the reference). Fix: map readers/authors and choose the licensing model; see Cost, Licensing, and Capacity.

· Symptom: a view calling a scalar UDF is significantly slower in DirectQuery (sometimes by an order of magnitude). Cause: compatibility level 130: no inlining, row-by-row execution, and non-parallel plan. Fix: raise the level (after regression testing, Query Store as safety net), or unfold the function in the query (joins or CASE), or convert it to an inline table-valued function.

· Symptom: some queries regress after moving to a higher level. Cause: new cardinality estimator. Fix: enable Query Store in read/write mode first; move to target level; compare; force previous plan for rare regressions; automatic tuning FORCE_LAST_GOOD_PLAN.

· Symptom: the report loads the entire table before filtering. Cause: M functions with no SQL equivalent (Text.Proper, some conversions) break folding. Fix: transform in SQL (view), or keep non-foldable transformations after foldable steps.

In a kit-based deployment environment, keep one compatibility level version per environment in code (ALTER DATABASE ... SET COMPATIBILITY_LEVEL) and test low before high.

· Symptom: “Opération” instead of “Opération.” Cause: a UTF-8 file read as cp1252 (or vice versa) during installation or import. Fix: standardize script encoding (UTF-8 with BOM), explicitly specify encoding in tools, test with an accented “sentinel” label during acceptance testing.

· Symptom: “Café” and “Cafe” grouped together, or duplicate keys rejected. Cause: AI collation. Fix: accent-sensitive collation for business labels; differentiate behavior by column (COLLATE) rather than by database.

· Symptom: label order differs between an SQL query and the visual. Cause: model sorting follows its own culture/collation, independent from the database. Fix: an explicit sort column (sort number) for labels that matter (months, statuses), rather than relying on alphabetical order.

· Symptom: 11 p.m. visits appear the next day, or “today” is empty until 2 a.m. Cause: UTC vs local time; TODAY() in UTC. Fix: store UTC and local time (datetimeoffset or two columns); local date/time dimension calculated in the datamart; AT TIME ZONE 'Romance Standard Time' for conversion (handles daylight saving time); offset parameter in the model for “today” measures.

· Symptom: “number of distinct products” is 120 for a director and 45 for a salesperson, even though the catalog is identical. Cause: RLS filters a dimension; the filter propagates to facts then, via a bidirectional filter or securityFilteringBehavior, flows back to another dimension. Fix: limit propagation (single direction), calculate the measure on the dimension without going through facts, or document that the measure is scope-dependent; test each role.

A test database kept on General Purpose 8 vCore while used two days per month: switching to serverless (min 0.5 vCore, pause) brings compute cost down to a fraction (order of magnitude: up to 90% reduction possible, depending on activity durations), at the cost of resume time.

· Symptom: “Column X in table Y cannot be found” during refresh. Cause: renaming on the datamart side without adapting the model. Fix: exposure views with a stable contract; for any breaking change, add the new column, migrate the model, then remove the old one.

· Symptom: customer reports lose their visuals. Cause: the measure's internal identifier (lineageTag) changed. Fix: modify in place; if deletion is unavoidable, publish a breaking-change note.

· Symptom: everything works in testing, not in production. Cause: different data volume, permissions, network, or service level. Fix: acceptance testing environment with similar data volume, anonymized data; document differences; see Acceptance testing and business validation.

· Symptom: real customer data in development. Cause: production copy without anonymization. Fix: dynamic masking, anonymized datasets, restricted access; see Security, Privacy, Compliance.

· Symptom: a human error destroys a table, no one knows how to restore it. Cause: trust that “the cloud backs up everything.” Fix: point-in-time restore (PITR) tested once per quarter, documented retention, written procedure; reminder: backup does not replace deployment discipline.

AI excels at explaining an error message and suggesting a list of causes; it is less reliable at prioritizing the real cause in your context, and it may invent numeric limits. Safeguard: any limit or pricing value must be verified in the official documentation. See 08-sqlserver-saas-ia.md.