Send Victoria Holt to Space

https://dbainspace.com/finalists/victoria-holt

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
http://www.simple-talk.com/community/blogs/dba_in_space_finalists/default.aspx

DBA in Space video
http://www.youtube.com/watch?v=28LA0sTaxW8

Make it Count
http://sqlblogcasts.com/blogs/simons/archive/2011/12/12/make-it-count.aspx?utm_source=twitterfeed&utm_medium=twitter&utm_campaign=Feed%3A+SimonsSqlServerStuff+%28SimonS+SQL+Server+Stuff%29


Oxford Brookes Alumni
https://www.facebook.com/brookesalumni

Open University
https://www.facebook.com/theopenuniversity

Singletrack Mountain Bike Magazine
http://singletrackworld.com/forum/topic/please-send-my-friend-into-space

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.

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

http://www.eduserv.org.uk/newsandevents/news/2011/vote-our-dba-into-space
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
http://www.meetup.com/Interplanetary/messages/boards/thread/18517332
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
http://www.rocketeers.co.uk/node/1751#comment-339
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.
http://www.brlsi.org/

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

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
http://www.thisisbath.co.uk/Victoria-needs-votes-win-space-race/story-14082305-detail/story.html

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.

 
WHY I WANT TO GO TO SPACE

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

MY FIRST TWEET FROM SPACE WILL BE

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

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

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.

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.

NoSQL
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.

Hadoop
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).

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

Appliances
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

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
http://www.sqlpass.org/summit/2011/Live/LiveStreaming.aspx

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

Database-as-a-Service

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.
http://www.microsoft.com/windowsazure/sqlazure/

VMware vFabric Data Director
The first database supported on Data Director is VMware vFabric Postgres 9.0 (vPostgres),..
http://www.vmware.com/products/datacenter-virtualization/vfabric-data-director/overview.html

Project RedDwarf – Database as a Service
http://www.openstack.org/blog/2011/04/announcing-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
http://aws.amazon.com/rds/

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.

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
http://www.bradmcgehee.com/2010/07/free-sql-server-dmv-starter-pack/

The Periodic Table of DMVs
http://www.brentozar.com/dmvs/

Performance Tuning with SQL Server Dynamic Management Views by Louis Davidson and Tim Ford
http://www.simple-talk.com/books/sql-books/performance-tuning-with-sql-server-dynamic-management-views/
Scripts
http://www.simple-talk.com/redgatebooks/davidsonford/dmv_code.zip

Recap of April 2010 DMV a Day Series
http://sqlserverperformance.wordpress.com/2010/05/02/recap-of-april-2010-dmv-a-day-series/

MySQL Clustering

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

MySQLCluster 7.1
http://www.mysql.com/products/cluster/

Information on MySQL Cluster and MySQL Replication blog
http://www.clusterdb.com/

MySQL Cluster Manager 1.1.2 – creating a Cluster is now trivial
http://www.clusterdb.com/mysql-cluster/mysql-cluster-manager-1-1-2-creating-a-cluster-is-now-trivial/

MySQL Cluster 7.1 Cheat Sheet
http://www.clusterdb.com/mysql-cluster-7-1-cheat-sheet/

MySQL Cluster 7.2: NoSQL, Key/Value, Memcached
http://dev.mysql.com/tech-resources/articles/mysql-cluster-7.2.html
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
http://www.oracle.com/uk/education/eblast/uk-mysql-training-230811-ol-459574-en-gb.html?sc=WWOU11036077MPP003C015

Oracle University Free Summer learning on MySQL Part II
http://www.oracle.com/webapps/dialogue/ns/dlgwelcome.jsp?p_ext=Y&p_dlg_id=9799792&src=7297607&Act=8

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. "
http://www.theregister.co.uk/2011/08/16/scalebase_database_sharding/

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?

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

Ethic 9 PROBLEMS 
• 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
http://www.microsoft.com/showcase/en/gb/details/f3cf3282-efca-478c-bb74-cd3e588c4733

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

SQL Server  Denali Resource Center
http://msdn.microsoft.com/en-us/sqlserver/denali_resource_center.aspx

SQL Server  Denali Upgrade Assistant
http://social.technet.microsoft.com/wiki/contents/articles/sql-server-upgrade-assistant-tool-for-denali.aspx

What's New (SQL Server Denali)
http://msdn.microsoft.com/en-us/library/bb500435%28v=SQL.110%29.aspx

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
http://msdn.microsoft.com/en-us/library/ms189580.aspx

