Skip to content

Upgrade to Build 1856

Sam Betts edited this page Nov 12, 2026 · 4 revisions

Updating the solution → Upgrade to Build 1856

This page is for administrators whose deployment is on Stable 1854 or older. Stable 1856 changes the database more than most releases, so read this page before you upgrade to 1856 or any later build. It explains each of its four migrations, how long they take and what they lock, the order for a manual (DBA) upgrade, and what to do before and after you deploy. For the general method, and for skipping several releases in one upgrade, see Updating the solution.

Stable build 1856 (upgrading from 1854)

Stable 1856 was published on 26 October 2026 and upgrades Stable 1854. It contains PR #768 (data-loss and reliability fixes in the web app and both importers), PR #770 (the default-off Copilot credit history preview) and PR #784 (that preview's connections are managed with the existing Portal.Administration role).

Upgrade question What changes
SQL Four migrations, each with a manual SQL script attached to the release. 202610221500001_CopilotAgentNameAndFlagRecency is purely additive (three NULLable columns on copilot_agents), and 202610221500002_EventFileExtensionUnicode changes the type of one column of the small event_file_ext lookup table. Both are quick at any database size, and both must be applied before the new importer starts. 202610221500003_UniqueOnlineMeetingIds deletes rows: it removes the duplicate Teams meeting rows that builds before 1856 stored in online_meetings, then narrows and indexes meeting_id. How long it takes depends on how many duplicates there are and on the disk; see Migration 003. 202610230900001_AddCopilotCostPreview only creates the empty tables of the Copilot credit history preview, whether or not you turn the preview on; see its database upgrade.
Saved installer JSON Saved schema 3.4.0 → 3.5.0, additive. It adds ImportTaskSettings.CopilotCostManagement, the Copilot credit history (preview) switch, which defaults to off. Load your existing configuration in the new installer and save it again.
App settings Two new optional runtime settings, neither written by the installer: UsageReportsDaysBack (default 25 days) and CleanUpConcealedIdentityUsers (default on). You don't need to set either, unless you want to keep the usage-report rows of concealed identities. In that case, set CleanUpConcealedIdentityUsers to false before upgrading (why).
Security Rotate the runtime app registration's client secret after upgrading. Builds before 1856 sent it with every Teams call notification. See Teams and calls troubleshooting → After upgrading from a build before 1856.
Teams calls The importer replaces the calls webhook's Graph subscription by itself, on …/api/CallRecordWebhook?cs=v1, and deletes the old one an hour later. Nothing to do.
Copilot credit history (preview) New, and off by default. Nothing to do unless you want to pilot it; see Copilot credit history (preview).
One-off effects A drop in enabled users at the next complete read of the directory (why); a much longer first Teams crawl, because Teams data imports again (why); usage reports reach 25 days back, so their first run can download more; placeholder users created from concealed usage reports are deleted, with their usage-report rows, so the user count and past usage-report totals fall (why); and Copilot Adoption's Teams-meeting credits can fall, because a meeting stored more than once could be counted once per copy (why).
Scale-out Both importer WebJobs run as singletons. If you build the App Service package by hand, keep each WebJob's settings.job (details).
Downtime and space Plan a maintenance window with both importers stopped, as the release notes require, and once 003 has run start only the 1856 importers. 001, 002 and 202610230900001_AddCopilotCostPreview need only brief schema locks. 003's column change and index build lock online_meetings while they run, unless SQL Server can do them ONLINE, and it needs free space (details).

Migration and manual-script order

