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 12 December 2011

Send Victoria Holt to Space

DBA in Space Last Day of Voting

It has reached the last day of voting for the Redgate DBAinSpace competition. I have to say it has been  quite a busy week trying every way possbile to promote yourself, Redgate and get votes. I have to say a major thanks to my brother Roger Holt for his determination and persistance with trying to get votes over the last week. It has been a fun experience and for everyone at work, it meant they got a whole week of me not talking about SQL. Voting still possible till 12 tomorrow  https://dbainspace.com/finalists/victoria-holt

You will find me also at 

DBA in Space Finalist Victoria Holt
The home of great sql server articles

DBA in Space video

Make it Count

Oxford Brookes Alumni

Open University

Singletrack Mountain Bike Magazine

SQL Server Community
Help with the local user groups in the region  tweeting
SQLServerClub in Bristol http://www.sqlserverclub.co.uk/ and
Avon Information Management Group in Bath http://avonim.wordpress.com/

Once upon a time ..


Once upon a time there was a DBA who through the years aspired to learn, to live, to breath, the universe of the DBA. There was excitement and thrills to watch the database space expand. To breathe new life into a field that hadn't changed that much over the last 30 years. The tools required to manage such an ever expanding set of data need to evolve and expand with agility to match the rapid growth of data which IDC say would be a hypothetical stack of DVDs that will reach halfway to Mars.  To see the Red planet, Redgate would be proud. The complexities of data management and chaotic changes to the type of data, creates a new universe. A data universe where both structured and unstructured data collide.

The DBA travelled around to explore those strange, new worlds. And sought out new data life and new data civilizations. To follow her name into space to ensure the data sent to space is archived. Please vote for Victoria Holt in the DBAinSpace competition that Redgate are running. To experience a once in a life opportunity this DBA would treasure for the rest of her known life. To Vote  https://www.dbainspace.com/finalists 

An extraordinary competition  for an out of this world experience for the DBA to collect more data.

Merry Christmas to all.

Saturday 10 December 2011

DBA in Empty Space

Vote to launch me into space to start DBA cloud services in space.

Articles listed

These are links where you can currently see details about the DBA in Space Redgate competition.

Vote our DBA into space! 
Promotion from My Company

Short url:  http://bit.ly/vBNb0v

Interplanetary and Space Affairs
This group is managed by volunteers from the The British Interplanetary Society (BIS). The society is devoted to initiating, promoting and disseminating new concepts and technical information about space flight and astronautics.  Messages/boards
Short url: http://bit.ly/vmpeQj

Rocketeers DBA in Space
Rocketeers is a journal documenting an interest in commercial spaceflight ("NewSpace"), particularly in the growing number of British firms involved in this fascinating area of technology. UK software company Redgate funds space flight competition. Forum Entry
Short url: http://bit.ly/sAqaxN

Bath Royal Literary and Scientific Institution (BRLSI)
The Bath Literary and Scientific Institution was founded in 1824, but was a direct descendant of Bath societies going back to the 1770s. The Educational Charity promotes science, philosophy, the arts and current affairs and has been kind enough to support my endevours by adding details to their weekly newsletter.

Friday 9 December 2011

DBAinSpace Launch me into Space

This is the video of my bid to be launched into space on the Space Adventures suborbital flight with the competition from Redgate.

VOTE NOW   https://www.dbainspace.com/finalists

Hi, I’m a Senior DBA specialising in SQL Server. I’m very passionate about data and databases. I’m already very excited about the launch of SQL Server 2012 next year, seeing how cloud database services bring agility and elasticity and watching to see how the 2 universes of structured and unstructured data collide.

My name was sent to the moon on the Lunar Reconnaissance Orbitar and is on route to Mars with NASA’s Mars Science Laboratory rover which was launched the other week.

The Redgate DBA in Space competition is an astronomical chance of a lifetime.  Please send me into space.

Exploration of Data - a Poem

When I was young, man stepped onto the moon
To view such a sight without StreamInsight
Brought meteors, stars and the sun

These spatial discoveries, fuelled man’s quest for flight
To question the data, to which we rely

So what’s to be left but to take into space,
The future, the passionate,the excited DBA.

Please send DBA Victoria Holt into space. https://www.dbainspace.com/finalists

Wednesday 7 December 2011

Day 3 DBA in Space Race

Today an article was published in the Bath Chronicle entitled Victoria needs votes to win her own space race. The article is

or below

