# Microsoft SQL Server

> Send conversation messages stored in Microsoft SQL Server to Dimension Labs with the universal API.

If your conversations already land in a SQL Server or Azure SQL Database table, you can send them to Dimension Labs from there. A scheduled job on your side queries the rows that are new since its last run and posts each message to the [universal API](/api/tracker).

## What you need

- A SQL Server login that can create logins or users, and grant access to the table that holds your conversations.
- The server name and database, and network access to it from wherever the job runs. Azure SQL Database blocks all access to its public endpoint until you add a firewall rule for the job's IP address ([Azure SQL Database IP firewall rules](https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure)).
- A Dimension Labs API key for a new **Universal API** source. See [Your API key](/api/tracker#your-api-key).

## 1. Get access

Create a SQL login and a database user for the job, and let it read only the conversations table. On SQL Server, create the login in `master` and the user in your database ([CREATE LOGIN](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql), [CREATE USER](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql)):

```sql
CREATE LOGIN dl_sync WITH PASSWORD = 'choose-a-long-password';
-- then, in the database that holds your conversations:
CREATE USER dl_sync FOR LOGIN dl_sync;
GRANT SELECT ON support.conversation_messages TO dl_sync;
```

On Azure SQL Database you can skip the login and create a contained database user instead: `CREATE USER dl_sync WITH PASSWORD = '...';`. `GRANT SELECT ON` a table is described in [GRANT object permissions](https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql).

Keep the password in a secrets manager, never in code you commit.

## 2. Query new rows

Use the `mssql` package, which runs on tedious, the JavaScript driver Microsoft lists for SQL Server ([Node.js Driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/node-js/node-js-driver-for-sql-server)). Each run asks for the rows changed since the last run, using `updated_at` as the cursor:

```sql
SELECT message_id, conversation_id, customer_id, sender_type, body, channel, sent_at, updated_at
FROM support.conversation_messages
WHERE updated_at >= @since
ORDER BY updated_at;
```

- `@since` is a named parameter, set with `request.input('since', sql.DateTime2, value)` ([node-mssql](https://github.com/tediousjs/node-mssql#data-types)). The code passes the cursor as a JavaScript `Date`.
- `options.useUTC` defaults to `true`, so `datetime2` values without an offset are read and written as UTC ([configuration](https://github.com/tediousjs/node-mssql#configuration-1)). Store your times in UTC, or use `datetimeoffset` columns.
- `requestTimeout` defaults to 15 seconds. The code raises it to 5 minutes, so a long history query can finish.

## 3. Map the columns

This guide assumes a table with one row per message, with these columns. Rename them to match yours.

| Column | Holds |
|---|---|
| `message_id` | The message's own id |
| `conversation_id` | The conversation, ticket or chat the message belongs to |
| `customer_id` | Your internal id for the customer in that conversation, on every row, including agent and bot rows. Not an email address or phone number. |
| `sender_type` | Who wrote the message, for example `customer`, `agent` or `bot` |
| `body` | The message text (`nvarchar`) |
| `channel` | Chat, email, voice and so on |
| `sent_at` | When the message was sent (`datetime2` in UTC, or `datetimeoffset`) |
| `updated_at` | When the row was written or last changed (same type) |

| Dimension Labs | SQL Server column | Notes |
|---|---|---|
| `text` | `body` | Skip rows where it is empty. Strip HTML if bodies are HTML. |
| `userId` | `customer_id` | If it is null, use the `sessionId`. |
| `incoming` | `sender_type` | `true` only when the customer wrote it. Agent and bot rows are `false`. |
| `sessionId` | `conversation_id` | Replace any colons. |
| `conversationId` | `conversation_id` | Same value as `sessionId`. |
| `dimensionlabs_timestamp` | `sent_at` | Pass the `Date` through `toMillis`. |
| `platformJson` | `message_id`, `channel` | Add any join keys you have, such as an account or store id. |

**Personal data.** Don't select email, phone or name columns. If your only customer key is an email, send a consistent hash of it instead. Message text can still hold personal details: redact them in the function before you send. See [PII redaction](/data/pii-redaction).

## 4. Send it

Install the driver in your project first: `npm install mssql`.

```js
// sql-server.mjs. Environment: MSSQL_SERVER (for example yourserver.database.windows.net), MSSQL_DATABASE, MSSQL_USER, MSSQL_PASSWORD, DL_API_KEY.



const HOUR = 60 * 60 * 1000;
const SQL = `SELECT message_id, conversation_id, customer_id, sender_type, body, channel, sent_at, updated_at
  FROM support.conversation_messages WHERE updated_at >= @since ORDER BY updated_at`;
const clean = (s) => String(s ?? '').replace(/<[^>]+>/g, ' ').replace(/\s+/g, ' ').trim();

// Sends every row written or changed at or after `since` (Unix ms). Returns the new `since`.
export async function syncSqlServer({ since, historical = false }) {
  const pool = await new sql.ConnectionPool({
    server: process.env.MSSQL_SERVER,
    database: process.env.MSSQL_DATABASE,
    user: process.env.MSSQL_USER,
    password: process.env.MSSQL_PASSWORD,
    requestTimeout: 5 * 60 * 1000,
    options: { encrypt: true, trustServerCertificate: false, useUTC: true },
  }).connect();
  try {
    const { recordset } = await pool.request()
      .input('since', sql.DateTime2, new Date(since))
      .query(SQL);
    let newest = since;
    for (const row of recordset) {
      const text = clean(row.body);
      const id = String(row.conversation_id).replaceAll(':', '-');
      if (text) {
        const ts = toMillis(row.sent_at);
        await sendToDimensionLabs({
          text,
          userId: row.customer_id ? String(row.customer_id).replaceAll(':', '-') : id,
          incoming: row.sender_type === 'customer',
          sessionId: id,
          conversationId: id,
          dimensionlabs_timestamp: ts,
          platformJson: { message_id: String(row.message_id), channel: row.channel },
        }, { historical: historical || ts < Date.now() - HOUR });
      }
      newest = Math.max(newest, toMillis(row.updated_at));
    }
    return newest;
  } finally {
    await pool.close();
  }
}
```

`encrypt: true` is the driver's default. For a server with a self-signed certificate (a local test server, say), the driver's docs say to set `trustServerCertificate` to `true`.

The cursor follows `updated_at`, so a row that changes later (a status column, say) is sent again. That's fine: duplicates are removed automatically. An index on `updated_at` keeps the query fast as the table grows.

## 5. Run it

Save the code as `sql-server.mjs` in a project set up as in [Set up your project](/api/tracker#set-up-your-project), and run it with the [scheduled sync](/api/scheduled-sync) runner: `import { syncSqlServer as sync } from './sql-server.mjs';`.

- **History first:** `node run.mjs 2025-01-01` sends every row since that date to `/trackhistorical`. Then start a [backfill](/dimensions/backfill) from the **Backfill** tab.
- **Then every 15 minutes:** the scheduled job sends new rows to `/track`, and they are enriched automatically every day. A message whose `sent_at` is more than an hour old when the job sees it (a late-loaded batch, say) goes to `/trackhistorical`, and needs a backfill.
- **Re-runs are safe:** duplicates are removed automatically.
- **No API rate limit:** the database is the only limit. The query reads all new rows into memory at once. For a very large history, start from a recent date and upload the older rows as a file.

You can also export the rows to a CSV file and [upload it](/data/upload-a-file). Run it through the [file checker](/data/check-a-file) first.

## SQL Server reference

- [Node.js Driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/node-js/node-js-driver-for-sql-server)
- [node-mssql](https://github.com/tediousjs/node-mssql)
- [CREATE LOGIN](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql)
- [CREATE USER](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql)
- [GRANT object permissions](https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql)
- [Azure SQL Database IP firewall rules](https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure)

Checked against Microsoft's and node-mssql's documentation on 2026-10-26.