Start from a database stamped through 202610080900001_AddPromptCategories, the last migration of Stable build 1852 (Stable build 1854 added none).

  1. 202610221500001_CopilotAgentNameAndFlagRecency. Adds three NULLable columns to dbo.copilot_agents: name_seen_utc and flag_seen_utc (datetime2(3)) and name_is_fallback (bit). They record how recent an agent's stored display name and custom-agent flag (is_custom_agent) are. The importer commits Copilot activity newest-first, in batches, so an older batch committed after a newer one used to put an agent's older name or flag back; from this release it can't. Existing rows start with NULL, meaning "not known", and behave exactly as before until a new batch records a time. No index, no backfill, no data moves.
  2. 202610221500002_EventFileExtensionUnicode. Changes dbo.event_file_ext.extension_name from varchar(250) to nvarchar(250), keeping its nullability and collation, and drops and re-creates its unique index IX_event_file_ext. As varchar, a file extension in a script outside the database's code page, Greek for example, was stored as question marks, so different extensions could share one row. From this release new extensions are stored exactly as logged. Extensions already stored with question marks stay as they are.
    • Under a SQL collation, such as Azure SQL's default SQL_Latin1_General_CP1_CI_AS, a few values that were different as varchar are equal as nvarchar: ss and ß, for example. If the table holds such a pair, the migration keeps the older row, re-points every reference to the other one (each foreign key to event_file_ext.id, and event_meta_sharepoint.file_extension_id) and deletes it, so the unique index can be built. Reports then count those extensions as one. Normally there is no such pair.
  3. 202610221500003_UniqueOnlineMeetingIds. Removes the duplicate rows that builds before 1856 stored in dbo.online_meetings, moves every reference onto one row per meeting, then narrows meeting_id to nvarchar(850) and makes it unique. It deletes rows: see Migration 003.
  4. 202610230900001_AddCopilotCostPreview. Creates the empty copilot_cost_* tables, their indexes and the dbo.copilot_cost_purge_preview procedure used by the Copilot credit history preview. It doesn't change any existing table, and it is applied whether or not you turn the preview on.

Manual upgrade. For a DBA upgrade, the release attaches a <migration-id>.manual.sql script for each migration. Run them in migration-id order, after 202610080900001_AddPromptCategories: 001, then 002, then 003, then 202610230900001_AddCopilotCostPreview. For 202610230900001_AddCopilotCostPreview, use the corrected copy attached to Stable 1860, not 1856's: 1856's copy could finish without recording itself (see the fourth point below).

  • The first two apply their schema change, then record themselves in __MigrationHistory only if their predecessor is already recorded there. Otherwise they stop with an error such as EventFileExtensionUnicode: NOT stamped - prerequisite migration 202610221500001_CopilotAgentNameAndFlagRecency is not stamped in __MigrationHistory. Run its manual script first.
  • The third checks first. If 202610221500002_EventFileExtensionUnicode isn't recorded, it changes nothing at all and stops with UniqueOnlineMeetingIds: prerequisite migration 202610221500002_EventFileExtensionUnicode is not stamped in __MigrationHistory. Run the manual scripts in migration-id order. Nothing has been changed; NOT stamped. It deletes rows, so take a backup before you run it. Its header recommends running it with sqlcmd -b. If you need to stop it part-way, see Stopping the manual script part-way. It records itself only once the unique index exists. If it doesn't get that far, it stops with UniqueOnlineMeetingIds: NOT stamped - the schema work did not complete: …; deal with the errors above that message, then run the script again.
  • The fourth, 202610230900001_AddCopilotCostPreview.manual.sql, needs 202610221500003_UniqueOnlineMeetingIds recorded first. Use the copy attached to Stable 1860, not 1856's. When the predecessor isn't recorded, 1856's copy reports Required predecessor migration is missing., but sqlcmd and SSMS carry on to its next batch: it creates the copilot_cost_* tables, their indexes and dbo.copilot_cost_purge_preview, records nothing in __MigrationHistory, and sqlcmd still exits with code 0. The 1860 copy stops before creating anything, with AddCopilotCostPreview: NOT stamped - prerequisite migration 202610221500003_UniqueOnlineMeetingIds is not stamped in __MigrationHistory. Run the manual scripts in migration-id order. Nothing has been changed., and says NOT stamped whenever it doesn't record itself. It isn't a new migration: on a database where it is already recorded, it changes nothing and says AddCopilotCostPreview: __MigrationHistory is already stamped; nothing to do. See section 11 of the 1860 release notes.

If you ran 1856's copy by hand, check that both migrations are recorded:

SELECT MigrationId FROM dbo.__MigrationHistory
WHERE MigrationId IN (N'202610221500003_UniqueOnlineMeetingIds', N'202610230900001_AddCopilotCostPreview');

