> For the complete documentation index, see [llms.txt](https://doc.batch.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://doc.batch.com/getting-started/features/customer-engagement-platform/profiles/cloud-sync/create-a-sync-from-mssql-to-batch-profile-attributes.md).

# Create a Sync from MSSQL to Batch Profile Attributes

### Before you start

To create an MSSQL → Batch sync, you'll need:

* Access to the **Batch dashboard**
* An **MSSQL-compatible database** (Azure SQL Database, Azure SQL Managed Instance, Microsoft Fabric Warehouse, or SQL Server 2012+) containing one row per profile
* Credentials to connect: either a **username/password** or a **Microsoft Entra ID service principal** (Client ID + Client Secret)
* A table (or view) that follows the **Cloud Sync input format** (see below)

***

### 1) Prepare your MSSQL table

Cloud Sync expects your MSSQL source (table or view) to include:

1. A **profile identifier** (to know which profile to update)
2. A **cursor field** (to know what changed since the last run)
3. Any number of **attribute columns** (sent to Batch as profile attributes)

***

#### 1.1 One row per profile

Your source must contain **one row per profile**.\
Each row is interpreted as an update to a single Batch profile.

***

#### 1.2 Required columns

Your table (or view) must include:

| Column            | Required | Description                      |
| ----------------- | :------: | -------------------------------- |
| `custom_id`       |     ✅    | The profile identifier in Batch  |
| `last_updated_at` |     ✅    | Cursor used for incremental sync |

**Important:** `last_updated_at` must be updated **every time any synced attribute changes**, otherwise updates may not be picked up by the next run.

***

#### 1.3 Attribute naming rules

Cloud Sync reads your MSSQL columns and converts them into Batch profile attributes.

Because the Batch Profile API uses characters like `$`, `(`, or `)` that are not valid in SQL column names, Cloud Sync relies on **prefixes** in column names to represent typed or native fields.

**Supported prefixes**

| Prefix    | Meaning                                   | Example                |
| --------- | ----------------------------------------- | ---------------------- |
| `date__`  | Date attribute                            | `date__birthday`       |
| `url__`   | URL attribute                             | `url__avatar`          |
| `batch__` | Native profile fields (instead of `$...`) | `batch__email_address` |

***

#### 1.4 Example schema (e-commerce)

Here's a table format you can use as a reference:

```sql
CREATE TABLE customer_data.batch_profiles (
    custom_id          NVARCHAR(255),
    last_updated_at    DATETIME2,

    -- Native profile fields
    batch__email_address NVARCHAR(255),

    -- Standard attributes
    plan               NVARCHAR(100),
    country            NVARCHAR(100),
    lifetime_value     FLOAT,
    is_vip             BIT,

    -- Typed attributes
    url__avatar        NVARCHAR(2048),
    date__birthday     NVARCHAR(10),
    date__last_purchase NVARCHAR(10)
);
```

**How this maps in Batch:**

* `custom_id` identifies the profile
* `batch__email_address` updates the profile's native email field
* `plan`, `country`, `lifetime_value`, `is_vip` become attributes
* `url__avatar` is interpreted as a URL attribute
* `date__birthday` and `date__last_purchase` are interpreted as date attributes

***

#### 1.5 Using a View

If your raw table doesn't match the expected naming or format, create an **MSSQL View** that converts your schema into the correct conventions.

Example:

```sql
CREATE OR ALTER VIEW customer_data.batch_profiles_view AS
SELECT
    CAST(u.user_id AS NVARCHAR(255))          AS custom_id,
    CASE
        WHEN u.updated_at > o.last_order_updated_at THEN u.updated_at
        ELSE o.last_order_updated_at
    END                                        AS last_updated_at,

    u.email                                    AS batch__email_address,
    u.country,
    u.plan,

    u.avatar_url                               AS url__avatar,
    CONVERT(NVARCHAR(10), u.birth_date, 23)    AS date__birthday,

    CONVERT(NVARCHAR(10), o.last_order_date, 23) AS date__last_purchase,
    o.total_spent                              AS lifetime_value,
    u.is_vip
FROM raw.users u
LEFT JOIN raw.user_orders_summary o
    ON u.user_id = o.user_id;
```

This approach lets you:

* rename fields with the correct prefixes (`batch__`, `date__`, `url__`)
* compute a reliable `last_updated_at`
* ensure you always expose **one row per profile**

***

#### 1.6 Handling nulls

If a column value is `NULL`, Batch interprets it as **attribute removal** for that profile.

If you don't want an attribute removed:

* ensure your view returns a non-null value, or
* exclude the column from the sync entirely.

***

#### 1.7 Attributes limits and constraints

When syncing data from MSSQL to Batch, all attributes sent through Cloud Sync must respect the same limits and constraints as the Batch Profile API. See Profile API documentation, the attributes object.

***

### 2) Set up database access

Batch connects to your MSSQL database using either **username/password** or **Microsoft Entra ID** (recommended for Azure SQL).

#### Option A — Username / Password

Create a dedicated read-only user in your database and grant it `SELECT` access on the relevant table or view:

```sql
CREATE LOGIN batch_sync WITH PASSWORD = 'YourStrongPassword!';
CREATE USER batch_sync FOR LOGIN batch_sync;
GRANT SELECT ON customer_data.batch_profiles_view TO batch_sync;
```

#### Option B — Microsoft Entra ID (recommended for Azure SQL)

This method uses a **service principal** (App Registration) to authenticate without storing a password.

**2.1 Create an App Registration in Entra ID**

1. Go to [entra.microsoft.com](https://entra.microsoft.com)
2. Navigate to **Entra ID → App registrations → New registration**
3. Give it a name (e.g. `Batch Cloud Sync`) and register it
4. Note the **Application (Client) ID** — you'll need it later
5. Go to **Certificates & secrets → New client secret**
6. Note the **Value** of the client secret — it is only shown once

**2.2 Create a database user for the service principal**

In your Azure SQL Database, run the following as an admin:

```sql
CREATE USER [Batch Cloud Sync] FROM EXTERNAL PROVIDER;
GRANT SELECT ON customer_data.batch_profiles_view TO [Batch Cloud Sync];
```

{% hint style="warning" %}
The user name in brackets must match exactly the **display name** of your App Registration in Entra ID.
{% endhint %}

***

### 3) Create the Sync in the Batch dashboard

Cloud Sync is configured from the dashboard via a dedicated **Sync module**.

1. Open the **Batch dashboard**
2. Go to **Data → Cloud Sync**
3. Click **Create Sync**
4. Select **MSSQL** as the source

***

#### 3.1 Configure your MSSQL connection

Enter:

* **Host** — your server hostname (e.g. `myserver.database.windows.net` for Azure SQL, or `<workspace>.datawarehouse.fabric.microsoft.com` for Microsoft Fabric Warehouse)
* **Port** — default is `1433`
* **Database** — the name of your database
* **Schema** — the schema containing your table or view
* **Table or View** — the name of the source table or view

Then fill in your credentials:

* **Username / Password** if using SQL authentication, or
* **Entra ID Client ID** and **Entra ID Client Secret** if using Microsoft Entra ID

{% hint style="warning" %}
When using Microsoft Entra ID authentication, an encrypted connection is required. Make sure your server accepts encrypted connections.
{% endhint %}

Batch validates the connection before continuing.

***

#### 3.2 Configure profile mapping

Cloud Sync applies a simple mapping model:

* `custom_id` → identifies which Batch profile to update
* `last_updated_at` → used only for incremental sync logic
* all other columns → mapped to profile attributes

***

### 4) How incremental sync works

Cloud Sync uses **incremental processing**, which means it does not re-import your full dataset at every run. Instead, it fetches only the rows that changed since the last successful sync.

***

#### 4.1 The `last_updated_at` cursor

Batch stores the last successful cursor value internally.

At each run, Batch fetches only rows where:

* `last_updated_at` is greater than the last stored cursor

This makes sync runs faster, more scalable, and more cost-efficient.

***

#### 4.2 Inserts, updates, and deletes

Incremental syncs naturally capture:

* ✅ inserts
* ✅ updates

They do **not** automatically capture:

* ❌ deletes

If you need deletions reflected in Batch, implement soft deletes by setting all attributes to `NULL` in the view when a profile is deleted.

***

#### 4.3 Best practices for reliable incremental syncs

To avoid missing changes:

* Ensure `last_updated_at` updates **every time a synced column changes**
* Ensure `last_updated_at` reflects the most recent change across **all** synced columns — not just one of them. If your source table tracks update timestamps per field, compute `last_updated_at` in your view using the maximum across all relevant timestamps
* Use a View if you need computed fields or type conversions
* Index on `last_updated_at` for large tables

***

### 5) Test and enable your Sync

Before enabling the schedule:

1. Run a **test sync**
2. Verify:
   * Profiles are created or updated correctly
   * `batch__`, `date__`, and `url__` fields are interpreted correctly
   * Null values behave as expected (null → attribute removal)

Once enabled, Batch automatically handles:

* batching
* retries


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://doc.batch.com/getting-started/features/customer-engagement-platform/profiles/cloud-sync/create-a-sync-from-mssql-to-batch-profile-attributes.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.