ContextQMD
Back to Airbyte

MSSQL

docs/integrations/destinations/mssql.md

2.0.021.6 KB
Original Source

MSSQL

Supported sync modes

Sync modeSupported?
Full Refresh - OverwriteYes
Full Refresh - AppendYes
Full Refresh - Overwrite + DedupedYes
Incremental Sync - AppendYes
Incremental Sync - Append + DedupedYes

Output Schema

Each stream will be output into its own table in SQL Server. Each table will contain the following metadata columns:

  • _airbyte_raw_id: A random UUID assigned to each incoming record. The column type in SQL Server is VARCHAR(MAX).
  • _airbyte_extracted_at: A timestamp for when the event was pulled from the data source. The column type in SQL Server is BIGINT.
  • _airbyte_meta: Additional information about the record. The column type in SQL Server is TEXT.
  • _airbyte_generation_id: Incremented each time a refresh is executed. The column type in SQL Server is TEXT.

See here for more information about these fields.

Getting Started

Setup guide

  • MS SQL Server: Azure SQL Database, SQL Server 2016 or greater

Network Access

Make sure your SQL Server database can be accessed by Airbyte. If your database is within a VPC, you may need to allow access from the IP you're using to expose Airbyte.

Permissions

You need a user configured in SQL Server that can create tables and write rows. We highly recommend creating an Airbyte-specific user for this purpose. In order to allow for normalization, please grant ALTER permissions for the user configured.

Target Database

You will need to choose an existing database or create a new database that will be used to store synced data from Airbyte.

Configuration

You'll need the following information to configure the MSSQL destination:

  • Host
    • The host name of the MSSQL database.
  • Port
    • The port of the MSSQL database.
  • Database Name
    • The name of the MSSQL database.
  • Default Schema
    • The default schema tables are written to if the source does not specify a namespace. The usual value for this field is "public".
  • Username
    • The username which is used to access the database.
  • Password
    • The password associated with this username.
  • JDBC URL Parameters
    • Additional properties to pass to the JDBC URL string when connecting to the database formatted as 'key=value' pairs separated by the symbol '&'. (example: key1=value1&key2=value2&key3=value3).
  • SSL Method
    • The SSL configuration supports three modes: Unencrypted, Encrypted (trust server certificate), and Encrypted (verify certificate).
      • Unencrypted: Do not use SSL encryption on the database connection
      • Encrypted (trust server certificate): Use SSL encryption without verifying the server's certificate. This is useful for self-signed certificates in testing scenarios, but should not be used in production.
      • Encrypted (verify certificate): Use the server's SSL certificate, after standard certificate verification.
        • Host Name In Certificate (optional): When using certificate verification, this property can be set to specify an expected name for added security. If this value is present, and the server's certificate's host name does not match it, certificate verification will fail.
  • Load Type
    • The data load type supports two modes: Insert or Bulk
      • Insert: Utilizes SQL INSERT statements to load data to the destination table.
      • Bulk: Utilizes Azure Blob Storage and the BULK INSERT command to load data to the destination table. If selected, additional configuration is required:

MSSQL with Azure Blob Storage (Bulk Upload) Setup Guide

This section describes how to set up and use the Microsoft SQL Server (MSSQL) connector with the Azure Blob Storage Bulk Upload feature. By staging data in an Azure Blob Storage container and using BULK INSERT, you can significantly improve ingestion speed and reduce network overhead for large or frequent data loads.

Why Use Azure Blob Storage Bulk Upload?

When handling high data volumes or frequent syncs, row-by-row inserts into MSSQL can become slow and resource-intensive. By staging files in Azure Blob Storage first, you can:

  1. Aggregate Data into Bulk Files: Data is written to Blob Storage in batches, reducing overhead.
  2. Perform Bulk Ingestion: MSSQL uses BULK INSERT to directly load these files, typically resulting in faster performance compared to conventional row-by-row inserts.
Prerequisites
  1. A Microsoft SQL Server Instance
    • Compatible with on-premises SQL Server or Azure SQL Database.
  2. Azure Blob Storage Account
    • A storage account and container (e.g., bulk-staging) where data files will be placed.
  3. Permissions
    • Blob Storage: ability to create, read, and delete objects within the designated container.
    • MSSQL: ability to create or modify tables, and permission to execute BULK INSERT.
Setup Guide

Follow these steps to configure MSSQL with Azure Blob Storage for bulk uploads.

1. Set Up Azure Blob Storage
  1. Create a Storage Account & Container
    • In the Azure Portal, create (or reuse) a Storage Account.
    • Within that account, create a container (e.g., bulk-staging) for staging your data files.
  2. Establish Access Credentials
    • Use a Shared Access Signature (SAS) scoped to your container.
    • Ensure the SAS token or role assignments include permissions such as Read, Write, Delete, and List.
2. Configure MSSQL

See the official Microsoft documentation for more details. Below is a simplified overview:

  1. (Optional) Create a Master Encryption Key If your environment requires a master key to store credentials securely, create one:

    sql
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<your_password>';

  2. Create a Database Scoped Credential Configure a credential that grants MSSQL access to your Blob Storage using the SAS token:

    sql
    CREATE DATABASE SCOPED CREDENTIAL <credential_name>
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '<your_sas_token>';

  3. Create an External Data Source Point MSSQL to your Blob container using the credential:

    sql
    CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://<storage_account>.blob.core.windows.net/<container_name>',
    CREDENTIAL = <credential_name>
);

    You’ll reference <data_source_name> when configuring the connector.

