Easiest way to retrofit retry logic on LINQ to SQL migration to SQL Azure

I have a couple of existing ASP .NET web forms and MVC applications that currently use LINQ to SQL with a SQL Server 2008 Express database on a Windows VPS: one VPS for both IIS and SQL. I am starting to outgrow the VPS's ability to effectively host both SQL and IIS and am getting ready to split them up. I am considering migrating the database to SQL Azure and keeping IIS on the VPS.

After doing initial research it sounds like implementing retry logic in the data access layer is a must-do when adopting SQL Azure. I suspect this is even more critical to implement in开发者_JS百科 my situation where IIS will be on a VPS outside of the Azure infrastructure.

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

I don't see a need to get complicated on this one. Wrap the whole thing in a try catch, wrap that in a while < retry count and you're done.

You can get a big smarter and trap for different types of errors, if you'd like to handle them differently, no need retrying if it's a genuine data error.

Make sure you still Trace out each exception, to track for other errors besides timeouts.

You can have a look at Aspect Oriented Programming (AOP) frameworks to manage retry logics with minimum effort. One such framework is AspectF which loosly implements the AOP principles. It's easy to understand and work with.

The basic principle is that you retry any transaction that fails for a reason that may no longer be true. So if a transaction fails due to a deadlock, or bad connection to the database server you should retry it a few times with a sort sleep between each retry. This all depends on your transaction being well defined.

Depending on your logic you may be able to implement retry at the “page request level”.

• This depends on it being safe to redo the work a page request does, or for the complete progressing of a page request to be inside of a database transaction.
• Also think about any in memory state you keep on the server.
• An HttpHander added to the page processing pipeline should be able to do this for all pages

I don’t think there is any “magic you can spindle” on your code to sort this out, if you don’t have clear transactions defined.

If you are not using any transaction you could try writing all your sql so it is safe to run it any number of times and then just have a custom subclass of the command object that reties when needed.