> 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/build/productize/connect-engine/sql-server.md).

# SQL Server

Configure Microsoft SQL Server as the engine for your Data Product.

Microsoft SQL Server suits existing SQL Server estates, Windows-centric environments, or projects that need a governed RDBMS with full T-SQL surface area. Use the `mssql` adapter. This page covers the connection. For grants, materialization internals, indexing, and the full reference, see the [SQL Server engine manual](https://v2.dataos.info/references/engine-guide/sql-server) in References.

## Before you start

* A SQL Server instance (2019–2022) or an Azure SQL Database, with `connection.database` already created: Vulcan doesn't create the database. SQL Server has no `CREATE SCHEMA IF NOT EXISTS`, so the adapter creates referenced schemas through guarded DDL when the login has the privilege.
* A login for Vulcan, plus a separate read-only login for metadata scanning.
* DataOS access: a `roles:id:data-dev`-equivalent role tag, `depot:rw:<mssql-depot-name>` (or `depot:r` for read-only), **Can Use** on Compute/Depots/Secrets, and a git-sync Secret.

## Permissions

Three roles, each with a distinct scope: an admin role (sysadmin/DBA) that creates logins and grants privileges, the Vulcan service login that runs models, and a read-only consumer login.

| Grant                                                 | Required for                          |
| ----------------------------------------------------- | ------------------------------------- |
| `db_ddladmin`, `db_datareader`, `db_datawriter` roles | Running models and managing tables    |
| `CREATE SCHEMA`                                       | Creating schemas referenced by models |
| `db_datareader` (consumer login)                      | Read-only access for BI and endpoints |

Run minimum grants as a sysadmin:

```sql
CREATE LOGIN vulcan_svc WITH PASSWORD = '<strong-password>';
USE <database>;
CREATE USER vulcan_svc FOR LOGIN vulcan_svc;
ALTER ROLE db_ddladmin ADD MEMBER vulcan_svc;
ALTER ROLE db_datareader ADD MEMBER vulcan_svc;
ALTER ROLE db_datawriter ADD MEMBER vulcan_svc;
GRANT CREATE SCHEMA TO vulcan_svc;
```

Grant the consumer login `db_datareader` the same way. Verify with a join across `sys.database_role_members` and `sys.database_principals`.

## Connection options

Connect directly with credentials, or through a [DataOS Depot](#using-a-depot) (recommended for production).

| Option             | Required | Description                                                                               |
| ------------------ | :------: | ----------------------------------------------------------------------------------------- |
| `type`             |    Yes   | Must be `mssql`                                                                           |
| `host`             |    Yes   | SQL Server instance hostname                                                              |
| `database`         |    No    | Target database. Must already exist                                                       |
| `user`, `password` |    No    | Username/password authentication                                                          |
| `port`             |    No    | Defaults to `1433`                                                                        |
| `driver`           |    No    | `pymssql` (default) or `pyodbc`                                                           |
| `driver_name`      |    No    | ODBC driver name, only used with `driver: pyodbc`                                         |
| `odbc_properties`  |    No    | ODBC connection properties, for example `authentication: ActiveDirectoryServicePrincipal` |

### Authentication methods

* **Username and password** (default): set `user` and `password` with `driver: pymssql`.
* **Microsoft Entra ID / Azure AD**: install the `mssql-odbc` extra, set `driver: pyodbc`, `driver_name`, and `odbc_properties`.
* Always inject the password from an environment variable: `password: "{{ env_var('MSSQL_PASSWORD') }}"`.

## Example

```yaml
gateways:
  local:
    connection:
      type: mssql
      host: localhost
      user: sa
      password: "{{ env_var('MSSQL_PASSWORD') }}"
      port: 1433
      database: warehouse
```

### Using a Depot

```yaml
gateways:
  default:
    connection:
      type: depot
      address: dataos://<mssql-depot-name>?purpose=rw
```

## 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`. `FULL` compiles to `SELECT * INTO` on first run, then `TRUNCATE TABLE` + `INSERT`; `INCREMENTAL_BY_UNIQUE_KEY` uses a native `MERGE INTO`. Table cloning and `MANAGED` models aren't supported in SQL Server. For how each kind materializes, see the [SQL Server engine manual](https://v2.dataos.info/references/engine-guide/sql-server).

> Azure SQL Database reuses this same adapter (`azuresql` connection-config type): the only functional difference is that Azure SQL is single-catalog, while on-premises SQL Server supports cross-database `USE`. SQL Server also defaults a bare `VARCHAR` (no length) to `VARCHAR(1)`, silently truncating data. Always declare an explicit length.

By default `MERGE` updates all non-key columns on every matching row; set `mssql_merge_exists = true` in `physical_properties` to skip identical updates via an `EXISTS ... EXCEPT` comparison — but not for tables with `GEOMETRY`, `XML`, `TEXT`, `NTEXT`, `IMAGE`, or most user-defined types, which `EXCEPT` doesn't support. SQL Server has no `CREATE INDEX IF NOT EXISTS`; guard index creation with an `IF NOT EXISTS` check against `sys.indexes`. Wrap lifecycle DDL in `pre_statements` / `post_statements` guarded with `@IF(@runtime_stage = 'evaluating', ...)`, or `vulcan plan` executes it.

## Limits and performance

A 2–4 vCPU, 4–8 GiB instance usually supports development. Start `concurrent_tasks` at 2 for daily incrementals, and for first backfills raise `tempdb` size and disk headroom — `SELECT * INTO` and bulk `MERGE` both spill through `tempdb`. Index the incremental `time_column` / `unique_key`, and pair API replica growth with a connection pooler since each replica consumes its own SQL Server connections.

If concurrency degrades, fix in order: remove cross-join-shaped queries, add indexes on hot filter/join columns, add a connection pooler, size up the instance, then investigate `tempdb` contention on bulk loads and MERGE.

## Troubleshooting

| Symptom                                  | Likely cause                                  | Fix                                                   |
| ---------------------------------------- | --------------------------------------------- | ----------------------------------------------------- |
| String or binary data would be truncated | Bare `VARCHAR` defaulted to length 1          | Declare explicit column lengths.                      |
| `relation raw.raw_orders does not exist` | Missing table, grant, or external declaration | Grant `SELECT`; add to `inputs.yaml`.                 |
| `vulcan plan` runs DDL                   | Lifecycle DDL not guarded                     | Wrap it in `@IF(@runtime_stage = 'evaluating', ...)`. |
| `mssql_merge_exists` throws on `EXCEPT`  | Unsupported column type                       | Disable the optimization for that table.              |
| Endpoint OOM on large result             | API memory too low                            | Raise API memory and limit result size.               |
| `pymssql` fails to build                 | Required local headers absent                 | Use the supplied wheel, or set `driver: pyodbc`.      |

For the full failure-mode table, health-check queries, and recovery procedures, see [Troubleshooting](https://v2.dataos.info/references/engine-guide/sql-server/troubleshooting) in the SQL Server engine manual.

Once `vulcan info` succeeds, continue to [Assets](https://v2.dataos.info/build/productize/assets.md).


---

# 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/build/productize/connect-engine/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.
