A few simple notes on Sharepoint / SQL Server Storage

3. How to manipulate / differentiate storage of small content and large content? :

4. Some quick notes on specific Sharepoint objects:

4.1 Short description Sharepoint Databases:

See section 1.

4.2 Relation of Web application, to Site collection, and content database:

Web Application (WebApp) > Site Collection = Top Level site + optional subsites

A Web Application hosts Site Collection(s). Sites in a site collection, share administrative settings

When you create a WebApp, you specify:
- Name of the IIS website (toplevel site)
- port, path of the virtual direcory, the url, the security settings etc..
- Database Server and database name.

From the Adminatrative webpage, the Admin can create the above from an easy to use Admin console.
Note that the relation of the choice of the content database, relates to the WebApp/Site collection.

Per default, a Content database is created for each Web Application.

Also, directly after creation of the WebApp, it is possible to attach the WebApp to another database
of your choice (the former will be deleted, and the new one populated with standard sharepoint objects).
You can do that using powershell, or simply by using the webbased Administration Console.

4.3 Libraries and Lists:

On a site, you can show documents in an organized way, using libraries.
For example, you can create a library of common documents for a project, and project team members
can use their client tools to find documents, edit them, and checkin/checkout those files.
Once created, a library may somewhat show "the look and feel" of a fileshare, but it is not.
The files are either stored in the SQL Server content database, or, if using EBS/RBS, on an external storage system.

Each library displays a list of files and descriptions about the files, which helps users to use the files,
and to work together. So, the sharepoint Admin controls how documents are viewed, tracked, managed, and created.
Also, you can use workflows to enhance collaboration on documents in libraries.

4.4 Sharepoint services:

This section just serves to "get an idea" about the different Sharepoint services.

The term "service" in a Sharepoint environment, often refers to the specific apllications that support
al sorts of functionality in this environment.
Besides those services, in a Windows environment we can expect to find some true background "services"
as well. These then are the services you can see, and control, using various Windows admin tools like
using the following commands:

C:\TEMP> sc query > listing.txt

Or by using the applet:

C:\TEMP> services.msc
The first command, just prints a listing of all installed services to a text file (listing.txt).
In a Sharepoint 7 environment, we should expect at least the following services:

SERVICE_NAME: OSearch
DISPLAY_NAME: Office SharePoint Server Search

SERVICE_NAME: SPAdmin
DISPLAY_NAME: Windows SharePoint Services Administration

SERVICE_NAME: SPSearch
DISPLAY_NAME: Windows SharePoint Services Search

SERVICE_NAME: SPTimerV3
DISPLAY_NAME: Windows SharePoint Services Timer

SERVICE_NAME: SPTrace
DISPLAY_NAME: Windows SharePoint Services Tracing

Depending on the size of the installation (what features are active), this list might be somewhat longer
as to include also "loadbalancer services" and "single-signon services" (and possibly others as well).

If you would take a quick look at figure 1 again, your configuration might consist of 3 tiers:
1. Webservers with IIS and al sorts of Sharepoint related information under "wwwroot".
2. Sharepoint as the middle tier.
3. SQL Server as the backend database engine.

However, often (1) and (2) are often combined on the same machine, while multiple of those machines
are collected in a "farm".

Although you can control, and modify operations on the services by using Windows tools (like services.msc),
not all operations are allowed using the standard tools.
For example:

-Starting and stopping a service is allowed using Windows tools (like using "net start" or "net stop").
-For adjusting account information for the services, you should use Sharepoint own tools like "stsadm" (Sharepoint7)
or powershell (Sharepoint 2010), or other Sharepoint "aware" means to change such critical data.

The get a further idea of the "stack" of services on tiers (1) and (2),
take a look at the following example stop script (cmd) where we assume tiers (1)&(2) are on one machine:

net stop w3svc
net stop iisadmin
net stop ssosrv
net stop DCLauncher
net stop DCLoadBalancer
net stop OSearch
net stop SPAdmin
net stop SPSearch
net stop SPTimerV3
net stop SPTrace
net stop SPWriter
iisreset /stop

5. Metadata, pointers, and BLOBs:

Let's first see how SQL Server stores blobs inside the database. So, here we want to explore the default
situation where blobs (the documents) are fully stored inside the database.

After that, we will try to explore how outline storage (on filesystems/SAN) is implemented.

5.1 Blob metadata, with inline storage:

5.1.1 Metadata on documents (or blobs)

In SQL 2K5/2K8, we can try a couple of standard queries, in order to find "alien" objects,
in this case, originating from Sharepoint.
Using the following query (in a Content database), we get an impression of objects (like tables, views, sp's)
which are not installed at a default installation of SQL Server. So, they are installed afterwards,
like the adding of database objects that results from a creation of a WebApp in Sharepoint.

select substring(name,1,20) as "Name", substring(type_desc,1,20) as "Type", create_date
from sys.all_objects where is_ms_shipped=0 and type_desc='VIEW' or type_desc='USER_TABLE'
and name not like '%CI_AS' order by name -- in my case I want to exclude some collation tables

This produces (in my case) a list like:

Name	Type
AllDocs	USER_TABLE
AllDocStreams	USER_TABLE
AllDocVersions	USER_TABLE
AllLinks	USER_TABLE
AllLists	USER_TABLE
AllUserData	USER_TABLE
AllLists	USER_TABLE
..	..
Docs	VIEW
DocStreams	VIEW
DocVersions	VIEW
..	..
SiteQuota	USER_TABLE
Sites	USER_TABLE
..	..
Workflow	USER_TABLE

The above listing, is just a small sample (partial listing)..

With not too much effort, we can browse through the datamodel of these tables and views.
Then, after some investigation, we can clearly see that the "Alldocs" and "AlldocStreams" tables,
gives us all we need to know about "id's" (of blobs), the "content" of the blob, the "size", the associated
"site_id" which uses this blob, and much more.
Joining these tables with the AllLists view, will reveal the list or library (and other information) too.

You can try for example the following three queries to obtain lots of information about the document storage
within a content database:

USE [YourContentDatabase]
GO

-- Query 1:

SELECT AllLists.tp_Title AS 'List Name',
AllDocs.LeafName AS 'File Name',
AllDocs.DirName AS 'URL',
AllDocStreams.Content AS 'Document Content (Binary)',
AllDocStreams.Size AS 'Size'
FROM AllDocs
JOIN AllDocStreams
ON
AllDocs.Id=AllDocStreams.Id
JOIN AllLists
ON
AllLists.tp_id = AllDocs.ListId
ORDER BY AllDocStreams.Size DESC

-- Query 2:

SELECT AllDocStreams.Id, AllDocStreams.[Content], AllDocStreams.Size, AllDocs.Version, AllDocs.TimeLastModified,
AllDocs.CheckoutUserId, AllDocs.CheckoutDate, AllDocs.IsCurrentVersion, AllDocs.DirName,
AllDocs.LeafName, AllDocs.[Level]
FROM AllDocs INNER JOIN
AllDocStreams ON AllDocStreams.Id = AllDocs.Id AND AllDocs.[Level] = AllDocStreams.[Level]
WHERE AllDocs.TimeLastModified > getdate() - 30 -- use here as getdate()- number_of_days_ago
ORDER by AllDocs.TimeLastModified

-- Query 3: Suppose you want information on .doc, .xls, and .pdf files:

SELECT [AllDocs].[DirName], [AllDocs].[LeafName], [AllDocStreams].[Content]
from [AllDocs],[AllDocStreams] where (LeafName like ‘%.doc’ or LeafName like ‘%.xls’ or LeafName like ‘%.pdf’)
and [AllDocStreams].[Content] is not NULL and [AllDocs].[Id] = [AllDocStreams].[Id]“

You can query on specific Webs and Sites as well, where you are not focusing on document information,
but you for example just want to obtain lists of Webapps and sites. For example:

SELECT Id, OwnerId, TimeCreated, RootWebId FROM Sites

- The "AllDocs" Table regsiters lot's of metadata about documents, like document id, site id, Webid,
TimeLastWritten, Version, and much more.
- The "AllDocStreams" Table registers all document id's, associated site id's, blob contents,
document sizes in bytes, Parent id's (The identifier of the document's parent container).
- The "AllUserData" Table registers data for any item associated with any list- and document librarylists.
- The "AllLists" Table contains information of SharePoint lists such as list’s name,
list’s description and the list’s item count, and more.


5.2 Blob inline storage (No EBS/RBS):

It's well known that SQL Server is optimized for ascii oriented storage (typical administrtive data).
Don't forget that BLOBs and relational database data are very different entities.
Microsoft itself refers to this kind of content, as unstructured data, as opposed to the more structured,
relational data normally stored in a database.