Spoke to Breeze Radio in Bath http://bath.thebreeze.com with an interview about my bid to go into space. Details to follow on their website.

To vote to send me into space with Redgate's competition go to  https://www.dbainspace.com/finalists

DBA in Space a Spatial event

Day 2 of voting for the DBA in Space competition. Please vote to say that I can go into space. The DBA with the most votes will win the astronomical prize.


My name has been sent to the moon on the lunar reconnaissance orbiter and to mars on the NASA science laboratory rover so to complete the trio to boldly go where no other database geek has gone before would be astronomical. Thanks #RedGate


Space the final frontier to explore new database worlds to seek out new tools #RedGate, new sql civilizations, to go where no dba has been.

The finalists are listed here  https://www.dbainspace.com/finalists

Tuesday 6 December 2011

DBA in Space Finalist

Red Gate Software has teamed up with Space Adventures to offer one lucky DBA a ticket into space. The Space Adventures flight takes you into suborbital space, 10 times higher than commercial aircraft, and a third of the way to the International Space Station.

Regate " think DBAs, the exceptional individuals who manage the smooth running of our planet's data supply, have been under-appreciated for too long. That's why we're holding DBA in Space. DBAs deserve better. Better recognition, better software tooling, better prizes." www.dbainspace.com

My brother Roger's passion for space exploration and his membership of the British Interplanetary Society has been my inspiration for all things space related. 

Voting is open to the public and you can vote once a day for the week that voting is open. Please vote for me.

SQLBits 9 Photo taken by Tobiasz J Koprowski
My 100 word short Biography
I am a senior certified MCITP Database Administrator at Eduserv . I am very passionate about data and databases. I have experience of managing a large SQL Server Estate throughout the database lifecycle. With the database landscape continually changing I proactively look at ways to improve the management of SQL Server.  Currently looking at: the adoption of SQL Server 2012, database infrastructure for the cloud (DBaaS) and architectural designs for relational and BI platforms.   Out of work activities include: part time research for a Phd for improving database management systems and involvement with the SQL community through SQLBits.

 I need the most votes to go into space.  https://www.dbainspace.com

Wednesday 9 November 2011

Managing Database as a Service

Before looking at Database-as-a-Service there are 3 concepts that cloud computing traditionally utilize within the ecosystem, virtualization, standardisation and automation. The essential concepts being:
  • Elasticity
  • Rapid provisioning
  • On demand self service
  • Resource pooling
  • Measurable service
  • Standardised reporting
Database-as-a-Service (DBaaS) is in its infancy and conceptually sits within the hierarchy of service layers Platform-as-a-Service (PaaS). DBaaS utilizes on premise software such as SQL Server to deliver a managed database service.  The service layers are

Table: Examples of Service Offerings

IaaS- Infrastructure as a Service
PaaS – Platform as a Service
DBaaS - Database as a Service

Management of database services has now broaden with multiple owners. It is managed in the private cloud by database administrators. In the public cloud, the public create databases and provision the resources they require for scalability using services such as SQL Azure. This service framework aims to reduce total cost of ownership by reducing the hardware footprint.  This reduced number of SQL Server instances, reduces the number of servers requiring management and maintenance within the database landscape. Consolidation of database services on a shared platform reduces what was known as database server sprawl. 

DBaaS is agile as it has inbuilt processes for the quick provisioning of database services.  The elasticity of the service is provided by instantly being able to expand and collapse capacity.

In the SQL Server MVP Deep Dives (2011) chapter by Peter Ward he discusses further what he calls SQL Service as a service. There he mentions that the SQL Server topology satisfies the following: 
  • Provides self-service functionality to allow end-users to request new, expand, or shrink the resources they’ve been allocated
  • Proactively monitors server and database utilization and reallocates resources as required 
  • Provides expand or collapse capacity, with no downtime 
  • Provides self-service provisioning of new resources 
  • Meters resource utilization for chargeback – a key tenet

PaaS allows SQL Server to run physical or virtual servers to provide Relational Database Management Systems or Business Intelligence systems. Database-as-a-Service is a cloud service which instigates a new model for providing database services that is a deviation from the core managed database service.

In conclusion cloud computing and Database-as-a-Service are just a new way of presenting and managing services which provide the industry with reductions in total cost of ownership. However organizations will continue to need managed database services whether they are provided through traditional, cloud or hybrid managed services.

Friday 14 October 2011

Big Data – What is the Big Deal?

