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

# SQL Server

export const CommonDatasourceAddConnector = ({datasource = "the data source", title = "the connector", destination = false, siteNameShort = "Sync"}) => {
  return <>
      <p>
        {siteNameShort} で{datasource} のデータを使用できるようにするには、まず以下の手順でコネクタを追加する必要があります：
      </p>
      <ol>
        <li>{siteNameShort} のダッシュボードから<strong>接続</strong>ページを開きます。</li>
        <li><strong>接続を追加</strong>をクリックして<strong>コネクタを選択</strong>ページを開きます。</li>
        <li>
          <strong>{destination ? "同期先" : "データソース"}</strong>タブをクリックして
          <strong>{title}</strong> 行に移動します。
        </li>
        <li>
          行末にある<strong>接続を設定</strong>アイコンをクリックして、<strong>新しい接続</strong>ページを開きます。この操作により、{' '}
          <strong>接続を追加</strong>ダイアログボックスが開きます。
          <br />
          <strong>Note：</strong> <strong>接続を設定</strong>アイコンが表示されない場合は、<strong>コネクタをダウンロード</strong>アイコンをクリックして {title} コネクタをインストールしてください。
        </li>
        <li><strong>接続を追加</strong>ダイアログボックスに接続名を入力します。</li>
        <li><strong>追加</strong>をクリックして、コネクタの<strong>設定</strong>タブを開きます。</li>
      </ol>
      <p>
        新しいコネクタのインストールについての詳細は、{' '}
        <a href="../connections">接続</a>を参照してください。
      </p>
    </>;
};

export const CommonDatasourceIntroDestination = ({datasource = "the data source", siteName = "CData Sync"}) => {
  return <p>
      {siteName} アプリケーションから{datasource} コネクタを使用して、サポートされている任意のデータソースから同期先の{datasource} へデータを移動できます。これを行うには、コネクタを追加し、コネクタへの認証を行い、接続を完了する必要があります。
    </p>;
};

export const driverVersion = "M";

export const siteNameShort = "Sync";

export const siteName = "CData Sync";

<CommonDatasourceIntroDestination datasource="SQL Server" siteName={siteName} />

## Prerequisites

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

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

### Creating a {siteNameShort} 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 {siteNameShort} 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**:

```sql theme={null}
  -- 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 {siteNameShort} 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**:

```sql theme={null}
  -- 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:

```sql theme={null}
GRANT ALTER ON SCHEMA::[<SchemaName>] TO [sync_user];
```

For a general guide to creating SQL Server users, see <a href="https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-ver17" target="_blank" rel="noopener noreferrer">Create a Database User - SQL Server</a>.

## SQL Server コネクタを追加

<CommonDatasourceAddConnector datasource="SQL Server" title="SQL Server" destination={true} siteNameShort={siteNameShort} />

## 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.

## 接続を完了する

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

{siteName} uses the following data-type conversions when it loads data into your SQL Server database:

| **CData Type** |    **SQL Server Type**   | **Notes**                              |
| :------------: | :----------------------: | -------------------------------------- |
|     bigint     |          bigint          |                                        |
|     binary     | varbinary(\<ColumnSize>) | 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(\<ColumnSize>) | If ColumnSize > 4000, ColumnSize = MAX |

Most destinations that are available in {siteNameShort} allow custom column mapping. See [Mapping Columns](../../transformations/etl#mapping-columns) for information about how to remove or adjust column mappings and apply different transformations to tasks in {siteNameShort}.

## 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](../../jobs/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. |

## Always Encrypted Support

{siteName} connects to SQL Server destinations by using the CData JDBC driver. The JDBC driver supports `Always Encrypted` columns, which enables {siteNameShort} to securely read and write data that is protected by client-side encryption.

When {siteNameShort} 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 {siteNameShort}.

Applications that are external to SQL Server, including {siteNameShort}, 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 {siteNameShort}, 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](../../jobs/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 <a href="https://jp.cdata.com/support/submit.aspx" target="_blank" rel="noopener noreferrer">CData Technical Support</a>.

For specific guidance about diagnosing and resolving query timeout errors for SQL Server, see Microsoft's documentation <a href="https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-query-timeouts" target="_blank" rel="noopener noreferrer">Troubleshoot query time-out errors – SQL Server</a>.

### Additional Errors and Issues

For more help and troubleshooting resources for other issues and errors that you might encounter, explore the <a href="https://jp.cdata.com/kb/" target="_blank" rel="noopener noreferrer">CData Knowledge Base</a>, join the <a href="https://community.cdata.com/community" target="_blank" rel="noopener noreferrer">CData Community</a>, or contact <a href="https://jp.cdata.com/support/submit.aspx" target="_blank" rel="noopener noreferrer">CData Technical Support</a> to open a support ticket.