CODEWITHSUNDEEP
c#sql-serverazureazure-sql-database
No1Lives4Ever
No1Lives4Ever

Reputation: 6903

Reliable connection with Azure SQL Database

I developing a C# application that storing data in Azure SQL Database.

As you probably know, Azure SQL Database is placed somewhere on the Internet. Not over LAN network (but this question also relevant for reliable network like LAN).

I've noticed that from time-to-time that I'm getting errors like "Connection is closed" (or another network errors). It's really easy to simulate this with Clumsy. The reasons for those errors are bad network conditions.

So, my first idea to solve this is "try again". When I getting this error, I simply try again and then it's working good. Like a magic.

This maybe solving the problem, but, open another kind of problems. Not all the situations are good with this solution. I'll explain:

I'll separate the scenarios for two types:

  1. Retry cant make any damage - operation like SELECT or DELETE. Retrying will have the same expected result. So, with this type of problems - my solution is working fine!
  2. Insert or Update - retry will damage the information.

I'll focus the the point number 2. For example, let's say I have:

The "Pay" Stored Procedure is:

UPDATE tblUsers SET [Credits] -= @requestedCredits WHERE ID=@ID

Calling the SP is tricky problem:

So, "Retry" strategy here is not an option.

Solutions I'm thought on:

I'm though to solve this problem by adding a "VersionID" for each row. My SP now: 

UPDATE tblUsers SET [Credits] -= @requestedCredits, VersionId=NEWID() WHERE ID=@ID AND VersionID=@OldVersionId

Before making the user Pay(), I'll check the VersionID (Random GUID) and if this GUID wasn't changed after network failure while paying, I'll try again (proof that the data wasn't changed on the DB). If this VersionId changed, so the user is paid for the service.

The problem is when I using multiple machines at same time, this making this solution problematic. Because another instance maybe made a Pay() on the version-id and I'll think that my change is executed by me (which wrong).

What to do?

Upvotes: 4

Views: 613

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294407

You never blindly retry. In case of error you read current state then re-apply the logic and then write the new state. What 'apply the logic' means will differ from case to case. Present the user again with the form, refresh a web page, run a method in your business logic, anything really.

The gist of it is that you can never simply retry the operation w/o first reloading the persisted state. The only truth is what's in the DB and the error is big warning that your cached state is stale.

Upvotes: 1

Abhay Saraf
Abhay Saraf

Reputation: 1212

It sounds like you are making SQL queries from a local/on-premise/remote (i.e. non-Azure property) to a SQL Azure database.

Some of the possible mechanisms of dealing with this are

  • Azure hosted data access layer with API

    Consider creating a thin data access layer API hosted on Azure WebApp or VM to be called from the remote machine. This API service can interact with SQL Azure reliably.

    SQL is more sensitive to timeout and network issues than say a HTTP endpoint. Especially if your queries involve transfer of large amounts of data.

  • Configure an increased timeout

    The database access mechanism being used by the C# application is not specified in the question. Many libraries or functions for data access allow you to specify an increased timeout for the connection.

  • Virtual Private Network

    Azure allows you to you create a site-to-site or point-to-site VPN with better network connectivity. However, this is the least preferred mechanism.

Upvotes: 1

Related Questions