Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

SP newbie here. I need to build a SharePoint 2010 application to track customer support cases. This will be the first time we've used SP, and I can set it up how I please.

The built-in template for issue tracking lacks flexibility we need, so I started setting up Lists as I would SQL tables for other apps (Companies, Contacts, Cases, etc.), allowing users to manage cases by adding/editing those Lists. However, I would also like to access the data in those Lists via SQL queries for an external-facing case manager on our website.

I've seen that you can use an external db for a List, and you show it in SP as an external data source. I've also read that the SP Content Database does not work well as a relational db, which would appear to make external queries difficult.

For us, does it make more sense to have a separate db for those Lists, connected to SP via an external data source, or should I just access the Content Database via API calls? I'm also concerned about maintenance with the external db method (adding new columns, etc.)

share|improve this question

2 Answers 2

Generally speaking you should never query the Content Database directly or perform any SQL operations on the SharePoint databases. Microsoft hates you for even thinking about it and will put a hex on your family. Instead, the recommended path is to use web service calls or the Client Object Model to access data in SharePoint from external systems.

Also, for the future, it is best to try not to think of SharePoint as an RDBMS, as it does not function in that capacity at all. Many people start from that frame-of-mind and have to step back out of a solution when they realize it won't work.

That said, storing it instead in a non-SharePoint database and then loading that database into SharePoint via Business Data Connectivity Services would be the approach I would recommend. There are a couple of caveats involved if you try to use more esoteric column types, or if you plan to involve InfoPath in any way, but it's a good approach to work with.

Install SharePoint Designer if you haven't already and play around with it. It's a little arcane at first but it sounds like it should be exactly what you want. Here's a good resource to get you started:

http://chakkaradeep.com/index.php/sharepoint-2010-business-connectivity-services-walkthrough/

share|improve this answer
    
Always good to avoid MS hexes! :-) I have installed SP Designer and will definitely look into Business Data Connectivity. Thanks! –  SteveEx Apr 4 '13 at 19:43

For whatever you do , consider these points as the principle guidelines for having a nightmare-free SharePoint in your enterprise.

  1. Never query the content database directly. You will end up violating your agreement and license with Microsoft and run out of MSFT support if you do that.

  2. To decide a solution architecture for any business requirement involving applications for Support and Operations Management consider that while SharePoint supports bringing external database content through its Business Connectivity Service Application and external content type architecture , it has its own limitation.

  3. Use Event Receivers if you want the Cases to be simultaneously saved in a different custom database for harnessing SQL Server features like Reports , Scheduling for SSIS jobs and other exports for different line of business applications. However the downside is - this will have data duplicity like having them both in SharePoint content database and your custom database.

Said so here is something I would do if the external site resides somewhere distant from my sharepoint site to access the data on these lists.

SharePoint provides access to lists data and perform CRUD operations through its web service architecture for list. Essentially , you can access the data via web service requests to the individual lists from a connected web service client.

More reading on this here

share|improve this answer
    
THanks @Arko-D! Can you give me any examples of the limitations of using Business Connectivity Service Application in this context? –  SteveEx Apr 4 '13 at 19:55
    
This link should help you:- sharepointanalysthq.com/2010/07/bcs-external-list-limitations –  Arko D Apr 4 '13 at 20:26
    
Yeah, those are some pretty big limitations! Especially workflows and versioning, which I think will be important. Thanks again, @Arko-D. –  SteveEx Apr 4 '13 at 20:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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