Welcome

Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk . Microsoft Data Platform MVP

"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein



Wednesday 20 June 2012

The Dichotomy of Database Models

There has been significant debate on ACID and BASE and which is the way forward. A summary of the two models is described here.

ACID model

The relational database model for transactions are based on ACID properties, the term coined by Haerder & Reuter in 1983. This guarantees reliability, consistency and recoverability. It has four properties:

Atomicity
It is an ‘all or nothing’ approach. The transaction is performed in its entirety or not performed at all. Therefore if any part of the transaction fails it all fails and no change is made.

Consistency
The database transforms from one consistent state to another consistent state. The database cannot be left in an inconsistent state.

Isolation
Transactions execute independently of each other. This ensures that even if concurrent executions of transactions happen they are left in a state as if the transactions have executed serially. Partial and incomplete transactions should not be visible to other transactions.

Durability
Successful completed transactions are committed into the database and stored permanently. They persist after restarts.

New Patterns – CAP theorem

The concepts and patterns discussed in the paper ‘NoSQL Databases’ by Christof Strauch demonstrates the new concept to reduce complexity. The CAP-theorem was coined by Eric Brewer in 2000. It has three guarantees:

Consistency
The data is correct all of the time after the execution of an operation. A distributed system is typically considered to be consistent if, after an update operation, all readers see the updates in a shared data source.

Availability
It guarantees that data can be read and written all the time due to the architectural design and implementation.

Partition Tolerance
The system has the ability to continue to operate in the presence of a failure of part of the system.

BASE model
The concepts of NoSQL datastores use the  BASE pattern.  “The BASE approach according to Brewer forfeits the ACID properties of consistency and isolation in favour of “availability, graceful degradation, and performance” Strauch.

The acronym BASE is composed of the following characteristics:

Basic Availability
It uses replication techniques to reduce data unavailably. It is highly distributed and uses sharding and partitioning of data within its storage model.

Soft State
This allows data to be inconsistent and assumes that it is managed by the developer and not the database.

Eventual Consistency
At some point in the future the data will reach a consistent state. It is unknown when that will happen.

Brewer contrasts ACID with BASE  Design Patterns considering the two concepts as a complementing spectrum.
 
NoSQL Databases article page 32 by Christof Strauch
In conclusion we have two complementary design patterns for a universe where both structured and unstructured data reside.

Friday 1 June 2012

Cube Synchronisation Scale Out Methods

There are 4 Analysis Services Synchronisation Methods
  • Analysis Services Synch Method
  • Backup/Restore Database
  • Attach/Detach Database
  • Robocopy Method
You can read more after the Querying and Processing cubes for scale out section

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/03/16/analysis-services-synchronization-best-practices.aspx

Synchronize Analysis Services Databases
http://msdn.microsoft.com/en-us/library/ms174928.aspx

Other useful articles for scaling out Analysis Services are

REAL PRACTICES: Performance Scaling Microsoft SQL Server 2008 Analysis Services at Microsoft adCenter
http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/03/14/real-practices-performance-scaling-microsoft-sql-server-2008-analysis-services-at-microsoft-adcenter.aspx

Scale-Out Querying with Analysis Services
http://sqlcat.com/sqlcat/b/whitepapers/archive/2007/12/16/scale-out-querying-with-analysis-services.aspx (download white paper http://technet.microsoft.com/library/Cc966449 )

Analysis Services 2008 R2 Performance Guide
Includes
Design Patterns for Scalable Cubes
Tuning Query Performance
Tuning Processing Performance
Special Considerations
http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/10/10/analysis-services-2008-r2-performance-guide.aspx