The easiest way to modify retry logic when migrating LINQ to SQL to SQL Azure

Question

I have several existing ASP.NET web forms and MVC applications that currently use LINQ to SQL with a SQL Server 2008 Express database on Windows VPS: one VPS for IIS and SQL. I am starting to outgrow the ability of VPS to efficiently host both SQL and IIS, and I am ready to separate them. I am considering moving the database to SQL Azure and saving IIS to VPS.

After the initial research, it seems that implementing retry logic at the data access level is a prerequisite when using SQL Azure. I suspect this is even more important to implement in my situation where IIS will be on a VPS outside the Azure infrastructure.

I am looking for pointers on how to do this, with minimal effort and impact on the existing code base. Is there a good replay pattern that can be applied once at the LINQ to SQL data access level, as opposed to having to transfer all my LINQ operations to SQL into try / catch / wait / retry logic?

Answer 1

I do not see the need to complicate this issue. Wrap it all up in an attempt to catch, wrap it up after a while <try again and you're done.

You can get a big mind and trap for different types of errors, if you want to handle them differently, no need to retry if this is a genuine data error.

Make sure that you still track every exception to keep track of errors other than timeouts.

Answer 2

You can take a look at the Apect Oriented Programming (AOP) framework to control repeat logic with minimal effort. One such AspectF framework that freely implements AOP principles. Easy to understand and work.

Answer 3

The basic principle is that you repeat any transaction that does not work for a reason that may be more invalid. Therefore, if the transaction failed due to a deadlock or poor connection to the database server, you should retry several times with a sleeping sleep between each attempt. It depends on how well your transaction is defined .

Depending on your logic, you can implement a retry at the page request level.

• It depends on how safe it is to repeat the work that the page request does, or to fully advance the page request within the database transaction.
• Also think about any state of memory that you store on the server.
• The HttpHander added to the page processing pipeline should be able to do this for all pages.

I don’t think your code has “magic you can spindle” to figure it out if you do not have clear transactions.

If you are not using any transaction, you can try to write all your sql so that it can be safely run as many times as you like, and then just have a custom subclass of the command object, which is executed if necessary.