Linking Servers
http://msdn.microsoft.com/en-us/library/ms188279.aspx

Distributed Queries
http://msdn.microsoft.com/en-us/library/ms188721.aspx

Guidelines for using Distributed Queries
http://msdn.microsoft.com/en-us/library/ms175129.aspx

Troubleshooting SQL Server 2005/2008 Query Performance/Scalability Issues
http://sqlcat.com/files/folders/280/download.aspx

A cheat sheet for SQL Server developers
http://www.dotnet4all.com/snippets/2008/04/factsheet-for-sql-server-developers.html
http://www.pinaldave.com/sql-download/SQLServerCheatSheet.pdf

Filestream Overview
http://technet.microsoft.com/en-us/library/bb933993.aspx

Using filestream with other SQL Server Features
http://msdn.microsoft.com/en-us/library/bb895334.aspx

Administering Servers by using Policy Based Management
http://technet.microsoft.com/en-us/library/bb510667.aspx

Kerberos Authentication and SQL Server
http://technet.microsoft.com/en-us/library/cc280744.aspx

Alter Database Set options
http://msdn.microsoft.com/en-us/library/bb522682.aspx

DDL Triggers and Logon triggers
http://blogs.technet.com/b/vipulshah/archive/2007/12/04/ddl-triggers-and-logon-triggers.aspx

Introduction to Change Data Capture in SQL Server 2008
http://www.bradmcgehee.com/2010/04/an-introduction-to-sql-server-2008-change-data-capture/

Enabling change data capture
http://msdn.microsoft.com/en-us/library/cc627369.aspx

Examples of Restore Sequences for Several Restore Scenarios
http://technet.microsoft.com/en-us/library/ms190960.aspx

Policy Based Management Security
http://sqlserver-qa.net/blogs/sql2008/archive/2009/02/23/4979.aspx
http://sqlserver-qa.net/blogs/sql2008/archive/2008/10/13/4895.aspx

Generic T-SQL Query Collector Type
http://technet.microsoft.com/en-us/library/bb630356.aspx

Max degree of Parallelism option
http://technet.microsoft.com/en-us/library/ms181007%28SQL.90%29.aspx

Ownership chains
http://msdn.microsoft.com/en-us/library/ms188676.aspx

Trustworthy database property
http://technet.microsoft.com/en-us/library/ms187861.aspx

Using Mirrored Backup Media Sets
http://msdn.microsoft.com/en-us/library/ms175053.aspx

Verifying backups
http://msdn.microsoft.com/en-us/library/ms189587.aspx

Considerations for restoring the model and msdb database
http://msdn.microsoft.com/en-us/library/ms190749.aspx

Resource Governor concepts
http://technet.microsoft.com/en-us/library/bb934084.aspx

Distributed Transactions
http://msdn.microsoft.com/en-us/library/ms188386.aspx

How it works SQL Server 2005 NUMA Basics
http://blogs.msdn.com/b/psssql/archive/2008/01/24/how-it-works-sql-server-2005-numa-basics.aspx

How to Configure SQL Server to use soft NUMA
http://msdn.microsoft.com/en-us/library/ms345357.aspx

Alter server configuration
http://msdn.microsoft.com/en-us/library/ee210585.aspx

Understanding how to set the sql server I/O Affinity Option
http://support.microsoft.com/kb/298402

How it works IO Affinity mask should I use it
http://blogs.msdn.com/b/psssql/archive/2010/11/19/how-it-works-io-affinity-mask-should-i-use-it.aspx

How it works soft NUMA I/O completion thread. lazy writer workers and memory nodes
http://blogs.msdn.com/b/psssql/archive/2010/04/02/how-it-works-soft-numa-i-o-completion-thread-lazy-writer-workers-and-memory-nodes.aspx

Filestream Design and Implementation Considerations
http://sqlcat.com/whitepapers/archive/2011/02/22/filestream-design-and-implementation-considerations.aspx

Introducing the sql server 2008 performance data collector
http://www.bradmcgehee.com/wp-content/uploads/presentations/Mastering%20the%20SQL%20Server%202008%20Performance%20Data%20Collector.pdf

Multi Server Administration Poster

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

Performance Data Collection Poster

A summary of the key features

Policy Based Management Poster

A summary of the key requirement.

Central Mangement Server Poster

A summary of functionality.

 

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.

Wordle Word Clouds

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

