Varchar(MAX) in SQL Endpoint

Yes, this is expected behavior. Enabling New Metadata Sync only applies to endpoints created after the setting is enabled. Existing endpoints continue using the old sync engine, so there isn't currently a way to switch an existing one over.

That's also why you're seeing VARCHAR(MAX) work with the newly created Lakehouse but not the existing one.

For now, recreating the Lakehouse is the practical option. If the existing Lakehouse already has data, I wouldn't try to change it in place. I would create the new Lakehouse with the setting enabled first and then move or reference the existing Delta tables as appropriate.

Microsoft has mentioned an upgrade path for existing endpoints, but I would check the current roadmap/documentation before depending on that.