Hi Shaishav,

There was not much difference after running the script in the given link to clean the audit trail and version history. It has only come down by few thousands.

How odd. The IsSqlCe test is based on the connection string's providerName, so that shouldn't be an issue. Can you check what it's actually running with something like

SELECT sqltext.TEXT, req.session_id, req.status, req.start_time, req.command, req.cpu_time, req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 

The query it should be running for SQL Server is still fairly unpleasant and slow, but it shouldn't be taking hours.

UPDATE cmsPropertyData SET versionId2=cmsContentVersion.id FROM cmsContentVersion INNER JOIN cmsPropertyData ON cmsContentVersion.versionId = cmsPropertyData.versionId

It shows that the above query is running

Reassuring that it's not using the SQL CE version, though strange that it's taking so long. My test database has 8.5 million rows in cmsPropertyData and only takes a couple of minutes to run that query.

You could do the change of the versionId column manually before starting the migration, and the migration should then quietly skip that bit. That might let you experiment with indexes to speed it up.

This SQL should be what the migration runs, though you might need to drop an index or two first:

ALTER TABLE cmsPropertyData ADD [versionId2] [int] NULL

UPDATE cmsPropertyData SET versionId2=cmsContentVersion.id FROM cmsContentVersion INNER JOIN cmsPropertyData ON cmsContentVersion.versionId = cmsPropertyData.versionId

ALTER TABLE cmsPropertyData DROP COLUMN [versionId]

sp_rename 'cmsPropertyData.versionId2', 'versionId', 'COLUMN'

It finishes the given query in SSMS very fast, like in 3 mins. But this keeps running for hours from the application. As a workaround, i'm running the alter, update and rename query from SSMS before migration and then start Umbraco migration, now it is not getting stuck at the update query. I will let you know once the whole migration process is done. Thanks.

Hi Steve Megson,

We are now getting an error in PostMigrations.

{"@t":"2019-10-03T14:21:55.0051163Z","@mt":"Database configuration failed","@l":"Error","@x":"System.NotSupportedException: Surrogate pairs are not supported.\r\n   at Umbraco.Core.Strings.DefaultShortStringHelper.CleanCodeString(String text, CleanStringType caseType, Char separator, String culture, Config config)\r\n   at Umbraco.Core.Strings.DefaultShortStringHelper.CleanString(String text, CleanStringType stringType, String culture, Nullable`1 separator)\r\n   at Umbraco.Core.Strings.DefaultShortStringHelper.CleanStringForUrlSegment(String text, String culture)\r\n   at Umbraco.Core.StringExtensions.ToUrlSegment(String text, String culture)\r\n   at Umbraco.Core.Strings.DefaultUrlSegmentProvider.GetUrlSegment(IContentBase content, String culture)\r\n   at Umbraco.Core.Strings.ContentBaseExtensions.<>c__DisplayClass0_0.<GetUrlSegment>b__0(IUrlSegmentProvider p)\r\n   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()\r\n   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)\r\n   at Umbraco.Core.Strings.ContentBaseExtensions.GetUrlSegment(IContentBase content, IEnumerable`1 urlSegmentProviders, String culture)\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.GetDto(IContentBase content, Boolean published) in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1356\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.<RebuildMemberDbCacheLocked>b__82_1(IMember m) in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1581\r\n   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()\r\n   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)\r\n   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.RebuildMemberDbCacheLocked(IScope scope, Int32 groupSize, IEnumerable`1 contentTypeIds) in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1542\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.Rebuild() in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1393\r\n   at Umbraco.Web.Migrations.PostMigrations.PublishedSnapshotRebuilder.Rebuild() in d:\\a\\1\\s\\src\\Umbraco.Web\\Migrations\\PostMigrations\\PublishedSnapshotRebuilder.cs:line 27\r\n   at Umbraco.Core.Migrations.PostMigrations.RebuildPublishedSnapshot.Migrate()\r\n   at Umbraco.Core.Migrations.MigrationPlan.Execute(IScope scope, String fromState, IMigrationBuilder migrationBuilder, ILogger logger)\r\n   at Umbraco.Core.Migrations.Upgrade.Upgrader.Execute(IScopeProvider scopeProvider, IMigrationBuilder migrationBuilder, IKeyValueService keyValueService, ILogger logger)\r\n   at Umbraco.Core.Migrations.Install.DatabaseBuilder.UpgradeSchemaAndData(MigrationPlan plan)","SourceContext":"Umbraco.Core.Migrations.Install.DatabaseBuilder","ProcessId":14792,"ProcessName":"iisexpress","ThreadId":7,"AppDomainId":2,"AppDomainAppId":"LMW3SVC2ROOT","MachineName":"SLBLR-LT-209","Log4NetLevel":"ERROR","HttpRequestNumber":7,"HttpRequestId":"c6965a3d-c7ee-4be3-83ae-84250fb34d59"}

Could you please share your source code repository or the branch name, so that we could debug and find out the error ?

Here's my repository: https://github.com/stevemegson/Umbraco-CMS/tree/v8/migration-performance

It looks like you're back into unmodified code with that error, though. The cache is trying to generate clean versions of member names in the same way that document names get cleaned to generate URLs (I don't think we ever use a URL for a member anywhere, but the cache doesn't know that). I'd guess that the most likely reason for surrogate pairs in a member name is someone including emoji.

Thanks Steve for the hint about emojis. I also got the "Surrogate pairs are not supported" error. I removed the emojis from content, and then I successfully upgraded from Umbraco 7 to 8.