Two rows means nothing more is needed. If 202610230900001_AddCopilotCostPreview is missing although the copilot_cost_* tables exist, nothing is damaged: record the predecessor, by running its manual script from Stable 1856 or by running the installer's database upgrade, then run the 1860 copy, which completes and records itself.

All four are safe to re-run: on an upgraded database they change nothing. The scripts apply the schema only; the installer's database upgrade also refreshes the SQL extensions, so after a manual upgrade run Forcing schema updates. Upgrading from before 1854? Run the earlier scripts first: see the full script chain.

Apply 001 and 002 before the new importer starts

The new importer's SQL uses both changes. The installer applies the migrations before it deploys the new WebJobs. If you upgrade the database by hand, run the 001 and 002 scripts before you start or restart the WebJobs on the new build. Otherwise:

  • without 202610221500001, the importer's Copilot merges fail with SQL error 207, Invalid column name 'name_seen_utc'. (with the same error for name_is_fallback and flag_seen_utc), until the migration is applied;
  • without 202610221500002, an audit batch holding a file extension outside the database's code page fails on every retry on the unique index IX_event_file_ext.

Migration 003 isn't needed first. Until it has run, the new importer's Teams meeting step still works and no longer stores a meeting again with every batch, but each lookup reads the whole of online_meetings, which is slow on a large table. Run it in the same window if you can.

Timing and locks

Migration 1M audit events 10M audit events 100M audit events
202610221500001_CopilotAgentNameAndFlagRecency Instant Instant Instant
202610221500002_EventFileExtensionUnicode Seconds Seconds Seconds
202610230900001_AddCopilotCostPreview Seconds Seconds Seconds

Migration 003 isn't in this table: its time depends on online_meetings and on the disk, not on the number of audit events. See Migration 003: how long it takes.

  • 001 adds NULLable columns with no default, a metadata-only change on every SQL Server edition, so its time doesn't depend on the size of the database. Each column add takes a brief schema-modification lock on copilot_agents, which holds one row per agent. No ONLINE operation is needed.
  • 002 rewrites a table that holds one row per distinct file extension, typically hundreds to a few thousand rows, however many audit events you have. The column change and the index build each take a brief schema-modification lock on event_file_ext, which pauses the importer's merge for that moment. They run ONLINE where the edition supports it (Enterprise, Azure SQL Database, Azure SQL Managed Instance), and fall back to offline otherwise. The one exception is a database with rows to merge (above): re-pointing them reads event_meta_sharepoint once, because its file_extension_id has no index. Allow a few minutes per 100 million rows in that case; that's an estimate, not a measurement, and the script says when it happens (EventFileExtensionUnicode: <N> extension row(s) equal another one under Unicode comparison; re-pointing their references at the oldest row of each group.).
  • A schema lock still waits for long-running readers and writers, so run the upgrade in a quiet period, as for any release.
  • 202610230900001_AddCopilotCostPreview creates empty tables, their indexes and a procedure, so its time doesn't depend on the size of the database. It doesn't rewrite an existing table or build an index on one, but its schema locks can still wait behind other database activity.

If 002 stops without changing anything. It checks first that nothing else depends on extension_name. If someone has added their own index, constraint, foreign key or schema-bound object on that column, it stops with EventFileExtensionUnicode: ABORTING - these depend on dbo.event_file_ext.extension_name, so its type cannot change: <list>. Drop them, re-run the upgrade, then re-create them. Nothing was changed. Do what it says. A stock database has no such object.

Migration 003: duplicate Teams meeting rows are removed

Why. When a Copilot interaction happens in a Teams meeting, the importer stores the meeting in dbo.online_meetings and links the interaction to it in dbo.copilot_event_meetings. When builds before 1856 checked whether a meeting was already stored, they never compared its meeting ID. So every import batch that contained an interaction in a meeting, and every retry of that batch, stored the meeting again. One meeting could end up with many rows, and each of its interactions was linked to whichever copy existed when its batch ran. From 1856 the importer looks a meeting up by its ID alone, so it stores each meeting once. This migration cleans up the copies that are already there.

