SmartTableSync 0.5.0
See the version list below for details.
dotnet add package SmartTableSync --version 0.5.0
NuGet\Install-Package SmartTableSync -Version 0.5.0
<PackageReference Include="SmartTableSync" Version="0.5.0" />
<PackageVersion Include="SmartTableSync" Version="0.5.0" />
<PackageReference Include="SmartTableSync" />
paket add SmartTableSync --version 0.5.0
#r "nuget: SmartTableSync, 0.5.0"
#:package SmartTableSync@0.5.0
#addin nuget:?package=SmartTableSync&version=0.5.0
#tool nuget:?package=SmartTableSync&version=0.5.0
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
rowversioncolumn, 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
- Overview and guides
- Manual mode, step by step
- Auto mode, step by step
- Run it in your own app: background service, Hangfire, Quartz.NET
- Every setting, with examples
- Identities, rights and troubleshooting
- How each strategy finds changes
- Known limitations
- Changelog
License
| Product | Versions 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. |
-
net10.0
- Microsoft.Data.SqlClient (>= 7.0.2)
- Microsoft.Data.SqlClient.Extensions.Azure (>= 7.0.2)
- Microsoft.Extensions.Configuration.Abstractions (>= 10.0.3)
- Microsoft.Extensions.DependencyInjection.Abstractions (>= 10.0.3)
- Microsoft.Extensions.Options (>= 10.0.3)
- System.Security.Cryptography.Pkcs (>= 10.0.13)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
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