The 3rd SQL PASS Keynote was given by David J. DeWitt of the Data and Storage Platform Division. IT was a brilliant insightful session.

The session started explaining the definitions of Big Data. It is a massive collection of records. To some, “Big Data” means using a new a NoSQL system like Hadoop and Map Reduce or the old traditional parallel relational DBMS to manage the data. Data is the currency of this generation with the realization that data is too valuable to delete.

Not Only SQL - It's about recognizing that for some problems other storage solutions are better suited. NoSQL has a flexible data model, faster time to deliver, relaxed consistency model such as eventually consistent, the willingness to trade consistency for availability, low upfront software costs. Some data is just not worth storing in a relational databases, validating, cleansing, ETL, analyzing or controlling the quality.

There are 2 types of NoSQL:

Key/Value Stores
Examples: Mongo, CouchBase, Cassandra, Windows Azure.
This is single value retrievals based on key - Think NoSQL OLTP.

This is large volumes of data stored in a distributed file system - Think NoSQL data warehousing.

SQL is sometimes termed 'schema first' and NoSQL 'schema later'.

The other idea that was presented throughout the session was the idea that there are two universes in the new reality. Structured Vs Unstructured.

This is not a paradigm shift. The world has changed and the new reality is the RDBMS and NoSQL databases need to work together to address the current requirements in a complementary fashion.

The rest of the session went on to explain about Hadoop and its ecosystem and how the 2 technologies work together.

Hadoop = HDFS (file system store) + MapReduce (programing paradigm, process)

Some applications need data from both universes in the new world. Where this is the case Sqoop is used to connect the Unstructured (Hadoop) to Structured (RDBMS).

Thursday 13 October 2011

The fantastic 12 of SQL Server 2012

Day 2 of the SQL PASS Summit keynote was given by Quentin Clark, Corporate Vice President, SQL Server Database Systems Group and it covered these 12 sections

Summary slide from the keynote 

Big Data
Microsoft has partnered with Hortonworks to provide an Hadoop based Windows Azure service which will be out before the end of the year. "The Apache Hadoop software library is a framework that allows for the distributed processing of large data sets across clusters of computers using a simple programming model."
ODCB Drivers for Linux and Change Data Capture for SSIS and Oracle
Interoperability new drives for PHP, Java and Hadoop  

SQL Azure changes
Backing up SQL Azure databases was shown in the demo
SQL Azure will support 150GB databases and any collation by the end of the year
The Azure management portal uses the Metro UI
SQL Azure federations to scale

Microsoft SQL Server appliances and reference architectures allow customers to deploy data warehouse (DW), business intelligence (BI) and database consolidation solutions in a very short time, with all the components pre-configured and pre-optimized. The Fast Track Data Warehouse 3.0 Reference Guide and blueprint is http://msdn.microsoft.com/en-us/library/gg605238.aspx . The three appliances are
  • HP Business Decision Appliance (BDA)
  • Microsoft and HP Business Data Warehouse appliance (BDW)
  • Fast Track Data Warehouse

Wednesday 12 October 2011

SQL Server 2012

The Day One Keynote Wednesday 12 October for SQL Pass keynote was given by Ted Kummert, Senior VP, Business Platform Division, Microsoft Corp PASS Summit 2011

The keypoints annouced were:-

The name for SQL Server Denali is SQL Server 2012.

SQL Server Data Tools is the name for Project Juneau. It brings a new paradigm for database development within the familiar toolset of Visual Studio for T-SQL development.

Power View is the name for Project Crescent. It provides an ad-hoc reporting tool for business users. 

SQL Server 2012 is to support Hadoop. Oracle and EMC will also release a distribution of Hadoop and the Greenplum database and Hadoop will run on the same platform. 

The Hive ODBC driver will be available next month

Data Explorer was announced. The Data Explorer tool allows you to browse data sources in the cloud.  Has phases of discovery, enrichment and sharing. It can handle Big Data joining multiple data sets from say the cloud, excel, market place, sql etc.

The PASS Summit 2011 Live Keynote Streaming is

Taken from the SQL Server UK Tech Days, the features include

Wednesday 5 October 2011


Database as a service providers are rapidly increasing. These are a few I found

SQL Azure

SQL Azure delivers cloud database services which enable you to focus on your application, instead of building, administering and maintaining databases. It is built on SQL Server technologies and is a component of the Windows Azure platform.

VMware vFabric Data Director
The first database supported on Data Director is VMware vFabric Postgres 9.0 (vPostgres),..

