# MS SQL Server

> Source: https://docs.clonepartner.com/connectors/mssql/

<!-- sources: src/connectors/mssql.ts, src/connectors/readonly-sql.ts, references/envoy/connectors.md -->

# MS SQL Server

The `mssql` connector queries and writes Microsoft SQL Server (including Azure SQL) tables over its own connection pool, with a separate read-only pool for dashboard queries.

## When to use it

Use `mssql` as a relational source or destination on SQL Server, and as a mapping database or cursor store for sync tasks. For PostgreSQL or MySQL, use the [PostgreSQL](/connectors/postgres/) or [MySQL](/connectors/mysql/) connectors.

## Configuration

| Key | Type | Required | Default | Description |
|---|---|---|---|---|
| `server` | string | Yes | — | Server hostname or IP |
| `database` | string | Yes | — | Database name |
| `user` | string | Yes | — | Login username |
| `password` | string | Yes | — | Login password (stored as a secret) |
| `port` | number | No | 1433 | Server port |
| `encrypt` | boolean | No | `true` | Encrypt connection (required for Azure SQL) |
| `trust_server_certificate` | boolean | No | `false` | Trust self-signed certificates |
| `pool_size` | number | No | 4 | Number of parallel connections |
| `readonly_pool_size` | number | No | 2 | Connections in the read-only pool (`ApplicationIntent ReadOnly`) |

## Methods

| Method | Arguments | Returns | Behavior |
|---|---|---|---|
| `find` | `table` (required), `filter`, `select`, `limit`, `offset`, `order_by` | array | Streams rows with backpressure: the driver request pauses when the internal queue reaches 1000 rows and resumes as downstream consumes |
| `find_one` | `table` (required), `filter`, `select` | object | Returns the first matching row |
| `insert_one` | `table` (required), `data` (required) | object | Inserts a single record |
| `upsert` | `table` (required), `data` (required), `conflict_columns` (required), `update_columns` | object | Inserts or updates using a `MERGE` statement keyed on `conflict_columns` |
| `delete` | `table` (required), `filter` | object | Deletes rows matching the filter |
| `execute` | `statements` (required) | object | Executes a raw SQL statement |
| `select` | `sql` (required), `params` | array | Runs a read-only `SELECT` on the read-only pool (`SELECT` or `WITH` only; `ApplicationIntent ReadOnly`) |

`filter` uses the shared JSON filter syntax with `gt`, `gte`, `lt`, `lte`, `ne`, `in`, `nin`, `like`, `ilike`, and `exists`, plus `and`/`or` nesting. `ilike` compiles to `LOWER(col) LIKE LOWER(pattern)`; the `regex` operator is not supported on SQL Server.

## Example

```yaml
name: export_crm_accounts
connectors:
  source:
    type: mssql
steps:
  - name: read_accounts
    type: call_method
    config: |
      {
        "connector": "source",
        "method": "find",
        "args": { "table": "accounts", "filter": { "is_active": true } }
      }
```

The matching `connectors.yaml` entry for CLI runs:

```yaml
source:
  type: mssql
  config:
    server: sql.example.internal
    database: crm
    user: envoy_reader
    password: ${MSSQL_PASSWORD}
```

## Behavior notes

- `select` rejects anything that is not a `SELECT` or `WITH ... SELECT` statement and runs on connections opened with `ApplicationIntent ReadOnly`, so dashboard queries can be routed to a readable secondary.
- The connector implements the mapping-DB interface (a `resource_mapping` table created on first use) and the cursor store, so it can serve as `lookup_connector` or `cursor_store` for sync tasks. See [Idempotent sync and the mapping DB](/building/idempotent-sync/).
- Connection pooling is managed by the connector itself (`pool_size` parallel `tedious` connections); size it to the task's step concurrency.

## Related

- [Connectors overview](/connectors/) — all compiled connector types
- [PostgreSQL](/connectors/postgres/) — the same method set for PostgreSQL
- [Idempotent sync and the mapping DB](/building/idempotent-sync/) — how the mapping table is used