We have had the experience of using a variety of relational and analytical databases as the data storage for some very data-intensive application software. In some of the applications, the data-handling was done by the middleware with the help of ORMs (like Hibernate, LINQ), in other cases, we have used traditional approach of encapsulating logic in the databases in Stored Procedures, Functions etc.
We have used following database technologies in various projects.
We have used Microsoft SQL Server, MY SQL and Oracle in many projects.
Design and Development
- Design of relational data model from application software requirement
- Query optimization while handling huge amounts of data (design of indexes)
- Extensive use of views, temporary tables, stored procedures, user-defined functions, triggers on a case-by-case basis
- Use of distributed transaction for multiple connectors as well as single connector transaction with advanced features like save-points
- Use of full text search capabilities
- Use of proprietary database technologies like MS SQL Server CLR Stored Procedure programming
- Design of multidimensional schema from the transactional database
- Design of MDX queries
- Use of sharding, re-partitioning, bulk copy scripts
Deployment and maintenance
- Deployment of federative servers
- Deployment of HA/Failover
- Monitoring databases with the help of network management protocols like SNMP/WMI
- Database maintenance (like shrinking with DBCC commands for MS SQL Server)
- Security arrangements (schema and access control)
- Back-up scheduling
- Automated ETLM (mainly with the help of Microsoft DTS and custom codebases)
We have used in-memory databases like HSQL DB for on-demand business analytics, for low/medium data volumes and to cater for high performance requirements
Document Oriented/KV Database
We have used Mongo-DB in a couple of projects for higher data retrieval performance.
We have used BaseX XML DB engine for advanced XQuery and full text queries on very large xml files.