Day 3 PASS SQL Rally Nordic

Day 3 launched straight into sessions. The session designing Hybrid Systems for the Enterprise by Buck Woody was based on the ebook  Building Hybrid Applications in the Cloud on Windows Azure http://www.tinyurl.com/9ffms3t . The session provided an excellent guide for creating a cloud architecture. The session finished quoting Plato “The beginning is the most important part of the work.”

The next couple of sessions were very useful, exploring under the hood with the sessions using statistics and relational algebra. The sessions were Optimizing column stores with statistical analysis by Thomas Kejser and the SQL Server 2012 Query Optimiser by Conor Cunningham.

Column store was originally written about in papers in the 1970’s and has only now been incorporated into SQL Server. Mathematical techniques were discussed, low cardinality, correlation of columns, entropy, mutual information leading to the big unsolved question of computer science  P=NP. More information is here http://blog.kejser.org/2012/07/27/what-is-the-best-sort-order-for-a-column-store/

The Query Optimiser session gave a flying visit to how the optimiser works. The optimizer finds a good plan rather than optimal plan as it may take to days to find the absolute optimal plan. SQL Server does a good job at selecting a good plan. The relational algebra trees were explained from the basics to logical , physical  tree concepts answering the question what is a query?  More details on previous talks on this subject matter http://sqlbits.com/Speakers/Conor_Cunningham

The last 2 sessions I attended covered Windows 2012 Infrastructure for SQL Server and Cloud Ready Data Services.

All in all an excellent conference with great sessions and lots of exchanging SQL views with like-minded people.

Day 2 PASS SQL Rally Nordic

The main conference started with a keynote delivered by Kamal Hathi BI on Big Data. This started with a historical look at database\ data progression. With social and web analytics and live data fields, how do we predict future outcomes? Utilising advanced analytics on big data this can be visually displayed with the creation of a storyboard connecting the many data sets such as SQL Server, Hadoop, Excel etc. with the Microsoft toolset. There is no one single tool but a collection of tools for the job.

Other sessions I attended were from the DBA track which covered extended events, clustering and memory management for SQL Server 2012.

An interesting session shared technical information on building highly scalable and available cloud applications. Key consideration for the architectural design

• Data Warehousing is not a good fit for the cloud
• Scale out not Scale up
• Everything has a limit
• Design for failure
• Design for continuity
• Optimise for density

The key to a successful cloud, distributed computing system, is all in the architectural design. A phase from the session “Telemetry is Life” left me thinking about patterns and practices.  The end of another  great day.

PASS SQL Rally Nordic 2012 Day 1

The PASS SQL Rally Nordic from October 1-3 2012 in Copenhagen started with 3 pre-conference seminars. I attended a session covering the Microsoft Business Intelligence (BI) Presentation layer taken by Stacia Misner. This excellent session covered the theory of the BI Maturity Model from TDWI by Wayne Eckerson, a roadmap to analytical completion and collaborative decision making. Then this was mapped to the BI stack of SharePoint, SQL Server Reporting Services, PowerView, PowerPivot, Performance Point and Excel.

Reading Material from the session

BI Maturity Model from TDWI by Wayne Eckerson
http://download.101com.com/tdwi/Poster/TDWI_BI_Maturity_Model_Poster_2005.pdf

Book on Competing on Analytics: The New Science of Winning  Thomas H. Davenport and Jeanne G. Harris

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