server,*: remove remaining uses of OptionalErr, UnsupportedWithMultiTenancy, OptionalGossip and related code

**Describe the problem**

The code still currently contains "imperfect" code paths that ought to be either generalized (to become independent of whether they run in system tenant or secondary tenant); or be entirely specialized to the system tenant because they don't make sense in secondary tenants at all (for example if they pertain exclusively to operating the storage layer).

**Eventually** we want all the SQL layer to not use KV-level data structures directly. All should go through a KV API over RPC and checked via the capability subsystem.

This issue is a dependency of #54252.

**To Reproduce**

Search for the following:

- `OptionalErr`
- `OptionalGossip`
- `OptionalNodeID`
- `OptionalNodesStatusServer`
- `TenantSQLDeprecatedWrapper`
- `UnsupportedWithMultiTenancy`

All these need to be eliminated.

Jira issue: CRDB-26645

Epic CRDB-26091