Multi-Database Support
1. Feature Overview
The PostgreSQL-Database Support feature enhances the DataTrust (DT) application to support both Microsoft SQL Server and PostgreSQL for metadata and session database requirements.
Previously, DT supported SQL Server as the primary database platform. With this enhancement, customers can use either SQL Server or PostgreSQL based on their environment and business requirements.
The feature is designed to provide consistent DT functionality across both supported database platforms without impacting existing application capabilities.
2. Feature Objective
The objective of this feature is to provide database flexibility within the DT application and reduce dependency on a single database platform.
The enhancement enables:
- Support for Microsoft SQL Server.
- Support for PostgreSQL.
- Metadata storage and retrieval using either supported database.
- Session data storage and retrieval using either supported database.
- Consistent application functionality across both databases.
- Flexibility for customers to select the database platform based on their environment.
3. Functional Description
DT can now be configured to work with either SQL Server or PostgreSQL for supported metadata and session database operations.
Once the database is configured, the application performs its regular operations using the selected database.
The user experience and core DT functionality remain consistent regardless of whether SQL Server or PostgreSQL is being used.
The feature covers database-dependent operations across DT modules, including:
- Metadata management
- Session management
- Query execution
- Scenario execution
- Data validation
- Data reconciliation
- Data profiling
- Reporting
- Dashboard operations
- Batch processing
- Data management operations
4. Supported Database Platforms
Database Platform |
Support |
|---|---|
Microsoft SQL Server |
Supported |
PostgreSQL |
Supported |
Existing SQL Server environments continue to be supported, while PostgreSQL environments can now be configured for supported DT database requirements.
5. Database Compatibility
The feature ensures that DT operations work consistently across SQL Server and PostgreSQL.
The following areas are covered as part of database compatibility:
- Data retrieval
- Data insertion
- Data updates
- Data deletion
- Metadata processing
- Session processing
- Scenario execution
- Data validation
- Reconciliation
- Reporting
- Dashboard information
- Batch processing
The expected results should remain consistent when the same operation is performed using either supported database.
6. Data Integrity
Data integrity is maintained when DT operates with either SQL Server or PostgreSQL.
The feature supports validation of:
- Record counts
- Column values
- Null values
- Default values
- Data types
- Data precision
- Data length
- Date and time values
- Special characters
- Unicode and multilingual data
Data should not be lost, duplicated, incorrectly transformed, or altered unexpectedly because of the selected database platform.
7. Database Switching
The feature supports scenarios where DT environments use different database platforms.
For example:
SQL Server Environment
DT Application
↓
SQL Server
↓
Metadata / Session Data
PostgreSQL Environment
DT Application
↓
PostgreSQL
↓
Metadata / Session Data
The application should provide the same expected functionality in both environments.
8. Impact on Existing Users
There is no intended functional change for existing users who are using SQL Server.
Existing SQL Server-based deployments should continue to operate as before.
The major enhancement is the additional option to use PostgreSQL where required.
9. Modules and Functional Areas Covered
The database enhancement is applicable to functional areas that depend on metadata and session database operations.
Validation should include:
- Query Studio
- Scenario Studio
- FDR
- TDR
- Validation
- BDV
- Data Profiling
- Metadata Extraction
- Reporting
- Dashboard
- Database Management
- Asset Inventory
- System Activity
- Security
- Batch processing
10. Performance and Scalability
The feature should maintain acceptable performance across both supported database platforms.
Validation should include:
- Normal-volume data processing
- Large-volume data processing
- Batch execution
- Parallel execution
- Scenario execution
- Metadata extraction
- Reporting
- Large exception datasets
Database selection should not introduce unexpected performance degradation for supported workloads.
12. Security and Access Control
Existing security behavior should remain consistent across SQL Server and PostgreSQL environments.
The following should be validated:
- User authentication
- Role-based access
- Group permissions
- Folder-level permissions
- Scenario execution permissions
- Data access permissions
- Sensitive data protection
Users should only be able to access functionality and data for which they have appropriate permissions.
13. Expected Benefits
The Multi-Database Support feature provides the following benefits:
- Database flexibility: Customers can use SQL Server or PostgreSQL.
- Deployment flexibility: DT can be deployed in different database environments.
- Reduced dependency: DT is no longer limited to a single database platform.
- Consistent functionality: Core DT functionality remains consistent across supported databases.
- Improved scalability: Provides a foundation for supporting additional database platforms in the future.
- Customer adaptability: Supports different customer infrastructure and technology requirements.
14. Feature Outcome
After implementation of this feature, DataTrust supports Microsoft SQL Server and PostgreSQL for the supported metadata and session database requirements.
Users can configure DT according to their environment while continuing to use existing DT capabilities such as query execution, scenario processing, validation, reconciliation, profiling, reporting, and batch operations.
The feature improves the overall flexibility and adaptability of the DataTrust application while maintaining compatibility with existing SQL Server deployments.