July 10, 2023 at 8:11 pm
Lately we've been asked to create several small databases. These seemed related. When we discussed this with the application developers they sent a link (https://microservices.io/patterns/data/database-per-service.html) that talks about individual services having their own database. The article also talks about schema per service, which is what we are advocating, though there isn't much to be found with that. It also seems to me that the logical conclusion of this is the splintering of databases that should be together simply because the front-end creates several different and individual services. The one advantage I can see if some of the services have needs that would be better addressed by a different platform. But in our case all the databases are SQL Server databases.
What are people's thoughts and what are your experiences with this methodology?
July 10, 2023 at 8:46 pm
In SQL Server, we have more databases on a service more easily than some other RDBMS. For the sake of argument, whether there is a separate db or schema isn't really relevant. These are still logical grouping constructs for security and organization. The place I might look for databases v schema is if I think the load will be high, especially with tempdb, and where I might ever separate these into different instances/servers. In that case, separate databases makes more sense.
The people doing microservices want everything to go through the service API, which makes sense for working with singletons. If you have enough horsepower, this works well. However, if you want to aggregate/analyze/report, then you send a lot of data through the API. In general, lots of people doing micro/mini services will need a data warehouse where they ETL/xfer this data around for aggregation. In lots of not-spotify-sized systems, we can just report in a the RDBMS.
If you choose to separate out, and you don't think you'll need separate instances at some point, then putting these services as separate schemas reduces the need to aggregate things for reporting. This also lets you run checks that might detect FK/quality issues between items in different schemas.
What I often find is that when we let the services manage all data quality, data quality suffers. Developers leave holes, they don't error check, there are places clients find ways to add weird data. Services might not be consistent in how they ensure transactions across service boundaries. I don't know I'd worry about FKs between schemas, but being able to query across schemas is handy.
Almost no one thinks about the load at scale with these services. And there is a load. What works great with a few hundred users on dozens of services can be a bottleneck with it's thousands of users (which you can get with little services) and making transactions across these service boundaries.
I'm not against it, but it's not as simple as most developers think it is.
July 10, 2023 at 8:47 pm
In SQL Server, we have more databases on a service more easily than some other RDBMS. For the sake of argument, whether there is a separate db or schema isn't really relevant. These are still logical grouping constructs for security and organization. The place I might look for databases v schema is if I think the load will be high, especially with tempdb, and where I might ever separate these into different instances/servers. In that case, separate databases makes more sense.
The people doing microservices want everything to go through the service API, which makes sense for working with singletons. If you have enough horsepower, this works well. However, if you want to aggregate/analyze/report, then you send a lot of data through the API. In general, lots of people doing micro/mini services will need a data warehouse where they ETL/xfer this data around for aggregation. In lots of not-spotify-sized systems, we can just report in a the RDBMS.
If you choose to separate out, and you don't think you'll need separate instances at some point, then putting these services as separate schemas reduces the need to aggregate things for reporting. This also lets you run checks that might detect FK/quality issues between items in different schemas.
What I often find is that when we let the services manage all data quality, data quality suffers. Developers leave holes, they don't error check, there are places clients find ways to add weird data. Services might not be consistent in how they ensure transactions across service boundaries. I don't know I'd worry about FKs between schemas, but being able to query across schemas is handy.
Almost no one thinks about the load at scale with these services. And there is a load. What works great with a few hundred users on dozens of services can be a bottleneck with it's thousands of users (which you can get with little services) and making transactions across these service boundaries.
I'm not against it, but it's not as simple as most developers think it is.
July 19, 2023 at 7:12 am
"In theory, theory and practice are the same. In practice they are not" - A Einstein
The biggest issue you will face going forward, as Steve says, is scaling. If you have the time, get the DB folk and the developers to build a test system with their favoured approach and see what works the best. If you don't have the time then the expertise of the subject experts (ie the DB folk) should be favoured.
This is a risk-based approach. An organisation should trust its subject experts to propose and explain their favoured solution, and then use it. Sometimes the experts get it wrong, but ametures get things wrong much more often.
Steve has highlighted some advantages of separate schemas in a single database compared to separate databases. This is not the first operational DB issue he has looked at, he knows his stuff.
I also have experienced many issues between developers and DB people over design. Managers appreciate explanations and risk analysis, it is their job to take the best decision for the business. Let the DB folk explain about multiple schemas and scaling and associated performance issues and cost. Hopefully the PHB (see Dilbert) will make the right decision.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 19, 2024 at 6:24 am
In my experience, the decision to have a separate database per microservice depends on the specific requirements of the system. In some cases, it makes sense to have a separate database per microservice, while in other cases, it may be better to have a shared database. It's important to carefully consider the pros and cons of each approach and choose the one that best meets the needs of the system.
February 19, 2024 at 12:39 pm
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply