Exam 70-442 Objective 5

Tested myself on Exam 70-442 (Designing and Optimizing Data Access by Using Microsoft SQL Server 2005) Objective 5 (Performance Tuning a Database and a Database Application) and received only 32%.  I've got a bit of study to do, specifically:

1. Optimize and tune queries for performance
   Creating Partitioned Views
   Table Hints (Transact-SQL)
   SET ANSI_PADDING (Transact-SQL)
   Introducing SQL Trace
   max degree of parallelism Option
2. Optimize indexing strategies
   XML Best Practices
   Planning Guidelines for Partioned Tables and Indexes
   Creating Partitioned Tables and Indexes
3. Scale database applications
   Creating Distributed Partitioned Views
4. Resolve performance problems
   Dynamic Management Views and Functions (Transact-SQL)
   Performance Optimizations for the XML Data Type in SQL Server 2005
   sys.dm_os_wait_stats (Transact-SQL)
5. Optimize data storage
   Data Normalization

Exam 70-442 Objective 4

Tested myself on Exam 70-442 (Designing and Optimizing Data Access by Using Microsoft SQL Server 2005) Objective 4 (Designing a Transaction Strategy) and received 100%.  However, I've still got a bit of study to do, specifically:

1. Manage concurrency by selecting the appropriate transaction isolation levels
2. Design the locking granularity level
3. Design transaction scopes
4. Design code that uses transactions
   Optimization using Single Phase Commit and Promotable Single Phase Notification
   Committing a Transaction in Single-Phase and Multi-Phase
   Enlisting Resources as Participants in a Transaction
   Transaction Management Escalation

Exam 70-442 Objective 3

Tested myself on Exam 70-442 (Designing and Optimizing Data Access by Using Microsoft SQL Server 2005) Objective 3 (Designing Error-Handling Routines) and received only 78%.  I've got a bit of study to do, specifically:

1. Design code that validates input data and permissions
2. Design code that detects and reacts to errors
3. Design user-defined messages to communicate application events
   FETCH (Transact-SQL)

Exam 70-442 Objective 2

Tested myself on Exam 70-442 (Designing and Optimizing Data Access by Using Microsoft SQL Server 2005) Objective 2 (Designing a Database Query Strategy) and received only 60%.  I've got a bit of study to do, specifically:

1. Write and modify queries
   Declarative Security Used with Class and Member Scope
2. Design queries for retrieving data from XML sources
   How To: Use Role Manager in ASP.NET 2.0
3. Design a cursor strategry
   DECLARE CURSOR (Transact-SQL)

Exam 70-442 Objective 1

Tested myself on Exam 70-442 (Designing and Optimizing Data Access by Using Microsoft SQL Server 2005) Objective 1 (Designing Efficient Access to a SQL Server Service) and received only 46.8%.  I've got a bit of study to do, specifically:

1. Design appropriate data access technologies
   ServerConnection Class
   Special Considerations When Using Query Notifications
   Query Notifications in ADO.NET 2.0
   Database Administrator's Guide to SQL Server Database Engine .NET CLR Environment
   Connecting to an Instance of SQL Server
   ADO.NET Architecture
2. Design an appropriate data access object model
   Updating an Application to SQL Server Native Client from MDAC
   Using ADO with SQL Server Native Client
   Using Row Versioning-based Isolation Levels
   Understanding Row Versioning-Based Isolation Levels
   Row Versioning Resource Usage
3. Design a cursor strategy for a data access component
   DECLARE CURSOR (Transact-SQL)
4. Design caching strategies
   ASP.NET Caching Overview
5. Design client libraries to write applications that administer a SQL Server service
   Configuring SQL Server in SMO
   A Technical Comparision of Replication and Remote Data Access Features in SQL Server 2005 Mobile Edition 3.0
   How to: Implement a Business Logic Handler for a Merge Article (Replication Programming)
   How to: Programmatically Monitor Replication (RMO Programming)
   Microsoft.SqlServer.Replication Namespace
   Replication Management Objects Concepts
6. Design queries that use multiple active results sets (MARS)
   Multiple Active Result Sets (MARS) in SQL Server 2005

Introduction to SQL Server Data Services

