> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wirekite.io/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Server

> This guide explains how to configure SQL Server as a target database for Wirekite data loading and replication.

## Overview

Wirekite supports SQL Server 2016 and above as a target database for:

* **Schema Loading** - Create target tables from Wirekite's intermediate schema format
* **Data Loading** - Bulk load extracted data using ODBC bulk copy
* **Change Loading (CDC)** - Apply ongoing changes using MERGE operations

<Note>
  SQL Server loaders use ODBC bulk copy for high-performance data loading. The Change Loader uses shadow/merge tables with `_wkm` suffix for atomic change application.
</Note>

## Prerequisites

Before configuring SQL Server as a Wirekite target, ensure the following requirements are met:

### Database Configuration

1. **Version**: SQL Server 2016 or above
2. **ODBC Driver**: Install ODBC Driver 18 for SQL Server on the loader host
3. **User Permissions**: Create a dedicated Wirekite user with:
   * CREATE TABLE privilege
   * INSERT, UPDATE, DELETE on target tables
   * Read/write access to Wirekite internal tables (`wirekite_progress` and `wirekite_action`)

### File System Access

* Ensure the user running Wirekite has write permissions to the work directory
* Verify sufficient disk space for intermediate files during change loading

<Tip>
  Use the Wirekite cmdline tool or sqlcmd to verify database connectivity before running loaders.
</Tip>

***

## Schema Loader

The Schema Loader reads Wirekite's intermediate schema format (`.skt` file) and generates SQL Server-appropriate DDL statements for creating target tables.

<Note>
  The Schema Loader generates both base tables and merge tables (with `_wkm` suffix) for CDC operations.
</Note>

### Required Parameters

<ResponseField name="schemaFile" type="string" required>
  Path to the Wirekite schema file (`.skt`) generated by the Schema Extractor. Must be an absolute path.
</ResponseField>

<ResponseField name="createTableFile" type="string" required>
  Output file for CREATE TABLE statements. Includes both base tables and merge tables for CDC operations.
</ResponseField>

<ResponseField name="createConstraintFile" type="string" required>
  Output file for constraint definitions (indexes, unique constraints, check constraints).
</ResponseField>

<ResponseField name="createForeignKeyFile" type="string" required>
  Output file for FOREIGN KEY constraints. Can be applied after initial data load for better performance.
</ResponseField>

<ResponseField name="logFile" type="string" required>
  Absolute path to the log file for Schema Loader operations.
</ResponseField>

### Optional Parameters

<ResponseField name="dropTableFile" type="string" default="none">
  Output file for DROP TABLE IF EXISTS statements. Set to "none" to skip generation.
</ResponseField>

<ResponseField name="createRecoveryTablesFile" type="string" default="none">
  Output file for recovery table creation SQL. Set to "none" to skip.
</ResponseField>

<ResponseField name="createMergeTables" type="boolean" default="true">
  When `true`, generates merge tables (`_wkm` suffix) for CDC operations. Set to `false` if only doing data loads without change capture.
</ResponseField>

<ResponseField name="mergeTablesOnly" type="boolean" default="false">
  When `true`, only creates merge tables, skipping base table creation.
</ResponseField>

***

## Data Loader

The Data Loader reads Wirekite's intermediate data format (`.dkt` files) and loads records into SQL Server target tables using ODBC bulk copy for high performance.

### Required Parameters

<ResponseField name="dsnFile" type="string" required>
  Path to a file containing the SQL Server connection string.
</ResponseField>

**Connection string format:**

```
sqlserver://username:password@host:port?database=databasename&encrypt=disable&connection+timeout=30
```

**Example:**

```
sqlserver://wirekite:secretpass@sqlserver-target.example.com:1433?database=myapp&encrypt=disable&connection+timeout=30
```

<ResponseField name="inputDirectory" type="string" required>
  Directory containing data files (`.dkt`) to load. Files are processed in parallel based on `maxThreads`.
</ResponseField>

