Every release, reconstructed from the project's commit history, what changed, and why. Full detail and diffs on GitHub.
SQL Server compresses a bulk-insert batch of 102,400 rows or more directly into a compressed rowgroup instead of routing it through the delta store, expensive on its own and, on COLUMNSTORE_ARCHIVE, synchronous in the load path, leaving behind many small, prematurely-compressed rowgroups instead of fewer rowgroups grown to full size. Found while investigating a chunked archival load where raising the batch size 10x produced a 30x slowdown instead of 10x, traced via sys.dm_db_column_store_row_group_physical_stats to exactly this threshold. Copy-sqmTableData now detects a columnstore destination with a single sys.indexes metadata check, no table scan, and caps the batch size actually passed to SqlBulkCopy at ColumnstoreBatchSizeCeiling (default 100,000, deliberately below the threshold), independent of -BatchSize/DefaultBatchSize. Live-verified against a real clustered-columnstore table.
Copy-sqmTableData already had -BulkCopyTimeOut (default 300 seconds), but neither Invoke-sqmTableTransfer nor Invoke-sqmChunkedTableTransfer exposed it, so every chunk transfer had 300 seconds hard-wired with no way to change it. The value applies per batch, not per table: a destination checkpoint, a data/log autogrowth, or a moment of IO contention can hold a single batch past 300 seconds easily, and the whole copy then dies with "Execution Timeout Expired". For a chunked transfer that is the expensive failure mode, a chunk is retried from its first row, so a stall near the end of a large chunk throws away everything it had already copied. Seen on a real run: 23 chunks, 145,625,625 rows, throughput steady between 13,950 and 14,826 rows/s the whole way, and still one chunk aborted on exactly this timeout. -BulkCopyTimeOut is now reachable on both functions, default unchanged at 300, 0 disables the limit entirely, usually the better trade for an unattended migration. Also in this release: -DestinationTable on both functions, for loading into a differently-named destination, such as a freshly re-partitioned "_New" copy ahead of a rename-swap cutover.
Invoke-sqmChunkedTableTransfer used to require -ChunkColumn and abort at a fixed -MaxChunkValues of 500, both had to be known up front. A perfectly sound column with, say, 800 monthly values failed the run and forced a second attempt with the right number, even though that number was already known from the GROUP BY the function runs anyway. Both parameters are now optional: the new Get-sqmChunkColumnCandidate ranks date-typed and period-named columns by naming convention and estimates each candidate's chunk count from the column's statistics histogram, a metadata read that costs the same on a 344-million-row table as on an empty one, so it is safe to call from the GUI too. The choice, its estimate and the reason are logged; with no suitable candidate the run aborts and names what it rejected and why. Without an explicit -MaxChunkValues, the new MaxChunkValueCeiling config key applies (default 2000). Also in this release: the GUI gained a "transfer mode" group (Automatic / Plain / Chunked) so a chunked run no longer requires dropping into PowerShell, plus fixes for a GUI title stuck on "v0.1.0.0" and a results grid that could crash on mixed chunked/plain results.
Invoke-sqmChunkedTableTransfer's destination existence/pre-count checks splatted a params hashtable that already carried ErrorAction = 'Stop' alongside an explicit -ErrorAction SilentlyContinue, a duplicate common parameter. That went undetected through 0.1.14.0-0.1.17.0's testing because it ran under PowerShell 7, which tolerates the duplicate. The module targets, and is actually deployed on, Windows PowerShell 5.1, whose parameter binder rejects it outright with ParameterBindingException: ParameterAlreadyBound, hit on every normal (non--Truncate) resume of an existing destination, the common case. Fixed by dropping the redundant ErrorAction from the connection params hashtable, since every query call already passes -EnableException, which fully overrides it anyway. Re-verified the full chunked-transfer test suite under real Windows PowerShell 5.1 this time, not just PowerShell 7.
A plain transfer of a large table is always faster than chunking it, no per-chunk overhead. Chunking's whole benefit is resumability, skipping already-complete chunks, which only matters once the destination already holds a meaningful part of the data. The large-table warning now only suggests Invoke-sqmChunkedTableTransfer once the destination already contains at least ChunkAdviceMinExistingPercent (default 30%) of the source's rows, metadata lookup, no extra scan. A fresh/empty destination just gets the faster plain copy. Also added: the GUI window title now shows the module version and a powershelldba.de credit, plus an About dialog.
Found on a live 570-million-row chunked transfer: the destination per-chunk row-count snapshot introduced in 0.1.14.0 had no query timeout, so it inherited ADO.NET's 30-second default. On a large table with non-clustered indexes already disabled for the run, that scan can exceed 30 seconds, and on failure the code silently fell back to treating every chunk as "never copied", even when the destination already held real data from a prior run. That would have re-copied every already-complete chunk on top of itself. Confirmed via the transfer log: every chunk showed a plain copy with zero "already complete, skipped" entries, only possible if the snapshot had silently failed for the whole run. Fixed with an explicit query timeout on all three snapshot queries, and a failed destination snapshot now aborts immediately, before any chunk is touched, instead of guessing.
Raising the default batch size in 0.1.14.0 had a side effect: the live "rows copied" progress notification defaulted to firing every -BatchSize rows, so a single chunk (typically far smaller than the new 500,000-row batch size) never crossed that threshold and showed no progress at all until it finished, even though the copy itself was correct. -NotifyAfter now defaults to the smaller of -BatchSize and 25,000, decoupling progress granularity from the batch commit size.
Real-world testing on a large table found Invoke-sqmChunkedTableTransfer spending most of its time on row counting, not copying data. Each chunk ran up to four live COUNT_BIG(*) WHERE [column] = value scans, a skip-check before the copy and a verification after, both sides, all without index support since indexes are disabled for the whole run. On a table with hundreds of chunks that made row-counting dominate the entire run. Replaced with three snapshots taken once instead of per chunk: the source's per-chunk counts come from the same query that already replaced the old DISTINCT chunk-value lookup, the destination's per-chunk counts are snapshotted once before the loop, and the post-copy verification is one more scan after the last chunk, compared in memory. Also raised the default batch size from 200,000 to 500,000 rows, measured faster in practice.
The large-table warning dialog used a plain MessageBox.Show(). Ctrl+C copies the whole dialog text, intro sentence, every flagged table, the yes/no question, not just the command to paste into PowerShell, which got unwieldy with multiple large tables flagged at once. Replaced it with a small dialog that has a read-only textbox for context plus an explicit "Copy to clipboard" button that copies only the clean command lines.
A plain Invoke-sqmTableTransfer call on a huge table used to silently do an all-or-nothing copy, with no hint that Invoke-sqmChunkedTableTransfer exists or would help. It now checks the source row count (metadata lookup, no scan) against a configurable threshold (Set-sqmTransferConfig -LargeTableRowThreshold, default 10,000,000) and, if exceeded, warns with a ready-to-paste Invoke-sqmChunkedTableTransfer command, including a suggested -ChunkColumn, picked from the table's date-typed columns by naming convention. The GUI runs the same check before starting a transfer, not after, so an oversized table can be caught and cancelled in time.
Fewer round-trips on large tables. Applied consistently everywhere the default is read: the module's central config, every function's fallback, and the GUI's initial batch-size field.
Ticking a table's checkbox in the GUI grid to fetch its row count ran a full COUNT_BIG(*) on the UI thread, checking a huge table alone could freeze the window for as long as the scan took. Switched to the same sys.dm_db_partition_stats metadata lookup used elsewhere.
The button always ran Compare-sqmDatabaseRowCount -VerifyMismatches unconditionally, a real COUNT_BIG(*) scan for any mismatched table, synchronously freezing the whole window for however long that took. Added a checkbox (unchecked by default) so the fast metadata-only comparison is the default, with exact verification available on demand for a final customer-facing report.
Invoke-sqmTableTransfer's own post-copy row-count compare ran unconditionally after every chunk, even though Invoke-sqmChunkedTableTransfer always discarded that specific (whole-table, not chunk-scoped) result and replaced it with its own. Added -SkipRowCountCompare and wired it into every per-chunk call.
The consolidated end-of-run comparison used SELECT COUNT_BIG(*) on both sides, a full scan, minutes on a very large table. Added a -Fast switch to Compare-sqmTableRowCount that reads SUM(row_count) from sys.dm_db_partition_stats instead (exact, transactionally maintained, no scan), used only for the end-of-run check where nothing is still actively writing to the table.
Chunked tables have no primary/unique key, so resumability works by comparing per-chunk row counts. That covers a clean stop between chunks, but a chunk killed mid-SqlBulkCopy left partially-committed rows behind, retrying just re-ran the whole chunk's SELECT, doubling those leftover rows. Added a targeted DELETE for a chunk's existing rows before any retry where the destination already has a nonzero, mismatched count.
Invoke-sqmChunkedTableTransfer called Invoke-sqmTableTransfer once per chunk, and that function disables/rebuilds indexes around every call, so a table split into hundreds of chunks rebuilt every index hundreds of times. Moved table creation and constraint disable/rebuild to run once around the whole chunk loop instead, guaranteed via a finally block.
The previous fix relied on dbatools' -ForceExplicitMapping parameter, which doesn't exist in every dbatools version still in production use. Added Invoke-sqmDirectBulkCopy, which drives Microsoft.Data.SqlClient.SqlBulkCopy directly with explicit name-based column mappings, no dependency on dbatools' internal -Query handling at all.
Traced a chunked-transfer data-corruption bug to dbatools' Copy-DbaDbTableData -Query mode: without -ForceExplicitMapping, it leaves SqlBulkCopy.ColumnMappings empty and falls back to implicit ordinal mapping against the destination's full physical column list, which counts computed columns even though nothing can be written to them. A computed column anywhere before the end of the table silently shifted every later column's mapping by one position. Reproduced against a real 108-column production table with a computed column at position 3.
A prior fix removed destination-only columns from the SELECT list to match column order, but removing (rather than replacing) a column still shifted every later column's ordinal position. Replaced removed columns with typed CAST(NULL AS ...) placeholders instead.
Added Invoke-sqmChunkedTableTransfer: splits a table by a discriminating column (e.g. a reporting/snapshot date) and transfers it one distinct value at a time, with a per-chunk row-count skip-check that makes a re-run after a partial failure resume from where it left off, without needing a primary/unique key. Also in this release: trigger disable/enable added to the constraint-handling pipeline, a consolidated database-wide comparison report (Compare-sqmDatabaseRowCount / Export-sqmDatabaseComparisonReport), and a GUI Connect-button scoping fix.
Table data transfer between SQL Server instances built on dbatools: metadata scripting with dependency resolution, partitioned-table handling, safe foreign-key/index disable and guaranteed re-enable around the copy, row-count reconciliation, full HTML reporting, and a WinForms GUI. -SkipCompleted added to resume an interrupted multi-table run. Sync-sqmTableData added for incremental insert/update/delete sync via a staging table. Bilingual (DE/EN) GUI text and log messages.