> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/engine-guide/sql-server.md).

# 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`.

{% hint style="info" %}
`MANAGED` models and table cloning are not supported.
{% endhint %}

## Authorize

Access setup is required before configuration.

Use [Permissions and grants](https://v2.dataos.info/references/engine-guide/sql-server/permissions-and-grants.md) 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](https://v2.dataos.info/references/engine-guide/sql-server/connect-to-sql-server.md) for gateway configuration, local Docker development, and production deployment.

## Build

Choose model kinds after validating the connection.

Use [Materialization behavior](https://v2.dataos.info/references/engine-guide/sql-server/materialization-behavior.md) 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:

```sql
CREATE LOGIN vulcan_scanner WITH PASSWORD = '<strong-password>';
USE <database>;
CREATE USER vulcan_scanner FOR LOGIN vulcan_scanner;
ALTER ROLE db_datareader ADD MEMBER vulcan_scanner;
```

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](https://v2.dataos.info/references/engine-guide/sql-server/limits-and-performance.md) to size compute, set concurrency, schedule workloads, and tune bottlenecks.

### Troubleshooting

Use [Troubleshooting](https://v2.dataos.info/references/engine-guide/sql-server/troubleshooting.md) 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.

{% file src="/files/dYuDCS0dcncZ6cNxEJ60" %}

## Related

* [Overview](https://v2.dataos.info/references/engine-guide/engine-guide.md): Compare cross-engine rules and behavior.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/references/engine-guide/sql-server.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
