Robust Error Handling in Azure SQL

Robust error handling in Azure SQL requires a two-pronged approach: managing transient errors within the client application and handling logical errors within your T-SQL code.

Client Application Error Handling

Applications connecting to Azure SQL must be resilient to transient errors—momentary connectivity failures caused by events like hardware upgrades or load balancing. Implementing retry logic is the best practice for handling these faults.

Use an Exponential Back-off Strategy

• Wait for at least 5 seconds before the first retry.
• Increase the delay exponentially for each subsequent retry, up to a maximum of 60 seconds.
• Introduce a small amount of randomization to the delay to prevent multiple application instances from retrying simultaneously and overwhelming the service.
• Set a maximum number of retries before giving up and terminating the operation.

Leverage Built-in Retry Logic

• For ADO.NET and .NET Core: Use the ConnectRetryCount and ConnectRetryInterval parameters in your connection string to automatically handle transient connection failures.
• For other languages: Check if your client library provides a managed way to handle retries.

Use a Circuit Breaker Pattern

• After a certain number of failures, the circuit breaker "trips" and all future requests fail immediately.
• The circuit breaker will periodically test the service to determine if it has recovered, allowing requests again once healthy.

T-SQL Code Error Handling

Within your stored procedures, functions, and other T-SQL code, use TRY...CATCH blocks to handle non-transient, logical errors gracefully.

Wrap Statements in TRY...CATCH

Enclose any code that might fail in a TRY block. If an error occurs, control passes to the CATCH block.

Check Transaction State

• Use XACT_STATE() to check if a transaction is committable.
• If XACT_STATE() is negative, use ROLLBACK TRANSACTION.
• Use SET XACT_ABORT ON to make transactions uncommittable if an error occurs.

Retrieve and Log Error Details

• ERROR_NUMBER(): The error number
• ERROR_MESSAGE(): The complete error message
• ERROR_SEVERITY() : The severity level
• ERROR_STATE(): The error state number
• ERROR_PROCEDURE(): The procedure or trigger where the error occurred
• ERROR_LINE(): The line number where the error occurred

Re-throw Errors with THROW

After logging the error, use the THROW statement to propagate the error back to the client application.

Comprehensive Error Handling Workflow

1. Client-side: A client application attempts a database operation using a library like ADO.NET.
2. Transient Error: If a transient error (e.g., error code 40613) occurs, the application retries using exponential back-off.
3. T-SQL Execution Error: If a logical T-SQL error occurs, the TRY block transfers control to the CATCH block.
4. Server-side Handling: The CATCH block logs error details, checks transaction state, and rolls back if needed.
5. Error Propagation: The stored procedure uses THROW to send the error back to the client.
6. Client-side Final Action: The client logs the failure, notifies the user, and does not retry for logical errors.

Best Practices for Error Handling in Azure SQL

1. Handle Transient Errors Gracefully

• Implement retry logic: Use exponential backoff with capped retries.
• Use built-in retry features: Configure connection strings with retry settings.
• Apply circuit breaker pattern: Prevent repeated failures from overwhelming the system.

2. Use TRY...CATCH in T-SQL

• Wrap risky operations: Use TRY...CATCH blocks to catch runtime errors.
• Log error details: Capture error number, message, severity, and procedure.
• Use THROW: Re-throw errors with modern syntax instead of RAISERROR.

3. Manage Transactions Safely

• Use XACT_STATE(): Check transaction state before committing or rolling back.
• Enable XACT_ABORT: Automatically roll back on error to avoid partial commits.

4. Avoid Leaking Sensitive Information

• Sanitize error messages: Do not expose internal details to clients.
• Log full errors internally: Keep detailed logs for diagnostics and auditing.

5. Monitor and Alert

• Use Azure Monitor: Track error rates and performance metrics.
• Set up alerts: Notify teams when thresholds are breached.

6. Document and Standardize

• Define error codes: Create a consistent error response format for clients.
• Include remediation tips: Help consumers understand and resolve issues.

Pro Tip: Combine client-side retry logic with server-side error handling for robust and resilient applications.