I need some advice on whether my hope of keeping my application stable is too big an ask for SQL Server 2005. I will provide some background in the hope it will help you give me useful advice.
I have a single PC (Pentium 3.2 with 1GB RAM) running Windows XP SP2. The PC is and will remain dedicated to running a telephony product and should never be used for anything but run that application. There are currently no links to any other computers, however eventually there will be a need for an external comm to read and possibly update the values in a couple of tables. An application has been developed using a 3rd party product that doesn't have a decent user interface nor a database but will interface with most commercial database products. I have developed the database using SQL Server 2005 Express. It comprises 7 tables the biggest currently having 99 records but that is likely to grow to a max of about 400 records. The telephony application runs 9 threads each of which interrogates the database at regular intervals (every half second or so) to see if any values have changed that require it to initiate a dial-out. If an inbound call is received these threads may interrogate the table to retrieve some information requested by the dial-in user. The GUI is handled by a MS Access front end that operates on the same tables - linked tables. Every 5 seconds an On Timer event in the MS Access application refreshes the dispay to reflect any changes that have occurred in the underlying tables. The user is also able to update various values in this table using custom forms.
Two days ago I ran the application at idle without fault for about 4 hours before the telephony application crashed with a "[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation" error. I tried it again several times and had the same fault occur randomly over time periods varying between a few minuts and a few hours. After some hours of investigation I concluded it was Norton's firewall that was causing the problem. I disabled the firewall and put the system under intense load without fault for about 14 hours. After that time I took the load off the machine, opened one of the tables in Access (not by the use of a form) and changed one of the values in the table. At basically the same time the telephony system crashed again with the above error. I don't know if this caused the problem or if the timing was just coiincidental. Nevertheless I have no idea what might cause this problem or how to prevent it from happening. I am going to put a trap in my telephony product to try to re-establish the connection if it detects a lost connection, but I am not sure that will be the right answer.
I need to have this application run reliably 24/7 for, potentially, years at a time without human intervention. I chose SQL Server 2005 because it claims to be very robust. Am I asking too much of the product? Can I reasonably expect SQL Server 2005 to just keep working? And what could be the possible cause of this random error.