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.

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

Database Conference Wish List

Database conferences are the place to share ideas and learn new technology.  They are the place to expand ones database horizons and dream. My wish list for conference attendance would have to be

Professional Conferences

SQLBits
UK Location
http://sqlbits.com/
Probably End September 2012

SQLRally Nordic
Copenhagen
http://www.sqlpass.org/sqlrally/2012/nordic/
October 1-3 2012

SQL Pass Summit
Seattle, WA
http://www.sqlpass.org/summit/2012/
November 6-9 2012

Research Conferences

To explore cutting edge ideas in the database world.

ICDE 29th IEEE International Conference on Data Engineering
Brisbane, Australia
http://www.icde2013.org/
April 8-11 2013

2013 ACM SIGMOD/PODS 
New York, USA
http://www.sigmod.org/2013/ 
June 22-27, 2013

39th International Conference on Very Large Data Bases
Rive del Garda, Trento
http://vldb.org/2013/
August 26-31, 2013

Data Conferences

Data and information management.  

Enterprise Data World 2012
Atlanta, Georgia
http://edw2012.wilshireconferences.com/index.cfm
April 29 - 3 May 2012

SQL Relay

I attended SQL Relay 2012 in Clifton, Bristol yesterday which brought together  SQL experts from the southwest. The location was fantastic at the Avon Gorge Hotel which overlooks Brunel’s suspension bridge. The al fresco dining for lunch in the glorious Sun added to the interesting SQL discussions.

There were overview sessions from Microsoft in the morning on SQL Server 2012 and Deep Dive sessions in the afternoon from SQL Server MVP's.

The morning sessions delivered by Microsoft by Morris Novello, SQL Server Product Manager and Andrew Fryer, Technical Evangelist discussed the explosion of data volumes, new innovations in the cloud and new development features within SQL Server 2012. The practical overview of SQL Server 2012 new features showed the breadth and depth of the changes to the product which will take SQL Server to a new level.

The afternoon MVP’s Tony Rogerson covered SANless high availability with Always On and Martin Bell covered Distributed Replay. Considering disk geometry added an interesting dimension to Always On and being able to mitigate risk against hardware consolidation and changes using distributed replay was insightful.

Data Warehouse Physical Architecture Options

Custom Data Warehouse Architecture
Scale not defined
Custom design
  
Fast Track Reference Architecture
Scale between 4 and 80TB
Rapid Deployment and Use reference configurations optimized for data warehousing.
http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/fast-track.aspx

HP Business Data Warehouse Appliance
Optimised for up to 5TB
Highly tuned solution that integrates hardware and software.
http://www.microsoft.com/sqlserver/en/us/solutions-technologies/appliances/hp-bdw.aspx

Parallel Data Warehouse
Scale to 100s of terabytes
Parallel processing (MPP) architecture to gain scalable performance, flexibility, and hardware choices with the most comprehensive data warehouse solution available.
http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/pdw.aspx

SQL Server 2012 Databix

Enriched SQL-Flavoured cereal to nourish you and your customers

With three new ingredients to fortify customers and partners!

Cloud Based Learning Routes for SQL Server 2012

The volume of free training available for SQL Server in the cloud is continually increasing. The best sites I have found are below.

TechNet Virtual Labs: SQL Server 2012 Virtual Labs
http://technet.microsoft.com/en-us/virtuallabs/hh859575
This allows you to evaluate and test Microsoft's products and technologies with a series of hands-on labs that you can complete in 90 minutes or less.

Microsoft Virtual Academy
http://www.microsoftvirtualacademy.com/Home.aspx
This is a training portal where you can learn at your own pace, focusing on Microsoft technologies, gain points and get recognition. There are tracks in SQL Server, SQL Azure, Clustering, Business Intelligence etc.

SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos
http://technet.microsoft.com/en-us/sqlserver/ff977043
These videos are for those wanting to pursue SQL Server 2008 Microsoft Certified Master certification. Each module establishes the level of depth and breadth required to achieve this certification with deep dive subject matter included. These are good watch even if you are not thinking of taking the MCM exams.

SQL Server Best Practices
http://technet.microsoft.com/en-us/sqlserver/bb671430
Technical White Papers provide real-world guidelines, expert tips, and guidance for SQL Server.

EBooks
http://www.red-gate.com/community/books/
Free PDF downloads of ebooks specializing in database and programming technologies.

SQLBits
http://www.sqlbits.com/
Excellant community conference videos of recorded sessions which are available to watch on demand. 

24 Hours of PASS
http://www.sqlpass.org/24hours/spring2012/
The 24 Hours of PASS event is a virtual stage of SQL Server experts with in-depth sessions on the hottest SQL Server topics over 24 consecutive (and free!) technical webcasts.