Project RedDwarf – Database as a Service

Amazon Relational Database Service (Amazon RDS)
Amazon RDS automatically patches and backs up your database, storing the backups for a user-defined retention period and enabling point-in-time recovery.

You benefit from the flexibility of being able to scale the compute resources or storage capacity associated with your relational database instance via a single API call. Amazon RDS supports both MySQL and Oracle Databases

Sunday 2 October 2011

SQLBits 9 Query across the Mersey

Held at the Britannia Adelphi hotel in Liverpool SQLBits 9 was yet another amazing conference. It was a privilege to be an official helper for this event. The 3 day event started with conference organization on the Wednesday evening followed by the training day seminar. I attended Upgrading your DBA Skills to SQL Server Denali with Christian Bolton of Coeo. The session covered various parts of the Box,  on premise server, element in the Denali strategy.  By day 2 the heat in the hotel was overpowering due to the heatwave. The sessions on Friday and Saturday covered an array of talks across the database landscape.  The keynote on the Friday covered the ecosystem of the Appliance strategy. The third part of the Denali strategy Cloud was not covered during this event.
Here are some observations on key topics of interest during the event.

There was a significant buzz about the up and coming release of Denali, the next version of SQL Server (as announced at SQL Pass yesterday is SQL Server 2012) which will ship in the first half of next year.

The Microsoft strategy for SQL Server consists of Box (on premise server), Cloud (SQL Azure) and Appliance (prebuilt and preconfigured servers). There are a few key items which will no longer be supported. It will not be possible to upgrade from SQL Server 2005 in one go and Data transformation Services (DTS) will not be supported.

Further advancements in the business intelligence (BI) arena will provide an end to end enterprise information management platform.  The BI suite consists of enhancement to master data services (MDS), Integration Services (SSIS), a new tool for Data Quality Services (DQS) and Impact analysis and lineage tool (currently project Barcelona).  However, it is unlikely that project Barcelona will ship in the initial release of Denali. In addition to this, significant improvements have been made to reporting services, under project crescent, to provide a visual design experience and to revolutionise query performance using the new column store for OLAP cubes.

For database administrators the upgrade and planning tools for installing database servers and migrating databases have been significantly overhauled so that it is possible to install the latest versions of the software and replay workflows to aid in migration and testing. In the service environment, to leverage to ability to monitor event data for troubleshooting SQL Trace / Profiler will be deprecated and extended events will replace that functionality.

Denali will introduce further security improvements and changes to facilitate compliance and increase flexibly and management.  These are just a few of the changes in addition to the changes to high availability and the disaster recovery solutions. Always on, a new component, provides improvements to high availability using a mixture of 2 existing technologies, mirroring and clustering, and can be across geographically dispersed locations with the ability to add multiple replicas.
The development process will be set to change at a later date with Codename “Juneau”.

In summary, a vast amount of new technological components were discussed, from the new HP / Microsoft appliances to NoSQL databases to existing SQL Server components. Denali will be a major release of SQL Server, the largest since SQL Server 2005.  The main takeaway is that SQL Server is no longer the small database server application that doesn’t scale or perform.

Thursday 22 September 2011

Dynamic Management View (DMV)

Useful Dynamic Management View (DMV) resources

Free SQL Server DMV Starter Pack
A free eBook  that describes 28 different ways that DMVs can be used with SQL Server

The Periodic Table of DMVs

Performance Tuning with SQL Server Dynamic Management Views by Louis Davidson and Tim Ford

Recap of April 2010 DMV a Day Series

Wednesday 21 September 2011

MySQL Clustering

For MySql clustering a few useful articles about providing high availability are below

MySQLCluster 7.1

Information on MySQL Cluster and MySQL Replication blog

MySQL Cluster Manager 1.1.2 – creating a Cluster is now trivial

MySQL Cluster 7.1 Cheat Sheet

MySQL Cluster 7.2: NoSQL, Key/Value, Memcached
This provides a new option for multi-site clustering.
MySQL Cluster 7.2.1 is integrated with MySQL Server 5.5. You have all the benefits of an ACID RDBMS, combined with the performance capabilities of Key/Value store.This allows schema-less data storage.
Oracle University Free Summer learning on MySQL Part I

Oracle University Free Summer learning on MySQL Part II

ScaleBase shatters MySQL for scalability
The article explains "it is a proxy server that sits in front of the actual database and in this case, the tool breaks a monolithic relational database into chunks and spreads it out across multiple physical servers. "

