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

Monday 23 July 2012

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.

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

Saturday 21 July 2012

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.


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="************"

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

Sunday 1 July 2012

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

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.