SQL Server: Separate schemas or separate databases

Question

We have two products that get implemented at customer sites, one of which requires the presence of the other. I implemented a separate schema within the same database as the main product for the database objects needed by the add-on. Because the add-on could theoretically become an add-on to future products as well (although none are currently planned), I am reconsidering that decision. We currently use 2005, but plan to migrate to 2008 R2 or Denali in a year or so.

One factor is that maintaining the separate schemas in separate VS 2010 database projects is difficult because of the inability to restrict the view of a VS project to one schema when comparing the project schema to a database that contains another schema.

Are there any reasons to avoid splitting the two schemas into separate databases, assuming they will always be in the same SQL Server instance?

Backups are handled by scripts that operate on all databases in the instance, so that is not a concern. We are hoping to offer the products on a hosted (SaaS) basis in the future, so the impact on multi-tenancy is a factor. We would likely host multiple customers within an instance.

Answer 1

You don't get transactional consistency if they're in two separate databases. Depending on your HA/DR mechanism, the databases might get out of sync. Using database mirroring, for example, one database can be far ahead of the other in terms of transaction logs applied. One database might be current up to 10AM on the mirror, but the other database might only be current up to 9:55AM. In the event of a failover, boom, your two databases aren't in sync.

Answer 2

Remember that in the same database you can enforce foreign key constraints while you cannot in separate databases without writing triggers.