24 hours of PASS

24 hours of PASS provided 24 back to back hours of free SQL Server Training. The event ran from Thursday 20 September 2012 until Friday 21 September 2012. There were some amazing sessions again http://www.sqlpass.org/24hours/fall2012/ . This event gave you a glimpse into the level of forthcoming technical content to be covered at the PASS Summit 2012.

I was very interested in the session delivered by Denny Lee on the Introduction to Microsoft’s Big Data Platform and Hadoop Primer. He defined Big Data as 4V's volume, velocity, variability and variety utilising techniques and technologies that make handling big data at extreme scale economical. Big Data is allowing us to ask new sets of questions. He discussed Scale up and Scale out commoditized distribution.

Hadoop

 

He moved onto discussing what is Hadoop explaining that a lot of data is machine generated these days and the data is loaded first then modelled after.  The infrastructure allows automatic distribution and replication across nodes. Hadoop was based on the Yahoo Nutch project in 2003 and renamed to Hadoop in 2005. A reference book to look at is Tom White's Hadoop: The Definitive Guide.

To find out more about Microsoft's product  https://www.hadooponazure.com/ .

The Multi Facets of a Data Scientist

The Data Scientist role is an interesting evolution in the BI and Data Mining filed.  This role is the new name for a quant and an e-branding of statistical literacy as "data science". There are a few interesting quotes I have seen in the last few weeks defining data scientists which I thought were worth sharing.

•    Defines a Data Scientist as a person with mathematical and statistical skills, an investigative mind, an understanding of computer languages like C++ and Java and ability to write code. http://www.forbes.com/sites/tomgroenfeldt/2012/01/17/big-data-needs-data-scientists-or-quants-or-excel-jockeys/
•    A data scientist is somebody who is inquisitive, who can stare at data and spot trends. It's almost like a Renaissance individual who really wants to learn and bring change to an organization. http://www-01.ibm.com/software/data/infosphere/data-scientist
•    A data scientist includes not only crunching numbers, but also visualizing the results. http://www.technologyreview.com/view/425561/big-data-means-business-needs-mathematicians/
•    The data scientists look for patterns as branches and tributaries join and pull away from the “river.” http://www.forbes.com/sites/danwoods/2012/07/23/what-is-a-data-scientist-tom-wheeler-of-clickfox/2/
•    A new class of engineer, the "data scientist," whose job it is to perform the sophisticated mathematical gymnastics required to extract actionable information from this mass of numbers. http://www.technologyreview.com/view/425561/big-data-means-business-needs-mathematicians/

An article in computing http://www.computing.co.uk/ctg/analysis/2201583/computing-research-the-power-of-data-science explained the difference between the BI specialist and Data Scientist.

“ BI specialists understand data and analytics, their focus tends to be on the technical aspects such as implementing the software and controlling how data is stored within the system….. A data scientist is generally less concerned with the technical nuts and bolts and more interested in the analytical side, revealing the messages that lie hidden in the data and uncovering insights that can deliver immediate competitive advantage”

Data Scientists have good mathematical and communication skills, who will be analytic innovators through their ability to gain insight from big data. A recent study from EMC shared further insight on this new field http://practicalanalytics.files.wordpress.com/2012/01/datascientistinfographic.jpg

Database Landscape

Came across these excellant 2 diagrams from 2 very interesting blog posts.

“NewSQL-as-a-Service” Cloud Database

This diagram  shows where all the main database engines fit into the wider data management landscape.  Taken from http://xeround.com/blog/2011/04/newsql-cloud-database-as-a-service although the original article NoSQL, NewSQL and Beyond: The answer to SPRAINed relational databases is by Matthew Aslett which is part of the The 451 Group’s new long format report on emerging database alternatives, NoSQL, NewSQL and Beyond.
http://blogs.the451group.com/information_management/2011/04/15/nosql-newsql-and-beyond

Don’t Become a One-trick Architect
This article by Thomas Kejzer gives an historical look at architectures and data http://blog.kejser.org/2011/12/08/dont-become-a-one-trick-architect/#more-377
This is the evolutionary tree of life for data storage engines

SQL Server 2012 Install by Configuration File

Automating a SQL Server installation for unattended installation creates a consistent deliverable for a SQL Server build.

To create the configuration file run through the GUI installation selecting your predefined defaults and prior to clicking Install collect the configuration file from the specified path listed on the Ready to Install page e.g.  C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20120619_140314\ConfigurationFile.ini

The configuration file will need additional configuration details for unattended use as sensitive information is not included.

•  IACCEPTSQLSERVERLICENSETERMS is required to acknowledge acceptance of the license terms. 

• The QUIET and QUIETSIMPLE settings are required to allow the Setup to display progress only, without any user interaction.

A few parameter may need updating between server builds.

Security

The other sensitive information which is not written to the configuration file are the passwords. These can be passed into the installation configuration at run time using

Setup.exe /SQLSVCPASSWORD="************" /AGTSVCPASSWORD="************" /ISSVCPASSWORD="************" /SAPWD="************"
/ConfigurationFile=DefaultConfigurationFile.INI

The Service and SQL account parameters are:

SQLSVCPASSWORD: Account for SQL Server service: Domain\User
AGTSVCPASSWORD: Agent account name: Domain\User
ISSVCPASSWORD:  Account for Integration Services: Domain\User
RSSVCPASSWORD:  Account for Reporting Services: Domain\User
ASSVCPASSWORD: Account for Analysis Services: Domain\User
SAPWD:  Specifies the password for the SQL Server sa account.

The Microsoft article that describes the install of SQL Server 2012 using a configuration file can be found here http://msdn.microsoft.com/en-us/library/dd239405.aspx. A full list of parameters are stated here http://technet.microsoft.com/en-us/library/ms144259

Performance Dashboard Reports

The new custom  Performance Dashboard Reports for SQL Server 2012  can be obtained from http://www.microsoft.com/en-gb/download/details.aspx?id=29063

As with the earlier versions of these reports they allow a database administrator to quickly identify whether there is a current bottleneck on the system, and capture additional diagnostic data that can help resolve some common problems such as:

• Common performance problems that can be indeitifed through the dashboard are CPU bottlenecks (and what queries are consuming the most CPU)
• IO bottlenecks (and what queries are performing the most IO)
• Index recommendations generated by the query optimizer (missing indexes)
• Blocking
• Latch contention

Deployment

There are 3  parts to the deployment process.

1.  Run the run installer. The dashboard automatic install path places the files in C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard.
2. In the Performance Dashboard folder execute the setup.sql script.
3. Test the deployment by opening a report. Open the report by selecting the server instance in SQL Server Maangement Studio (SSMS) and in the menu options select Reports, then; Custom reports and navigate to the install path C:\Program Files\Microsoft SQL Server\110\Tools\Performance Dashboard. Then select  performance_dashboard_main.rdl and Click open and select Run.

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