What it does
Migrates an active, not yet partitioned table into a partitioned copy in a separate archive database, one calendar month per chunk. On the first call the archive table is created with the source's structure and partitioned by month over the source's actual date range; -PrimaryKeyFromUniqueIndex turns an existing unique nonclustered index of the source into the clustered PRIMARY KEY of the copy. Every month is then copied with the copy engine of sqmDataTransfer (SqlBulkCopy), with rows, duration and rows/s per month on the console.
Works with real date columns as well as date surrogates (an INT or string column holding YYYYMMDD or YYYYMM). By default the migration stops at the previous month. -IncludeOpenPeriods transfers everything, including the current month: open months are copied again on every run, so rows written into the still active source later, and changed rows, follow automatically. -PurgeSourceAfterArchive deletes each completed month from the source after a row-count check and reclaims the space. -CutoverToArchiveView finally renames the source table and replaces it with a view over the archive copy, existing application code keeps using the same table name.
When to use it
An entire, active table needs to move permanently to another database, e.g. one with a different backup or storage profile, without touching application code. Built for very large tables (hundreds of millions of rows): every month is its own chunk, a stopped run loses at most the month in progress, and the console shows throughput per month instead of staying silent for hours.
Common pitfalls
- The archive database must already exist, it isn't created automatically
- Without an index that has the date column as its leading column, every month reads the whole source table, create one first for very large tables
- Without
-IncludeOpenPeriods the current month stays in the source, and after a cutover in the renamed original table
- Open months are never deleted by
-PurgeSourceAfterArchive: rows arriving between the row count and the delete would be lost
- A key is only needed for the cutover together with
-IncludeOpenPeriods (final sync, see below). For a heap pass -PrimaryKeyFromUniqueIndex or -KeyColumn (up to five columns)
- The renamed original table is never deleted automatically, deliberately, check it before dropping it for good
Benefits
- Resumable without a key: each run counts rows per month in source and archive (one GROUP BY each). A month with equal counts is skipped, a month with a different count (leftover of a stopped run) is emptied in the archive and copied again, no duplicates
- A month only counts as
Completed in dbo.sqm_ArchiveMonthLog after its row count has been verified
- Cutover with
-IncludeOpenPeriods is atomic: final sync of the open months, rename and view creation run in one transaction under an exclusive table lock, no row can slip in between
- Self-healing after earlier attempts: if the archive table was dropped, stale log entries are reset, and an unused partition function/scheme left over from an earlier run is recreated with the current boundaries instead of being reused
- The archive copy is registered automatically for its own sliding-window maintenance
Step by step, without the GUI
The same function is called several times with the same base parameters; every call continues where the previous one stopped. Only step 5 is hard to undo.
Import-Module sqmPartitionTool
$p = @{
SqlInstance = 'SQL01'
Database = 'Sales'
Schema = 'dbo'
Table = 'Bookings'
ArchiveDatabaseName = 'SalesArchive'
DateColumn = 'BOOKDATE'
PrimaryKeyFromUniqueIndex = 'UX_Bookings'
IncludeOpenPeriods = $true
}
Test-sqmPartitionReadiness -SqlInstance 'SQL01' -Database 'Sales' -Schema 'dbo' -Table 'Bookings' -PartitionColumn 'BOOKDATE'
Invoke-sqmTableArchiveMigration @p -WhatIf
Invoke-sqmTableArchiveMigration @p -CreateArchiveTableOnly
Invoke-sqmTableArchiveMigration @p
Get-sqmPartitionStatus -SqlInstance 'SQL01' -Database 'SalesArchive' -Schema 'dbo' -Table 'Bookings'
Invoke-sqmTableArchiveMigration @p -CutoverToArchiveView
New-sqmPartitionExtendJob -SqlInstance 'SQL01'
