SmartTableSync 0.5.0

There is a newer version of this package available.
See the version list below for details.
dotnet add package SmartTableSync --version 0.5.0
                    
NuGet\Install-Package SmartTableSync -Version 0.5.0
                    
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="SmartTableSync" Version="0.5.0" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="SmartTableSync" Version="0.5.0" />
                    
Directory.Packages.props
<PackageReference Include="SmartTableSync" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add SmartTableSync --version 0.5.0
                    
#r "nuget: SmartTableSync, 0.5.0"
                    
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#:package SmartTableSync@0.5.0
                    
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.
#addin nuget:?package=SmartTableSync&version=0.5.0
                    
Install as a Cake Addin
#tool nuget:?package=SmartTableSync&version=0.5.0
                    
Install as a Cake Tool

SmartTableSync

A self-configuring .NET engine that keeps tables synchronized from one SQL database to another (parent to child). Give it table names: it inspects each table's schema, picks how to detect changes and how to merge them, and keeps the child up to date on the schedule your host chooses.

Built for Azure SQL Database, where it runs in production, and works with Azure SQL Managed Instance and SQL Server (on-premises or in a VM) in any combination.

What it does

  • Syncs the tables you list, one way: inserts, updates and deletes.
  • Chooses per table how to find changed rows: SQL Server Change Tracking, a rowversion column, a timestamp column, or a full re-scan for small tables.
  • Copies a new table in full once, then only what changed, and resumes where it stopped.
  • Needs no DDL rights at run time and signs in with managed identities or any SQL Server sign-in.
  • Notices schema changes in the parent; in Auto mode it adds new columns to the child by itself.

Install

dotnet add package SmartTableSync

That's the only package you need: it includes every sign-in method, from SQL logins and Windows authentication to managed identities and other Microsoft Entra methods (Authentication=Active Directory ... in the connection string).

Use it

Register the engine and run it on your own schedule, here every 5 minutes in a background service:

using SmartTableSync.Abstractions;
using SmartTableSync.Hosting;

var builder = Host.CreateApplicationBuilder(args);
builder.Services.AddSmartTableSync();
builder.Services.AddHostedService<SyncWorker>();
builder.Build().Run();

public sealed class SyncWorker(ISyncOrchestrator orchestrator) : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        using var timer = new PeriodicTimer(TimeSpan.FromMinutes(5));
        do
        {
            await orchestrator.RunAsync(stoppingToken);
        }
        while (await timer.WaitForNextTickAsync(stoppingToken));
    }
}

appsettings.json:

{
  "ParentDbConnectionString": "Server=tcp:myserver.database.windows.net,1433;Database=ParentDb;Authentication=Active Directory Managed Identity;User Id=<client id>;Encrypt=True",
  "ChildDbConnectionString": "Server=tcp:myserver.database.windows.net,1433;Database=ChildDb;Authentication=Active Directory Managed Identity;User Id=<client id>;Encrypt=True",
  "SmartTableSync": {
    "TableList": "Product,Customer,sales.Order",
    "Mode": "Manual"
  }
}

The child database needs a table, a table type and a merge procedure for every synced table. In Manual mode (the default) you create them from the scripts of the SmartTableSync.Scaffold tool; in Auto mode the engine creates them itself with a separate provisioning connection (ChildProvisioningConnectionString).

Hangfire, Quartz.NET and other hosts work the same way: call ISyncOrchestrator.RunAsync from your scheduler. Overlapping runs are safe, also across several instances.

How changes are found

Strategy Used when Deletes reach the child
Change Tracking SQL Server Change Tracking is on for the table on every run
RowVersion the table has a rowversion column at the delete check, hourly by default
Timestamp the table has a UTC datetime column updated on every change (default LastUpdate) at the delete check
Full re-scan none of the above, and the table has up to 50,000 rows at the delete check

Documentation

License

Apache License 2.0. See LICENSE and NOTICE.

Product Compatible and additional computed target framework versions.
.NET net10.0 is compatible.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
0.5.4 120 10/22/2026
0.5.3 103 10/20/2026
0.5.2 101 10/20/2026
0.5.1 96 10/20/2026
0.5.0 117 10/20/2026
0.5.0-preview.1 76 10/19/2026