Useful resources for the 70-450 exam

Compare Edition Features
http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

SQL Server PowerShell Overview
http://msdn.microsoft.com/en-us/library/cc281954.aspx

Central Management Server
http://www.sqlwebpedia.com/content/sql-2008-central-management-server
There are two tables used to store the database server details:-

msdb.dbo.sysmanagement_shared_server_groups_internal
msdb.dbo.sysmanagement_shared_registered_servers_internal

http://thomaslarock.com/2010/05/sql-2008-central-management-server/
http://sqlcms.codeplex.com/

Understanding Non-uniform Memory Access
http://msdn.microsoft.com/en-us/library/ms178144.aspx

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
http://msdn.microsoft.com/en-us/library/ms345357.aspx

ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU=1 TO 4
 
Max degree of parallelism Option
http://technet.microsoft.com/en-us/library/ms181007.aspx

General guidelines to use to configure the MAXDOP option
http://support.microsoft.com/kb/329204

Table Partitioning Sliding Window Case
https://blogs.msdn.com/b/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx

Partitioned Table and Index Strategies Using SQL Server 2008
http://technet.microsoft.com/en-us/library/dd578580(SQL.100).aspx

Create Partition Scheme
http://msdn.microsoft.com/en-us/library/ms179854.aspx

sp_estimate_data_compression_savings
Returns the current size of the requested object and estimates the object size for the requested compression state.
http://msdn.microsoft.com/en-us/library/cc280574.aspx

FILESTREAM Storage in SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc949109(v=sql.100).aspx

The FILESTREAM directory structure contains
Filestream.hdr - a metadata file describing the data container
$FSLOG directory - the FILESTREAM equivalent of the database transaction log
http://www.sqlskills.com/BLOGS/PAUL/post/FILESTREAM-directory-structure.aspx

Create Statistics
http://msdn.microsoft.com/en-us/library/ms188038.aspx

Clone a database - creating a statistics only copy
http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/21/cloning-in-sql-server-2005.aspx

SQL Server Statistics ebook
http://www.red-gate.com/our-company/about/book-store/sql-server-statistics

Clustered Index Design Guidelines
http://msdn.microsoft.com/en-us/library/ms190639.aspx

Index with Included Columns
http://msdn.microsoft.com/en-us/library/ms190806.aspx

Plan guides (plan freezing) in SQL Server 2005/2008
http://blogs.msdn.com/b/sqlblog/archive/2009/02/19/plan-guides-plan-freezing-in-sql-server-2005-2008.aspx

Guide to SQL Server Isolation Levels
http://www.littlekendra.com/2011/02/08/isoposter/ 


Tips on Optimizing SQL Server Composite Indexes
http://www.sql-server-performance.com/2007/composite-indexes/

Reorganizing and Rebuilding Indexes
http://technet.microsoft.com/en-us/library/ms189858.aspx

SQL Server 2008 Full-Text Search: Internals and Enhancements
http://technet.microsoft.com/en-us/library/cc721269(SQL.100).aspx

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)
http://msdn.microsoft.com/en-us/library/ms187787.aspx

Monitoring Best Practices by Using Policy-Based Management
http://technet.microsoft.com/en-us/library/cc645723.aspx

Enterprise Policy Management Framework
A reporting solution against a desired state defined in a policy
http://epmframework.codeplex.com/

Microsoft® SQL Server 2008 R2 Best Practices Analyzer
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591
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.
http://msdn.microsoft.com/en-us/library/ms175941.aspx
http://msdn.microsoft.com/en-us/library/ms186406.aspx

An Introduction to SQL Server 2008 Audit
http://www.bradmcgehee.com/2010/03/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.
http://www.bradmcgehee.com/2010/04/an-introduction-to-sql-server-2008-change-data-capture/

Change Tracking Overview
A lightweight solution that provides an efficient change tracking mechanism for applications.
http://msdn.microsoft.com/en-us/library/bb933875.aspx

Comparing Change Data Capture and Change Tracking
http://msdn.microsoft.com/en-us/library/cc280519.aspx

Auditing in SQL Server 2008
http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx

SQL Server Alerts: Soup to Nuts
http://www.simple-talk.com/sql/database-administration/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.
http://msdn.microsoft.com/en-us/library/ms162557.aspx

Create Event Notification
Log and review changes or activiity occuring on the database
http://technet.microsoft.com/en-us/library/ms189453.aspx

