The premise of this document is to provide reader with ideas on how to manage a growing number of reference databases as teams adopt Datical.
This document makes following assumptions:
- Each Datical project will have multiple pipelines. As such, each pipeline will require its own database (SQL Server) or schema(s) (Oracle)
- Others?
Reference Databases for SQL Server
Introduction
During early phase of Datical adoption, teams typically setup Reference Databases on their own, i.e., they may not be using centralized infrastructure from day 1. This allows teams flexibility to manage and configure their Reference Databases as well as underlying servers. However However, as Datical usage grows, the approach above would create a sprawl of Reference Databases. To To better manage demand, it would be ideal to move Reference Databases to centralized/shared infrastructure. Reference
Note that Reference Databases are typically structure-only databases without any significant amount of data (you . You may still need lookup data or test data to validate objects), one .
Reference Databases for SQL Server
You could put a large number of Reference Databases on single SQL Server instance. The The number of Reference Database per instance would be dictated by the size of each Reference Database (size depends on number of objects in the database and the amount of data needed in the database). How many Reference Databases can you put on a single SQL Server instance?
If dealing with multiple versions of SQL Server (2008, 2012, 2014, 2016, etc.). You could setup different versions of SQL Server on the same server. Each version of SQL Server would run as a separate Database Engine instance.
The best practice, however, for scaling out SQL Server-based Reference Databases would be to arrange separate servers for each version of SQL Server. And then run only one version of Database Engine (SQL Server instance) per server.
Reference Database for Oracle 11g
Since
Reference Databases for Oracle 12c
...