Skip to main content

​
Prerequisites

This section describes the prerequisites that you must complete before adding a SQL Server destination connector in . 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

supports connections to SQL Server 2008 (major version 10) and later.

​
Creating a User

You can connect to SQL Server by using any SQL user account that has sufficient permissions. However, as a best practice, CData recommends that you create a dedicated SQL Server user specifically for data replication from the 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 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.

​
Authenticate to SQL Server

After you add the connector, you need to set the required properties.
  • 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:
  1. For Trust Server Certificate, select whether you want to trust the server certificate. By default, the Enable checkbox is selected.
  2. Define advanced connection settings on the Advanced tab. (In most cases, though, you should not need these settings.)
  3. Click Create & Test to create your connection.

​
Data-Type Mapping

uses the following data-type conversions when it loads data into your SQL Server database:
CData TypeSQL Server TypeNotes
bigintbigint
binaryvarbinary(<ColumnSize>)If ColumnSize > 4000, ColumnSize = MAX
booleanbit
datedate
decimaldecimal
doublefloat
floatfloat
integerint
localdatetimedatetime2
smallintsmallint
timetime
timestampdatetimeoffset
varcharnvarchar(<ColumnSize>)If ColumnSize > 4000, ColumnSize = MAX
Most destinations that are available in allow custom column mapping. See Mapping Columns for information about how to remove or adjust column mappings and apply different transformations to tasks in .

​
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 NameDefault ValueDescription
Command Timeout300Controls the timeout, in seconds, for destination connections.
Convert Date-Time Values to GMTNot enabledConverts all local date and time values to GMT before inserting them into the destination database.

​
Always Encrypted Support

connects to SQL Server destinations by using the CData JDBC driver. The JDBC driver supports Always Encrypted columns, which enables to securely read and write data that is protected by client-side encryption. When writes to Always Encrypted columns, the JDBC driver performs the required client-side encryption before the data is sent to the destination. This behavior allows encrypted columns to be included in replication workflows without causing write failures.

​
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 . Applications that are external to SQL Server, including , 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 , 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 your job’s Advanced tab. (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.