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
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.
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
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.
Related
Overview: Compare cross-engine rules and behavior.
Last updated
Was this helpful?