Is it bad practice for services to share a database in SOA?

Question

I have recently been reading Hohpe and Woolf's Enterprise Integration Patterns, some of Thomas Erl's books on SOA and watching various videos and podcasts by Udi Dahan et al. on CQRS and Event Driven systems.

Systems in my place of work suffer from high coupling. Although each system theoretically has its own database, there is a lot of joining between them. In practice this means there is one huge database that all systems use. For example, there is one table of customer data.

Much of what I've read seems to suggest denormalising data so that each system uses only its database, and any updates to one system are propagated to all the others using messaging.

I thought this was one of the ways of enforcing the boundaries in SOA - each service should have its own database, but then I read this:

https://stackoverflow.com/questions/4019902/soa-joining-data-across-multiple-services

and it suggests this is the wrong thing to do.

Segregating the databases does seem like a good way of decoupling systems, but now I'm a bit confused. Is this a good route to take? Is it ever recommended that you should segregate a database on, say an SOA service, an DDD Bounded context, an application, etc?

Answer 1

Decoupling only works if there really is separation. Consider if you have an ordering system:

• Table: CUSTOMER
• Table: ORDER

If that's all you've got, there's no reason to decouple them. On the other hand, if you have this:

• Table: CUSTOMER
• Table: ORDER
• Table: CUSTOMER_NEWSLETTER

Then you could argue that ORDER and CUSTOMER_NEWSLETTER are part of two totally separate modules (ordering and marketing). Perhaps it makes sense to move these into separate databases (one for each table), and have both modules share access to the common CUSTOMER table in its own database.

By doing that you're simplifying each module, but you're increasing the complexity of your data layer. As your application grows larger and larger, I can see an advantage to separating. There will be more and more "data islands" that really have no relation to each other. However, there will always be some data that cross-cuts all modules.

The decision to put them in different physical databases would typically be based around real-world constraints like frequency of backups, security restrictions, replication to different geographic locations, etc. I wouldn't separate tables into different physical databases just because of separating concerns. That can be handled more simply with different schemas or views.

Answer 2

Where I work we have an ESB to which 6 different applications (or should I say "endpoints") are connected. Those 6 applications work with 3 different Oracle schemas on 2 database instances. Some of these applications coexist in the same schema not because they are related but because our database infrastructure is managed by an external provider and obtaining a new schema just takes forever (also, we don't have DBA access of course)... It really takes so long that at one point we thought of reusing an existing schema "temporarily" to be able to continue development. To enforce "separation" of data, table names are prefixed, for example "CST_" for customer. Also, we have to work with a schema that for some valid reasons we cannot absolute change... It's strange I know. Of course, as it always happens, "temporarily" has changed into "temporar-namently" if you know what I mean ;-)

Our different applications connect to their respective database schema and work with their own PL/SQL packages and we absolutely forbid ourselves to interact directly with tables/data that is outside our application domain.

When one of the application connected to the ESB needs information outside from its domain, it calls the related service on the ESB to obtain the data, even if that information is in fact in the same schema, requiring in theory just a little join statement in one of the SQL requests.

We do that in order to be able to split our application domain into different schemas/databases, and in order for the services on the ESB to still work properly when it happens (it's Christmas soon, we're corssing fingers)

Now, this may look strange and awful from the outside but there are reasons to that and I just wanted to share this concrete experience to show you that one or more databases is not that important. Wait, it is !, for many reasons (+1 for Scott Whitlock, see last paragraph about backup and such that mya lead you into trouble) But it is equally important I think to have your SOA services being properly designed, at least that is my opinion, and I'm not a DBA. Ultimately, all your databases belong to your "enterprise datawarehouse", right?

Finally, I won't rephrase Scott Whitlock's last paragraph, particularly this

I wouldn't separate tables into different physical databases just because of separating concerns.

is really super important. Don't do it if there is no reason.

Answer 3

I've seen the worst possible nightmares in software architecture due to data integration, and the best weapon against this type of mess that I've encountered so far id DDD-Style Bounded Contexts. Which is not very far away from "SOA done right", on a certain sense.

However, data itself is not the best way to attack the problem. One should focus on the expected/needed behavior and bring the data where it matters. We might end up having some duplication this way, but this is not normally an issue compared with the blocks to system evolution almost always associated with data-integrated architectures.

To put it simple: if you are looking for loosely coupled systems, don't stay coupled on the data. Go for weel encapsulated systems and a well-structured communication channel in between, acting as "lingua franca".

Answer 4

Decoupling databases and keeping the data consistent between them is an expert level task. It is very easy to get wrong and end up with the problems of duplicates etc, that the current system is designed to avoid. Frankly, taking a working system and doing this is pretty much a guarantee of introducing new bugs for no real value to the users.

Answer 5

If done correctly, separating business concerns into different databases (or at least different schemas) is a virtue.

Please see Martin Fowler's description of the CQRS Pattern:

As our needs become more sophisticated we steadily move away from [treating an information system like a CRUD datastore]... The change that CQRS introduces is to split that conceptual model into separate models for update and display... There's room for considerable variation here. The in-memory models may share the same database, in which case the database acts as the communication between the two models. However they may also use separate databases, effectively making the query-side's database by a real-time ReportingDatabase. In this case there needs to be some communication mechanism between the two models or their databases.

And NServiceBus Architectural Principles:

Command Query Separation

A solution that avoids this problem separates commands and queries at the system-level, even above that of client and server. In this solution there are two "services" that span both client and server - one in charge of commands (create, update, delete), the other in charge of queries (read). These services communicate only via messages - one cannot access the database of the other...

And Command and Query Responsibility Segregation (CQRS)

Command and Query Responsibility Segregation

Most applications reads data more frequently than they write data. Based on that statement, it would be a good idea to make a solution where easily you can add more databases to read from, right? So what if we set up a database dedicated just for reading? Even better; what if we design the database in a way so it’s faster to read from it? If you design your applications based on the patterns described in CQRS architecture, you will have a solution that is scalable and fast at reading data.