Monday, 20 September 2010

Data access options - SharePoint Lists or Database Tables

Problem:  I have built a composite application that uses SP 2010 lists for storing the application data.  There are 2 related lists within the application that are high transactional lists like a shopping cart (order with order items).  These 2 lists have had many fields added to them over the duration of the project and are no longer light lists. 
Hypothesis: SharePoint 2010 supports up to 50 million lists and I need to insert, edit and read often the information in these 2 related lists.  The lists are getting rather complex and transactional support is not available vie LINQ to SharePoint.  I need a full audit history which is pretty useful in SharePoint.  The volume and complexity of this transactional data is to heavy for SP especially as the lists grow.  I have to use SQL Server to store the data directly and link the appropriate SP lists using BCS and external content types.  My preferred methods of accessing data are the Entity Framework v4 or Linq to SQL.  As my data is pretty small, I don't need a multi tier system and we are using LINQ to SharePoint. 
Resolution: Create the SP lists in SQL, use LINQ to SQL to add and work with the SQL data.  I use tomb tables in SQL Server to keep a full audit history.  There are now 2 areas of storage: SP lists and directly to SQL tables.  Not ideal but worth that change.

Pros of using SharePoint List vs Database tables
SharePoint ListsDatabase Tables
SharePoint Security and PermissionsTransactional
WorkflowPerformance & higher transaction volumes
VisioningDistinct queries and aggregations
Office intregrationGreater Flexibility
Native / Easy setupEasier for existing .NET developers
SP generates UI
Update 18 Oct 2010 - Storing data for SharePoint 2010 comparison


Post a Comment