We have 3 level hierarchy in our business model. Entities at level 3 contains huge data (like dailyfacts tables).
In our current DB architecture, we have one single database & all level 1 entities separated by db schemas. All level 2 entities are being used only for logical grouping & don't contain any data as such. Then comes all level 3 entities. All daily level data is stored with respect to these entities. There is a dailyfacts table in each schema which stores data for all level 3 entities under that level 1 entity. Plus there are some other additional table are being used to store data for level 3 entities.
Currently our business is growing very rapidly & with this DB architecture we are not able to handle data properly. SQL timeouts & deadlocks have became daily headaches. Also if load on one entity increases then it starts affecting other entities as well. It very hard to scale horizontally with current DB architecture.
So after long discussion with team, we came to conclusion that we should create separate databases for each level 3 entity & one master database which will maintain all business model hierarchy. But the problem with this approach is that we will end up creating almost 100 databases(as per current number of entities & it will keep on increasing as & when new customer comes). & maintaining these many databases will become pain in the a** after sometime.
I am not an expert on DB architecture. So I want to know,
- Is it OK to have these many databases in production environment? Are we moving in right direction?
- If it is OK then what all things we should look at before/after migration? (all are newbies here)
- Is there any other alternative to solve above problem?
We are using,
- DBMS : SQL Server 2014
- OS : Windows Server 2012 R2
- Processor : Intel Xenon CPU E5-2630 v3 @2.40GHz, 16 Cores
- RAM : 128 GB
Thanks in advance.