Deep Dive into Data Management in SharePoint applications

1. Deep Dive into Data Management in SharePoint applications Raj Chaudhuri

2. Quasi-statutory warning • You are going to hear a lot of opinion in this session. My opinion.

3. Agenda • The SharePoint data modelS (note the capital S) • The core SharePoint “Lists” data model : our focus today • Discussion of: • Data definition • Data manipulation • Querying and extraction • The Object Model: the SharePoint “business tier” • Conclusion

4. the Sharepoint data model(s)

5. SharePoint Data Model(S) • Data stored in external data stores, accessed directly using custom components • Data stored in external data stores, accessed using BCS or Excel Services • Data stored in SharePoint “service databases”, accessed using Service Applications • Data stored in Web Part Properties (content DB), accessed using the web part manager • Data stored in SharePoint Lists

6. The SHarePoint “lists” data model

7. Characteristics • Designed for collaborative applications, and as such: • Updates are more important than Inserts • Both are more important than querying • Multiple people are expected to work on the same instance of data at the same time • So, no locks. Instead, auditing and versioning. Caveat: Check in/check out model in document libraries

8. More Characteristics • Designed for user serviceable applications. As such: • The data structures can change at any time • Hence, each data structure is largely self-contained. Relations in the classical sense are possible, but cludgy • So can relations • Hence, rather than consider relationships between entity types, we should consider relationships between entity instances • The UI must automatically adapt to match • So, STOP asking for “customized” UI for SharePoint data entry. You’re missing the POINT of SharePoint.

9. Even More Characteristics • Permissions are an important part of the data model • As are associated actions: reactive ones like “triggers” (Event Receivers), and proactive ones like associated custom actions, quick steps etc.

10. Logical Data Model

11. Conceptual Data Model

12. Columns • Columns are identified by a GUID and an internal name, both of which need to be unique in the column’s namespace. They also have a Display Name, which need not be unique, can be changed by the user, and is usually localized in Language Packs. • Columns can be defined at the list level. They are then available in that list only. Such columns are called List Columns. • Columns can also be defined at the site level. They are then available on that site and sites below it in the site collection. Such columns are called Site Columns.

13. Columns Contd. • Site columns can be added to lists. A particular site column may be added to a list only once. • When a site column is added to a list, the list actually gets a column which is an exact copy of the site column, not the site column itself. This column is called a site column in list. Its properties may be changed in the list. • A site column in list maintains a link to the original site column. Any changes made to the site column can be applied to all site columns in lists that were created from that site column.

14. Content Types • Content Types in SharePoint can, and in fact have to, derive from other content types. A derived content type can inherit all fields of the base content type. • All content types in SharePoint ultimately derive from a base content type called Item. An entire category of content types derive from another type called File. These represent files in library-type lists. File itself derives from Item. • Content Types are identified by a unique ID. This ID not only identifies the content type, but also identifies the base content type.

15. Content Types Contd. • Content Types are defined at the site level. They are then available on that site and sites below it in the site collection. • Content Types can also be defined at the list level. They are then available only in that list. • A site content type can be associated with a list. This creates an exact copy of the site content type in the list, called a Content Type In List. This maintains a link to the original, and any changes made to the original can flow down to all copies.

16. Lists • Lists are created from List Definitions or List Templates. A definition defines what content types a list can have, and what columns. • The columns in a list can come from the content types, or be new ones defined in the list itself, or be site columns in the list. • The number of columns that the list receives from its associated content types is the union of the columns in all those content types.

17. Lists • There are a lot more columns in a list than are apparent. For example, each workflow associated with a list causes a column to be maintained. There is a column that tracks the content type of each item, another which tracks the version, a column called FsObj that tracks if the item is a file or folder, and so on.

18. Physical data model • This should not matter. It is meant to be opaque to all consumers of SharePoint technology. • That means, do NOT directly modify, query, or even look at the structures of, any SharePoint database. • NOT EVEN if some blog of some expert tells you to. • NOT EVEN if I tell you to • Do NOT, okay?

19. That said… • List items are stored in real SQL Server tables, as real rows. • A single SharePoint list item can be wrapped over a number of SQL Server rows. • The number is 6 by default. It can be adjusted, but not through any UI; only programmatically. Do not do this. Unless you know what you are doing. And you don’t. I don’t either. Not yet.

20. We really should not be discussing this… • The total number of bytes that a single SharePoint list item can occupy is hardcoded to 8000. • This total is to be reached by adding the number of bytes of storage required by each column. That, in turn, depends on the column’s field type. This does not include files and attachments. • 256 bytes out of 8000 are reserved for system-defined columns. • For more details, read: • http://technet.microsoft.com/hi-in/library/cc262787(en-us).aspx (SharePoint Server 2010 capacity management: Software boundaries and limits)

21. One more time • Worry about the physical only as a last resort • And DO NOT look inside the databases.

22. Discuss: Data definition

23. Discuss: Data Manipulation

24. The object model: Sharepoint’s “Business tier”

25. The object model • ALL access to SharePoint data (at least for manipulation) must happen via the object model. • This ensures the maintaining of context, security and integrity. • This also causes event receivers to fire, and workflows to execute as appropriate.

26. Object model hosts • The object model may be hosted by • A SharePoint web application • A SharePoint service application • A SharePoint Windows service, especially the Timer service • A custom application (Careful here. An application that uses the SharePoint web services is indirectly using the OM hosted in a SharePoint web application. Ditto an app that uses the Client Object Model).

27. Effect • The object model, and hence the host, directly talks to the appropriate databases. • Event receivers are executed in the host. Workflows execute in the host.

28. Discuss Querying and Extraction

29. In Conclusion • The SharePoint “Lists” data model is a very powerful and flexible model, IF… • Your data is collaborative in nature, and • User-servicability is important to you • Do NOT try to use lists instead of SQL database tables. They are meant for different kinds of usage. For example: • Do not use lists if your data is insert-heavy, updated rarely, and queried frequently. • Especially the last. If you need complicated reporting, you should not be using lists. • At least, be prepared to move the data from lists into some other data store before you do heavy-duty reporting. Just like we do for data warehouses.

30. In Conclusion • And remember, lists are a package deal. You get centralized backup, user servicability, a simplified permission system, workflows, event receivers, and an adaptive user interface. • You cannot pick and choose. You get the whole deal if you decide on lists. • And so, just as an example, please stop trying to precisely customize list forms. You are defeating the entire POINT…of SharePoint

31. Raj Chaudhuri raj@rajware.net raj.chaudhuri@communitiesrus.in +919930217314

32. Thank You