Thanks Steve. We were able to locate the emoji in UmbracoNode table and finished with the migration. Will your performance changes be pushed to the main Umbraco repository and be part of any upcoming release ?

hi Steve, We are facing this error. can you tell us how to find imojis and remove? Appreciate your help. System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Umbraco.Web.Install.InstallException: The database failed to upgrade. ERROR: The database configuration failed with the following message: Surrogate pairs are not supported. Please check log file for additional information (can be found in '/AppData/Logs/') at Umbraco.Web.Install.InstallSteps.DatabaseUpgradeStep.ExecuteAsync(Object model) in d:\a\1\s\src\Umbraco.Web\Install\InstallSteps\DatabaseUpgradeStep.cs:line 47 --- End of inner exception stack trace --- at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at Umbraco.Web.Install.Controllers.InstallApiController.14.MoveNext() in d:\a\1\s\src\Umbraco.Web\Install\Controllers\InstallApiController.cs:line 236 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at Umbraco.Web.Install.Controllers.InstallApiController.

how did you find the imoji and removed. Please advise. We are also finding same issue.

This worked for me, had to go through a few tables:

SELECT TOP (200) id, contentNodeId, versionId, propertytypeid, dataInt, dataDecimal, dataDate, dataNvarchar, dataNtext
FROM  cmsPropertyData
WHERE (CAST(dataNtext AS VARCHAR(80)) <> CAST(dataNtext AS NVARCHAR(80)))

Its still slow when upgrading to 8.5.5

I was worried that the upgrade was in an errorstate. This is a great tip. I can see it actually does something.

The two active queries where one is cycling between suspended and running makes me think that the migration script is using query and subquery - which will result in a huge amount of updates :-)

Hi Cimplex, I am struck on the same step. Did you find any solution? Thanks

I'm stuck there too. Given that it was three years ago, do you remember whether you figured it out?

Edit: Cancel that; not stuck after all. After ~15 minutes it proceeded to the next step.

Hey Chris,

Do you mind telling me which version you were upgrading from? I'm looking to upgrade my v7 installation. I would like to upgrade it all the way up to 10 or 11. I know I looked into upgrading from v7 to v8 awhile back and you had to use the ProWorks migration tool, not sure if that is still a requirement or not. Any advice would be greatly appreciated!

I'm starting from 7.15.10, and eventually I'll be heading all the way up to 10.5.1 but I'm doing it via 8.5.5.

The latest version of the ProWorks tool requires 8.14 so I tried that first, but couldn't even get as far as the migration step because 8.14 rejected my password (8.5.5 was happy with it). It remains to be seen whether I have the same issue once I jump up to 10 :)

Did you have to use the ProWorks migration tool with going to V8.5.5? I know there was some talk about not having to use that on one of the later versions of V8 because Umbraco implemented something similar into their installer. Do you know if this is the case or not?

You can go to 8.5.5 without ProWorks. The impression I got is that only 8.6 and later need it.

I couldn't find any documentation saying that it was ever "fixed" in later versions; I think the advice was always "go via 8.5.5 or use ProWorks", but I might be mistaken.

Ahh yeah maybe thats what it was. Anyways, thanks for the replies! Are you jumping from v8.5.5 to the latest v8 and then to the latest v9, then lastly to the latest v10?

On a different site I was able to go directly from 8.5.5 to the latest 10 (which I think was 10.3.2 at the time). I expect to be able to do the same here.

Oh wow, that would save a lot of time! Just for reference, how much code was broken by jumping all the way from v7 to v10? Are you fixing the broken code in v8.5.5 and then doing the upgrade to v10?

The only thing you need to do in 8.5.5 is point it to the database and let it upgrade it. Once the database is at the 8.5.5 level, you can go straight to 10. There's no need to touch any code in 8.5.5.

As for how much will break with a typical site, I can't really say. The site I previously upgraded was built by a third party and had some very "interesting" code. I suspect that the one I'm doing now will be quite a bit easier :)

Oh that would be great. I thought there would be a lot of breaking changes with the way Umbraco handles certain things. My site too was built by a third party agency and sometimes I still cringe about the way they did certain things.

As for the upgrade, you just update the Umbraco nuget packages and let it do its thing? Should I save any particular files like the web.config and restore that before I try to do the db upgrade?

Sorry for all the questions but I have never done an upgrade before so I want to make sure I don't royally mess anything up.

Neither of my existing U7 sites used NuGet. I created a new U10 project (using the commands listed here) and set the connection string to the 8.5.5 database.

As for "royally messing things up"... you're working on a copy of the database, right? :)

Oh interesting, I may try that method. Thanks for sharing the link!

Oh yeah I'll be using a copy of the db and also have source control so I guess I'm just overly nervous doing big upgrades like this lol

I got some questions with the new project method. What are the exact steps you do when doing the upgrade this way?

1. You create the new project and then point the connection string to the database so it does the upgrade.
2. Then once the database is upgraded, you go back into your existing site code and then change the connection string there and umbraco version and all is well?

Also, what version of .Net are your sites on? Mine is still .NET Framework (I know, I know...migration to newer .NET is next on the list!). Will I run into any issues upgrading versions of Umbraco while being on the older .NET Framework?

You need to move to a newer .Net; Umbraco 10 requires .Net 6 and I think Umbraco 11 requires .Net 7. You're basically following the same steps as the 7 to 8 upgrade (i.e. bringing everything into a new project, which will have Umbraco 10 LTS or 11 and be running under .Net 6 or 7).

Yeah my plan was to get to probably the latest v8 and then upgrade my project to .NET 6 then go to v10. Thanks for that link and all the advice!