Welcome

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

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

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