Integration: Shared Database
In my last post, I introduced a scenario where we need to allow two applications to make use of the same customer information:
The Web Store already has a SQL Server database. Since we're still designing the Marketing application, we could just make it use the same database. The result would look like this:
This is probably the simplest solution that could possibly work, but it has a few downsides:
- Since changes to the schema could affect the other team, changes need to be co-ordinated.
- Storage and indexing need to be optimized for the access patterns of both applications, which might be hard to accommodate.
In essence, the key problem is coupling. We create a ripple effect any time we try to change a shared database.
Unless the applications are talking through a façade, such as a stored procedure layer, it's difficult for one application to isolate itself from another.
As the enterprise grows, the effects of this become much worse. If more applications are built on top of this database, adding a column to a table could involve meetings between four or five teams, all with different priorities.
Mommy, where do DBA's come from?
Over time, the database morphs from being "just a place where the application stores its data", to the most critical piece of infrastructure in the organization. To deal with this, organizations often hire dedicated Database Administrators. Their job isn't just to keep the server running, but to act as strict guardians of any changes to the schema.
With DBA's come a strictly defined change control process. Instead of just adding a column to a table, an application developer might find themselves having to justify their case to a DBA, even if the column isn't used by any other application. The DBA might be busy responding to change requests from other teams, leaving the application developer blocked.
Chances are you've seen the shared database solution many times. It's probably the most common way of sharing data between applications (or in monolithic applications that should have been many small applications). What are some of the positive and negative experiences you've had with it?
Welcome, my name is Paul Stovell. I live in Brisbane and work on Octopus Deploy, an automated deployment tool for .NET applications.
Prior to founding Octopus Deploy, I worked for an investment bank in London building WPF applications, and before that I worked for Readify, an Australian .NET consulting firm. I also worked on a number of open source projects and was an active user group presenter. I was a Microsoft MVP for WPF from 2006 to 2013.