Multiple applications for a single database architecture

Question

We are currently creating a new strategy for our internal applications. Currently, we have about 10-15 applications that are directly connected to the same database. This is obviously not very good, and we want to evaluate our options. As far as I can see, we have a choice:

• Duplicate the database and use replication etc. to synchronize them.
• Create a new application between the database and applications 10-15 on top.
• Other?

I would love to hear you intoxicated about this. I believe the second option is a path that also gives us an effective level for implementing caching. But how would you reveal this layer to all applications? Would webservices / rest be the way to go or are there other, better ways to do this?

Answer 1

I think the answer to the buzzword is “Service Oriented Architecture” is really option 2.

This is a large-scale undertaking, with many exciting deadlocks to explore - but basically, instead of thinking about databases and tables, think about the services that these 15 applications need. Some of them can be divided, some can be specific for one application. Find a way to expose these services to applications - but remember that a web service call can be significantly slower than an equivalent direct database call, so don't use a “service oriented architecture” to mean that you have to implement web services everywhere - it is more a thinking than a product specification.

Replication and synchronization - in my experience - create very fragile systems, with failure modes that make the brain even think.

The other is good, you don’t really say what specific problem you are trying to solve. If its performance is the cheapest way to solve performance problems, then throw hardware at the problem. If it is manageability, an SOA can help with this, but it often also introduces additional infrastructure to the composition, which also needs to be supported. Make sure that you really clearly understand what forces you to choose an architecture because there is no single “best” solution - all this involves compromises.

Answer 2

Analysis of the options that you reveal:

Duplicate the database and use replication etc. to synchronize them

If all applications should have access to the same database, I think this is a bad division, because you have encountered synchronization problems (outdated data, etc.).

Create a new application between the database and applications 10-15 on top.

This is a really good opportunity. If you do not want all of these applications to depend on the details of the database implementation (for example, changing one table affected the code of all of them), you can "hide" the database behind an application that offers "business significant" operations with these "client applications. "

If you run into performance issues, I would suggest clustering the database (instead of manually duplicating).

Answer 3

I do not understand why 10-15 applications should share a database? Do all applications really have to use all tables?

Start by moving all the tables unique to each application to a separate database.

When this is done, it should be pretty easy to see if there will be problems with multiple applications accessing the same database. This usually doesn’t matter if the applications do not start caching data (since you will never know if another application has updated some of the data that you are caching). The most typical approach is to use some kind of messaging for this.

Answer 4

An unexpected option is to have some logic in stored procedures / triggers, etc. Not a good idea, but an idea nonetheless.

I would say that your second option is the best choice here. If you are on the .NET platform, WCF is a really simple and efficient way.