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 Lists | Database Tables |
SharePoint Security and Permissions | Transactional |
Workflow | Performance & higher transaction volumes |
Visioning | Distinct queries and aggregations |
Office intregration | Greater Flexibility |
Native / Easy setup | Easier for existing .NET developers |
SP generates UI |
No comments:
Post a Comment