SP-2007. One Content Database per Site Collection

In SharePoint 2007 when you create an application you specify the name the database to use.  Most people, therefore, conclude that an application will use one database.  This is not necessarily a very scalable idea.  But then you will often hear that an application can have more than one database.  The more commonly heard statement is that a Site Collection can have its own database.

True, very true.  Each site collection under an application can be made to have its own database.  But how?  When you create a site collection you are never asked which database to use or if a new database needs to be created.

Well our friends at Microsoft have made this in a very confusing process.  But here goes:

• Let's start with creating a new application.  When we create the application we tell it to use the following database: WSS_CONTENT_1
• Now create a site collection (SC1) under this application and a few sites under the site collection.  All the sites will use the WSS_CONTENT_1 database.
• Now go to Central Administration, Applications, Content Databases.  Select WSS_CONTENT_1 and change its status to "Offline".
• Now under Central Administration, Applications, Content Databases create a new content database WSS_CONTENT_2.  Make sure its status is "Ready".
• Now create a new site collection SC2 and create a few sites for SC2.  They will use WSS_CONTENT_2.
• Now create yet another site under SC1.  It will use WSS_CONTENT_1.  This is despite the fact that WSS_CONTENT_1 is "Offline."

So what we have achieved through this roundabout way is a content database for each of our site collections SC1 and SC2.  It would have been a lot better if when creating a site collection in Central Admin one was asked if a new database is to be created or if one wants to use an existing database.

In the Central Administration, Application Management, Content Database module setting a database offline means that from this point on no new site collection will be created in this database.  However this database is still usable and new sub-sites under an existing site collection can be created.  One has to be very careful to take each content database offline once a site collection has been created to prevent any other site collection from being created to use it.  This way you can make sure that each site collection has its own database. 

BTW, the same can be achieved via an stsadm command:

stsadm -o createsiteinnewdb -url http://your.application.com/SiteCollection -owneremail owner@application.com -ownerlogin Application\Owner -sitetemplate sts -title “Title” -databaseserver servername -databasename WSS_Content_Somename

Why would you want a site collection to have its own database?  Well that depends on what you are building and is a subject that is best handled in another post.

Sharepoint Contents

Part 1 - Fundamentals
Content Management / Site Collection Administration


1. Sites, Lists, and Web Part Basics

* Navigation
* Site collection, sites, lists
* List columns and views
* Versioning
* Draft/publishing versioning model
* Email notifications and list emails
* Web parts
* Standard vs. published web part pages
* Content query web part
* Managing personal site and user profile
* Search syntax from end user perspective

2. Security: Authorization

* Authentication vs. authorization
* Site Level Security
o Permissions
o Permission levels
o Active Directory users and groups
o SharePoint groups
* List, folder list item security
* Security Principals
* Limitations

3. Office Integration

* Opening items in Outlook (offline)
o Calendar
o Tasks
o Contacts
o Document libraries
* InfoPath Form Publishing
* Importing lists from excel
* Excel Services and dashboard pages
* KPI (key performance indicator) lists

4. Advanced Document Management

* Site columns
o Creating/updating/propagating
o Scopes
* Content types
o Creating/updating/propagating
o Content types settings
* Information management policies
o Labeling
o Auditing
o Expiration
o Using with content types
* Workflow
o Out of box vs. SharePoint Designer vs. Visual Studio
o Standard workflow templates
o Association
o Initiation and status
o Relation to content types

5. Site collection administration

* Manage content and structure tool
* Recycle bin
* List and site templates
o Creating new
o List and site collection galleries
o Examining Microsoft templates
* Features from site administrator perspective
* Site directory and self-service site collection creation
* What SharePoint does not share between site collections
* Modifying site image and theme

6. SharePoint Designer

* Editing look and feel
o Customized/uncustomized pages
o Master/content pages
o Safe vs. application pages and problems that they cause
o CSS Stylesheets customization
* working with external data sources
o SQL databases
o Lists on other sites
o Data view configuration
* building workflows
o Creating new
o Examining workflows from Microsoft templates

7. Web Content Management