Wednesday 7 September 2011

Research Question Revised

After various iterations my research question stands as 

How can the complex interactions, both extrinsic and intrinsic to the database, lead to a better understanding of database systems and their management?

In what ways could this lead to the emergence of improvement and innovation?

Monday 11 July 2011

Database Ethics

This article provides a reflection on the ethics required by a database administrator (DBA). This philosophical code of behaviour sets out the principles for the profession. The DBA ethics storyboard is a visualization of the ethics and is shown in figure1.

Figure 1 DBA Ethics Storyboard

Ethic 1 DBA - the role of a DBA requires
• a person who is a database advocate
• being proactive rather than reactive
• having numerous interconnected responsibilities
• clear communication with all the interconnected technologists
• making recommendations to the public
• that if a mistake is made corrective action needs to be taken immediately
• to exercise governance and agility

Ethic 2  DATA
• the data the organizations hold is the most precious asset it has. A DBA must protect it
• the quality of the data should be maintained
• all types of data whether structured or unstructured need looking after
• data is retained and archived appropriately

Ethic 3 SECURITY - a DBA is responsible for
• making data available to authorized users and ensuring data it is inaccessible to the unauthorized user
• identification of sensitive data, managing it securely and auditing access
• patching and a DBA should have a patching philosophy to prevent security issues arising

Ethic 4 ARCHITECTURE - a DBA  should
• ensure good data modelling and design techniques are used 
• use the right database application for the requirements
• have an entity relationship diagram should for each database
• have a master data management process to define and manage entities

Ethic 5 DEVELOPMENT - a DBA should ensure
• the development scripts written are version controlled
• coding standards are followed
• the scripts include a description of the tasks

Ethic 6 AVAILABILITY- a DBA should
• monitor the system to ensure it is always available when it is needed
• regularly review error logs
• manage scheduling and job success rate
• ensure there is a capacity management process in place
• ensure good performance is maintained

Ethic 7 DISASTER RECOVERY - a DBA should ensure
• backups are taken regularly and regularly verify the quality of the said backups
• every database has a documented disaster recovery plan.
• that a recovery time objective and a recovery point objective are defined
• backups are stored offsite
• a server configuration snapshot is stored offsite

Ethic 8 CHANGE - a DBA should
• monitor changes to the system
• follow processes for management.
• have a multiple tier environment to validate changes
• have a risk assessment strategy
• ensure rollback can occur for failed changes
• identify the consequences of a change
• have a verification process to determine if the change was successful

• have a process for problem management

Ethic 10 DOCUMENT - a DBA should ensure
• appropriate documentation is written or obtained
• a summary of essential information such as configuration information be created
• there is a process for keeping the documentation up to date and reviewed regularly
• a self-documenting system is created where possible

Ethic 11 AUDITING - a DBA should ensure there are regular database audits which
• periodically audit each database and provide a health check
• have a predefined checklist to follow
• have a description of the purpose of each check

Ethic 12  BEST PRACTICE- a DBA should
• follow best practice for design, development and administration based on vendor recommendations, practical field experience, database usergroups,  and environmental constraints
• aim for standardization and automation

Ethic 13 IMPROVEMENT- a DBA should
• reflect and iterate throughout the database system
• have a documented database roadmap

SQL Denali links

Microsoft Tech Days UK 2011 

A webcasts from uk tech days sharing details about Denali features

SQL Server Denali CTP3
Download at: http://technet.microsoft.com/en-gb/hh225126.aspx

SQL Server  Denali Resource Center

SQL Server  Denali Upgrade Assistant

What's New (SQL Server Denali)

Wednesday 6 July 2011

More SQL Server 2008 Reading

More articles to read to expand your SQL knowledge which may help through the administration certification process.

Configuring linked servers for delegation

Linking Servers

Distributed Queries

Guidelines for using Distributed Queries

Troubleshooting SQL Server 2005/2008 Query Performance/Scalability Issues

A cheat sheet for SQL Server developers

Filestream Overview

Using filestream with other SQL Server Features

Administering Servers by using Policy Based Management

Kerberos Authentication and SQL Server

Alter Database Set options

DDL Triggers and Logon triggers

Introduction to Change Data Capture in SQL Server 2008

Enabling change data capture

Examples of Restore Sequences for Several Restore Scenarios

Policy Based Management Security

