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.