Forum Discussion
Multiple databases versus single database with multiple schemas
Hi,
I am designing a solution where data comes from multiple sources into an on-premises SQL Server for analytical purposes. The sources are real-time events via Kafka mapping into tables as is information and then developing augmented tables and then aggreating to make the final tables (similar to medallion architecture). Similarly, I have near-real-time data coming from other sources, which would follow the same pattern: raw, augmented/filtered/enhanced, and aggregates as of the real-time data flow. I will be leveraging SSIS packages for data movements from raw to augmented/filtered/enhances, augmented to aggregate layers.
For this, should I have different databases for each of my raw, enhanced/filtered, and aggregated data tables? Or should I have a single database with three schemas for each of my raw, augmented/filtered/enhanced, and aggregate layers?
Which approach is feasible, and the main questions I have in mind are:
If someone is querying data in one layer, would there be performance issues for parallel queries happening in other layers?
How is compute isolated within SQL Server—multiple databases versus a database with multiple schemas?
I appreciate any ideas, guidance, and possible reference documentation.
Thanks,
Raj
Use Single Database with Schemas If:
Your workload is medium-sized and manageable within one database.
Cross-layer queries are frequent, and query simplicity is a priority.
You want to minimize administrative overhead.
Use Multiple Databases If:
You have very high data volumes and concurrent queries.
Compute isolation is critical to prevent one layer’s workload from impacting another.
You require distinct configurations for each layer (e.g., filegroups, recovery models).
For your case, where data flows through multiple layers (raw → augmented → aggregate), start with a single database and multiple schemas. Monitor the performance and, if resource contention becomes an issue, consider splitting into multiple databases.Mks_1973- Thanks very much for a prompt suggestion! this is very helpful.
