MSSQL Driver Documentation

This documentation explains the MSSQL driver implementation and how connection parameters map to UI fields in the Gigantic application.

Connection Parameters

Technical Details

The MSSQL driver implementation uses the mssql Node.js library which internally uses the tedious library for connections.

Key technical aspects:

• Connection pooling is supported through the pooled flag in configuration
• Authentication can be either SQL Server authentication (user/password) or Windows authentication
• The driver uses INFORMATION_SCHEMA views and sys tables for metadata extraction
• Schema extraction includes table names, column information, and data types
• DDL generation uses sys tables to create CREATE TABLE statements
• The driver requires VIEW DATABASE STATE permissions for accessing sys.dm_db_partition_stats
• Bulk insert operations are supported through the bulkBatchInsert method
• Foreign key relationships are detected by querying sys.foreign_keys and related tables
• Proper handling of Microsoft SQL Server specific data types (uniqueidentifier, datetime2, etc.)

When SSH tunneling is enabled, connections are first established through the tunnel before connecting to the MSSQL server.

Connection String Format

MSSQL connections are built using the tedious connection format:

Server=[host],[port];Database=[database];User ID=[user];Password=[password];Encrypt=true;TrustServerCertificate=true;

For Windows authentication, the connection string omits User ID and Password:

Server=[host],[port];Database=[database];Integrated Security=true;Encrypt=true;TrustServerCertificate=true;

Authentication Options

MSSQL supports several authentication types:

SSH Tunnel Support

The MSSQL driver supports SSH tunneling for secure access to databases behind firewalls or in private networks. For detailed information on configuring SSH tunnels, please refer to the SSH Tunnelling documentation.

API Endpoints Used

MSSQL connections are primarily used in:

• Tap creation (data source discovery)
• Sink creation (data destination for anonymized data)
• Pipeline execution (data extraction and loading)

Node.js Driver Dependency

The MSSQL driver depends on the mssql Node.js library:

• Library: mssql
• Purpose: Connects to Microsoft SQL Server databases
• Features: Connection pooling, prepared statements, bulk operations, transactions

Large Tables and Resume

This section lists only what is specific to SQL Server. How resume, parallel ranges and row estimates work in every driver is explained in Large databases.

Reading

• Tables are read in chunks ordered by the primary key, so a job that stops resumes after the last key it saved. Only the primary key is used: a heap or any other table with no primary key is read with one cursor and starts over if the job stops.
• Every primary key column must be an integer (int, bigint, smallint, tinyint, bit), a decimal with no decimals and up to 15 digits, or text-like (char, varchar, nvarchar, uniqueidentifier). A key with a numeric, float, real, money or date/time column, or a decimal with decimals or more than 15 digits, is not used, because a JavaScript number cannot hold it exactly.

Parallel Ranges and Row Estimates

• Primary key ranges are cut from a TABLESAMPLE (n PERCENT) REPEATABLE sample of the key, sized from the pages of the table in sys.dm_db_partition_stats. When the sample is too small, a single integer key is split evenly between its minimum and maximum.
• The row estimate is the row_count of the table (heap or clustered index) in sys.dm_db_partition_stats, which SQL Server keeps without running statistics.

Loading

• Before loading, the job marks each table it loads with the extended property gigantics_nocheck, runs NOCHECK CONSTRAINT ALL and DISABLE TRIGGER ALL on it, and turns IDENTITY_INSERT on.
• In insert loads, the foreign keys of other tables that point to the loaded tables are disabled with NOCHECK too, and marked. A foreign key that was already disabled without the mark is left as it is.
• The truncate option runs TRUNCATE TABLE, and DELETE if that fails.
• At the end, the job enables the constraints again with CHECK CONSTRAINT ALL, removes the mark, enables the triggers and turns IDENTITY_INSERT off. Foreign keys of other tables are enabled WITH CHECK when they were trusted before.
• By default the constraints of the loaded tables are enabled without checking the existing rows, so SQL Server marks them as not trusted. Set trustConstraints: true in Custom Params to enable them WITH CHECK: SQL Server then reads each whole table, so raise requestTimeout for big tables. If the check fails, the job logs a warning and leaves the constraints enabled but not trusted.
• If a job dies, its marks stay. A job that loads other tables warns that the marked ones still have their constraints and triggers off, and the next job that loads a marked table turns them back on.