However, various datatypes are available for storage of large text bloks, XML, and "binary" data.

binary datatypes:

- image: up to 2GB, e.g.: to store pdf, word files, images, or other binary data
- varbinary(max): up to 2G, e.g.: to store pdf, word files, images, or other binary data
The storage size is the actual length of the data entered + 2 bytes.
- binary: Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000.

Or we can use FILESTREAM Storage. FILESTREAM is a storage attribute of the existing VARBINARY(MAX) data type.
FILESTREAM alters how the BLOB data is stored—in the file system rather than in the SQL Server data files.
However, the Filegroup supporting the FILESTREAM data, is just a database file(s) on the same LUN(s)/Disk(s)
as the other regular database files.

While the "image" datatype was the way to go in SQL Server 2000, it will be depreciated in newer SQL versions.
So, in SQL 2K5/2K8, image is still available, but the "varbinary" is the best type for storage of any type of blob.

Not using RBS, the BLOB is registered as stored in the Content Database in the AllDocStreams table,
in a varbinary field called Content (in Sharepoint7 it was the image datatype).
Each document version counts as a record, and even the Contents field is replicated, even if only a metadata
change occurred. This is quite inefficient indeed.

The following figure illustrates the storage of blobs inside SQL Server (no EBS/RBS).

Fig 3: High level overview Inline blob storage (no EBS/RBS):



Extracting files:

It's instructive, as a sort of exercise, to extract all files to the filesystem. Although it maybe viewed as pretty useless,
it's still informative to see how it can be done, using only simple sql tools and methods.
It must be said that it's certainly against the policy of Microsoft, to directly interface to the Content database
in this way.

Method 1:

USE WSS_Content -- name of your content database
GO

DECLARE
  @Command VARCHAR(4000),
  @FileID VARCHAR(128),
  @MyFilename nvarchar(max)

DECLARE cur1 CURSOR FOR
SELECT Id FROM [WSS_Content].[dbo].[AllDocStreams]

OPEN cur1
FETCH NEXT FROM cur1 INTO @FileID

WHILE (@@FETCH_STATUS = 0)
BEGIN

  SELECT @MyFilename = LeafName from [WSS_Content].[dbo].[AllDocs] WHERE Id = @FileID

  SET @Command = 'bcp "SELECT Content from [WSS_Content].[dbo].[AllDocStreams] WHERE Id = ''' +
  @FileID + '''" queryout "C:\SQLExtract\' +
  @MyFilename+'" -T -n -Slocalhost'

  EXEC xp_cmdshell @Command

  FETCH NEXT FROM cur1 INTO @FileID

END

CLOSE cur1
DEALLOCATE cur1



5.2 Documents on outline storage (filesystems/SAN):

6. Other Storage pointers:

6.1 Growth and Planning sizing:

SharePoint uses:

• Recycle bin
• Versioning
• Search and index information of Content
• MetaData
• Regular Content (in DB or SAN or Share)

For Database storage of blobs, studies have shown that a good rule of thumb for initial planning is: 3.5 x file system storage.


6.2 Backup / Recovery:

Here, we are don't take into consideration specialized methods/tools like using "Data Protection Manager",
or any other 3rd party tools.

6.2.1 Full Backup / Recovery:

This really means full backup, and NOT on the item level (document, site, library etc..).

- Only SQL Storage: consistent model for backup/recovery.
Just all or nothing scenario. Possibly slow, but fully consistent.

- EBS: SQL backup: consistent model for backup/recovery.
But Sync between SQL Storage + SAN Storage could be difficult.
Although a COM interface (called the EBS Provider) keeps these two stores in sync, it's only
at events where the COM interface recognizes file Save and Open commands and invokes redirection calls to the EBS
This then, is done with well-known clients. The EBS Provider thus ensures that the SQL Server content database
contains metadata references to their associated BLOB streams in the external BLOB store.
But this is only garanteed with using well-known clients.

- RBS: SQL backup: consistent model for backup/recovery.
But Sync between SQL Storage + SAN Storage should not be so difficult. SAN storage = FILESTREAM Filegroup.

- Shared Library (file share): SQL backup: consistent model for backup/recovery.
Sync between SQL Storage + File Share (SAN) Storage could be difficult.


6.2.2 Item level Recovery:

Still to do.....