* Publishing infrastructure and publishing features
* Publishing content types
* Page layouts and fields controls
* Page layout association with content type
* Custom content types
* Using SharePoint Designer to modify page layouts
* Publishing process
* Customizing content query web part look and feel
* Customizing master pages

8. Final Project

* Set up of "ABC Company" site collection and navigation
* Secure "ABC Company" site collection
* Set up document management for "ABC Company"
o Document libraries
o Content types
o Document center site publishing



Part 2 - Administration
System Administration / Architecture


10. SharePoint Central Administration

* Topology
o Servers in farm
o Services on server
* Global configuration
* Application security
* Web application management
o What is a web application, application pool in IIS, SharePoint, impersonation vs. process account
o What is the basic database organization in SharePoint databases and why we need multiple content databases for the same web application
o Content database tables
o Create or extend or delete web application
o Remove SharePoint from IIS web site
o Assignment permissions to users for the entire web application
* SharePoint Site Management
o Create and delete site collections
* Intranet/extranet deployment scenarios
* Shared services provider configuration

11. Alternative Access Mappings

* Full URL rendering
* Zones, public URLs, internal URLs
* Authentication considerations
* Load balancing considerations

12. Shared Services: Administering User Profiles, Audiences, and Personal Sites

* User profiles
o Import from Active Directory
o Creating new properties
* Audiences
o Creating audiences
o Using audiences in pages, web parts, and list items

13. Shared Services: Administering and Configuring Search

* Search architecture
* Content sources and schedules
* Crawl rules and security
* Properties and scopes
* Relevance inclusions / keywords / logging
* Customizing search center and web parts

14. Site Definitions and Features Architectural

* What has changed
* New feature architecture
* How to use features as administrators
* Simple feature
* Solutions

15. Single Sign-On Administration

* Creating applications definitions
* Using single sign on credentials in SharePoint Designer

16. Business Data Catalog Administration

* Lob systems, entities, properties, and methods
* Authorization
* Delegation vs. RevertToSelf authentication

17. Backup and Restore

* What to backup
* Backup method descriptions and limitations
* MOSS backup and restore tool through user interface and stsadm command-line
* Individual site collection backup and restore through stsadm command-line
* Third party solutions

18. Upgrading from 2003

* Basics of upgrade processes
* Step by step process of manual content database migration

19. Quick Deployment Guide

* Initial planning: define the team and the scope for the first stage of deployment
* Pilot deployment as an intranet: focus on collaboration, document management and portal features
* Test deployment on production hardware: plan capacity and figure out server farm structure
* Production deployment
* Beyond the initial stages

20. Basic SharePoint Installation

* Examining prerequisites
o Application server role
o Domain controller role
o SQL server
o Email
* Installing SharePoint files and central administration site
o Initial parameters
o Using the wizard
o Browsing through central administration iste
* Configuring services
* Creating shared services provider
* Creating end-user web application and a site collection
* Configuring Shared Services provider
o User profiles
o Search
o Business data catalog
o Excel services


Part 3 - Development Outline
Developement / Architecture


21. SharePoint API and development in ASP.NET Application

* Site collection organization
* List data manipulation
* Security
* Global Administration

22. Impersonation

* ASP.NET impersonation basics
* Using SharePoint impersonation api

23. Master Pages and CAML in the object model

* Examining the default master page for SharePoint
* Creating new Master Pages
* Using SPGrid and other SharePoint controls
* Using SPSiteData

24. Web parts

* Basic web part development in SharePoint 2007
* Web part class details
* Editors
* CreateChildControls
* User controls
* Web part manager
* Deployment
* Security

25. Event Handlers

* Synchronous
* Asynchronous
* Deployment

26. Site Definitions and Features Developmentn

* Visual Studio project structure for feature development
* Fields feature
* Content type features
* List feature
* List feature activation logic
* Custom forms for a content type
* Changing the item template for a custom form
* Solution deployment: building a solution package

27. Custom Search Queries

* Building a custom search query in a web part
* Advantages over using scopes

28. Business Data Catalog Development and Single Sign On

* Business data catalog architecture
* LOB (Line of Business) System objects, entities, identifiers, methods
* Using business data catalog API and writing custom web parts

29. Custom Workflow

* Using Visual Studio 2005 to build workflows
* Building custom workflow with InfoPath forms
* Workflow api

