We are building an Arabic-first Umbraco 17 solution (with English culture variant) and are evaluating using Arabic_100_CI_AI as default SQL Server collation instead of SQL_Latin1_General_CP1_CI_AS. Because standard Latin collation lacks correct Arabic linguistic sorting (ORDER BY, on both cms controlled queries and custom queries or lookup-tables , reports ). Now questions are :
Does Umbraco support running its database with an Arabic collation instead of default one? Or are there any known compatibility issues, limitations, or recommended practices regarding core CMS functionality, Examine, package compatibility, migrations, or upgrades when using an Arabic database collation?
If changing the database default collation is not recommended, what is recommended way to have correct Arabic sorting and search.
I don’t know specifically and I’ve not got any experience with this, but here is what AI has to say about it:
Umbraco’s only documented database requirement is a case-insensitive collation — the docs say the Data Access Layer doesn’t support case-sensitive naming, and recommend a CI variant such as SQL_Latin1_General_CP1_CI_AS. Arabic_100_CI_AI meets that, so it isn’t forbidden, but it’s untested territory rather than officially supported.
If you do change it, the main risks are:
Collation conflict errors during migrations and upgrades (Cannot resolve collation conflict between "X" and "Y" — the classic v7→v8 case is issue #7663). Temp tables inherit tempdb’s collation, so match the SQL Server instance collation to the database collation on every environment.
ALTER DATABASE ... COLLATEdoesn’t change existing columns — you’d need to create the database with the collation before installing Umbraco.
_AI collapses hamza forms and tashkeel for equality too, not just sorting — that affects unique constraints (dictionary keys, member logins, lookup tables). I’d use _AS at the database level and handle diacritic-insensitivity in search.
SQLite dev environments won’t match, and packages (Forms, Commerce, Deploy) aren’t tested against Arabic collations.
The key point though: changing the DB collation won’t fix your search. Umbraco’s search runs through Examine/Lucene, which is completely independent of SQL collation. Lucene sorts by raw term bytes (code point order) — exactly the ordering that scatters the alef variants.
What I’d recommend instead:
Keep SQL_Latin1_General_CP1_CI_AS as the database default. nvarchar stores Arabic correctly regardless — collation only affects comparison and sorting, so you lose no data.
Set COLLATE Arabic_100_CI_AS at column level on your own custom/lookup tables, and add ORDER BY col COLLATE Arabic_100_CI_AS in reports and ad-hoc queries against Umbraco tables.
Fix search properly in Examine: use ArabicAnalyzer (already available via Lucene.Net.Analysis.Common) on your Arabic culture-variant fields via a PerFieldAnalyzerWrapper. It normalises alef forms, teh marbuta and tashkeel, plus stems.
For linguistic sort order, either use ICUCollationKeyAnalyzer (Lucene.Net.ICU) with an ar collator, or precompute a sort key at index time with CompareInfo.GetSortKey(...) and store it hex-encoded (not base64 — base64 isn’t ordinal-order-preserving).
The one genuine argument for the database-wide change is backoffice list-view sorting by Name, which is SQL-driven against umbracoNode.text. If that’s a hard requirement, altering that single column’s collation is doable, but means dropping/recreating its indexes and re-verifying after every upgrade.