10

I am working on a project to create a new bespoke intranet website for around 50 users. The data volumes are relatively small, but in order to model the data I need to store (about the 100 or so projects my company is currently running) I need to have around 20 different tables in my database schema.

As we were planning on developing this application in MOSS 2007 (our current intranet solution) I am considering using a SharePoint list for each of these "tables" and joining the data together using CAML or LINQ to SharePoint when I need to retrieve it.

In order to give an idea of data volumes, we estimate that we will have no more than 10,000 list items in total across all 20 lists after a year of growth.

My team is split between those who think this is a really good example of embracing SharePoint properly and those who are convinced it will not perform well enough and I will need to use a SQL database instead.

My questions are:

  • Is this the right thing to do?
  • Do Microsoft offer any guidance on when SharePoint is likely to run out of steam when doing this sort of bespoke development (I can't find any)?
  • Does anyone have any experience doing something similar and if so what was performance like?
Improve this question

5 Answers 5

Reset to default
1

I would suggest to use SQL Server as the data store and EF as DAL to access the data. This then can be wrapped into a WCF layer as WCF application pool then can run with the right version of .NET for EF. WCF-EF would be best solution for accessing SQL Server.

The important think in software design is using the needed techonologies, tools and techniques to solve a problem. Here, as you are designing a brand new system with no dependencies to legacy or back office LOB systems, you can have your application designed in layers to integrate and communicate efficiently without being constrained to use SharePoint specific tecghnologies like BDC or BCS. Of course if there is a case to make data visible in SharePoint context like lists then these technologies can be considered, it might be possible to engineer a system where the solution can be architectured and layered in such a way that some data can be in SharePoint and some can be in SQL Server.

I agree with the suggestion that the data storage solution can be hybrid solution where some data can be stored as SharePoint Lists while some being in a relational database. SQL Server will also provide transactions when data is committed to multiple tables when there is dependency between entries, this way any failure to a transactional operation will cause all the transaction to roll back and changes will only be commited once all the oprations succeed.

Desicion to use the hyprid approach or go for a full SharePoint or full SQL Server based implementation would depend on whether the solution needs any of the SharePoint features and to what extend the SP features are important against the benefits of a relational database like transactions. The benefits of both must be established and weighed.

I would personly go for WCF-EF approach if there is no need to view-edit data as SharePoint lists and if some data needs to be edited, viewed, then I would consider the transactional and performance considerations against SharePoint features and if SQL Server is needed more than SharePoint features than I would probably develop custom web parts as forms to view and edit data which then will provide the benefits of both worlds.

Improve this answer
2

I agree with John, but since you need to use 2007 I would not use BDC. It was just too difficult to use for the lack of features. With 20 tables I would start considering the integration of several different types of technology. For the complex relational items I would plug them into a proper SQL database and surface them through the use of webparts/application pages or even Silverlight. For the flat sort of data or data that needs workflows you might consider adding them to lists or libraries. I'm not suggesting duplicating the data - but there is no reason that you cannot take data through an approval or some other wf driven process and then store it in the SQL database via a web service.

SharePoint gives you a ton of flexibility in this respect and you can use UI technologies like Silverlight and jQuery, too. If you could use SharePoint 2010 (even Foundation) you could use the BCS and have a more flexible manner to surface the data in SharePoint while it is still stored inside the SQL Database. Also, the relationality of SharePoint lists was improved substantially in 2010.

Improve this answer
1
  • I like the 2010 Foundation suggestion. It's free, kind of and BCS is very powerful.
    – Tom Resing
    Commented Jun 26, 2012 at 19:01
1

SharePoint lists aren't usually the best route to go for relational data like that. I would suggestion one of two routes:

1) Build the database in SQL and expose the data through custom SharePoint pages/web parts and/or through the Business Data Catalog

-or-

2) Build the database as an Access Web Database. This gives you the basic database and forms capabilities of Access but allows you to host it in SharePoint and have a web interface.

Improve this answer
0

The factors in your plan make this a difficult decision. Even within SharePoint, there are many smaller technologies which can be chosen rightly and incorrectly. If you had SP 2010 at your disposal for development, I'd say go that route. Otherwise, bespoke. Maybe it's a good time to upgrade your intranet farm, or atleast ramp up a separate SP2010 server. My feeling is, with the proper decisions, you'd be able to configure a new SP2010 server (separate farm) and develop your app with BSC and forms well before you develop your bespoke app. (I've used this strategy before. Business leaders wanted PowerPivot Dashboards, but didn't want to upgrade the SP2007 farm. I can argue it was practical.)

Remember that SharePoint faciliates development to a point, and then hinders, imo.

Improve this answer
0

Whatever solution you choose (they all have pros and cons), from my working experiences, using the correct pattern to uncouple the data storage layer and business layer is always a good idea.

If you (or your team) changes your mind in the middle of the project, an uncoupled data storage layer can be replaced with another one easily.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.