What it does

  1. Trims any meeting_id longer than 850 characters. None is expected: a real ID is about 106 characters, the user's object ID followed by the meeting's thread ID.
  2. Keeps one row per meeting_id: the oldest, unless the oldest has no subject and a newer copy has one. It moves every reference to the other copies onto that row, and then deletes the copies. In a stock database the only references are in copilot_event_meetings.meeting_id, and that table's key doesn't include meeting_id, so moving them can't collide. Custom tables and constraints can make a move collide with a unique key or index. The migration never resolves that by deleting rows from your own tables or a different interaction's link. It also never prunes colliding links if the link table has added columns, an incoming foreign key or an enabled delete trigger. Instead, it stops with error 50001, naming the table and the index but no row values. It rolls back only the batch it was working on, and the batches that had already finished stay done. Merge or remove the colliding rows, preserving any data you need, then run the upgrade again.
  3. Changes meeting_id from nvarchar(max) to nvarchar(850). An nvarchar(max) column can't be an index key, so until now every lookup of a meeting read the whole table.
  4. Creates the unique index UX_online_meetings_meeting_id, with IGNORE_DUP_KEY = ON. From then on a meeting can't be stored twice. A duplicate raw SQL insert, including one from the older importer's merge, skips that row rather than failing. An EF insert can still report a concurrency error when SQL skips its row.

If more than one row has no meeting_id at all, each one gets a placeholder ID, unidentified-meeting:<row id>, because a unique index treats missing values as equal. No importer writes such rows.

What you'll notice

  • Copilot Adoption's Teams-meeting credits can fall. Seat holders' time saved (modelled) credits each distinct meeting that a seat holder used Copilot in, and it counted distinct meeting rows. So a person whose interactions in one meeting were linked to two copies was credited for that meeting twice. After the migration they're credited once, so the Teams-meetings figure, and the hours it adds, fall wherever that happened. That's a correction, not lost data: no Copilot interaction is deleted, and each one stays linked to its meeting. See Copilot Adoption Metrics Reference → Time already saved by seat holders.
  • dbo.online_meetings has fewer rows, one per meeting. Anything you count from it yourself falls by the number of copies removed.
  • A meeting that was renamed between batches keeps one subject: the oldest one stored. The importer keeps that subject from now on.
  • The importer's Teams meeting step does less work. With the copies gone and the new index, it finds a meeting with an index seek instead of reading the whole table, so ordinary batches spend far less time checking for meetings. Don't expect every batch to be faster, though: for large batches, and on a busy database, writing to the transaction log still dominates.

Migration 003: how long it takes, what it locks and the space it needs

What decides the time is mainly the number of duplicate copies in dbo.online_meetings, then the size of the table. The number of audit events doesn't matter. The work is disk-bound, and the figures below are examples, not a promise. Removing copies ran at about 12,000 copies a second on a quiet disk. On a heavily contended disk it fell as low as 45 to 64 a second. At that rate, the 1.6 million copies in the first run below would take 7 to 10 hours.

Measured on synthetic data, with LocalDB (where every step runs offline) on a quiet disk:

online_meetings before the migration Total Of which, the column change Of which, the index build
3.6 million rows, 1.6 million of them copies, with 6 million links 287 s 71 s 15 s
2 million rows, no copies 88 s 58 s 16 s
1 million rows, 445,000 of them copies 47 s Not recorded Not recorded

In the first run, removing the copies, which moved 1.5 million links, took 131 s. On a busy disk, the same upgrade took 345 s in one run and 643 s in another.

Rule of thumb. On a healthy disk, allow about a minute per million rows in online_meetings, plus about a minute and a half per million copies. On a busy or slow disk it's the copy removal that grows: at 45 copies a second, each million copies takes about six hours. If you can't be sure the disk will be quiet, give the migration a maintenance window and watch its progress messages (below).

To size yours, use these read-only queries. The second reads the whole table, so run it at a quiet time:

EXEC sp_spaceused N'dbo.online_meetings';

SELECT COUNT_BIG(*) AS meeting_rows,
       COUNT_BIG(meeting_id) - COUNT_BIG(DISTINCT CAST(meeting_id AS nvarchar(850))) AS duplicate_copies
FROM dbo.online_meetings;

