Skip to content

0.5.0

Choose a tag to compare

@github-actions github-actions released this 20 Oct 01:05
· 52 commits to main since this release
4f497ed

The first release on NuGet, as SmartTableSync
and 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.

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

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

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

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

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 and
    Auto mode step by step (every scaffold command and SQL statement),
    and your own app (background service, Hangfire, Quartz.NET, with
    complete Program.cs files). Known limitations moved to their own page;
    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.

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 was followed step by step, and an app that references only
    SmartTableSync signs in to Azure SQL with Microsoft Entra.

Install: dotnet add package SmartTableSync --version 0.5.0, and the scaffold tool:
dotnet tool install --global SmartTableSync.Scaffold --version 0.5.0