3. Connector Configuration

You’ll need to supply:

  1. MSSQL Connection Details
    • The server hostname/IP, port, database name, and authentication (username/password).
  2. Bulk Load Data Source
    • The name of the external data source you created (e.g., <data_source_name>).
  3. Azure Storage Account & Container
    • The name of the storage account and container.
  4. SAS Token
    • The token that grants Blob Storage access.

See the Getting Started: Configuration section of this guide for more details on BULK INSERT connector configuration.

Namespace support

This destination supports namespaces. The namespace maps to a SQL Server schema.

Changelog

<details> <summary>Expand to review</summary>
VersionDatePull RequestSubject
2.2.152026-01-2672297Upgrade CDK to 0.2.0
2.2.142025-11-0569130Upgrade to Bulk CDK 0.1.61.
2.2.132025-09-2466684Pin to CDK artifact
2.2.122025-06-2662078Add SSH tunnel support
2.2.112025-05-3061017Integration test fixes
2.2.102025-05-2960897Internal fixes
2.2.92025-05-1960791Fix bug in detecting schema change when stream has no columns
2.2.82025-05-0859735Cleanup: Remove unused code
2.2.72025-05-0756444CDK: Internal refactor; perf improvements
2.2.62025-04-2158146Fix numeric bounds-handling
2.2.52025-04-1858140Upgrade to latest CDK
2.2.42025-04-1157563Improve BULK INSERT documentation.
2.2.32025-04-1658085Internal refactoring
2.2.22025-04-0756391Add support for Azure blob storage auth via storage account key.
2.2.12025-03-2756402Improve Azure blob storage load logic.
2.2.02025-03-2356353Bulk Load performance improvements
2.1.22025-03-2556346Internal refactor
2.1.12025-03-2456355Upgrade to airbyte/java-connector-base:2.0.1 to be M4 compatible.
2.1.02025-03-2455849Misc. bugfixes in type-handling (esp. in complex types)
2.0.52025-03-2455904Fix handling of invalid schemas (correctly JSON-serialize values)
2.0.42025-03-2055886Internal refactor
2.0.32025-03-1855811CDK: Pass DestinationStream around vs Descriptor
2.0.22025-03-1255720Restore definition ID
2.0.12025-03-1255718Fix breaking change information in metadata.yaml
2.0.02025-03-1155684Release 2.0.0
2.0.0.rc132025-03-0755252RC13: Bugfix for OOM on Bulk Load
2.0.0.rc122025-03-0554159RC12: Support For Bulk Insert Using Azure Blob Storage
2.0.0.rc112025-03-0455193RC11: Increase decimal precision
2.0.0.rc102025-02-2454648RC10: Fix index column names with hyphens
2.0.0.rc92025-02-2154197RC9: Fix index column names with invalid characters
2.0.0.rc82025-02-2054186RC8: Fix String support
2.0.0.rc72025-02-1153364RC7: Revert deletion change
2.0.0.rc62025-02-1153364RC6: Break up deletes into loop to reduce locking
2.0.0.rc52025-02-0753236RC5: Use rowlock hint
2.0.0.rc42025-02-0653192RC4: Fix config, timehandling, performance tweak
2.0.0.rc32025-02-0453174RC3: Fix metadata.yaml for publish
2.0.0.rc22025-02-0452704RC2: Performance improvement
2.0.0.rc12025-01-2452096Release candidate
1.0.32025-01-1051497Use a non root base image
1.0.22024-12-1849891Use a base image: airbyte/java-connector-base:1.0.0
1.0.12024-11-04#48134Fix supported sync modes (destination-mssql 1.x.y does not support dedup)
1.0.02024-04-11#36050Update to Dv2 Table Format and Remove normalization
0.2.02023-06-27#27781License Update: Elv2
0.1.252023-06-21#27555Reduce image size
0.1.242023-06-05#27034Internal code change for future development (install normalization packages inside connector)
0.1.232023-04-04#24604Support for destination checkpointing
0.1.222022-10-21#18275Upgrade commons-text for CVE 2022-42889
0.1.202022-07-14#14618Removed additionalProperties: false from JDBC destination connectors
0.1.192022-05-25#13054Destination MSSQL: added custom JDBC parameters support.
0.1.182022-05-17#12820Improved 'check' operation performance
0.1.172022-04-05#11729Bump mina-sshd from 2.7.0 to 2.8.0
0.1.152022-02-25#10421Refactor JDBC parameters handling
0.1.142022-02-14#10256Add -XX:+ExitOnOutOfMemoryError JVM option
0.1.132021-12-28#9158Update connector fields title/description
0.1.122021-12-01#8371Fixed incorrect handling "\n" in ssh key
0.1.112021-11-08#7719Improve handling of wide rows by buffering records based on their byte size rather than their count
0.1.102021-10-11#6877Add normalization capability, add append+deduplication sync mode
0.1.92021-09-29#5970Add support & test cases for MSSQL Destination via SSH tunnels
0.1.82021-08-07#5272Add batch method to insert records
0.1.72021-07-30#5125Enable additionalPropertities in spec.json
0.1.62021-06-21#3555Partial Success in BufferedStreamConsumer
0.1.52021-07-20#4874declare object types correctly in spec
0.1.42021-06-17#3744Fix doc/params in specification file
0.1.32021-05-28#3728Change dockerfile entrypoint
0.1.22021-05-13#3367Fix handle symbols unicode
0.1.12021-05-11#3566MS SQL Server Destination Release!
</details>