Refactoring away from the Shared Database pattern

Question

At work, we have several applications with databases in a centralized SQL server. Whenever one application needs to work with data from another application, it just queries it or updates it through the database. I believe this is the “Shared Database” pattern as described in the Enterprise Integration Patterns book (Hohpe & Woolf).

These cross database dependencies are causing us many, many headaches. The largest of these right now is that we’re running into performance issues on the SQL server, and can’t scale out because of the cross-database dependencies. I think what we should do is move away from the Shared Database pattern towards a messaging system as described in the EIP book. Each application would be responsible for all of it’s own data, and other apps that want to access that data would get it through services (on a messaging bus?).

• Where do we start refactoring towards the messaging pattern?
• Do we start by refactoring one of the applications to manage its own application database?
• Then what about the other applications are currently integrated with that one through the database?
• Is this the best way to decouple our database dependencies or should we be starting somewhere else?

Answer 1

I'd suggest a 3 phase transition.

1. Add a messaging layer to each of your applications.
2. Change all cross-application data access to use the newly created messaging layer.
3. Scale the (now-independent) databases as needed.

Also, say you have 3 applications; A, B, and C.

You could also view this as 3 separate transitions:

• Application A

  • Add Messaging to A
  • Change Calls to A in B & C

• Application B

  • Add Messaging to B
  • Change Calls to B in A & C

• Application C

  • Add Messaging to C
  • Change Calls to C in A & B

At this point in the process the results the results are the same as at the end of phase 2, and phase 3 can proceed. The difference is simply whether it is more productive to focus on a type of refactoring or to focus on an application.

Answer 2

Messaging can certainly be one of the more elegant ways to go. However, it also requires a bit of work and will have to change over time as each database changes. We've taken the "easier" approach by simply having each application know how to logon to the other database, and then query each database from there. We've found that most of the cross database queries are pretty light and therefore do not pose a substantial code base in the second app. There is some duplication of select queries, but it has been less work than a messaging system would have been.

It all depends on the degree to which you'll be pushing and pulling data around. If it is substantial, then messaging is the best way to go. If it is minimal, then perhaps a simple new connection to the other database is a way to go.

Answer 3

I'd also consider how the applications use the database. Typically a database (both design & implementation) should fall into one of two different categories: transactional (OLTP) or reporting (OLAP).

A well designed (and implemented) transactional database should provide excellent performance in a typical Line-Of-Business application scenario; likewise, a well designed (and implemented) reporting database should provide excellent performance when querying large amounts of complexity of data.

Another important distinction is the difference between 'real-time' and 'near real-time'.

Lets assume your various application need to do both transactional (real-time) operations and some reporting on current/older data; you'll need two data stores: a transactional one that is built for solely for operational speed to support the 'real-time' operations of the applications, and another that contains 'historical' data which will be built purely for reporting.

The trick then is to figure out how much data you need to keep in the transactional data store, and when / how to move it to the reporting data store.