Generic T-SQL Query Collector Type

Max degree of Parallelism option

Ownership chains

Trustworthy database property

Using Mirrored Backup Media Sets

Verifying backups

Considerations for restoring the model and msdb database

Resource Governor concepts

Distributed Transactions

How it works SQL Server 2005 NUMA Basics

How to Configure SQL Server to use soft NUMA

Alter server configuration

Understanding how to set the sql server I/O Affinity Option

How it works IO Affinity mask should I use it

How it works soft NUMA I/O completion thread. lazy writer workers and memory nodes

Filestream Design and Implementation Considerations

Introducing the sql server 2008 performance data collector

Monday 27 June 2011

Multi Server Administration Poster

Details about the configuration of Multi Server Management for looking the the health resources of SQL Servers.

Monday 13 June 2011

The Complex Interactions of Database Systems

Research Event: Tuesday 14 June 2011
Presentation Abstract:

This presentation provides an insight into my research field of interest which is focused upon the complex interactions of database systems. It draws together details from the current technical and non technical field and the findings from conducting a literature review. There is lack of research in the specific field of study which crosses multiple field boundaries.

Relational database systems have expanded to include large volumes of structured and unstructured data; there has been a rapid change in usage, demand for data, information and knowledge.  Database administrators need to manage technical components to respond to requirements with influence from many areas including cultural, economic, political etc. This led to research questions into a system of interest which could benefit the industry as a whole.

The sequential mixed methods design is to be used to investigate how the interaction of complex systems affects the management of database systems. An aim of this research is to gain an understanding of how improvement and innovation could emerge from database management systems.

Friday 27 May 2011

Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008

Useful resources for the 70-450 exam

Compare Edition Features

SQL Server PowerShell Overview

Central Management Server
There are two tables used to store the database server details:-



Understanding Non-uniform Memory Access

Hardware NUMA,  to find the number of memory nodes available to SQL Server run

SELECT DISTINCT memory_node_id
FROM sys.dm_os_memory_clerks

How to: Configure SQL Server to Use Soft-NUMA

Max degree of parallelism Option

General guidelines to use to configure the MAXDOP option

Table Partitioning Sliding Window Case

Partitioned Table and Index Strategies Using SQL Server 2008

Create Partition Scheme

Returns the current size of the requested object and estimates the object size for the requested compression state.

FILESTREAM Storage in SQL Server 2008

The FILESTREAM directory structure contains
Filestream.hdr - a metadata file describing the data container
$FSLOG directory - the FILESTREAM equivalent of the database transaction log

Create Statistics

Clone a database - creating a statistics only copy

SQL Server Statistics ebook

Clustered Index Design Guidelines

Index with Included Columns

Plan guides (plan freezing) in SQL Server 2005/2008

Guide to SQL Server Isolation Levels

Tips on Optimizing SQL Server Composite Indexes

Reorganizing and Rebuilding Indexes

SQL Server 2008 Full-Text Search: Internals and Enhancements

Create Fulltext Stoplist 
To prevent a full-text index from becoming bloated SQL Server discards commonly occurring strings that do not help the search.  For example, words such as "a," "and," "is," and "the" are left out of the full-text index. http://technet.microsoft.com/en-us/library/cc280405.aspx

Contains (Full Text)

Monitoring Best Practices by Using Policy-Based Management

Enterprise Policy Management Framework
A reporting solution against a desired state defined in a policy

Microsoft® SQL Server 2008 R2 Best Practices Analyzer
Import existing policirs, these policies are located in C:\Program Files\Microsoft SQL Server\100\Tools\Policies.

Designing and Understanding DDL Triggers
DDL triggers fire stored procedures in response to an eventin response to a variety of Data Definition Language (DDL) events.

An Introduction to SQL Server 2008 Audit

An Introduction to SQL Server 2008 Change Data Capture
Designed to capture insert, update, and delete activity applied to SQL Server tables.

Change Tracking Overview
A lightweight solution that provides an efficient change tracking mechanism for applications.

Comparing Change Data Capture and Change Tracking

Auditing in SQL Server 2008

SQL Server Alerts: Soup to Nuts
sys.messages: contains one row for each system error or warning that can be returned

SQL Server Management Objects (SMO)
A collection of objects for programming all aspects of managing Microsoft SQL Server.

Create Event Notification
Log and review changes or activiity occuring on the database

Configure a Fail-Safe Operator for Notifications

Multi-Server Administration with Master Target jobs

