# SQL Server / Azure SQL

> Connect Microsoft SQL Server, Azure SQL Database, Azure SQL Managed Instance or Amazon RDS for SQL Server with a read-only user, and load tables in full or incrementally by rowversion or an update column.

SQL Server / Azure SQL needs the Starter plan or above. DataxLand speaks the TDS protocol directly (no ODBC driver to install),
streams rows and copies the tables you pick into the workspace's own warehouse; it only runs `SELECT` statements and never writes
to your database. The reading session sets `DEADLOCK_PRIORITY LOW` and waits at most 60 seconds for locks, so it never gets in the
way of your running applications. It works with SQL Server 2016 and later, Azure SQL Database, Azure SQL Managed Instance and
Amazon RDS for SQL Server.

## 1. Prepare a user

Create a dedicated login and user for DataxLand with read-only rights:

```sql
-- on master (Azure SQL Database: create a "contained user" directly in the database and skip CREATE LOGIN)
CREATE LOGIN dataxland WITH PASSWORD = '<strong password>';
-- on the database to load
CREATE USER dataxland FOR LOGIN dataxland;      -- Azure SQL: CREATE USER dataxland WITH PASSWORD = '<strong password>';
ALTER ROLE db_datareader ADD MEMBER dataxland;  -- or: GRANT SELECT ON SCHEMA::dbo TO dataxland;
GRANT VIEW DEFINITION TO dataxland;             -- read primary keys, foreign keys, column descriptions
GRANT SHOWPLAN TO dataxland;                    -- estimate the cost of Si's SQL (optional)
```

Without `SHOWPLAN`, every SQL statement Si wants to run on the source waits for a technical person to confirm it. If the database
only accepts certain IP addresses (Azure firewall, security groups), allow DataxLand's server address (ask support for it).

## 2. Connect

1. **Data sources → Add source**, choose **SQL Server / Azure SQL**.
2. Fill in **Host** (e.g. `your-server.database.windows.net`; without `\instance-name` or `,port`), **Port** (1433
   by default; for a named instance use the instance's fixed port), **Database name**, **Schema** (`dbo` by
   default), **User** and **Password**.
3. **SSL**:
   - **require** (default): encryption required, self-signed certificates accepted (like `TrustServerCertificate=yes`); suits
     self-installed SQL Server.
   - **verify-full**: encryption required and the certificate must be issued by a public CA for the right host name.
     Recommended for Azure SQL.
   - **disable**: no encryption; only on a trusted network. Servers that require encryption will refuse it.
4. Click **Test connection**, pick tables (optionally **Filter by date (optional)**), then add the source.
   The password is encrypted at rest and never shown again.

## 3. Sync modes

SQL Server supports **Full** and **Incremental**. For incremental, the best cursor column is a **`rowversion`** column: SQL Server
bumps it on every insert or update, so no change is missed even when an application forgets to update `updated_at`. DataxLand
suggests rowversion columns first, then timestamp columns such as `updated_at`. If a table has none, add one (existing data is not
changed):

```sql
ALTER TABLE dbo.orders ADD row_version ROWVERSION;
```

The rowversion watermark never moves past transactions still open when the read starts (`MIN_ACTIVE_ROWVERSION()`), so rows that
commit late are picked up next time. With timestamp columns, each read goes back 10 minutes for safety. Incremental doesn't see
deleted rows; SQL Server Change Tracking / CDC is not supported today. See
[Sync modes and refresh schedule](/en/docs/concepts/sync-modes).

## Data types

| SQL Server type | In the dictionary | Notes |
| --- | --- | --- |
| `tinyint`, `smallint`, `int`, `bigint`, `rowversion` | BIGINT | rowversion read as a number |
| `decimal`, `numeric`, `money`, `smallmoney`, `float`, `real` | DECIMAL | |
| `bit` | BOOLEAN | `true`/`false` |
| `date` | DATE | |
| `datetime`, `datetime2`, `smalldatetime`, `datetimeoffset` | DATETIME | `datetimeoffset` converted to Vietnam time (UTC+7) |
| `char`, `varchar`, `nchar`, `nvarchar`, `text`, `xml`, `uniqueidentifier`, `time` | VARCHAR | |
| `geography`, `geometry`, `hierarchyid` | VARCHAR | read as text (WKT) |
| `binary`, `varbinary`, `image` | (left empty) | binary data isn't used |

## Si investigates with SQL

Si may run read-only SQL on the source while investigating, within limits on the number of statements and their cost (estimated
with `SHOWPLAN_XML`, without running the statement); statements over the limit wait for a technical person to confirm them.

## Related

- [PostgreSQL](/en/docs/sources/postgres)
- [MySQL / MariaDB](/en/docs/sources/mysql)
- [Security and privacy](/en/docs/security-privacy)
