DTAP for SharePoint

Overview: Development, Test, Acceptance and Production (DTAP).

To implement SharePoint solutions it is a good idea to have DTAP environments. DTAP can be as simple as 4 environments but I would recommend 5 or more separate environments for SharePoint.

Development
The base development machines can be standalone VM's with self contained SQL databases, AD, FAST, and SharePoint.  Additional software would include: InfoPath, SharePoint designer, U2U, Fiddler, firefox, IE8 and more.  You should have TFS or a source control repository for all developers to check compiling code into.

Continuous Integration (CI)

This box can range from very simple to a complex complete tear down rebuild on a daily basis with unit test.  At a minimum all code should be deployed daily.  All changes to CI and subsequent environments should be deployed via wsp's and PowerShell scripts.  Sometimes it is time efficient to write administration manual steps, this should be avoided where possible and if it is absolutely required, the documentation must be explicit and testable.

Testing
This environment is a build release, it is stable so testers can perform testing against a specific build.  Bugs should be documented (preferable in tied to source control changes).

Acceptance
End users should test the system pre go-live in this environment.  This phase can also be split into 2 environments namely: Acceptance and pre-production environments.  Used for user acceptance testing.

Production
This live system is only changed once the changes have gone thru change request management and have been deployed and tested on all the environments.  Pre-production should be as close a copy of the production environment as possible.  Production and pre-production must be kept as close as possible throughout the life time of the SharePoint farm.

Summary:

• Never do changes directly on the production environment. 
• Build a formalised change control process for SharePoint. 
• Minimum of 3 environments for small farms is my base guideline. 
• Environment changes should be done using wsp's and PowerShell not the UI where possible
  .

SharePoint is not a Database!

As users of SharePoint start using lists they start taking advantage of a very intuitive and highly configurable information management tool. Users who have been using Excel or Access in the past start seeing SharePoint as a kind of rapid application development platform for building structured data applications. However, this is where SharePoint’s list architecture breaks down because simply:

SharePoint is not a database!

Part of the problem is that business users designing lists are not familiar with basic database concepts - they just want to store their information. Part of the problem is false advertising by consultants and/or Microsoft that SharePoint is a miracle platform that can do anything - it does some things well but replacing your SQL server or even Access database isn't one of them. The other problem is there is no really good way for surfacing bi-directionally data from a database into SharePoint – the business data catalogue works only in read only mode and does not allow for updates back to the underlying data store.

Here are a list of some features that a database has that SharePoint does not support - the moment you need these features you will need a real database.

• Primary And foreign keys: one of the most basic concepts in a database is the enforcement of a unique identifier (either system generated or user supplied) that can identify a record and link across table in parent child relationships.
• Transactions: if two changes are required to go together and the second change fails you want the first change to be undone (rolled back).
• SQL language: complex queries can me written in code using a standard language. Queries can be saved and repurposed.
• Stored procedures: database programmers can write complex routines that can be called as reusable code blocks.
• Indexing and query optimization: used to improve performance, indexing allows the database designer to pre-index specific fields that are frequently used in queries to improve performance. In addition, most databases have optimization engines that based on what you are trying to fetch will optimize how the data is retrieved.
• Large data and binary fields: most modern databases allow you to store large binary files such as video files, large volumes of text, images, etc. in the database. SharePoint can be made to store large files as documents but you only get one per record and a generic binary object.
• Access outside of SharePoint: lists are accessible outside of SharePoint but only through XML or programming interfaces. Lists are not great data stores to be used for line of business applications written independently of SharePoint because the integration is relatively poor in comparison to running a SQL query on a database.
• Cascading deletes: if a parent record is deleted, its children should also be deleted. In most databases, this can be configured to happen automatically or else reject the delete of the parent until the children are deleted first. This eliminates the risk of orphan child records.

These features are considered basic in databases – they have been around for decades and even the most primitive databases such as Access or FileMaker Pro have most of these features.

Lists are great for flat, changing content especially when attached to unstructured documents. At least in the current version, lists are not substitutes for databases and shouldn’t be considered so. If you need a database, then you are better off building a proper database and using SharePoint as a front-end for surfacing that data through reports, dashboards, etc. or as a host for data entry components such as InfoPath forms or custom build web parts.

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