Instructions for using SQL Server 2008 in FIPS 140-2-compliant mode

Audit a SQL Server Configuration

Transparent Data Encryption

Cell Level Encrpytion

SQL Server 2008 Jumpstart Training Materials

Dynamic Management Views and Functions

Free Poster – SQL Server Dynamic Management Views

sp_trace_setevent - SQL Trace historical analysis


sp_who and sp_who2

Isolation Levels in the Database Engine

Capturing the Execution Plan


The Data Collector

The Reports for Data Collections

Linked Servers

MS DTC Distributed Transactions

Scalable Shared Databases Overview

SQL Server Replication


SQL Server Replication: Providing High Availability using Database Mirroring

Disaster recovery solution for distribution database in replication

Replication Security Best Practices

Peer-to-Peer Transactional Replication


High Availability Solutions Overview


Log Shipping Deployment

Database Mirroring Overview

SQL Server 2008 Failover Clustering

Introduction to Backup and Restore Strategies in SQL Server


Copy Only Backup


Backup Compression
To calculate the compression ratio of a backup use
SELECT backup_size/compressed_backup_size FROM msdb..backupset;
Using Mirrored Backup Media Sets


Backup Devices

Have a backup strategy document

Wednesday 4 May 2011

Data Metaphors

The more articles you read the more you become aware of the use of metaphors in the phenomena of data descriptions. The comparisons which allow visualization of various images of unprecedented data growth seem endless

  • Big Data  - http://www.capacent.is/library/Skrar/Analytics/economist-data-20100227.pdf
  • Data Avalanche  - discussed http://kevinekline.com/2010/11/09/tn-summit-2010-surviving-the-data-avalanche
  • Data Flood  - discussed in The Fourth Paradigm Data-Intensive Scientific Discovery http://research.microsoft.com/en-us/collaboration/fourthparadigm/
  • Data Explosion   - The digital universe decade http://www.emc.com/collateral/demos/microsites/idc-digital-universe/iview.htm
  • Data Revolution – from social data to open data sets http://giscience.org.uk/2011/03/21/the-open-data-revolution/
  • Data Deluge  - http://www.sciencedaily.com/releases/2011/04/110422131123.htm
  • Data Tsunami   - http://pubs.amstat.org/doi/pdfplus/10.1198/jasa.2011.ap10508

Sunday 10 April 2011

SQL Bits 8 Beside the Sea

SQL Bits 8 Beside the Sea in Brighton had the most glorious weather. The talks were full of amazing speakers and content. SQLBits is certainly Europe’s Premier SQL Server Conference. Talks covered many areas including looking at SQL Server under the hood, internals, modelling for the extreme, High Scale OLTP, Denali, visualization and virtualization, systems for troubleshooting with stakeholders which adds depth and breath to our database understanding which can help with our manageability of such things as Big Data (structured and unstructured data having complex interrelationships).

Microsoft's Andrew Fryer chats to Victoria Holt (@victoria_holt) at sqlbits8
Also on the Microsoft UKTechnet blog on What did you miss at SQL Bits?

Monday 28 March 2011

A picture is worth a thousand words

The term data visualization describes the representation of data sets in a graphical form. A complex idea can often be displayed as an image. This allows clearer communication of data in forms of data visualization tools. An example of tools
  • dashboards (performance dashboard),
  • scorecards,
  • charts, dials, maps, graphs etc.
SQL tools which can help visualizing data

SQL Director for Dependencies & Indexes

Graphical Database Schema Metadata Browser
A GUI for SchemaSpy (Graphical Database Schema Metadata Browser)

Systems thinking uses diagrams to express complex ideas, emergence and the chaotic universe.

Thursday 17 March 2011

Microsoft SQL Server 2008, Implementation and Maintenance

More exam links for 70-432. This article provides an great set of links to books online and the technical areas covered under the exam.

Implementation and Maintenance

Tuesday 15 March 2011

Chaotic Data Avalanche

A sql cloud poem

My database friend, is full of logs
It's acid compliant, relational too
The elasticity stretched, efficiency blogs
Dynamically virtual and data exploded
This leads me to say what tuples my friend
and governance, knowledge hath entropy born

what TCO, RTO, RPO, ROI...

Monday 14 March 2011

Troubleshooting Performance

A useful chart for iterative processes to deal with common query performance and scalability issues for SQL Server 2005/2008.


Database 2011 Predictions

