# Microsoft SQL Server Destination Reference Microsoft SQL Server is a relational database management system that allows you to query across relational, non-relational, structured and unstructured data with the language and platform of your choice. This guide serves as a reference for version 1 of Stitch’s Microsoft SQL Server destination. ## Details and features * [Stitch features](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#stitch-features) * [Destination details](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#destination-details) ### Stitch features High-level details about Stitch’s implementation of Microsoft SQL Server, such as supported connection methods, availability on Stitch plans, etc. | **Release status


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | Beta | | --- | --- | | **Stitch plan availability


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | All Stitch plans | | **Stitch supported regions


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** |

Operating regions determine the location of the resources Stitch uses to process your data. [Learn more](https://help.qlik.com/en-US/stitch/security/supported-data-pipeline-regions). | | **Supported versions


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** |

**Note:** SSL can only be used with versions of Microsoft SQL Server that support TSL 1.2. Check which versions support it in [Microsoft’s documentation](https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe). | | **Connect API availability


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | **Supported
the Success iconSupported**

This version of the Microsoft SQL Server destination can be created and managed using Stitch’s Connect API. [Learn more](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#destination-form-properties). | | **SSH connections


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | **Supported
the Success iconSupported**

Stitch supports using SSH tunnels to connect to Microsoft SQL Server destinations. | | **SSL connections


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | **Supported
the Success iconSupported**

Stitch will attempt to use SSL to connect by default. No additional configuration is needed. | | **VPN connections


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | **Unsupported
the Remove iconNot supported**

Virtual Private Network (VPN) connections may be implemented as part of a Premium plan. Contact [Stitch Sales](https://www.stitchdata.com/contact/) for more info. | | **Static IP addresses


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | **Supported
the Success iconSupported**

This version of the Microsoft SQL Server destination has static IP addresses that can be whitelisted. | | **Default loading behavior


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | **Upsert**

**Note**: Append-Only loading will be used if all conditions for Upsert are not met. [Learn more](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior). | | **Nested structure support


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | **Supported**

the Success icon

Supported


Nested data structures (JSON arrays and objects) will be loaded intact into a `NVARCHAR(MAX)` column. [Learn more](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#transformations--json-structures). | ### Destination details Details about the destination, including object names, table and column limits, reserved keywords, etc. **Note**: Exceeding the limits noted below will result in loading errors or rejected data. | **Maximum record size


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | 20MB | | --- | --- | | **Table name length


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | 113 characters | | **Column name length


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | 128 characters | | **Maximum columns per table


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | 1,024 | | **Maximum table size


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | None | | **Maximum tables per database


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | Limited by total number of objects in a database. The sum of objects cannot exceed 2,147,483,647. | | **Case sensitivity


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | Insensitive | | **Reserved keywords


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/index)** | Refer to the [Reserved keywords documentation](https://help.qlik.com/en-US/stitch/replication/loading/reserved-keywords#microsoft-sql-server-v1-reserved-keywords). | ## Replication * [Replication process overview](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#replication-process-overview) * [Loading behavior](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#loading-behavior) * [Primary Keys](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#replication--primary-keys) * [Incompatible sources](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#replication--incompatible-sources) ### Replication process overview A Stitch replication job consists of three stages: * [Step 1: Data extraction](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#replication--data-extraction) * [Step 2: Preparation](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#replication--stitch-internal-pipeline) * [Step 3: Loading](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#replication--loading) #### Step 1: Data extraction Stitch requests and extracts data from a data source. Refer to the [System overview guide](https://help.qlik.com/en-US/stitch/getting-started/basic-concepts-system-overview#system-architecture--extraction) for a more detailed explanation of the Extraction phase. #### Step 2: Preparation During this phase, the extracted data is buffered in Stitch’s durable, highly available internal data pipeline and readied for loading. Refer to the [System overview guide](https://help.qlik.com/en-US/stitch/getting-started/basic-concepts-system-overview#system-architecture--preparing) for a more detailed explanation of the Preparation phase. #### Step 3: Loading Stitch loads the data into Microsoft SQL Server. ### Loading behavior By default, Stitch will use **Upsert loading** when loading data into Microsoft SQL Server. If the conditions for Upsert loading aren’t met, data will be loaded using Append-Only loading. Refer to the [Understanding loading behavior guide](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior) for more info and examples. ### Primary Keys If Primary Keys are specified, Stitch creates a table in which Primary Key columns have a `NOT NULL` constraint. Errors occur when: * Primary Key data types differ between records * Primary Key names differ between records * The number of Primary Keys differs between records ### Incompatible sources No compatibility issues have been discovered between Microsoft SQL Server and Stitch's integration offerings. See all [destination and integration incompatibilities.](https://help.qlik.com/en-US/stitch/destinations/destination-integration-compatibility) ## Transformations * [System tables and columns](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#transformations--system-tables-columns) * [Data typing](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#transformations--data-typing) * [JSON structures](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#transformations--json-structures) * [Column names](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#transformations--column-naming) * [Table and schema names](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#transformations--table-schema-naming) * [Timezones](https://help.qlik.com/en-US/stitch/destinations/microsoft-sql-server/reference/#transformations--timezones) ### System tables and columns Stitch will create the following tables in each integration’s dataset: * [_sdc_rejected](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table) Additionally, Stitch will insert [system columns](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns) (prepended with `_sdc`) into each table. ### Data typing Stitch converts data types only where needed to ensure the data is accepted by Microsoft SQL Server. In the table below are the data types Stitch supports for Microsoft SQL Server destinations, and the Stitch types they map to. * **Stitch type:** The Stitch data type the source type was mapped to. During the Extraction and Preparing phases, Stitch identifies the data type in the source and then maps it to a common Stitch data type. * **Destination type:** The destination-compatible data type the Stitch type maps to. This is the data type Stitch will use to store data in Microsoft SQL Server. * **Notes:** Details about the data type and/or its allowed values in the destination, if available. If a range is available, values that exceed the noted range will be [rejected by Microsoft SQL Server](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table). | **Stitch type** | **Destination type** | **Notes** | | --- | --- | --- | | BIGINT | [BIGINT](https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver15) | | | BOOLEAN | [BIT](https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver15) | | | DATE | [DATETIMEOFFSET](https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-ver15) | | | DOUBLE | [FLOAT](https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ver15) | | | FLOAT | [FLOAT](https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ver15) | | | INTEGER | [INT](https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver15) | | | JSON | [NVARCHAR](https://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver15) | | | NUMBER | [DECIMAL](https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-ver15) | | | STRING | [NVARCHAR](https://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver15) | | ### JSON structures When Stitch replicates source data containing objects or arrays, Stitch will load the data intact into a column with the type `NVARCHAR(MAX)`. This is a Microsoft SQL Server data type that can contain semi-structured data like JSON arrays and objects. You can then use Microsoft SQL Server’s functions for semi-structured data to parse the data. Refer to [Microsoft SQL Server’s documentation](https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15) for more info. ### Column names Column names in Microsoft SQL Server: Stitch will perform the following transformations to ensure column names [adhere to the rules imposed by Microsoft SQL Server](https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-ver15): | **Transformation** | **Source column** | **Destination column** | | --- | --- | --- | | Remove leading characters which are not a-z, A-Z, 0-9 or \_ | `!customerid` | `customerid` | | Trim leading and trailing spaces | ` customerid ` | `customerid` | | Replace characters which are not a-z, A-Z, 0-9, \_, @, $ or # with an underscore | `customer-id` | `customer_id` | | Convert to lowercase | `CustomerID` | `customerid` | | Quote reserved words | `order` | `"order"` | ### Table and schema names Table and schema names in Microsoft SQL Server: Stitch will perform the following transformations to ensure table and schema names [adhere to the rules imposed by Microsoft SQL Server](https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-ver15): | **Transformation** | **Source name** | **Destination name** | | --- | --- | --- | | Remove leading characters which are not a-z, A-Z or \_. | `0-customers` | `customers` | | Trim leading and trailing spaces | ` customers ` | `customers` | | Replace characters which are not a-z, A-Z, 0-9, \_, @, $ or # with an underscore. | `customers!` | `customers` | | Convert to lowercase | `Customers` | `customers` | | Quote reserved words | `order` | `"order"` | ### Timezones Microsoft SQL Server will store the value as `DATETIMEOFFSET(3)`. ## Compare destinations **Not sure if Microsoft SQL Server is the destination for you?** Check out the [Choosing a Stitch Destination](https://help.qlik.com/en-US/stitch/destinations/choosing-a-stitch-destination) guide to compare each of Stitch’s destination offerings.