SQL Server Replication (cross-database queries & constraints)

Question

We want to replicate data from one database to several others (on another server). Would it make sense to replicate these tables to a shared database on the other server and have our cross-database queries reference the shared database... or would it make more sense to replicate out to each individual database on the other server? Would cross database joins pose a performance hit? Would cross-database constraints work as expected?

Replicating once to a shared database would help replication performance... I'm trying to evaluate whether or not any performance hit as a result of cross-database queries or constraints would be worth it.

Edit: It looks like cross database constraints are not possible in sql server? If this is true then we would have to replicate to each database

Answer 1

Cross database queries are somewhat slower that within the same DB. Foreign keys work within the same DB only. Usual approach is to create a separate schema in each DB (like ETL) and then replicate those tables to that schema. This approach is actually frequently used when replicating dimension tables between data marts.

When using cross-db approach, use triggers to implement constraints -- may be slow and complicated. Depending on your application, you may implement foreign keys as "logical only" and run periodic "look for orphans" queries to deal with referential integrity.