Just read an excellent article five-predictions-for-your-database-in-2011 predicting various trends in Databases in 2011. They predict a decline in NoSQL databases and move towards Database as a Service (DBaaS). DBaaS is about an entire change in philosophy producing efficiency, fluidity of resources, scale out, paying for actual usage and being able to be elastic. Elasticity discussed here

DaaS (Data as a Service) is about data collections in the sky.
DBaaS (Database as a Service) is about providing full database functionality.

Wednesday 2 March 2011

Useful Links for the MCTS Exam

Useful links for further reading for the MCTS exam.

TS: Microsoft SQL Server 2008, Implementation and Maintenance

Understanding and Managing the suspect_pages Table

Tablediff Utility

How to Rebuild System Databases in SQL Server 2008

Rebuilding the SQL Server master database - Part 1
Rebuilding the SQL Server master database - Part 2

SQL Server 2005: View all permissions (2) - Explode the roles! second script works

Partitioned Table and Index Strategies Using SQL Server 2008

Sql Server 2008 Geospatial Data Generator

SQL Server Audit Action Groups and Actions

fn_get_audit_file (Transact-SQL)

Giving Permissions through Stored Procedures

Database Audit Specifications in SQL 2008

sys.dm_db_index_physical_stats (Transact-SQL)
includes the Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

Allocation SQL Server Management Studio Add in

Internals Viewer for SQL Server

SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

XML Jumpstart Workbench

Replication Agent Security Model

Script to indentifty big replication transactions

Using Secondary Servers for Query Processing

Database Mirroring in SQL Server 2005

Monitoring SQL Server Database Mirroring with Email Alerts

SQL Server Best Practices Article

Alerting on Database Mirroring Events

Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 1 (Part 1 to Part 3)http://www.mssqltips.com/tip.asp?tip=1687

SQL Server Perfmon Counters Poster

Troubleshooting Performance Problems in SQL Server 2008

Isolation Levels in the Database Engine

Who did what?

Execution Plan Basics

Performance Tuning

fn_trace_gettable (Transact-SQL)


XML Tutorial

XML Data Modification Language Workbench

DDL Event Groups

DDL Events

Joe Celko The SQL Apprentice

Sorting Months By Number (SQL Spackle)

Creating Your Own Custom Data Collections

Monday 21 February 2011

Clone a SQL Database

Microsoft have written an article which explains how to clone a database. This allows for a copy of the database to be made without any data contained within it. This is advantageous as it protects sensitive data and prevents the need to move large data files. The article is ‘How to generate a script of the necessary database metadata to create a statistics-only database in SQL Server 2005 and in SQL Server 2008 at http://support.microsoft.com/default.aspx?scid=kb;EN-US;914288

This is available in SQL Server Management Studio for SQL Server 2005 SP2 and later and SQL Server 2008 and later. It is necessary to script all database objects in the database and change the advanced options setting to include items such as Script Statistics and histograms

Wednesday 12 January 2011

SQL Bits session submission

Jen Stirrup and I have submitted a joint presentation for SQLBits - Beside the seaside for the community to vote on.

The Illusion of Communication: Good service made visual

How can DBAs improve the quality and delivery of service to their customers? Standardisation, best practice paradigms and methodology can be used to improve the quality of service provided to the customer. However, unless these achievements are conveyed effectively to the customer, how is the customer going to know what you've been working hard to deliver? Dashboards and reports are the main way in which service information is communicated to customers. However, if the key message is distorted, then the customer won't understand properly - or even be actively misled.

This session takes a look at the historic development of databases systems, challenges, key turning points and definitions of what is a DBA. This is then followed by an overview of standardisation, the application of best practice paradigms, methodology and processes. Key guidelines around dashboard design with an application to service level information will be provided, along with demonstrations of these key design features in SSRS.

George Bernard Shaw once commented that 'The single biggest problem in communication is the illusion that it has taken place.' This session aims to help you to review your dashboards and reports, so that your efforts are communicated visually and effectively.

Submit a session for SQLBits

Central Management Server

This is a server which contains a central repository of sql servers which can be grouped in various ways. These could be grouped into customers, versions (2008, 2005) or state (Development, Staging, Production).

This group can be used to connect to SQL Server configuration manager, to run queries, object explorer or to evaluate policies for policy based management.

To create a central management server this can done through Registered Servers on the view menu. For more details

The Central Management Server only works in one windows domain, so if there are multiple domains that require to be managed there will need to be multiple Central Management Servers.