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

"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