SQL Server

Version 26.1.9516

Prerequisites
- Version Support
- Creating a Sync User
Add the SQL Server Connector
Authenticate to SQL Server
Complete Your Connection
Data-Type Mapping
Advanced Job Options
Common SQL Server Issues
- Timeout Errors
- Additional Errors and Issues

SQL Server

You can use the SQL Server connector from the CData Sync application to move data from any supported source to the SQL Server destination. To do so, you need to add the connector, authenticate to the connector, and complete your connection.

Prerequisites

This section describes the prerequisites that you must complete before adding a SQL Server destination connector in CData Sync. It outlines the supported SQL Server versions and the recommended approach for creating a dedicated user account to support secure and reliable data replication.

Version Support

Connections to SQL Server destinations in Sync are supported for SQL Server 2008 (major version 10) and later.

Creating a Sync User

Although you can connect to SQL Server by using any SQL user account that has sufficient permissions, it is a best practice to create a dedicated SQL Server user specifically for data replication from the Sync application. You can submit the following commands in SQL Server Management Studio to create a new SQL Server login and an associated database user named sync_user:

  -- Create the login sync_user with password '<Password>'.

CREATE LOGIN sync_user
WITH PASSWORD = '<Password>';
GO
  -- Create a database user for this new sync_user login.
CREATE USER sync_user
FOR LOGIN sync_user;
GO

By default, the new sync_user account has no assigned permissions or roles. To allow Sync to write, modify, and delete data in a SQL Server destination connection, you must grant INSERT, UPDATE, and DELETE permissions to sync_user. You can set these permissions at the database level by granting the db_datawriter role to sync_user, which allows INSERT, UPDATE, and DELETE operations on all tables in the database.

SQL Server destination connections must also have permission to read table schemas in the target database to properly map the incoming data to an existing table. To accomplish this, you also need to grant the db_datareader role to sync_user.

Submit the following commands to grant both roles to sync_user:

  -- Grant the db_datawriter role to sync_user.
ALTER ROLE db_datawriter ADD MEMBER sync_user;
  -- Grant the db_datareader role to sync_user,
ALTER ROLE db_datareader ADD MEMBER sync_user;

Note: Individual permissions such as INSERT, UPDATE, and DELETE can also be granted separately and restricted to the object level, as necessary.

Because the db_datawriter role does not grant permission to create, alter, or drop database tables, you must also grant ALTER permissions to sync_user. You can grant this permission at the schema level by using the following command:

GRANT ALTER ON SCHEMA::[<SchemaName>] TO [sync_user];

For a general guide to creating SQL Server users, see Create a Database User - SQL Server.

Add the SQL Server Connector

To enable Sync to use data from SQL Server, you first must add the connector, as follows:

Open the Connections page of the Sync dashboard.
Click Add Connection to open the Select Connectors page.
Click the Destinations tab and locate the SQL Server row.
Click the Configure Connection icon at the end of that row to open the New Connection page. If the Configure Connection icon is not available, click the Download Connector icon to install the SQL Server connector. For more information about installing new connectors, see Connections.

Authenticate to SQL Server

After you add the connector, you need to set the required properties.

Connection Name - Enter the connection name of your choice.
Server - Enter the host name or IP address of the server that hosts the SQL Server database. The default server is localhost.
Port - Enter the port number for SQL Server. The default port value is 1433.
Database - Enter the default database to which you want to connect when you connect to SQL Server.
User - Enter the username that you use to authenticate to SQL Server.
Password - Enter the password that you use to authenticate to SQL Server.

Complete Your Connection

To complete your connection:

For Trust Server Certificate, select whether you want to trust the server certificate. The default setting is True.
Define advanced connection settings on the Advanced tab. (In most cases, though, you should not need these settings.)
Click Create & Test to create your connection.

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
bigint	bigint
binary	varbinary()	If ColumnSize > 4000, ColumnSize = MAX
boolean	bit
date	date
decimal	decimal
double	float
float	float
integer	int
localdatetime	datetime2
smallint	smallint
time	time
timestamp	datetimeoffset
varchar	nvarchar()	If ColumnSize > 4000, ColumnSize = MAX

Most destinations that are available in Sync allow custom column mapping. See Mapping Columns for information about how to remove or adjust column mappings and apply different transformations to tasks in Sync.

Advanced Job Options

The following table lists advanced job options that are most frequently used with SQL Server destination connections. For a complete list of available job options, see Advanced Job Options.

Option Name	Default Value	Description
Command Timeout	300	Controls the timeout, in seconds, for destination connections.
Convert Date-Time Values to GMT	Not enabled	Converts all local date and time values to GMT before inserting them into the destination database.

Common SQL Server Issues

When you connect to SQL Server destinations, you might occasionally encounter errors. Common errors and issues fall into the following categories:

timeout errors
additional errors and issues

Timeout Errors

Timeout issues are more likely to occur when you replicate larger datasets and are typically straightforward to resolve. For SQL Server destinations, timeout issues are most often related to the Command Timeout value that is configured in Sync.

Applications that are external to SQL Server, including Sync, use a command timeout to determine how long the application waits for a response from SQL Server before cancelling a query. If the query does not complete within the specified time, SQL Server returns a timeout error.

In Sync, the default Command Timeout value is 300 seconds. Queries that consistently fail at or near the five-minute mark are commonly encountering a command timeout condition. In these cases, increasing the Command Timeout value can give SQL Server additional time to complete query processing and may help prevent timeout errors.

You can modify the Command Timeout value at the job level from the Advanced tab for a job. (For details about these options, see Advanced Job Options.) While increasing this value often resolves timeout-related issues, adjusting the timeout alone might not be sufficient in all scenarios. For example, reducing the amount of data that is processed by each query can also improve execution times and help avoid timeouts.

If you continue to experience timeout errors after adjusting the Command Timeout value, contact CData Technical Support.

For specific guidance about diagnosing and resolving query timeout errors for SQL Server, see Microsoft’s documentation Troubleshoot query time-out errors – SQL Server.

Additional Errors and Issues

For more help and troubleshooting resources for other issues and errors that you might encounter, explore the CData Knowledge Base, join the CData Community, or contact CData Technical Support to open a support ticket.