SQL Server: Separate schemas or separate databases

We have two products t开发者_运维技巧hat 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.

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.

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

I think that the decision of splitting the data in databases instead of schemes is highly dependable on if such products will always be used together and no other way, I mean, if it makes sense to think of such of products as one, or if they're two completely different products being used together.

Since you say that in the future you plan to use the add-on for other products as well I think the best solution would be to split the data across separate databases, 'cause later you're gonna face at least one of these scenarios:

• You install the add-on for another product and it contains information (at least the db-schema) for the current product.
• You will have to install the separate the add-on database every time with each other product.

Each of these make it hard to maintain... So I recommend to separate databases for the scenario you mention.

You should (severely!) test application performance for your application against both models (separate schema and separate database). If one proves unacceptable, go with the other. With the obvious said, the only truly compelling reason to use one form over the other ties in to the "add-on" functionality you describe. If this can be an "add-on" to multiple different systems, if you want a single add-on instance to be used by all installed and supported "base" applications, then you'd pretty much want to have that functionality encapsulated within its own database instance, so that it could be shared/accessed by any/all such instances.

Just as a thought-exercise, if you wanted (or had) to have the add-on functionality be stored only in the "first" application instance, with that code referenced by all subsequently installed instances, a solution supporting it could be based on synonyms (introduced in SQL 2005). Upon installation of a "base" app, a check would have to be made determining if and where the "add-on" code was found, and if it is found the necessary synonyms would be built referencing its hosting database. A similar installation routine would be required for when the add-on is installed, to identify and update all supported databases. (Its an intriguing idea, but separate database would be a better solution as regards to long-term maintenance and management.)