How does SharePoint handle unreliable database connections?

Question

Background

We're experiencing network issues with connections to our SQL box unexpectedly being closed. These are occurring a few times a day. Errors are being logged by the SharePoint WFEs but users haven't reported any problems yet.

This is not a fault in SharePoint but rather the machine hardware or VMWare. It's being looked into by the infrastructure team who might find a solution (one day)...

---

Question

Ignoring the specifics of this particular problem:

How does SharePoint in general handle unreliable database connectivity?

Does it retry transactions? At what point does it give up? Can it cause data consistency problems?

---

Update 8 Dec: Clarified question and added bounty.

Answer 1

It's impossible to answer in general terms. It all depends on the nature of the SQL connectivity issues, and also the specifics of the farm topology. Also the usage profile of SP activity is very important here.

You will have noticed "cannot connect to SQL" flooding you SharePoint server event logs, but it could well be the case that end users are not affected (or at least they don't think they are). It all depends on the task they are trying to accomplish and the features being used. If they are browsing pages, it will be obviously less of an issue.

In general SharePoint data is not transactional, operations will not be retried (at least buy the app server) of course there are some levels of protection from pure SQL perspective that means you shouldn't get in an inconsistent state (at least from end user operations). However list items missing is entirely possible.

You say "a few times a day" that is not a lot and if you were to corralate the IIS log files for those times with the event logs on the SP boxes, you would likely see little user activity.

s.

Answer 2

In my experience it depends on the operation. SharePoint seems to be generally pretty good in that a single action will get applied or not applied but have seen problems with what I would call group/associated operations. For example you can get orphan items in your db as a delete site might work, but the associated delete list, items etc might drop out. I'd recommend running the stadm databaserepair command just to check how your content db is tracking in terms of orphaned items.

Answer 3

Alex: do you have your SharePoint servers in a separate VLAN? We have found that is a good practice. Also, have you tried using Wireshark to see what is going on at the network layer?

In my experience, drops in connectivity in Sharepoint manifest themselves as a variety of errors, not just connectivity errors.

Answer 4

A lot of SharePoint calls work through COM. You could imagine that they use a lot of the services provided by COM to manage these types of things. SQL Profiler is a great tool for determining if a particular server/user/database may be causing issues with the commands it sends.

You would need to be a bit more specific on the activity that most commonly causes the "unreliability" so that I could reverse engineer the COM and database calls for that action (as Laney mentions above)