Road to version 23. Some data from the field.

Rock’n’Roll! That is my first – official – super verbose and actionable error message during Sync-NAVTenant on the road to version #23.

I know. This is the good old grumpy PowerShell: not so verbose. And it forced me to wake up the even older SQL Server Profiler trace.

Ah Gotcha.

This is yet again the consequences of the new table structure. In this database on-premises, there are custom created SQL Views that are referencing companion tables. Such views must be removed manually before the upgrade, refactored, and deployed again in version 23, post upgrade.

This is the price to upgrade (on-premises).

Fixed the problem, Synchronization ran smoothly in 9 minutes (read: in 9 minutes from the old to the new table extension data model).

At the end of the upgrade:

• From a total number of rows in the database (122.183.786) the synchronization reduced them to 78.637.352. That is approx. -35% of the number of rows in the database (more than 1/3 of the rows were gone, better say transformed).
• From 8.859 Tables to 7.929 Tables (-930 Tables)
• Database dimension drop from 82.3K MB to 79.9K MB (-2.4 GB approx.)

Other goodies from this upgrade include:

1. Rename instead of drop.

During the upgrade, if there is only one single companion table, instead of the CREATE, INSERT INTO, DROP sequence, the procedure wisely renames (sp_rename) the table object instead. And of course, a yellow card is raised into the profiler trace every time it happens: “Caution: Changing any part of an object name could break scripts and stored procedures.”.

This is the clear sign that the synchronization process is renaming the only companion table instead of performing all the create new, insert into, and drop mambo-jumbo.

• Remember to create statistics.

SQL Server, like a good friend, will always remember you to create statistics, when needed.

An upgrade of this size means a new bunch of tables with a discrete number of records will be created. Remember, then, to create statistics right after the synchronization and take the chance to review your maintenance plan to be sure these will be up to date before start working with the new shiny version 23.

What about SaaS deployment vs column statistics? Well, that is a good question. I do not know right now if statistics will be automatically created during (I believe so) or right after synchronization or somewhere in time, but I am confident Microsoft has in place the best strategy to avoid performance bottlenecks right after upgrades.