All approaches you are considering can work. if you're storing everything in one database, I'd recommend RLS.

Much more important question is do you need storing everything separately or in the same place.

Advantages of separated databases/servers:

  • Easy to scale
  • Would work faster on a lot of cases
  • Easy to delete/rebuild a chunk
  • Easier security

Advantages of unified database/server:

  • Cross-chunk queries are easier (For example: This venue is sold out on the dates you need. We recommend the following nearby venues)
  • Simple Backup/HA strategies

There are far more efficient ways to do that.

  1. Move tables you want shared into a separate Namespace with separate Code/Data databases.
  2. Map your data and code into original namespace.
  3. Verify that it all works as expected. So far it should work as before.
  4. Create a mirroring configuration.
  5. Add 2 created databases to the mirror configuration.
  6. Add second server as a DR mirror.
  7. Move databases to a DR mirror and mount them there.
  8. (Optional) Create a namespace with 2 mirrored databases.
  9. Add desired mappings on a second system.

Docs.