What it locks

  • Removing copies runs in short batches, each its own transaction, at low deadlock priority. If a batch collides with the importer, the batch gives way and is retried, so the importer doesn't lose its work: UniqueOnlineMeetingIds: batch <N> gave way to a concurrent writer (error <N>); retry <N> of 10. Stopping part-way is safe. Finished batches stay done, the batch that was running is rolled back, and running the migration again carries on with the copies that are left. See also Stopping the manual script part-way.
  • The column change and the index build are the steps that block: 71 s and 15 s in the 3.6-million-row run above. On Enterprise, Azure SQL Database and Azure SQL Managed Instance, the migration attempts both ONLINE, and says so as it starts: UniqueOnlineMeetingIds: EngineEdition=<N>; the column change and index build are attempted ONLINE, with an offline fallback. If SQL Server won't do one of them online, the migration logs UniqueOnlineMeetingIds: ONLINE column change unavailable (<reason>); retrying offline. or UniqueOnlineMeetingIds: ONLINE index build unavailable (<reason>); retrying offline., and does it offline. On Standard, Express, Web and LocalDB, both are always offline: UniqueOnlineMeetingIds: EngineEdition=<N>; ONLINE is not supported on this edition, so the column change and index build lock dbo.online_meetings while they run. The offline column change blocks all access to online_meetings; the offline index build blocks writes but normally allows readers. Either can make the importer's Teams meeting step wait. An ONLINE step avoids those long blocking locks, but it still needs a brief schema lock at its start and end. No ONLINE timings were measured here.
  • A last-resort pass. Something may keep storing copies faster than the migration removes them, as an importer still on the previous build does. In that case its fourth pass locks online_meetings exclusively for a final clean-up and an offline index build, on every edition: UniqueOnlineMeetingIds: meetings keep being stored again; locking dbo.online_meetings for a final pass and an offline build.

With the importer running. The migration itself is designed to survive an importer that is still running, even one on the previous build: copies stored while it runs are removed before the index is built, and no link is lost. That protects the migration, not the upgrade. The 1856 release notes require a maintenance window with both importers stopped, and once 003 has run, importers from an older build must not be started against the database again: deploy the 1856 packages before you resume the WebJobs. An offline column change also blocks the table, and an importer on the previous build keeps making copies for the migration to remove.

Stopping the manual script part-way. The clean way to stop it is to end its session: close the connection, or press Ctrl+C in sqlcmd. Stopping it with Cancel Executing Query in SSMS works too. The script sets XACT_ABORT ON, so the batch that was running is rolled back, and running the script again carries on from there. As a precaution, run SELECT @@TRANCOUNT; in that window before you close it. It should return 0; if it doesn't, run ROLLBACK;. If SSMS ever asks whether to commit uncommitted transactions, answer No.

Free space. Make sure the database has room before you start. On Azure SQL Database, check that it fits within the database's maximum size.

  • The new index adds about 240 bytes a row: about 460 MB per 2 million meetings.
  • The column change and the deleted copies leave online_meetings about twice its size until the table is rebuilt. In the tests, with 2 million meetings left, its clustered index grew from 579 MB to between 1.2 and 1.6 GB straight after the upgrade. It settled at about 1.16 GB once SQL Server had cleaned up the deleted rows. An ONLINE column change needs that space while it runs, too.
  • Moving the links onto the surviving rows grows the meeting index of copilot_event_meetings by about half. In the 3.6-million-row test, that made the table about 17% bigger.
  • Optional, afterwards: ALTER INDEX ALL ON dbo.online_meetings REBUILD; gives the space back. In the tests it brought the table back to 579 MB, and took 61 seconds, offline, for 2 million meetings. ALTER INDEX ALL ON dbo.copilot_event_meetings REBUILD; does the same for the links table. Both are offline on Standard and Express; on Enterprise and Azure SQL, add WITH (ONLINE = ON). A rebuild only saves space; it isn't needed for performance. In the tests the importer's meeting step read the same with or without it, and was just as slow when the disk was busy.

Progress. When the installer starts the migration it logs DB SCHEMA: Applying 'UniqueOnlineMeetingIds'. …. The manual script streams the migration's progress, in sqlcmd's output or the SSMS Messages tab:

