SQL Server


SQL Server


Overview

CData Sync supports the SQL Server data warehouse as a destination. The SQL Server Destination connector comes pre-installed with the Sync application so no extra installation is required.

Requirements

You will need:

  • The host name or IP of the machine running the SQL Server instance.
  • The port your instance is running on (usually 1433).
  • The database you’d like to replicate to.

Connecting to SQL Server

Create a connection to SQL Server by navigating to the Connections page in the Sync application. Select the SQL Server connector option from the Destinations Tab.

  • Set the Server connection property to the host name or IP of the machine running the SQL Server instance. Include port if your instance is not running on port 1433. (<hostname | ip address>, <port>)
  • Set the Database connection property to the Initial Catalog or Database to replicate to.

You can authenticate to SQL Server using either SQL Server Authentication or Windows Authentication.

Authenticate using SQL Server Authentication

  • Set the Authentication connection property to SQL Server Authentication
  • Set the User connection property to your SQL Server user.
  • Set the Password connection property to the password for the above user.

Authenticate using Windows Authentication

  • Set the Authentication connection property to Windows Authentication

Schema

Sync will use the dbo schema by default when replicating to your database. You can alter which schema to replicate to by setting the Destination Schema in your Job. See Advanced Job Options for more details.

Data-Type Mapping

CData Sync uses the following Data Type conversions when loading data into your sql server database:

CData Type SQL Server Type Notes
Boolean Bit  
Date Date  
Time Time  
TimeStamp DateTime  
Decimal Decimal  
Float Float  
Double Float  
SmallInt SmallInt  
Integer Int  
Long BigInt  
Binary VarBinary(<ColumnSize>) If ColumnSize > 1000, ColumnSize = MAX
Varchar Varchar(<ColumnSize>) If ColumnSize > 4000, ColumnSize = MAX