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:
-- 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
- Data sources → Add source, choose SQL Server / Azure SQL.
- Fill in Host (e.g.
your-server.database.windows.net; without\instance-nameor,port), Port (1433 by default; for a named instance use the instance's fixed port), Database name, Schema (dboby default), User and Password. - 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.
- require (default): encryption required, self-signed certificates accepted (like
- 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):
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.
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
MySQL / MariaDB
Connect MySQL or MariaDB (including Amazon RDS, Aurora, Azure Database for MySQL and Cloud SQL) with a read-only user, pick tables, and load them in full or incrementally by an update column.
BigQuery
Connect BigQuery with a service account key, choose the billing project and dataset, and set a scan limit to keep query costs under control.