Skip to content

configurable-retry-logic-config-file-sqlclient.md

Latest commit

 

History

History
138 lines (96 loc) · 8.09 KB

configurable-retry-logic-config-file-sqlclient.md

File metadata and controls

138 lines (96 loc) · 8.09 KB
title Configurable retry logic configuration file with SqlClient
description Learn how to use a configuration file to specify default retry logic providers and customize retry logic options in Microsoft.Data.SqlClient.
author David-Engel
ms.author davidengel
ms.reviewer v-deshtehari
ms.date 03/22/2021
ms.service sql
ms.subservice connectivity
ms.topic how-to

Configurable retry logic configuration file with SqlClient

[!INCLUDE dotnet-all]

[!INCLUDEDriver_ADONET_Download]

The default retry method when the safety switch is enabled is the xref:Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateNoneRetryProvider%2A?displayProperty=nameWithType for both xref:Microsoft.Data.SqlClient.SqlConnection and xref:Microsoft.Data.SqlClient.SqlCommand. You can specify a different retry method by using a configuration file.

Configuration sections

Default retry logic options for an application can be changed by adding the following sections inside the configSections section of the configuration file:

  • SqlConfigurableRetryLogicConnection: to specify the default retry logic for xref:Microsoft.Data.SqlClient.SqlConnection.
<section name="SqlConfigurableRetryLogicConnection"
        type="Microsoft.Data.SqlClient.SqlConfigurableRetryConnectionSection, Microsoft.Data.SqlClient"/>
  • SqlConfigurableRetryLogicCommand: to specify the default retry logic for xref:Microsoft.Data.SqlClient.SqlCommand.
<section name="SqlConfigurableRetryLogicCommand"
        type="Microsoft.Data.SqlClient.SqlConfigurableRetryCommandSection, Microsoft.Data.SqlClient"/>

Note

The following configurations should be specified inside the configuration section. Declare these new sections to configure the default retry logic through an application configuration file.

Connection section

The following attributes can be used to specify the default retry logic for all xref:Microsoft.Data.SqlClient.SqlConnection instances in an application:

  • numberOfTries: sets the number of times to try.

  • deltaTime: sets the gap time interval as a xref:System.TimeSpan object.

  • minTime: sets the allowed minimum gap time interval as a xref:System.TimeSpan object.

  • maxTime: sets the allowed maximum gap time interval as a xref:System.TimeSpan object.

  • transientErrors: sets the list of transient error numbers on which to retry.

  • retryMethod: specifies a retry method creator that receives the retry configuration via a xref:Microsoft.Data.SqlClient.SqlRetryLogicOption parameter and returns a xref:Microsoft.Data.SqlClient.SqlRetryLogicBaseProvider object.

  • retryLogicType: sets a custom retry logic provider, which contains the retry method creators that provide the retryMethod. These methods should meet the criteria for retryMethod. The fully qualified type name of the provider should be used. For more information, see Specifying fully qualified type names.

Note

It's not required to specify the retryLogicType if you use the built-in retry providers. To find the built-in retry providers, see Internal retry logic providers in SqlClient.

Command section

The following attribute can also be set for all xref:Microsoft.Data.SqlClient.SqlCommand instances in an application:

  • authorizedSqlCondition: Sets a pre-retry regular expression for xref:Microsoft.Data.SqlClient.SqlCommand.CommandText%2A?displayProperty=nameWithType to filter specific SQL statements.

Note

The regular expression is case sensitive.

Examples

  • Attempts to establish a connection up to three times with an approximate 1-second delay between tries by using the xref:Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateFixedRetryProvider%2A?displayProperty=nameWithType method and the default transient error list:

    <SqlConfigurableRetryLogicConnection retryMethod ="CreateFixedRetryProvider" 
                                        numberOfTries ="3" deltaTime ="00:00:01"/>

  • Attempts to establish a connection up to five times with up to a 45-second delay between tries by using the xref:Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateExponentialRetryProvider%2A?displayProperty=nameWithType method and the default transient error list:

    <SqlConfigurableRetryLogicConnection retryMethod ="CreateExponentialRetryProvider" 
                    numberOfTries ="5" deltaTime ="00:00:03" maxTime ="00:00:45"/>

  • Attempts to execute a command up to four times with a delay between 2 and 30 seconds by using the xref:Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateIncrementalRetryProvider%2A?displayProperty=nameWithType method and the default transient error list:

    <SqlConfigurableRetryLogicCommand retryMethod ="CreateIncrementalRetryProvider"
                    numberOfTries ="4" deltaTime ="00:00:02" maxTime ="00:00:30"/>

  • Attempts to execute a command up to eight times with a delay from one second to one minute. It's limited to commands with CommandText containing the word SELECT and exception numbers 102 or 997. It uses the built-in xref:Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateIncrementalRetryProvider%2A?displayProperty=nameWithType method:

    <SqlConfigurableRetryLogicCommand retryMethod ="CreateIncrementalRetryProvider" 
                        numberOfTries ="8" deltaTime ="00:00:01" maxTime ="00:01:00"
                        transientErrors="102, 997"
                        authorizedSqlCondition="\b(SELECT)\b"/>

Note

In the next two samples, you can find the custom retry logic source code from Configurable retry logic core APIs in SqlClient. It's assumed the CreateCustomProvider method is defined in the CustomCRL_Doc.CustomRetry class in the CustomCRL_Doc.dll assembly that is in the application's executing directory.

  • Attempts to establish a connection up to five times, with a delay between 3 and 45 seconds, error numbers 4060, 997, and 233 in the list, and using the specified custom retry provider:

    <SqlConfigurableRetryLogicConnection retryLogicType ="CustomCRL_Doc.CustomRetry, CustomCRL_Doc, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
                    retryMethod ="CreateCustomProvider" 
                    numberOfTries ="5" deltaTime ="00:00:03" maxTime ="00:00:45"
                    transientErrors ="4060, 997, 233"/>

  • This sample behaves like the previous one:

    <SqlConfigurableRetryLogicConnection retryLogicType ="CustomCRL_Doc.CustomRetry, CustomCRL_Doc"
                    retryMethod ="CreateCustomProvider" 
                    numberOfTries ="5" deltaTime ="00:00:03" maxTime ="00:00:45"
                    transientErrors ="4060, 997, 233"/>

Note

Retry logic providers will be cached at the first use on a connection or command for future use during an application's lifetime.

Note

Any errors when reading an application configuration file for retry logic settings won't cause errors in the application. The default xref:Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateNoneRetryProvider%2A?displayProperty=nameWithType will be used instead.

You can use event source tracing to verify or troubleshoot issues with configuring retry logic. For more information, see Enable event tracing in SqlClient.

See also