<ResponseField name="schemaFile" type="string" required>
  Path to the Wirekite schema file used by Schema Loader. Required for table structure information.
</ResponseField>

<ResponseField name="logFile" type="string" required>
  Absolute path to the log file for Data Loader operations.
</ResponseField>

### Optional Parameters

<ResponseField name="maxThreads" type="integer" default="5">
  Maximum number of parallel threads for loading tables. Each thread loads one table at a time.
</ResponseField>

<ResponseField name="hexEncoding" type="boolean" default="false">
  Set to `true` if data was extracted using hex encoding instead of base64.
</ResponseField>

<ResponseField name="databaseRemote" type="boolean" default="true">
  When `true`, indicates the database is remote. Affects how bulk copy operations are performed.
</ResponseField>

<Tip>
  For best performance, set `maxThreads` equal to the number of CPU cores available on the loader host.
</Tip>

***

## Change Loader

The Change Loader applies ongoing data changes (INSERT, UPDATE, DELETE) to SQL Server target tables using MERGE operations with shadow tables.

<Note>
  The Change Loader requires merge tables to be created by the Schema Loader. It processes up to 8 concurrent MERGE operations.
</Note>

### Required Parameters

<ResponseField name="dsnFile" type="string" required>
  Path to a file containing the SQL Server connection string.
</ResponseField>

**Connection string format:**

```
sqlserver://username:password@host:port?database=databasename&encrypt=disable&connection+timeout=30
```

<ResponseField name="inputDirectory" type="string" required>
  Directory containing change files (`.ckt`) from the Change Extractor.
</ResponseField>

<ResponseField name="workDirectory" type="string" required>
  Working directory for temporary CSV files during merge operations. Must be writable.
</ResponseField>

<ResponseField name="schemaFile" type="string" required>
  Path to the Wirekite schema file for table structure information.
</ResponseField>

<ResponseField name="logFile" type="string" required>
  Absolute path to the log file for Change Loader operations.
</ResponseField>

### Optional Parameters

<ResponseField name="loadLocal" type="boolean" default="true">
  When `true`, loads changes locally. When `false`, uses remote loading methods.
</ResponseField>

<ResponseField name="maxFilesPerBatch" type="integer" default="60">
  Maximum number of change files to process in a single batch before executing MERGE operations.
</ResponseField>

<Warning>
  The Change Loader should not start until the Data Loader has successfully completed the initial full load.
</Warning>

***

## Orchestrator Configuration

When using the Wirekite Orchestrator, prefix target parameters with `target.schema.`, `target.data.`, or `target.change.` depending on the operation.

**Example orchestrator configuration for SQL Server target:**

```
# Main configuration
source=oracle
target=sqlserver

# Schema loading
target.schema.schemaFile=/opt/wirekite/output/schema/wirekite_schema.skt
target.schema.createTableFile=/opt/wirekite/output/schema/create_tables.sql
target.schema.createConstraintFile=/opt/wirekite/output/schema/constraints.sql
target.schema.createForeignKeyFile=/opt/wirekite/output/schema/foreign_keys.sql
target.schema.logFile=/var/log/wirekite/schema-loader.log

# Data loading
target.data.dsnFile=/opt/wirekite/config/sqlserver.dsn
target.data.inputDirectory=/opt/wirekite/output/data
target.data.schemaFile=/opt/wirekite/output/schema/wirekite_schema.skt
target.data.logFile=/var/log/wirekite/data-loader.log
target.data.maxThreads=8

# Change loading (CDC)
target.change.dsnFile=/opt/wirekite/config/sqlserver.dsn
target.change.inputDirectory=/opt/wirekite/output/changes
target.change.workDirectory=/opt/wirekite/work
target.change.schemaFile=/opt/wirekite/output/schema/wirekite_schema.skt
target.change.logFile=/var/log/wirekite/change-loader.log
target.change.maxFilesPerBatch=30
```

For complete Orchestrator documentation, see the [Execution Guide](/run/execution).