# Getting Started

This guide covers prerequisites, environment setup, first run, deployment, and common troubleshooting.

## Prerequisites

| Component | Version / notes |
|-----------|-----------------|
| OS | Windows (required for ACE OLEDB and Access COM interop) |
| .NET Framework | 4.8 runtime |
| .NET SDK | For building from source |
| ACE OLEDB | Microsoft Access Database Engine 2016 Redistributable, **12.0** provider |
| Access (optional) | Full Access **or** Access Runtime — needed for DAO schema enrichment (defaults, GRANT, QueryDefs, Relations, Indexes). Data sync uses ACE OleDb only. |
| SQL Server Express | Target staging database (default instance `.\SQLEXPRESS`) |
| MySQL | 5.7+ or 8.x — web shop catalogue target |

### Install ACE OLEDB

Download the [Microsoft Access Database Engine 2016 Redistributable](https://www.microsoft.com/en-us/download/details.aspx?id=54920).

**Bitness must match the build.** The default project build is **x64** (`PlatformTarget` in `SyncEngine.csproj`). If you only have 32-bit Office/ACE installed, rebuild as x86 — see [build-and-test.md](build-and-test.md).

### SQL Server Express

- Default connection in `.env.example` uses `Server=.\SQLEXPRESS;Trusted_Connection=True`.
- The engine connects via `master` first and **creates the target database** if it does not exist (`SQL_EXPRESS_DATABASE`, default `AnniesAngels`).
- Mirrored tables are created in `SQL_EXPRESS_SCHEMA` (default `dbo`).

### MySQL

- Target database defaults to `annies_angels_shop` (`MYSQL_DATABASE`).
- `shop_products` and `shop_sync_log` must exist before the `mysql` or `all` mode runs — created by **CI4 shop migrations**, not by SyncEngine.
- Ensure the MySQL user has `INSERT`, `UPDATE`, and `SELECT` on the target database (and `CREATE` only if you manage schema outside CI4).

## Configure `.env`

1. Copy [`config/.env.example`](../config/.env.example) to **`.env` in the repo root**.
2. Fill in real values — placeholder paths (e.g. `c:\path\...`) cause exit code **2**.
3. Build the solution — MSBuild copies `.env` into the output folder beside `SyncEngine.exe`.

```bat
copy config\.env.example .env
notepad .env
dotnet build SyncEngine.sln -c Release
```

At runtime, `SyncEngine` loads `.env` from the exe directory first, then falls back to the repo root.

**Do not commit `.env`** — it contains credentials.

See [configuration.md](configuration.md) for the full variable reference.

## First-run sequence

Run from the build output directory or use the full path:

```bat
cd SyncEngine\bin\Release\net48

rem 1. Provision SQL Express schema (tables, keys, defaults, FKs, CHECKs, views) — no data
SyncEngine.exe schema-only

rem 2. Verify tables/views exist in SQL Express (SSMS or sqlcmd)
rem    Check the summary line: pksSkipped=0 indexesSkipped=0 defaultsSkipped=0 on a healthy re-run
rem    See docs/access-sql-express-compatibility.md for stat meanings

rem 3. Sync Access data into SQL Express
SyncEngine.exe access

rem 4. Publish products to MySQL
SyncEngine.exe mysql

rem 5. Production: run both legs in one command
SyncEngine.exe all
```

### Recommended dry-run check

Before writing to production databases, set `SYNC_DRY_RUN=true` in `.env`, rebuild, and run `all`. Review console and log output, then set `SYNC_DRY_RUN=false` for a real run.

## Deployment

Ship these files to the target machine:

| Item | Notes |
|------|-------|
| `SyncEngine.exe` | Main executable |
| `.env` | Connection settings and feature switches |
| `config\` | Includes `product-sync-map.json` |
| `App.config` | .NET binding redirects |
| `logs\` | Created automatically; ensure write permissions |

Schedule with **Windows Task Scheduler**:

- Program: path to `SyncEngine.exe`
- Arguments: `all`
- Start in: directory containing `SyncEngine.exe` and `.env`
- Run whether user is logged on or not (service account with DB access)
- If a run is still in progress when the next trigger fires, the second instance exits immediately with code **3** (see below)

## Troubleshooting

### Exit codes

| Code | Meaning |
|------|---------|
| 0 | Success |
| 1 | Runtime/sync failure |
| 2 | Configuration error |
| 3 | Another instance already running |

### Exit code 3 — another instance running

Only one `SyncEngine.exe` may run on a machine at a time. A global named mutex (`Global\SyncCoordinator.SyncEngine`) is acquired at startup; a second process logs `Another SyncEngine instance is already running; exiting.` and exits **before** loading configuration or connecting to databases.

| Symptom | Fix |
|---------|-----|
| Exit code 3 on Task Scheduler trigger | A previous run has not finished — wait for it to complete, or increase the schedule interval so runs do not overlap |
| Exit code 3 on manual start | Check Task Manager for a running `SyncEngine.exe` from a scheduled or prior manual run |
| Process crashed but still blocked | Unlikely — Windows releases the mutex when the process terminates (crash, kill, or power loss). If exit 3 persists, a live `SyncEngine.exe` is still running (possibly hung) — end that process in Task Manager |

There is no `.env` setting to bypass the guard. Scope is **per machine** — two servers syncing the same databases are not coordinated by this lock.

See [sync-architecture.md](sync-architecture.md#single-instance) for implementation details.

### Exit code 2 — configuration error

| Symptom | Fix |
|---------|-----|
| `No .env file found` | Copy `config/.env.example` to repo root `.env`, build, or place `.env` beside `SyncEngine.exe` |
| `ACCESS_DB_PATH still looks like a placeholder` | Set the real path to your `.mdb` file |
| `ACCESS_DB_PATH file not found` | Verify the path exists and the service account can read it |
| `MYSQL_USER is required` | Fill in MySQL credentials when running the `mysql` or `all` mode |
| Both sync legs disabled | Set at least one of `SYNC_ACCESS_TO_SQL_EXPRESS` / `SYNC_SQL_EXPRESS_TO_MYSQL` to `true` |

### Exit code 1 — runtime failure

| Symptom | Fix |
|---------|-----|
| ACE provider not registered | Install Access Database Engine; match x64/x86 to build |
| `Microsoft.ACE.OLEDB.12.0` error | Bitness mismatch — rebuild as x86 or install 64-bit ACE |
| SQL Express connection refused | Verify instance name, SQL Browser service, firewall |
| SQL Express connection lost mid-sync | Re-run when SQL Express is healthy — committed batches persist; failed batch rolls back. See [Leg 1 failure modes](sync-architecture.md#leg-1-failure-modes) |
| Access database locked mid-sync | Close Microsoft Access (or other process holding the `.mdb`); re-run sync |
| Access unavailable during Leg 1 in `all` mode | Leg 1 exits **1** but Leg 2 may still run against stale SQL Express data — check `sync_metadata.runs` |
| Access MSys permission denied | Set `ACCESS_DB_USER` or ensure COM fallback can open `Access.Application` |
| MySQL access denied | Check host, user, password, and database grants |
| MySQL connection lost mid-sync | Leg 2 reconnects and retries the failed row automatically (`SYNC_MAX_RETRIES`). If the leg still fails, re-run when MySQL is healthy. See [Leg 2 failure modes](sync-architecture.md#leg-2-failure-modes) |
| MySQL TLS / SSPI / `SSL Authentication Error` | SyncEngine pins `TlsVersion=Tls12` so Schannel offers TLS 1.2 (needed on SBS 2011 / Server 2008 R2 where OS defaults omit it). Redeploy the current build. If it still fails, install SP1/TLS updates on that server; when a reboot window exists, enable Schannel TLS 1.2 Client (`Enabled=1`, `DisabledByDefault=0`) under `HKLM\...\SCHANNEL\Protocols\TLS 1.2\Client` |
| Appears stuck after `Access → SQL Express sync disabled by config` | The MySQL leg has started — look for `Starting SQL Express → MySQL` then `Connecting to SQL Express` / `Connecting to MySQL`. If it stops on MySQL connect, wait up to ~30s for `Connection Timeout` or a TLS error; that points at Schannel/network, not a freeze |
| Silence after `MySQL connected` | Normal until the next lines: `MySQL schema OK; reading products...` then periodic `Flushed product batch size=… elapsedMs=… msPerRow=…` (one per flush, often 2000 rows, written as multi-row IODKU chunks of 500). Large catalogues can take many minutes over a remote MySQL host; absence of those flush lines after schema OK means the SQL Express product query is still running |
| MySQL table not found | Run CI4 shop migrations so `shop_products` and `shop_sync_log` exist before sync (exit **2**) |
| `shop_sync_log` missing after failed run | MySQL and API fallback both failed — check `logs/shop-sync-log-failed-YYYYMMDD.jsonl` or configure `SHOP_SYNC_API_URL` + `SHOP_SYNC_API_KEY` |

### Views missing in SQL Express

Access queries that use TRANSFORM/CROSSTAB or fail translation are skipped. Export SQL manually to `config/access-views.json` (see `config/access-views.json.example`).

### Tables skipped during sync

Tables without a detectable primary key are skipped with a warning during **data sync** (not during schema-only). System tables (`MSys*`, `~TMPCLP*`) and entries in `ACCESS_SQL_EXCLUDE_TABLES` are excluded from mirroring and FK/index provisioning.

### Schema-only warnings

| Log message | Meaning | Action |
|-------------|---------|--------|
| `Could not read MSysIndexes; trying DAO fallback` | OleDb denied `MSysIndexes` | Normal on many databases; indexes read via `Access.Application` |
| `No Access relationships defined` | `.mdb` has no FK relationships | Expected if relationships were never defined in Access |
| `Skipped default … untranslatable` | Default expression not in translator | Extend [`AccessDefaultTranslator`](../SyncEngine/Access/AccessDefaultTranslator.cs) or set default manually in SQL Express |
| `Skipped NOT NULL … contains NULL values` | Required in Access but NULL rows exist | Clean data in Access or leave column nullable |
| `Skipped PK` / `Skipped index` | SQL error (e.g. duplicate key, nullable PK column) | Fix data or see [access-sql-express-compatibility.md](access-sql-express-compatibility.md) |
| Non-zero `pksSkipped` / `indexesSkipped` / `defaultsSkipped` in summary | Real provisioning failure | Search log for `WRN` lines; `*Unchanged` counts are normal on re-runs |
| `Access.Application CreateInstance failed` / DAO-only path | Access **Runtime** cannot CreateInstance | Expected; SyncEngine uses `DAO.DBEngine.OpenDatabase` with `;PWD=` (no Access window). |
| `Access.Application … failed` with `0x80080005` / `CO_E_SERVER_EXEC_FAILURE` after Shell fallback also fails | Runtime/full Access could not be automated | Confirm interactive Shell+GetObject with [`scripts/Test-AccessRuntimeCom.ps1`](../scripts/Test-AccessRuntimeCom.ps1); avoid unattended SYSTEM sessions ([KB 257757](https://support.microsoft.com/en-us/topic/considerations-for-server-side-automation-of-office-48bcfe93-8a89-47f1-0bce-017433ad79e2)). Data sync still works via OleDb; supply [`config/access-views.json`](../config/access-views.json) for views if needed. |

### Access Runtime vs full Access (COM / DAO)

SyncEngine opens `Access.Application` for schema enrichment only (field defaults, MSys GRANT, QueryDefs, Relations, Indexes). **Data sync does not use COM.**

| Install | `CreateObject` / `Activator.CreateInstance` | What SyncEngine does |
|---------|-----------------------------------------------|----------------------|
| Full Microsoft Access | Works | CreateInstance → `OpenCurrentDatabase` |
| Access Runtime only | Fails with `0x80080005` (`CO_E_SERVER_EXEC_FAILURE`) | Opens the backend via **DAO.DBEngine.OpenDatabase** with `;PWD=` (no `MSACCESS.EXE` UI — avoids modal dialogs). MSys GRANT is skipped on this path. |

**Important:** MSACCESS `/pwd` is the **workgroup account** password, not the database encrypt password ([KB 161177](http://ftp.zx.net.nz/pub/archive/ftp.microsoft.com/MISC/KB/en-us/161/177.HTM)). Do not put `ACCESS_DB_PASSWORD` on `/pwd`. Shelling Access Runtime still shows modal dialogs that block automation; SyncEngine therefore uses DAO for Runtime instead of `MSACCESS.EXE`.

Confirm on the sync server (same account as SyncEngine). SBS 2011 / Windows 7 use **PowerShell 2.0** — from the repo root:

```bat
powershell -ExecutionPolicy Bypass -File .\scripts\Test-AccessRuntimeCom.ps1
```

Do not use `Set-ExecutionPolicy -Scope Process` on PowerShell 2.0 (that scope is PS 3+). Step 1 (CreateObject) may fail on Runtime; step 2 (DAO OpenDatabase) must complete **with no Access window or dialogs**.

## Next steps

- [build-and-test.md](build-and-test.md) — build variants and unit tests
- [configuration.md](configuration.md) — full settings reference
- [sync-architecture.md](sync-architecture.md) — how the sync pipeline works
- [access-sql-express-compatibility.md](access-sql-express-compatibility.md) — schema mirroring details and statistics
- [shop_products.md](shop_products.md) — MySQL field mapping