The first release on NuGet, as [SmartTableSync](SmartTableSync)
and [SmartTableSync.Scaffold](SmartTableSync.Scaffold). It syncs
tables in any schema and tables without a primary key, copies `decimal` values exactly, runs in a
Linux container as well as in Azure Functions, and connects to Azure SQL with a managed identity
using the one package. A quick start sets up the sync between two existing Azure SQL databases with
one script.

### Added

- **Publishing to NuGet** (`.github/workflows/publish.yml`). Pushing a tag such as `v0.5.0`
 builds, tests and publishes `SmartTableSync` and `SmartTableSync.Scaffold` with that version,
 through nuget.org trusted publishing (no API key), after a maintainer approves the run. The
 version comes from the tag only; the project files hold a local default, `0.0.0-dev`. See
 [releasing](docs/releasing.md).

- **Container host for Linux** (`SmartTableSync.Worker` and its `Dockerfile`). It keeps running
 and syncs every `SmartTableSync:Worker:IntervalMinutes` (default 5), or syncs once and exits
 with `--once`, for a Kubernetes CronJob or an Azure Container Apps job. Settings come from
 environment variables, logs go to standard output, and metrics go over OTLP when
 `OTEL_EXPORTER_OTLP_ENDPOINT` is set. CI builds the image. See
 [run it in a container](docs/guides/containers.md).

- **Tables in schemas other than `dbo`.** Name them `schema.Table` in `SmartTableSync:TableList`,
 the per-table settings and the scaffold tool (a plain `Table` still means `dbo.Table`). The
 child keeps the parent's schema; the table's procedures and table type are created in that
 schema, so tables with the same name in two schemas don't collide. The tracking tables stay in
 `dbo`. See [ADR-0020](docs/adr/0020-tables-in-schemas-other-than-dbo.md).
- **Tables without a primary key.** Such a table is keyed by its best unique index (all key
 columns `NOT NULL`, not filtered, not disabled; fewest columns, then lowest `index_id`), or by
 the columns in the new `SmartTableSync:KeyColumns:<Table>` setting (`"ColA,ColB"`), which are
 checked for duplicate values whenever the table is set up. The child table gets that key as its
 primary key. Change Tracking still requires a real primary key. Tables with a primary key are
 unaffected (same key, same schema fingerprint). See
 [ADR-0021](docs/adr/0021-key-fallback-for-tables-without-a-primary-key.md).

- **Quick start for existing databases** (`quickstart/`). One script takes a parent and a child
 Azure SQL database you already have and a table list. It shows which strategy each table will
 get, creates the Function App with its managed identities (Terraform, nothing on the databases),
 creates the database users by SID with only the rights each needs, in Auto or Manual mode, and
 runs and checks the first sync. It also has `-DryRun`, `-SyncNow` and `-Destroy`. See
 [quickstart/README.md](quickstart/README.md).

### Changed

- **The `SmartTableSync` package includes Microsoft Entra sign-in.** It now depends on
 `Microsoft.Data.SqlClient.Extensions.Azure`, so managed identities and every other
 `Authentication=Active Directory ...` connection string work with this one package; a host no
 longer adds that package itself (one that already did can remove it). Hosts that only use SQL
 logins or Windows authentication get the Azure sign-in libraries too, unused.
- The scaffold tool prefixes the files of a non-dbo table with the schema
 (`sales.sp_SyncOrderBatch.sql`); file names for dbo tables are unchanged.
- A table name with more than one dot, or longer than 100 characters, is rejected at startup with
 a clear message. Names that contain a dot were never meant to work and are now read as
 `schema.Table`.
- A table with no usable key at all now stops when it is set up, with a message naming
 `SmartTableSync:KeyColumns:<Table>`, instead of failing later in its first page query.
- **Documentation by task.** The README starts with a guide for each way to run SmartTableSync:
 the demo, the quick start, [Manual mode](docs/guides/manual-mode.md) and
 [Auto mode](docs/guides/auto-mode.md) step by step (every scaffold command and SQL statement),
 and [your own app](docs/guides/hosting.md) (background service, Hangfire, Quartz.NET, with
 complete `Program.cs` files). Known limitations moved to [their own page](docs/limitations.md);
 the README keeps a summary and links to it and to the roadmap.