UniqueOnlineMeetingIds: pass <N> found <N> duplicate meeting row(s) in <N>ms.
UniqueOnlineMeetingIds: pass <N> removed <N> of <N> duplicate row(s), repointed <N> reference(s), pruned <N> colliding reference(s) in <N>s.
UniqueOnlineMeetingIds: meeting_id is now nvarchar(850) (<online or offline>, <N>ms).
UniqueOnlineMeetingIds: UNIQUE UX_online_meetings_meeting_id created (<online or offline>, <N>ms).
UniqueOnlineMeetingIds: finished in <N>ms.

The removed line comes every 100 batches and at the end of each pass. If a concurrent writer stores a meeting again just before the index build, the build is retried after another clean-up pass: UniqueOnlineMeetingIds: a concurrent writer stored a meeting again before the index was built; running another mop-up pass. On a database where the migration has already run, it logs UniqueOnlineMeetingIds: UX_online_meetings_meeting_id is already UNIQUE with IGNORE_DUP_KEY = ON; nothing to do.

Rolling back

Don't roll back by redeploying older binaries on their own. The 1856 release notes are explicit: once the meeting migration has run, importers from an older build must not be resumed against the database, and a binary-only rollback is unsafe. If you have to go back, restore the database backup you took before the upgrade, then deploy the matching older build's packages.

Reversing the migrations doesn't get you back either. 202610221500002's Down deliberately does nothing, because converting the column back would turn every extension outside the code page into question marks again. 202610221500003's Down drops the unique index and widens meeting_id back to nvarchar(max), but the duplicate meeting rows it removed aren't restored. Rolling back to an older build also brings back the client secret in the calls webhook's clientState; see Teams and calls troubleshooting → After upgrading from a build before 1856.

Upgrade checklist for Stable 1856

  1. Use one build's components, take a backup, and plan a quiet window, as for any release (see Before you start). Migration 003 deletes rows, so make sure the backup is recent.
  2. Before you deploy anything:
    • load your saved configuration in the 1856 installer and save it again (schema 3.5.0). Leave Copilot credit history (preview) off unless you are deliberately piloting it (details);
    • decide whether to keep the usage-report rows of concealed identities. To keep them, set the CleanUpConcealedIdentityUsers app setting to false now (why);
    • size online_meetings and check that the database has the free space migration 003 needs, then stop both importers for the upgrade (details).
  3. Upgrade the database: with the installer, or by running 202610221500001_CopilotAgentNameAndFlagRecency.manual.sql, 202610221500002_EventFileExtensionUnicode.manual.sql, 202610221500003_UniqueOnlineMeetingIds.manual.sql and then 202610230900001_AddCopilotCostPreview.manual.sql, using the corrected copy attached to Stable 1860 (why). Check that all four are recorded in __MigrationHistory, using the query in Verify the upgrade.
  4. Deploy the binaries, and start the WebJobs only once 001 and 002 are recorded.
  5. Within the first cycle, check the calls webhook's log for [Calls Webhook] Replacing call-records subscription id … and [Calls Webhook] Created subscription id …, and an hour later for [Calls Webhook] Deleted replaced subscription id ….
  6. Rotate the client secret, then delete what may still hold the old one: dead-lettered call notifications (replay the ones you still want first, see Replaying the Teams calls dead-letter queue) and message-tracing captures from before the upgrade. See Teams and calls troubleshooting → After upgrading from a build before 1856.
  7. Expect the one-off effects in the table above. To get the enabled-user catch-up done at a time you choose, use Administration → User import → Clear checkpoint. The placeholder-user cleanup logs each run as Concealed-identity user cleanup: ….
  8. Optionally, once the upgrade is done, rebuild online_meetings to get back the space the column change left behind (ALTER INDEX ALL ON dbo.online_meetings REBUILD;). This only saves space; it isn't needed for performance.

Return to Updating the solution · All topics in this guide

Next: Upgrade to Build 1852

Start here

Copilot analytics

Using the portal

Install & configure

Beyond Copilot

Operate

Upgrading from an older build

Reference

Project

Clone this wiki locally