"Or is Activiti intended to be used with only 1 database schema per server?"
Activiti can be single-tenant, multi tenant with one database or multi tenant with different tenant schema's
"will Activiti remain in memory"
No, Activiti does not keep anything in memory. Only when going from a step A -> B the instance is loaded, changed and persisted again.
"will Activiti keep the database connections open"
No.
Only the job executor will regularly poll the database, but open/close the connections.