Necessity of a DB-replication as extra security layer

database-designreplication

I am confronted with a given infrastructure as following:

A "main" DB has a master-slave replication DB, which only contains certain tables.
A certain DB-user has read-only access to this replication.

A certain application currently uses this user to perform read-operations on the replication.
To my understanding this concept was introduced as security measurement. I am thinking that it might not be neccesary at all and a simple Read-Only user on the "main" DB would be enough.
I am not sure whether I am overlooking a crucial advantage of the current setup. The pros and cons I see:

Pro replica:

  • Extra layer of security?
  • If the application (publicly accesable) is attacked / DDoS´d, only the connected replica DB will be affected and the main DB can operate normally

Con replica:

  • Extra hardware ressources needed. These could be used to increase the main DB peformance
  • Currentness of data will always be slightly lagged (a few seconds)
  • Higher maintenance

I know this information might not be enough to properly evaluate the situation, but I feel like I am missing a crucial point. Currently I would like to remove the replication DB, setup Read-Only users on the main DB and increase its performance.

Any thoughts?

Best Answer

If the application (publicly accessible) is attacked / DDoS´d, only the connected replica DB will be affected and the main DB can operate normally

That's the reason.

If the Application running against the replica database is publicly accessible, then it can be attacked. If it is attacked then, as you say, the replica database will bear the brunt of that attack. The remainder of the company, running against the primary database, can continue operating [largely] as normal.

As long as the web application is of secondary importance to your company, then this arrangement makes sense. If you work for eBay or Amazon? Not so much!

That's not to say that the primary database is completely unaffected by this! The replication processes, trying to write to the over-loaded replica, may be impacted too, which may feed back into poorer performance on the primary or, depending on how the replication system works, potential disk space problems with logs not being shipped and cleared down as quickly as they should be.

Related Question