Rants of an ADO.NET Dev

Category Archives: Uncategorized

Despite “reliability” being one of the core features of TCP, network connections are typically unreliable, especially when being routed via the internet. This is even more of a problem with cloud computing, since most connections do route via the internet, and backend machines can go down at any moment for any reason. With this in mind, we have been making changes to ADO.Net to adjust from its client\server origins (with always-up enterprise hardware) to the cloud world (with commodity hardware).

Ensuring that only live connections were returned from the pool covered most low-traffic scenarios, since connections typically spend most of their time in the pool rather than being used. However, it provides little assistance for high-traffic scenarios (where connections do not remain in the pool for very long) or for code where connections are held open for a long time (which we strongly recommend against – but is inevitable in applications like SQL Server Management Studio).

One thing to be aware of for this type of resiliency is that we can only detect failures in the physical connection – if the connection’s state has become corrupt (e.g. commands always time out), then we will not be able to detect it and so we can not recover the connection. (In situations like these you can either abandon the pool by changing your connection string, or take the nuclear option and call ClearPool)

Idle Connection Resiliency

ADO.Net 4.5.1 combined with Azure SQL Database or SQL Server 2014 introduced a new feature to provide resiliency for more scenarios: Idle Connection Resiliency. With this feature, we can recover connections even if they were opened at the time that they failed. One of the difficulties with doing this is that SQL connections (unlike, say, a HTTP connection) have state – you may have changed the database you were connected to, or the collation that you were using, or the level of transaction isolation. Either way, when the connection is recovered, we need to ensure that it resumes whatever state it had previously (i.e. it should be completely transparent that you are now using a new physical connection). To do this we introduced a new TDS token called SESSIONSTATE (to allow the server to hand the client its current state as a blob) and a new Feature Extension called SESSIONRECOVERY (to resume the state on a recovered connection).

Let’s assume that you’ve attempted to execute a command on a dead connection. The process to recover it would look like this:

Execute* is called, check if the connection is alive (using a similar mechanism as the Connection Pool Resiliency)

If the connection is dead, then check if it is recoverable from both client and server perspective

There are a few things to note from this. Firstly, the entire process is async – even if you are executing synchronously, we run the process as sync-over-async. Secondly, there are a number of situations where we can not recover the connection. From the client side, the connection must be idle – for most connections this is guaranteed since we perform the check just before execution, but for MARS connections, it is possible that there is another open session (it doesn’t have to be actively in use by another thread – just being open is enough). From the server side there are a number of situations where the state will not fit, or can not be represented in the SESSIONSTATE blob, for instance a temporary table.

Retry Logic

Even though we continue to improve ADO.Net’s ability to handle unreliable connections, this does not eliminate the need to have your own retry logic in your code – our connection recoveries are best effort and can only detect a limited set of faults. If you want a Microsoft supported implementation of generic retry logic, then I would recommend the Transient Fault Handing Application Block.

Bonus chatter: .Net 4.5.1 is now being pushed out via Windows Update, or you can grab it straight from the Download Center (if you’re running Windows 8.1 – and why wouldn’t you be? – then you already have 4.5.1).

As a part of my work to automate testing for P3SS (POP3 to SMTP Server) I have been building a POP3 server that P3SS can run against and I can easily set and inspect the state of as well as introduce errors and ‘bugs’.

This is a basic, but fully RFC 1939 compliant POP3 server that is designed to make unit and functional testing of POP3 clients much easier. At present it supports:

Proper TCP support, including custom ports (110 is default)

Multiple clients (through multi-threading), with per client:

Usernames and passwords

Adding mail items and checking if they have been deleted and collected

Most POP3 Commands:

USER, PASS

APOP

DELE

LIST, STAT

NOOP (Note: NOOP is implemented such that it will respond even if the client is not logged in – which is against RFC specs)

RETR

RSET

QUIT

UIDL

Unit tests for all functionality

A simple base on which to build your own test servers (an echo server is also provided)

I am now working to add CAPA and encryption (both implicit and STLS). The addition of encryption may mean that I will have to rebuild part of the networking code, so you may see that the next version uses the Async CTP and (hopefully) does less low-level networking\stream\byte array code. Once the POP3 test server is complete and stabilized, I will move onto a test SMTP server, then add abilities to introduce errors and non-compliance into both (while also adding some tests for P3SS in between).

In the mean time, the code is available for one and all to use! And let me know (either on Codeplex or via email) if you have any feedback or suggestions.