Is this an appropriate architecture, or can improvements be made?

Question

Due to a combination of business/enterprise requirements and our architect's preferences we have arrived at a particular architecture that seems a bit off to me, but I have very limited architectural knowledge and even less cloud knowledge, so I would love a sanity check to see if there's improvement that can be made here:

Background: We are developing a replacement for an existing system that is a complete rewrite from the ground up. This requires us to source data from an SAP instance through BAPI/SOAP Web Services, as well as use some databases of our own for data not in SAP. Currently all of the data that we will be managing exists in local DBs on a distributed application, or in a MySQL database that will need to be migrated off of. We will need to create a handful of web applications that replicate the functionality of the existing distributed app, as well as providing admin related functionality over the data we control.

Business/Enterprise Requirements:

• Any databases we control must be implemented in MS SQL Server

• Minimize the number of databases created

• Phase 1 will have us deploy our applications to Azure, but we need the ability to bring these applications on-prem in the future

• Our Ops team wants us to dockerize everything as they feel it will make their management of the code a lot simpler.

• Minimze/eliminate replication of data

• The coding stack is going to be .NET Core for microservices and Admin apps, but Angular 5 for the main front-end application.

From these requirements our architect came up with this design:

Our front-ends will feed from a series of microservices (I use that term lightly as they are 'Domain' level and rather large), which will be broken into Read Services and Write Services in each domain. Both will be scalable and load balanced through Kubernetes. Each will also have a read-only copy of their database attached to them within their container, with a single master instance of the db available for writes that will push out updates to those read only copies.

(Sorry for the poor quality images, I'm redoing them from memory since, naturally, there's no actual documentation for this stuff except in the architect's head)

Service to Service communication will happen through a message queue that each service will listen to, and process any relevant messages. The primary use of this will be for email generation, as there isn't anything else we've identified that requires service to service communication for information yet. Anything "business logic" related that would require multiple services being involved would likely flow from the front-ends, where the front-ends would call each service individually and deal with atomicity.

From my perspective, the thing that rubs me the wrong way is the read-only db instances spinning up inside the docker containers for the services. The service itself and the db would have drastically different demands in terms of load, so it would make a lot more sense if we could load balance them separately. I believe MYSQL has a way of doing that with master/slave configurations, where new slaves can be spun up whenever load gets high. Especially while we have our system in the cloud and are paying for each instance, spinning up a new instance of the whole service when we only need another db instance seems wasteful (as does the opposite, spinning up a new db copy when we really just need a web service instance). However, I don't know the limitations of MS SQL Server for this.

My largest concern is around the MS SQL Server implementation. Coupling the read only instances so tightly to the services feels wrong. Is there a better way to do this?

NOTE: I asked this over on software engineering and they pointed me here. Sorry if this is not the appropriate SE.

Also there's no MS SQL Server tag

Answer 1

My largest concern is around the MS SQL Server implementation. Coupling the read only instances so tightly to the services feels wrong. Is there a better way to do this?

Essentially what you have designed is a caching system - the service containers have a local copy of the data presumably so that for reads they don't have to make an extra network trip.

As you've pointed out, a more standard approach is to have a cluster of read replicas that all of the containers can read from. This allows you to scale them separately from the application servers, which is good, because they generally need different things (do you really want to allocate large amounts of RAM to every application container?). This will add network calls for database reads, but until that's proven to be an issue I wouldn't complicate the architecture to solve it.

If it does become an issue, a much more lightweight way of handling the problem is to run an actual cache locally, like memcache or redis. You can tune the TTLs on individual objects to be appropriate, and it will automatically drop off rarely-requested data to keep the application server light.

Answer 2

I could talk a lot about the architecture yet this is a devops community so I will addresses your main concern about running the database only.

Short answer:

If you were to modify the design to say "Azure SQL" for each microservice then it would look okay to me. Each microservice can have its own separate Azure SQL database instance (likely running on a shared cluster but that is invisible to you). To move it on-prem later you can decide if you want to build an "Azure SQL-like" setup on kubernetes or simply run a traditional SQL Server cluster on-prem. Using Azure SQL doesn't lock your architecture into Azure as I will discuss below.

Long answer:

MS SQL Server requires a huge amount of memory compared to opensource databases or stateless application services. It also requires software licences (we will look at optimising cost below). So it may be the case that it simply isn't particularly cost-effective to run an licensed instance per service. You could license one SQLServer instance and run many per-service private databases on it. Better yet use Azure SQL the managed database-as-a-service version of SQL Server. That doesn't lock them into Azure as you can move to AWS which has "Amazon RDS for SQL Server". Every serious cloud provider will offer a professionally managed database service for all the major databases.

Also as you point out running a database in the same kubernetes pod as your application server code won't allow you to scale them independently. Also, stateless application servers can run on pods that have no persistent storage. Then stateless application servers can die and be restarted at will and moved between cloud availability zones trivially. Obviously, databases need persistent storage that is "owned" by pod. So a database needs a persistent volume claim. If you want them to run with high availability you need them to run as a stateful set. Running the database in the same pod as the application server is something that a developer might run locally, but I wouldn't recommend it for SQL Server, as it starts up 10x slower than your code. Rather on a mac laptop run the SQL Server docker image exposing its port. On a windows laptop run SQL Server Express natively.

It is considered a very standard architecture for each microservice has it's own logical (but not necessarily physical) database, but to run as a stateless service with many replicas, so that they can be scaled independently. Also Azure has very good support for Redis as a cache. It is therefore considered a standard architecture for each microservice to have its own logical private database, it's own private Redis cache, and many independently scaled pods. If you are renting Azure SQL and Azure Redis you don't get to choose how they run it physically. And why would you want to? Let the professionals figure out how to run a resiliant setup that has high performance that you can "pay for what you use" and focus on writing your business logic not on managing stateful services in the cloud that you can easily rent.

I have seen developers running MS SQL Server Express locally on their laptop to debug and unit test, then deploy to Kubernetes on Azure where the microservices databases are all running on Azure SQL. I have also seen teams run against Azure SQL in production, but test against SQL Server setup on plain VMs in Azure. Why? Because Azure SQL only comes with "production pricing". That organisation already had SQL Server on-prem and DBAs so it was cheaper for them to install and run SQL Server on VMs in Azure to host the test envs. All the microservices had their own private databases. In test those per microservice database where on a shared SQL Server instance on VMs with a two node cluster for resilience. In production the per microservices instances were all on Azure SQL for high performance and high availability, likely running a shared cluster that is hidden from the customer.