Case 10: Unshackling the Database Bottleneck: A Strategic Data Architecture Transformation
You join a small e-commerce company as an engineering leader that is struggling with scaling issues because it is using legacy tech-stack - ASP.Net for web app, VB.Net for backend services, TFS for version control, one monstrous SQL Server instance that acts as both OLTP and OLAP, while also leveraging SSAS, SSIS and SSRS.
The big fat SQL Server instance has lots of DB Objects like Tables, Triggers, Stored Procedures, SQL Agent Jobs, etc. signifying that the architecture is heavily DB-centric and more so handling the needs of varied business functions.
The company grew so big that the current DB instance is struggling to meet the current demands and the leadership is worried about its capacity to handle the growing traffic. While re-architecture of the non-db layers provided some relief, the DB remains a bottleneck for a lot of heavy work of all business functions (Sales Order Processing, Purchase Order Processing, Accounting, and various BI Reporting for leadership) is handled by the DB via 100s of user-defined stored procedures and functions.
Some of the things that DB teams witness often are below:
- The TempDB bloat to the point of maxing out the disk space resulting in the re-boot of SQL Server
- Frequent slowness of the execution time of the scheduled jobs that run stored procedures.
- When the BI team runs queries on the DB, they experience painful delay or worse non-responsiveness in some occasions.
As a seasoned enterprise leader:
- What problems do you see in the current as-is Data Architecture?
- What would your proposed to-be Data Architecture?
- How would you go about drawing a road-map for the new architecture from the current one?