Permissions

For a SQL Server sink, the database user needs:

• ALTER on the tables it loads and on the tables with foreign keys pointing to them. The extended property, NOCHECK, DISABLE TRIGGER, IDENTITY_INSERT and TRUNCATE TABLE all need it.
• VIEW DATABASE STATE, for the row estimates and the sample sizes.

Custom Params

The MSSQL driver supports additional custom parameters that can be specified in YAML format to fine-tune connection behavior, pooling, SSL settings, and other advanced options.

Connection Options

# Security and Encryption
encrypt: true
trustServerCertificate: true
cryptoCredentialsDetails:
# TLS configuration options
rejectUnauthorized: false
multiSubnetFailover: false

# Authentication Options
domain: "MYDOMAIN"
trustedConnection: false  # For Windows Authentication with msnodesqlv8 driver

# Connection Behavior
connectionRetryInterval: 500
maxRetriesOnTransientErrors: 3
fallbackToDefaultDb: false
connectionIsolationLevel: "READ_COMMITTED"  # or "READ_UNCOMMITTED", "REPEATABLE_READ", "SERIALIZABLE", "SNAPSHOT"

# Session Settings
appName: "Gigantic Application"
datefirst: 7  # First day of week (1-7)
dateFormat: "mdy"  # or "dmy", "ymd", "ydm", "myd", "dym"
language: "us_english"
textsize: 2147483647  # Maximum size of text or image data

# ANSI Settings
enableAnsiNull: true
enableAnsiNullDefault: true
enableAnsiPadding: true
enableAnsiWarnings: true
enableArithAbort: true
enableConcatNullYieldsNull: true
enableCursorCloseOnCommit: false
enableImplicitTransactions: false
enableNumericRoundabort: false
enableQuotedIdentifier: true

Pool Configuration

# Pool sizing options
min: 0
max: 10

# Pool timeout options
idleTimeoutMillis: 30000

Request Options

# Query execution options
requestTimeout: 15000
stream: false
parseJSON: false
arrayRowMode: false

# Result set options
multiple: false
rowCollectionOnDone: false
rowCollectionOnRequestCompletion: false

Authentication Configuration

# Different authentication types
authentication:
  type: "default"  # or "ntlm", "azure-active-directory-password", "azure-active-directory-access-token", etc.
  options:
    userName: myuser
    password: mypassword
    domain: "MYDOMAIN"

# Azure AD Token Authentication
authentication:
  type: "azure-active-directory-access-token"
  options:
    token: ${env:AZURE_AD_TOKEN}

# Azure AD Service Principal
authentication:
  type: "azure-active-directory-service-principal-secret"
  options:
    clientId: ${env:AZURE_CLIENT_ID}
    clientSecret: ${env:AZURE_CLIENT_SECRET}
    tenantId: ${env:AZURE_TENANT_ID}

Example Custom Configurations

More examples and details on the configurations options can be found here: https://github.com/tediousjs/node-mssql

Basic Configuration

encrypt: true
trustServerCertificate: false
max: 20
requestTimeout: 30000

Windows Authentication

trustedConnection: true
domain: "CORPORATE"
appName: "Gigantic Data Pipeline"

Azure AD Authentication

authentication:
  type: "azure-active-directory-password"
  options:
    userName: azure_user@mytenant.onmicrosoft.com
    password: ${env:AZURE_PASSWORD}
    tenantId: ${env:AZURE_TENANT_ID}
encrypt: true
trustServerCertificate: false

Advanced Configuration with Custom Settings

connectionIsolationLevel: "SNAPSHOT"
dateFormat: "ymd"
language: "British"
textsize: 2000000
enableAnsiNull: false
enableQuotedIdentifier: false
min: 2
max: 15
idleTimeoutMillis: 60000
requestTimeout: 60000