Configure a Fail-Safe Operator for Notifications
http://technet.microsoft.com/en-us/magazine/dd408824.aspx

Multi-Server Administration with Master Target jobs
http://www.sqlservercentral.com/articles/Administration/multiserveradministration/675/

Instructions for using SQL Server 2008 in FIPS 140-2-compliant mode
http://support.microsoft.com/kb/955720

Audit a SQL Server Configuration
http://sqlserverpedia.com/wiki/Audit_a_SQL_Server_Configuration

Transparent Data Encryption
http://www.mssqltips.com/tip.asp?tip=1514
http://www.bradmcgehee.com/2008/09/sql-server-2008-transparent-data-encryption/
http://technet.microsoft.com/en-us/library/bb934049.aspx

Cell Level Encrpytion
http://msdn.microsoft.com/en-us/library/cc278098(v=sql.100).aspx

SQL Server 2008 Jumpstart Training Materials
http://blogs.msdn.com/b/jbarnes/archive/2008/09/08/free-sql-server-2008-jumpstart-training.aspx

Dynamic Management Views and Functions
http://msdn.microsoft.com/en-us/library/ms188754.aspx

Free Poster – SQL Server Dynamic Management Views
http://kevinekline.com/2010/08/29/free-poster-sql-server-dynamic-management-views/

sp_trace_setevent - SQL Trace historical analysis
http://msdn.microsoft.com/en-us/library/ms186265.aspx

sys.dm_tran_locks
http://msdn.microsoft.com/en-us/library/ms190345.aspx

sp_who and sp_who2
http://www.sqlhacks.com/Administration/Who_vs_who2

Isolation Levels in the Database Engine
http://msdn.microsoft.com/en-us/library/ms189122.aspx

Capturing the Execution Plan
http://sqlinthewild.co.za/index.php/2011/01/04/capturing-the-execution-plan/

Understanding SET STATISTICS IO and SET STATISTICS TIME
http://www.sqlprof.com/blogs/sqlserver/archive/2008/03/23/understanding-set-statistics-io-and-set-statistics-time.aspx

The Data Collector
http://msdn.microsoft.com/en-us/library/bb677248.aspx
http://msdn.microsoft.com/en-us/library/bb677356.aspx

The Reports for Data Collections
http://www.databasejournal.com/features/mssql/article.php/3779846/Reports-for-SQL-Server-2008-System-Data-Collections.htm

Linked Servers
http://msdn.microsoft.com/en-us/library/ms188279.aspx

MS DTC Distributed Transactions
http://msdn.microsoft.com/en-us/library/ms190799.aspx

Scalable Shared Databases Overview
http://technet.microsoft.com/en-us/library/ms345392.aspx

SQL Server Replication

http://msdn.microsoft.com/en-us/library/ms151198.aspx

SQL Server Replication: Providing High Availability using Database Mirroring
http://sqlcat.com/whitepapers/comments/2348.aspx

Disaster recovery solution for distribution database in replication
http://sqlcat.com/whitepapers/archive/2009/09/23/using-replication-for-high-availability-and-disaster-recovery.aspx

Replication Security Best Practices
http://msdn.microsoft.com/en-us/library/ms151227.aspx

Peer-to-Peer Transactional Replication

http://msdn.microsoft.com/en-us/library/ms151196.aspx

High Availability Solutions Overview

http://msdn.microsoft.com/en-us/library/ms190202.aspx

Log Shipping Deployment
http://msdn.microsoft.com/en-us/library/ms188698.aspx

Database Mirroring Overview
http://msdn.microsoft.com/en-us/library/ms189852.aspx

SQL Server 2008 Failover Clustering
http://sqlcat.com/whitepapers/archive/2009/07/08/sql-server-2008-failover-clustering.aspx

Introduction to Backup and Restore Strategies in SQL Server

http://msdn.microsoft.com/en-us/library/ms191239.aspx

Copy Only Backup

http://www.mssqltips.com/tip.asp?tip=1772

Backup Compression
http://www.keepitsimpleandfast.com/2010/04/backup-compression-in-sql-server-2008.html
http://technet.microsoft.com/en-us/library/bb964719.aspx
To calculate the compression ratio of a backup use
SELECT backup_size/compressed_backup_size FROM msdb..backupset;
Using Mirrored Backup Media Sets

http://msdn.microsoft.com/en-us/library/ms175053.aspx

Backup Devices