SQL Queries for SharePoint Content Database

SQL Queries for SharePoint Content Database

Hi All,

The Sharepoint Content Database build up on SQL Server and has a good set of Tables.
These can be accessed and the data can be retrived just as any other SQL database.
Here are some useful queries that will help you.

NOTE: Never update any SharePoint database directly. Always use the SharePoint API (Object Model) for any updates.


When you will open the Sharepoint Database you will see a set of tables.Eg the ImmedSubscriptions table Ref the screen shot below.



We will begin with some of the basic tables.

Features Table that holds information about all the activated features for each site collection or site.

Sites Table that holds information about all the site collections for this content database.

Webs Table that holds information about all the specific sites (webs) in each site collection.

UserInfo Table that holds information about all the users for each site collection.

Groups Table that holds information about all the SharePoint groups in each site collection.

Roles Table that holds information about all the SharePoint roles (permission levels) for each site.

AllLists Table that holds information about lists for each site.

GroupMembership Table that holds information about all the SharePoint group members.

AllUserData Table that holds information about all the list items for each list.

AllDocs Table that holds information about all the documents (and all list items) for each document library and list.

RoleAssignment Table that holds information about all the users or SharePoint groups that are assigned to roles.

SchedSubscriptions Table that holds information about all the scheduled subscriptions (alerts) for each user.

ImmedSubscriptions Table that holds information about all the immediate subscriptions (alerts) for each user.



Some of the common queries that can be used against the content database:

-- Query to get all the top level site collections
SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated
FROM dbo.Webs
WHERE (ParentWebId IS NULL)

-- Query to get all the child sites in a site collection
SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated
FROM dbo.Webs
WHERE (NOT (ParentWebId IS NULL))

-- Query to get all the SharePoint groups in a site collection
SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.Groups.ID AS Expr1,
dbo.Groups.Title AS Expr2, dbo.Groups.Description
FROM dbo.Groups INNER JOIN
dbo.Webs ON dbo.Groups.SiteId = dbo.Webs.SiteId

-- Query to get all the users in a site collection
SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.UserInfo.tp_ID,
dbo.UserInfo.tp_DomainGroup, dbo.UserInfo.tp_SiteAdmin, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Email
FROM dbo.UserInfo INNER JOIN
dbo.Webs ON dbo.UserInfo.tp_SiteID = dbo.Webs.SiteId

-- Query to get all the members of the SharePoint Groups
SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.GroupMembership INNER JOIN
dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId INNER JOIN
dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID

-- Query to get all the sites where a specific feature is activated
SELECT dbo.Webs.Id AS WebGuid, dbo.Webs.Title AS WebTitle, dbo.Webs.FullUrl AS WebUrl, dbo.Features.FeatureId,
dbo.Features.TimeActivated
FROM dbo.Features INNER JOIN
dbo.Webs ON dbo.Features.SiteId = dbo.Webs.SiteId AND dbo.Features.WebId = dbo.Webs.Id
WHERE (dbo.Features.FeatureId = '00BFEA71-D1CE-42de-9C63-A44004CE0104')

-- Query to get all the users assigned to roles
SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle,
dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN
dbo.UserInfo ON dbo.RoleAssignment.PrincipalId = dbo.UserInfo.tp_ID

-- Query to get all the SharePoint groups assigned to roles
SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle,
dbo.Groups.Title AS GroupName
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN
dbo.Groups ON dbo.RoleAssignment.SiteId = dbo.Groups.SiteId AND
dbo.RoleAssignment.PrincipalId = dbo.Groups.ID

If you come accross any useful queries do let me know.

About Me

Namaskaramandi....Naa peru Murali Kumar Pitchika (Profile lo choose untaru)...Inka naa gurinchi chepplante chala undi....(meeru vinali kani). Nenu chala silent ani chepte adi abaddam antaru maa friends, nenu intellegent ante nammru maa friends, anduke alantivi cheppatam manesanu....(natho friendship cheste telusukuntarani)...kani okati matram nijam Nenu chala friendly ...(evaru kadanaru)...Enti introduction konchem over ga undi kadaaa.....parledu lendi , manam manam friendse kada adjust ayipodam....OK naa