Fault Retry Sample
The Fault Retry sample demonstrates recommended practices for creating high availability database applications using SQL Server mirroring technology in the back end. In principal this fault retry approach would be useful for ordinary servers or for clusters, although those scenarios have not been tested at this time. There are two components to the sample, a Fault Retry custom .NET data provider, and a sample application which uses the Fault Retry data provider. The Fault Retry data provider is a component which could be reused as part of any high availability application. The Product Editor sample application demonstrates how to use the Fault Retry data provider in a simple but realistic application. Note that there are limits to what the Fault Retry data provider can achieve in its current implementation. See the Notes section at the end of this document for a discussion of the implementation’s current limitations.
SQL Server mirroring provides a mechanism for automatically copying changes to database on the principal database server to a database on the mirror database server. A witness server may be used to facilitate automatic server failover.
When server failover occurs, clients can discover that the principal has gone down when they execute an operation which fails. While it is possible to sprinkle try/catch blocks throughout the application code to handle these situations, it is not a practical approach for more than very simple application.
The Fault Retry data provider demonstrates how you can seamlessly handle transient exceptions when the principal goes down by automatically retrying operations when certain types of SQL exceptions are raised during ADO.NET command execution. This approach works even with data binding scenarios where there is no explicit user code available to wrap with try/catch blocks.
The Product Editor sample application demonstrates how simple client applications can be if the retry logic is embedded in the Fault Retry data provider rather than in the application itself.
Both the Fault Retry data provider component and the Product Editor sample application demonstrate how to concisely apply a wide range of SQL Server and .NET Framework technologies together to build highly reliable applications.
Products, Technologies, and Features
This sample uses the following products, technologies, and features:
- SQL Server 2008
- Database mirroring
- AdventureWorksLT2008 sample database
- SQL Server Configuration Manager
- SQL Server Management Studio
- .NET Framework 3.5
- Client side mirroring support
- Strongly typed data sets
- Safely constructing connection strings
- Creating a custom .NET data provider
- LINQ to DataSet
- Windows Presentation Foundation (WPF)
- Data templates and hierarchical data templates
- Tree views
- Grid views
- Data binding
- Gradient brushes
- Lambda expressions
- Generic classes
- Generic methods
- Isolated storage
The Fault Retry sample application requires the following on your computer in addition to the sample itself:
Note that any edition of SQL Server 2008 (including Express) can be used on a witness server.
Installing the Sample Database
1. Download the AdventureWorksLT2008 sample database from the specified URL above onto the machine that will be the principal server. On that web page click on SQL2008.AdventureWorks_All_Databases.x86.msi
if your server is an x86 machine, SQL2008.AdventureWorks_All_Databases.x64.msi
if your server is an x64 machine, and SQL2008.AdventureWorks_All_Databases.ia64.msi
if your server is an IA64 machine. During installation select the feature which restores the database for you.
2. Configure the AdventureWorksLT2008 sample database for full recovery using SQL Server Management Studio (SSMS) or via the following script using SQLCMD:
ALTER DATABASE AdventureWorksLT2008
SET RECOVERY FULL;
3. Copy the AdventureWorksLT2008.bak database backup file to the mirror server’s local disk from the install directory (sytemdrive: Program Files\Microsoft SQL Server\100\Tools\Samples by default).
4. On the mirror server, restore the database using the WITH NORECOVERY option similar to the following script:
RESTORE DATABASE AdventureWorksLT2008
FROM DISK = 'C:\AdventureWorksLT2008.bak'
Be sure to replace C:\ in the above script with the local directory you copied the backup file to on the mirror machine.
for more details.
There are several different ways to configure mirroring depending on which security approach is used. See http://msdn.microsoft.com/en-us/library/ms365599.aspx
for the details on configuring your machines for mirroring. Make sure you open the firewall for both the SQL Server port (1433 by default) and the mirroring port (5022 by default) on the principal, mirror, and witness servers. Make sure your servers are running under a common domain account with sufficient privileges, or that you configure mirroring with certificates.
Compiling the Application
Using Visual Studio 2008 SP1 open the ADONETMirroring.sln file and compile the solution, or using the .NET Framework 3.5 SP1 SDK execute the following command using the .NET Framework Command Prompt (an ordinary command prompt will not work):
msbuild /nologo /verbosity:quiet /property:Configuration=Debug ADONETMirroring.sln
Running the Application
- Run the application you just compiled in ADONETMirroring\ProductEditor\bin\Debug\ProductEditor.exe.
- The first time you run the application the configuration dialog will appear. Enter the principal server name in the principal combo box, and the mirror server name in the mirror combo box. Click OK. Note: you can return to this dialog during product editing by clicking on the Configure button and add additional servers and change the selected servers.
- If the selected servers are up and available, a tree control on the right will be populated on the left hand side of the screen. Click on the arrow to the left of the category name to expand the category into sub-categories.
- Click on a sub-category. The products in that sub-category will be displayed in the right hand product editor pane.
- Edit the name and/or price of any products you wish, then click Save to save those changes, or click Clear to revert those changes.
- Use SQL Server Configuration Manager to stop the principal server, make an edit to a product, and then click save. Notice that the application handles any exceptions and updates what had initially been the mirror server. The mirroring mechanism in SQL Server switches the initial mirror server to be the principal when the principal goes down.
- You can connect to running servers using SQL Server Management Studio (SSMS) to observe which server is currently the principal and which one the mirror for a mirrored database.
- If you start the server which was initially the principal you can connect to it via SSMS and notice that it is now the mirror server. Note that you do not have to reconfigure the application even though the initial principal and mirror servers have now switched roles. ADO.NET correctly handles the switch over.
- When you are finished, click Exit.
Compiling the application produces the DLLs listed within the blue rectangle. The components within the red dashed box contain code which is specific to the product editing scenario. The other component (the Fault Retry data provider) is reusable for a wide range of applications. The Fault Retry data provider wraps the SQL Server managed data provider, and offers the exact same API. The Fault Retry data provider works by enclosing all operations which access the database with retry logic which reinvokes the operation if certain recoverable failures occurs, and which reconnects if a connection is closed by the server. If the retry limit is exceeded, then the original exception is rethrown and should be handled by the application.
The Fault Retry data provider is very reliable so long as all operations invoked are Idempotent. An idempotent database operation is one where no inaccurate data would be recorded if the operation is executed more than once. Not all database inserts and updates are idempotent. There are certain cases (particularly if an unreliable network is involved) where the client experiences a failure but the operation succeeds on the server. In such cases non-idempotent operations will either fail hard (for example, getting a key violation) or record incorrect (for example, duplicate) data in the database. Also, transactions which were initiated on the principal server, but then failover midway through the transaction will fail hard because the current implementations of the Fault Retry data provider, ADO.NET, and SQL Server do not retry all operations which were part of the transaction after failover. A somewhat more elaborate mechanism which could handle these two cases could be implemented in the Fault Retry data provider, however this additional mechanism would be expensive in terms of memory and performance. This mechanism could be added as an option in a future implementation if there is sufficient interest in the community.