Transient Fault Handling Application Block & Enterprise Library 6

A new version of Enterprise Library was recently released along with a new version of the Transient Fault Handling Application Block and It brings a couple changes when it come to querying SQL Database using ADO.NET.

The first apparent changes

• Namespace Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure has been replaced by  Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling
• SqlAzureTransientErrorDetectionStrategy has been replaced by SqlDatabaseTransientErrorDetectionStrategy
• The NuGet package has been exploded into many individual packages linked to a primary package.
• Take a look at the Migration Guide & the project on CodePlex

 

Why am I Getting so Many Deadlocks From Windows Azure SQL Database?

The short answer is that you’re probably not using transactions.

I came across this issue a few time and decided to create the ReliableModel. When I modify the state of my database, it automatically wraps my code with a TransactionScope. By default, TransactionScopes are set to timeout after 30 minutes. This configuration is set in the machine.config and it cannot be modified on Windows Azure Roles. Therefor, if your statements need more than 30 minutes to execute, I strongly recommend using Stored Procedures. Be sure to use Transactions in your Stored Procedures.

ADO.Net DataTable why did I ever stop using you?

Recently, I started working on a Windows Azure project which involves working with large datasets. The original design used a normalized database and the application would pull a few megabytes worth of data every time it had to accomplish a task. Needles to say that this wasn’t the most efficient way to go about working with all this data

Inserting Large Amounts of Data Into Windows Azure SQL Database

As some of you may have noticed, Windows Azure SQL Database is quite good at protecting itself. If you try to insert too many rows in a short period of time, it will simply close your connection and cause your transaction to roll back.

I was trying to insert close to 700 000 items into a table and was systematically getting cut off.

General Guidelines and Limitations (Windows Azure SQL Database)

Connection Constraints

Windows Azure SQL Database provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all Windows Azure SQL Database customers, your connection to the service may be closed due to the following conditions:

• Excessive resource usage
• Connections that have been idle for 30 minutes or longer
• Failover because of server failures

In my case, I was probably violating the first of the three reasons mentioned above. I tried to figure out how to go around this issue and after toying around with this problem for some time I came up with using a Table Value as a parameter for a Stored Procedure. I quickly found an example and came up with the following solution.

More information can be found in Windows Azure SQL Database Performance and Elasticity Guide

• SQL Database Performance Checklist

• SQL Database Engine Throttling

The degree to which a subscriber’s connectivity is blocked ranges from blocking inserts and updates only, to blocking all writes, to blocking all reads and writes. The time span for which throttling occurs is referred to as the Throttling Cycle and the duration of a Throttling Cycle is referred to as the Throttling Sleep Interval which is 10 seconds by default.

Throttling severity falls into one of two categories, Soft Throttling for “mildly exceeded” types and Hard Throttling for “significantly exceeded” types. Because significantly exceeded types pose a greater risk to overall system health, they are handled more aggressively than mildly exceeded types. Engine Throttling follows these steps to reduce load and protect system health:

1. Determines the load reduction required to return the system to a healthy state.
2. Marks subscriber databases that are consuming excessive resources as throttling candidates. If Engine Throttling is occurring due to a mildly exceeded type then certain databases may be exempt from consideration as throttling candidates. If Engine Throttling is due to a significantly exceeded type then all subscriber databases can be candidates for throttling with the exception of subscriber databases that have not received any load in the Throttling Cycle immediately preceding the current Throttling Cycle.
3. Calculates how many candidate databases must be throttled to return the system to a healthy state by evaluating the historical resource usage patterns of the candidate databases.
4. Throttles the calculated number of candidate databases until system load is returned to the desired level. Depending on whether throttling is Hard Throttling or Soft Throttling, the degree of throttling applied or the throttling mode, as described in Understanding Windows Azure SQL Database Reason Codes can vary. Any databases that are throttled remain throttled for at least the duration of one throttling cycle but throttling may often persist for multiple throttling cycles to return the system to a healthy state.

Keep in mind that you are sharing the following Database Server configuration.

As of this writing, each SQL Database computer is equipped with 32 GB RAM, 8 CPU cores and 12 hard drives. To ensure flexibility, each SQL Database computer can host multiple subscribers at a time.