http://dbarecovery.com/backup-and-recovery-exercises/sql-server-2005/sql-server-2005-create-backup-device.html
Have a backup strategy document

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

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?

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.

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

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...

Troubleshooting Performance

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

troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspx

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
http://devcentral.f5.com/weblogs/macvittie/archive/2010/12/01/the-database-tier-is-not-elastic.aspx.

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

Useful Links for the MCTS Exam

Useful links for further reading for the MCTS exam.

TS: Microsoft SQL Server 2008, Implementation and Maintenance

http://www.microsoft.com/learning/en/us/exam.aspx?id=70-432

Understanding and Managing the suspect_pages Table
http://msdn.microsoft.com/en-us/library/ms191301.aspx

Tablediff Utility
http://msdn.microsoft.com/en-us/library/ms162843.aspx

How to Rebuild System Databases in SQL Server 2008
http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

Rebuilding the SQL Server master database - Part 1
http://www.mssqltips.com/tip.asp?tip=1531
Rebuilding the SQL Server master database - Part 2
http://www.mssqltips.com/tip.asp?tip=1528

SQL Server 2005: View all permissions (2) - Explode the roles! second script works
http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

Partitioned Table and Index Strategies Using SQL Server 2008
http://technet.microsoft.com/en-us/library/dd578580(SQL.100).aspx

Sql Server 2008 Geospatial Data Generator
http://sqlblogcasts.com/blogs/thepremiers/archive/2008/07/09/virtual-earth-gt-sql-server-2008-geospatial-data-generator.aspx

SQL Server Audit Action Groups and Actions
http://technet.microsoft.com/en-us/library/cc280663.aspx

fn_get_audit_file (Transact-SQL)
http://technet.microsoft.com/en-us/library/cc280765(SQL.100).aspx

Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html

Database Audit Specifications in SQL 2008
http://www.sqldbatips.com/showarticle.asp?ID=136

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
http://msdn.microsoft.com/en-us/library/ms188917.aspx

Allocation SQL Server Management Studio Add in
http://ssmsallocation.codeplex.com/

Internals Viewer for SQL Server
http://internalsviewer.codeplex.com/

SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization
http://ola.hallengren.com/

XML Jumpstart Workbench
http://www.simple-talk.com/sql/t-sql-programming/xml-jumpstart-workbench/

Replication Agent Security Model
http://msdn.microsoft.com/en-us/library/ms151868.aspx

Script to indentifty big replication transactions
http://www.sqlservercentral.com/scripts/Replication/71730/

Using Secondary Servers for Query Processing
http://msdn.microsoft.com/en-us/library/ms189572.aspx

Database Mirroring in SQL Server 2005
http://technet.microsoft.com/en-us/library/cc917680.aspx

Monitoring SQL Server Database Mirroring with Email Alerts
http://www.mssqltips.com/tip.asp?tip=1859

SQL Server Best Practices Article
http://technet.microsoft.com/en-us/library/cc917713.aspx

Alerting on Database Mirroring Events
http://technet.microsoft.com/en-us/library/cc966392.aspx

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
http://www.quest.com/documents/landing.aspx?id=11635&technology=34&prod=&prodfamily=&loc

Troubleshooting Performance Problems in SQL Server 2008
http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

Isolation Levels in the Database Engine
http://msdn.microsoft.com/en-us/library/ms189122.aspx

Who did what?
http://www.sqlservercentral.com/scripts/Monitoring/62467/

Execution Plan Basics
http://www.simple-talk.com/sql/performance/execution-plan-basics/

Performance Tuning
http://www.sqlservercentral.com/search/?q=joseph+sack

fn_trace_gettable (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188425.aspx

ClearTrace
http://www.scalesql.com/cleartrace/

XML Tutorial
http://www.w3schools.com/xml/default.asp

XML Data Modification Language Workbench
http://www.simple-talk.com/sql/t-sql-programming/xml-data-modification-language-workbench/

DDL Event Groups
http://msdn.microsoft.com/en-us/library/bb510452.aspx

DDL Events
http://msdn.microsoft.com/en-us/library/bb522542.aspx

Joe Celko The SQL Apprentice
http://joecelkothesqlapprentice.blogspot.com/

Sorting Months By Number (SQL Spackle)
http://www.sqlservercentral.com/articles/T-SQL/71511/

Creating Your Own Custom Data Collections
http://www.databasejournal.com/features/mssql/article.php/3798221/Creating-Your-Own-Custom-Data-Collections.htm

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

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.

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.