SOFTware is HARD

A sequence number or an identity column guarantees the following: Number served is unique. Number served is sequential (ascending order) - Identity columns guarantees this; sequence number in Oracle RAC mode doesn't, unless ORDER is used. However: It does not guarantee the sequence is gap-free Sequence number/ identity columns do not partake in transactions Number loss/ gaps happen due to the following: Served number does not get utilised (typically due to transaction rollback) Server restart/ failures - database servers tend to cache sequences in memory for performance reasons. E.g. Oracle Sequence number set-up to be CACHEd in memory. According to Oracle 11gR2 Database Documentation : for the Sequence database concept, use of Oracle Sequence does not guarantee gap-free set of numbers.   If your application requires a gap-free set of numbers, then you cannot use Oracle sequences . You must serialize activities in the database using your own developed cod...

Some food for thought. Consider the following: Prefer BASE over ACID transactions Prefer asynchronous over synchronous transactions Keeping state is expensive Considering database sharding ( highscalability , codefutures , Pros and Cons ) by data, by transaction or by customer but avoid premature optimisation Design the system for automated rollback Create isolative structures; share nothing; such that nothing crosses the swimlanes Design systems for failure Create idempotent services where possible Database sharding requires changes in mindset: Tables may need to be denormalised to optimise sharding (as well as to workaround cross-shard joins/ queries) Scale-out instead of scale-up Do away with replication where possible Different sharding schemes are: Vertical partitioning – sometimes known as functional or feature partitioning where data relating to certain entities are grouped together. Different functions or features are put onto different shards. Rang...

Need to keep these handy. At the same time, this site is lovely ( http://www.connectionstrings.com/ ) for accessing Databases (like Oracle) from .NET. For OCI (or Type 2) drivers: jdbc:oracle:oci:@ TNS_ALIAS jdbc:oracle:oci:@ <HOST> :1521: <SID> jdbc:oracle:oci@// host: 1521/ service_name jdbc:oracle:oci:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP) (HOST= cluster_alias ) (PORT=1521)) (CONNECT_DATA=(SERVICE_NAME= service_name ))) jdbc:oracle:oci:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST= host1 )(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST= host2 )(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME= service_name ))) For Thin (or Type 4) drivers: jdbc:oracle:thin: username / password @// host: 1521/ service_name jdbc:oracle:thin@// host: 1521/ service_name jdbc:oracle:thin@// cluster-alias:port / service_name jdbc:oracle:thin:@ <HOST> :1521: <SID> jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP) (HOST= cluster_alias ) (PORT=1521)) (CONNECT...

Emerging Technologies Cloud – for starters, for for non-business critical functions like CRM, email, collaboration Collaboration User experience Data deluge – what to do with so much data? Top Business Prioritises Business process improvement Reduce enterprise costs Increase use of information & analytics Improve workforce effectiveness Attract & retain new customers Business Intelligence State of Business Intelligence today – 20% of users in an organisation have BI in place; 80% of users do not. BI Solution Architecture Source system ETL processes Data Warehouse solution (ODS, Staging, Analysis cubes) Report presentation Microsoft Business Intelligence Strategy Familiarity – Office integration Collaborative – SharePoint integration Manageability – MS SQL Data Warehouse Trends in Data-Warehouse Increase in volume Reduce cost Adoption of appliances Move into MPP Desire for real-time analytics Realisation of the importance of data quality Microsoft...

What is an ODS? An environment: where data from different OLTP databases is integrated which provides a view of enterprise data that addresses operational challenges across more than one business function Characteristics of ODS: subject-oriented - catered to specific function or application (customer-centricity, risk management) integrated - from multiple legacy systems or new and legacy systems timely - data is continuously/ frequently being updated, typically more frequently than daily current - data is typically current with little history detailed - data is sufficiently detailed; not only at a summarized level central version of reference data ODS should be a separate data store from the data warehouse. Difference between ODS and Data Warehouse ODS DW Data Currency Current/ near-current Historical snapshot Data Loading Insert/ Update/ Deletion allowed Only loaded

This is summarised from a book titled: Data Modeling Some best practices are described below: Database indexing index foreign keys index on columns with a lot of null values is useless frequently updated columns should not be indexed may not be a bad idea to use table scans for small tables (less than 1K rows) short-rowed tables (few columns) should use index-organised table b-tree index benefits performance if values are selective (distinct). The higher the index selectivity ratio, the better Database views perform better than SQL statements since views are pre-compiled (but Oracle does cache statements) stored procedures perform better than views generally Naming convention Constraint : <TableName> _<Type> _<ColumnName> where may be PK, FK, UQ (unique constraint), CK (check constraint) Index : <TableName>_<Type>_<ColumnName> where may be UX (unique index), IX (non-unique) View : <EntityOrTableName> _VW Code table s...