Skip to content
For the complete documentation index, see llms.txt. This page is also available as Markdown.

SQL Server

The full technical reference for building and running a Data Product on Microsoft SQL Server, covering connection, grants, materialization, local Docker setup, and troubleshooting.

Microsoft SQL Server is an on-premises or VM-hosted relational database engine that works with Vulcan. Use it for existing SQL Server estates, Windows-centric environments, or when you need a governed RDBMS with full T-SQL surface area. This page is the full manual: connection and authentication, grants, how each model kind materializes, the local Docker setup, indexing and MERGE tuning, and the failure modes you hit in real projects.

When to use

Choose SQL Server for existing SQL Server estates and Windows-centric environments.

It offers on-premises control over placement and licensing. Performance depends on instance size, IOPS, indexes, and volume. Use guarded raw DDL for native indexes, constraints, computed columns, triggers, and partitioned views.

Engine characteristics

Property
Value

Engine adapter type

mssql

Model dialect

tsql

Query execution

SQL Server instance; no separate warehouse to start, stop, or resize

Tested Vulcan image

tmdcio/vulcan-mssql:0.228.1.30 against SQL Server 2019 to 2022 on Draco 1.38

Azure SQL Database

Uses the same adapter with a different connection-config subclass

Once you choose SQL Server as your Data Product engine, use this guide to explore Authorize, Connect, Build, Serve, Operate, and Troubleshoot: covering access, connectivity, model development, data serving, performance, and issue resolution.

Depending on where you are starting from, you may enter this path at a different point.

Supported model kinds

FULL, SEED, VIEW, INCREMENTAL_BY_TIME_RANGE, INCREMENTAL_BY_UNIQUE_KEY, INCREMENTAL_BY_PARTITION, INCREMENTAL_UNMANAGED, SCD_TYPE_2, SCD_TYPE_2_BY_TIME, SCD_TYPE_2_BY_COLUMN, and EMBEDDED.

MANAGED models and table cloning are not supported.

Authorize

Access setup is required before configuration.

Use Permissions and grants to establish least-privilege SQL Server roles, DataOS permissions, and scoped Depot access.

Connect

This is the first hands-on stage after access provisioning.

Use Connect to SQL Server for gateway configuration, local Docker development, and production deployment.

Build

Choose model kinds after validating the connection.

Use Materialization behavior for T-SQL mappings, MERGE behavior, indexing patterns, lifecycle DDL, and model guidance.

Serve

Endpoints

Every deployed Data Product gets REST, GraphQL, and MySQL-wire endpoints. Each endpoint pushes down to the same SQL Server gateway.

Large result sets buffer before streaming. Size api.resources.limits.memory for expected payloads. Start with 3 GiB for high-row responses.

Metadata scanning and catalog

DataOS scans INFORMATION_SCHEMA for databases, schemas, tables, columns, and grants. It scans sys.* views for indexes, foreign keys, and view or procedure definitions.

SQL Server does not expose warehouse-style query history by default. Query Store requires separate enablement and is not scanned. Lineage therefore comes from parsed definitions and the Vulcan model graph.

Use a read-only scanner login separate from the Vulcan service login:

Scan every 6 to 12 hours. If lineage is missing after vulcan apply, verify inputs.yaml, then wait for the next scan.

Engine-native feature support

Feature
How
Notes

Database

connection.database

Must exist before apply.

Schema

Referenced by model names

Created when the login has privilege.

Regular / incremental table

FULL and incremental models

View

kind VIEW

CREATE OR ALTER VIEW.

SCD history

SCD_TYPE_2_BY_TIME / SCD_TYPE_2_BY_COLUMN

Indexes

Guarded CREATE INDEX in post_statements

Native support.

Triggers / stored procedures

Guarded T-SQL DDL

Opaque to SQL lineage.

Computed columns

Guarded ALTER TABLE ... ADD ... AS (...)

Row-level security

CREATE SECURITY POLICY

Requires DBA-level setup.

Materialized view

Not supported

SUPPORTS_MATERIALIZED_VIEWS is off.

Table cloning

Not supported

MANAGED models

Not supported

Boundaries

MANAGED models, materialized views, tuple IN clauses, table cloning, elastic scale-out, auto-suspend, and cross-database incremental strategies on Azure SQL are unsupported.

Server lifecycle, instance creation, licensing, replication, and Always On configuration remain DBA responsibilities.

Operate safely

Limits & performance

Use Limits & performance to size compute, set concurrency, schedule workloads, and tune bottlenecks.

Troubleshooting

Use Troubleshooting for failure modes, health checks, and recovery procedures.

Before production

  • Confirm least-privilege grants.

  • Validate the gateway.

  • Set concurrency and connection limits.

  • Test recovery procedures.

Full implementation example

Use this complete Data Product as a production-shaped starting point.

archive · 65KB
Open
  • Overview: Compare cross-engine rules and behavior.

Last updated

Was this helpful?