### Fixed

- **`decimal` and `numeric` values were rounded in the child.** Precision and scale weren't read
 from the parent, so the scaffold tool and Auto mode created `decimal(10,2)` as `decimal`,
 which SQL Server makes `decimal(18,0)`: 99.95 arrived as 100, with no error. Tables, table types
 and procedures now get the parent's exact type, for `datetime2(n)`, `time(n)` and
 `datetimeoffset(n)` too. A `decimal` or `numeric` column whose precision or scale differs from
 the parent is now detected, and the scaffold tool writes `<Table>_AlterColumns.sql` for it
 (see *Upgrading* below). This also closes the former limitation that such a change in the
 parent wasn't detected.
- Measuring the largest value of `(max)` columns while profiling a table queried it without a
 schema, so it depended on the identity's default schema.
- Running the demo a second time failed with *"There is already an object named 'Product'"*:
 the script applied a `CREATE TABLE` script left from the first run. It now empties its
 generated-scripts folders first, applies `*_AddColumns.sql` and `*_AlterColumns.sql`, and has a
 table fixed by `*_AlterColumns.sql` copied again on the next sync.
- The example Function sent only warnings and errors to Application Insights, and no
 `smarttablesync.*` metrics at all: the Application Insights SDK keeps only warnings from the
 worker by default, and no exporter was registered for the `SmartTableSync` meter. Its logs now
 go through the Functions host, so they arrive with their level and appear in the portal's Log
 stream like any Function's, and the metrics go through OpenTelemetry with the Azure Monitor
 exporter. See [logs and metrics](docs/setup.md#logs-and-metrics).

### Upgrading

- **Tables with `decimal` or `numeric` columns** are planned again on the first run after the
 upgrade (their schema fingerprint now includes precision and scale; other tables keep theirs).
 A child created by an earlier version has those columns as `decimal(18,0)` and rounded values:
 - The table stops with *"column ... is decimal(18,0) in the child but decimal(10,2) in the
   parent"*. Run the scaffold tool for it and apply `<Table>_AlterColumns.sql` to the child.
 - **Auto mode** then copies the table again by itself on the next run, so every value is exact.
 - **Manual mode:** also run the two `DELETE` statements at the end of that script, so the next
   run copies the table again; then apply the regenerated table type and procedure scripts.
- **Hosts that added `Microsoft.Data.SqlClient.Extensions.Azure` themselves** can remove it; the
 package now brings it.

### Tested

- **Automated, on every change:** 298 unit tests and 72 integration tests against a real SQL
 Server 2022 (Testcontainers), including syncs that run as the three identities with exactly the
 documented grants, Auto mode creating and evolving the child's tables, tables in other schemas
 and without a primary key, and exact `decimal` values. CI also builds the container image and
 checks the Terraform, the NuGet packages for known vulnerabilities, and the code with Semgrep
 and CodeQL.
- **On Azure, with the demo** (Azure SQL Database, Azure Functions Flex Consumption, managed
 identity): about 20,000 inserted, updated and deleted rows per table on Change Tracking,
 RowVersion and Timestamp tables, plus key changes, deleted and re-inserted rows, rows inserted
 and deleted between two runs, the same row updated 50 times, NULL and empty values, Unicode,
 `decimal` limits and rows over 1 MB. Parent and child matched row by row.
- **On Azure, with the quick start** in Auto mode on two new databases: four tables on four
 strategies, then inserts, updates, deletes and a column added in the parent, which Auto mode
 added to the child. Parent and child matched row by row.
- **The container host**, run once and running continuously, against SQL Server in Docker.
- **The NuGet packages:** the release workflow published them, they install from nuget.org, the
 [NuGet guide](docs/guides/nuget.md) was followed step by step, and an app that references only
 `SmartTableSync` signs in to Azure SQL with Microsoft Entra.


Full changelog: https://github.com/Sidorus/SmartTableSync/blob/main/CHANGELOG.md