When	Thursday 19 February @ 5:30pm
Where	Wellington SQL Server User Group, Intergen Offices, Plunket House, Lambton Quay
What	I presented on SQL Server Data Services
Impressions	Was well received. I started with a question; whether the audience would prefer the presentation I'd prepared, or the Microsoft presentation I had received that morning.  They chose my prepared presentation.  I was a little disappointed because the Microsoft presentation had flash graphics, and I couldn't be blamed for being under-prepared!  Nevertheless, there was a fruitful to-and-fro with the audience: Matty was insightful, as usual. Clive was incredulous and unimpressed with the lack of features provided thus far. Adrian asked a pertenant question afterwards, which I'd like to answer here as it's very relevant to the entire discussion.  He was honest about his (and I suspect many people's) confusion about Azure vs. SSDS. Think of Azure as like a Cloud O/S. Think of SSDS as like a Cloud DB. Yes, you could store blobs and entities to Azure, but over time you really shouldn't be, because Microsoft is going to add more functionality to SSDS to enable relational queries, backup, restore, and BI. My slides are here. Microsoft's slides are here.

Agenda Items for Team Meeting

Telecom Workgroup Solutions Team are having our AGM on Wednesday 25 February, this time in Auckland. Items for discussion so far are:

• Team Foundation Server. When are we going to it? What are the implications? Should we just join Stephen Burke's team wireless domain (after the move on Friday 6 March) or go it alone?
• David's idea to have a dual install SQL Server 2005 and 2008 on DEV. Merits and potential difficulties.
• Document repository. Guidelines for BLOB vs. Link.
• How to use DocsOnLine.
• My Azure SQL Server Data Services presentation.
• Digital Workflow

MS Architect Forum 12 Feb 2009

Venue Details:	When is the Event: 1:00pm Thursday 12 February 2009. Where is the Event: Microsoft’s Wellington offices, Mana Room - Level 12 Vodafone on the Park (formerly Mobil House), 157 Lambton Quay (the building behind Midland Park)
Title:	Cloud Economics for non-economists by Chris J.T. Auld
Description:	This session aims to bring the Cloud hype back down to ground. Learn how to discuss the cloud in terms that your CxO will understand. What sort of business model is amenable to the cloud? What does computing as a utility do for your balance sheet? How does cloud based computing companies to continue to innovate in these credit and capital constrained times? Why are advertising based businesses well suited to cloud deployment? Whether you're in start-up mode or have already crossed the tipping point this session will help you determine if the cloud makes sense for your business.
About Chris J.T Auld:	Chris is Director, Strategy and Innovation for Intergen. His appointment was the result of Intergen’s acquisition in February 2007 of Kognition which Chris had established in 2003. Chris has worked with high-level national and multinational clients such as Microsoft and Telecom New Zealand. Chris works closely with large organisations to assist them in propelling their strategy and maximising innovation, undertaking ‘adventurous’, high risk, high return projects wherever possible to add value. Chris’s vision is to revolutionise large businesses through dynamism, passion and leading-edge innovation. Known for his evangelical, ‘arm-waving’ style and his enthusiasm and drive, Chris brings an abundance of excitement and energy to every project he undertakes.

Transaction Isolation Levels

I was trying to think of an easy way to memorise/understand these.  What do others think of this approach?

Transaction Isolation Level	Lost Update	Dirty Read	Non-Repeatable Read	Phantom Reads	Shared Locks	Schema Stability Lock	Alternate Reality	Update Conflicts
Read committed	N	N	Y	Y	Acquired for read, released immediately		N
Read uncommitted	N	Y	Y	Y	Not acquired for read	Acquired	N
Repeatable read	N	N	N	Y	Acquired for read, held for duration		N
Serializable	N	N	N	N	Locks range or entire table		N
Snapshot	N	N	N	N	Not acquired for read		Y	Y
Read committed snapshot	N	N	Y	Y	Not acquired for read		Y	N

Web Application Stress Tool - 401 Unauthorized Errors

Problem	I'm using the Microsoft Web Application Stress Tool.  It's meant to hit a web page repeatedly for testing purposes.  However, when I check the results, they were all "401 - Unauthorised".
Explanation	It turns out that if anonymous access is disabled on the web site you are accessing due to the way IIS evaluates authentication, this behavior is expected. In Operations Manager, the default evaluation for a Web Application is to generate a Critical Alert if an HTTP return code of 400 or anything above comes back. In IIS, even if anonymous authentication is disabled for the site, it is still evaluated and will always return a 401. The aggregated results (The outcome) will succeed but the 401 return code will cause the test to fail...
Solution	The simplest fix (for development and testing purposes) is to enable anonymous access to the site.