SQL databases used in Sharepoint

There are quite a number of databases generated during a Sharepoint install and depending on the "Farm" configuration there might be more or less. A lot of SQL DBA's get quite annoyed when all these databases suddenly appear in their system and they have no idea what each database does or why it is there.

I have therefore decided to explain what databases get created during a MOSS install and what the purpose is behind each. A WSS install generates less databases and therefore I decided to focus on a MOSS install as this generates the most.

Below is a list of the databases generated by Sharepoint, the names may differ per deployment, but the purpose remains the same:

• Config Database for the Farm - Sharepoint_Config - stores configuration information about the servers deployed in the farm , their individual configurations settings and some security information. Without this database there is no Sharepoint.
• Content database for the Admin Console - Sharepoint_AdminContent_GUID - sharepoint uses its own technology to render the web based admin console for Sharepoint. Therefore it needs it's own content database to stored the configuration settings for the web parts used. The actual data configured using this console is stored in the config database for the farm. The name for this database is system generated and cannot be controlled during the installation process and therefore it ends with a GUID.
• Config database for the SSP (Shared Service Providers) - BPS_SharedServices_DB - during the configuration process a SPP is defined to configure all the Shared Services used by Sharepoint. All the Configuration settings for these services are stored in this database. The name of the database can be controlled during the creation process and should be descriptive of the purpose.
• Content database for the SSP Console - BPS_SSP_Content - just like the admin console the SSP also needs a web site to allow you to configure the shared services and these also use web parts and lists. Therefore the SSP also needs its own content database to store these settings.
• SSP Search database - BPS_SharedServices_Search_DB - this database is used by the Enterprise search service to store metadata about the information crawled including security information. This is typically used for information stored external from Sharepoint.
• WSS search database - WSS_Search_sps-dc1 - this database is used by the WSS core components to store metadata about content stored inside the Sharepoint web application content databases. This is created during the installation process.
• Web Application Content - Office_Content - this is the content database for the first user based web site in Sharepoint. Before the users can actually use Sharepoint a "Web Application", Site Collection and Site must be built. This database stores all the information generated within this web application.
• Additional content databases - Office_Content_2 - new content databases can be created to host additional "Site Collections" and "Web Applications" and there could be hundreds of these.

SharePoint 2010: Lists & Events Improvements

• Improved support for Events
• WebAdding & WebProvisioning
• ListAdding & ListAdded
• ListDeleting & ListDeleted
• Post Synchronous events
• SPEventReceiverDefinition.Synchronization (post processing after the commit of the action and before presentation with the result of the post)
• Cancel events & custom error pages
• MSF 4.0 introduces capability to cancel error and redirect user to custom error page
• Synchronous cancel with error URL
• Supported in SharePoint Browser UI only (no client support)
• WSS 3.0 events ran in context of user who triggered event, certain workflow actions were at system level
• WSF 4.0 adds event impersonation (the originating user & user token on SPEventPropertiesBase
• New Registration Capabilities
• New events registration at Site Collection Level
• SPSite & SPWeb event receiver registration via Features
• List Relationships and Joins
• Yes we now can join lists (not like a sql join)
• Set up a relationship first through a lookup field
• Projected fields
• Extra fields pulled from Parent List into view of child list
• no data is saved, it is just in view (read-only)
• show additional secondary data for presentation from parent
• Referential integrity (cascade operations)
• Select operations from both lists using LINQ (SPLinq)
• Supports LINQ, CAML, SPD 2010 (note: LINQ generates CAML under the covers)
• SPQuery.Join, SPQuery.ProjectedFields
• New Security considerations & issues
• Large List Support
• MSF 4.0 can now support 50 Million items (previously recommended 2000 for optimal performance)
• Configuration options per web application
• Admins can request privileged operations
• Two threshold (configurable using PowerShell)
• Warning threshold (2000 items)
• Throttling threshold (5000 items, error message when 5000 limit is reached)
• override query safety checks
• Normal user – 5000 items
• Super User – 20,000 items
• Configure time window for expensive queries (set time for expensive queries)
• Wide List Throttling
• SPListItem limited to 8K of data
• maximum number of joins – 6 , > 6 error is thrown
• Mark columns unique
• Unique columns must be indexed
• Existing data is validated for uniqueness
• uniqueness determined by SQL-collation (case sensitivity)
• List Item and Field Validation
• New column-level & row-level constraints on Lists & List Items

Query whole site collection using SPSiteDataQuery

Using the SPQuery class you can query one SharePoint list for items.

To set up a query across a whole site collection you can use the SPSiteDataQuery object.

The following method fetches all .doc files from all doclibs of the site collection and prints out a list of urls to those items.

public void TestSiteDataQuery() {   using (SPSite site = new SPSite("http://localhost"))   {     using (SPWeb web = site.OpenWeb("/"))     {       SPSiteDataQuery query = new SPSiteDataQuery();         // Search in doclibs only       query.Lists = "";         // Only .doc files       query.Query =       @"                                     doc                    ";         // Select only needed columns: file reference       query.ViewFields = "";         // Search in all webs of the site collection       query.Webs = "";         // Perform the query       DataTable table = web.GetSiteData(query);         // Generate an absolute url for each document       foreach (DataRow row in table.Rows)       {         string relativeUrl =            row["FileRef"].ToString().Substring(             row["FileRef"].ToString().IndexOf("#") + 1);         string fullUrl = site.MakeFullUrl(relativeUrl);           // Write urls to console         Console.WriteLine(fullUrl);       }     }   } }

SPQuery Few important things

1. Always specify the RowLimit to get limited results.

1. oQuery.RowLimit = 100;

2. To Get the Search Items from the sub folders of the list using SPQuery you can do following. Check here

1. SPQuery.ViewAttributes = "Scope='Recursive'";

2. or

3. SPQuery.ViewAttributes = "Scope='RecursiveAll'";

3. Be sure to create a new SPQuery instance each time you use it. You cannot create one SPQuery instance and then reuse it multiple times in a loop where you alter the SPQuery's Query value.

1. for (int i = 0; i < 10 ; i++)

2.

3. SPQuery oQuery = new SPQuery();

4. oQuery.Query = ""+i +"Value>Eq>Where>";

5. SPListItemCollection collListItems = list.GetItems(oQuery);

4. SPQuery query object doesn't need to include tags

1. oQuery.Query = " name="Status"> type="Text">Completed";

5. If you want to do CAML query which also consider the time in datetime fields then try this

1. oQuery.Query = " Name='Created'/> Type='DateTime' IncludeTimeValue='TRUE'> />Value>

6. SPQuery can't be used to search across site collection.One can use SPSiteDataQuery class to search across multiple Lists.

To query the lookup based value items

1. oQuery.Query = " name="Employee" lookupid="TRUE"> type="User">1";

7. To restrict the items to approved, use moderationtype attribute. more details here

1. SPQuery.ViewAttributes = "Scope='Recursive' ModerationType='HideUnapproved'";

8. If you want your query to return empty columns, you have to add Nullable='TRUE' to the viewfields.

1. query.ViewFields="";

To get the item related within past few days , you can use OffsetDays attribute along with Today.

1. query.Query = "";

Article:
SPQuery Best Practice