Search

SQL Server

Version 26.1.9516

SQL Server

CData Sync アプリケーションからSQL Server コネクタを使用して、サポートされている任意のデータソースから同期先のSQL Server へデータを移動できます。これを行うには、コネクタを追加し、コネクタへの認証を行い、接続を完了する必要があります。

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.

SQL Server コネクタを追加

Sync でSQL Server のデータを使用できるようにするには、まず以下の手順でコネクタを追加する必要があります。

  1. Sync のダッシュボードから接続ページを開きます。

  2. 接続を追加をクリックしてコネクタを選択ページを開きます。

  3. 同期先タブをクリックしてSQL Server 行に移動します。

  4. 行末にある接続を設定アイコンをクリックして、新しい接続ページを開きます。接続を設定アイコンが利用できない場合は、コネクタをダウンロードアイコンをクリックしてSQL Server コネクタをインストールします。新規コネクタのインストールについて詳しくは、接続を参照してください。

SQL Server への認証

コネクタを追加したら、必須プロパティを設定する必要があります。

  • 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:

  1. For Trust Server Certificate, select whether you want to trust the server certificate. The default setting is True.

  2. 高度な設定タブで接続の高度な設定を定義します。（ただし、ほとんどの場合これらの設定は必要ありません。）

  3. 作成およびテストをクリックして接続を作成します。

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.