ÿþ<html> <head> <title>Albert van der Sel : A few simple notes on Sharepoint Storage</title> </head> <body> <h1>A few simple notes on Sharepoint / SQL Server Storage</h1> Version : 0.9 <br> Date : 12/03/2012<br> By : Albert van der Sel<br> For who : For anyone who likes some simple notes about Sharepoint, focused on SQL Server storage<br> Status : Working document (not ready)<br> <hr/> <br> <font face="arial" size=2 color="black"> <br> <h3>1. Global Purpose of some of the main Sharepoint databases:</h3> <TABLE border=3> <TR> <TD>SharePoint_AdminContent_GUID</TD> <TD>Central Administration content database. The content database is used to store all SharePoint Services content related to Administration.</TD> </TR> <TR> <TD>SharePoint_Config</TD> <TD>The configuration database contains config info: metadata on databases, and all of the IIS Web sites, Web applications, Web Part packages, and site templates. </TD> </TR> <TR> <TD>WSS_Content</TD> <TD>This database contains data specific to a Web application and its site collections. It contains the content data, such as a document uploaded to a list or a library. By default, a single content database is created for each Web application.</TD> </TR> <TR> <TD>WSS_Search</TD> <TD>This database is used for the search services. It contains the information that is required for searching content.</TD> </TR> <TR> <TD>SharedServices_DB</TD> <TD>If Shared Services are used, each SSP uses its own database. This one is used for configuration settings. It may be related to user profiles, memberships, audiences, calculations, administration etc..</TD> </TR> <TR> <TD>SharedServices_SSP</TD> <TD>Stores the content for this SSP website.</TD> </TR> </TABLE> <br> <br> <h3>2. Global Architecture Sharepoint:</h3> Fig 1: High level overview Sharepoint, focused on storage models:<br> <br> <br> <img src="sharepoint1.jpg" align="centre"/> <br> Note from figure 1, that Sharepoint apps can store documents directly in the database, or, with additional<br> software, to an external filesystem (like on a SAN).<br> Per default, Sharepoint will store documents inside the backend SQL Server "Content" database(s).<br> <br> However, especially at large volumes of documents, SQL Server storage is not optimized for storage<br> of files, as internal (inline) "blobs".Especially when Sharepoint is integrated with Office programs,<br> on any "save" action, a blob will be stored in the content database(s), which will inflate those databases rapidly.<br> <br> If you want (or need to), you can choose to store those files on a filesystem, like local storage, or a SAN.<br> This is called "external storage of files", or "outline" storage. Technically, we then should not speak<br> of "blobs" anymore, because the objects are just files on a filesystem, while SQL Server only stores "metadata"<br> about the documents (like a pointer on how to locate the file: see section 5).<br> <br> Four solutions exists (up to now):<br> <font face="arial" size=2 color="brown"> <br> <B> -EBS or "External Blob Storage", using a 3rd party "storage provider"<br> <br> -RBS or "Remote Blob Storage", from MS (default solution is in conjuction with SQL Filestream)<br> <br> -RBS using a 3rd party "storage provider" and "Third Party" software (I/O directly to Storage, not using SQL Filestream)<br> <br> -Special "Third Party" Software, that couples a Sharepoint library to a File Share.<br> </B> <font face="arial" size=2 color="black"> <br> <br> &#8658; The older solution is Microsoft's <B>EBS</B> (External Blob Storage), which can be used with<br> Sharepoint 2007 & 2010. However, you need a thirdparty "storage provider".<br> EBS "sits" close on the lower stack of Sharepoint and needs extensions in order to "talk" to<br> to the storage system. EBS is only available in conjunction with a third party storage provider.<br> The term EBS is just a generic name for any provider that let sharepoint middleware Servers talk<br> to remote storage (iSCSI, FC SAN).<br> It works alongside (parallel) to the Database. Metadata is always stored in the database, and you<br> can "tune" (on basis of size, or type of file) what get's on the filesystem, and what gets into the database.<br> <br> To coordinate the two data stores, the "core" is a COM interface, <B>ISPExternalBinaryProvider</B>,<br> where semantics are implemented to recognize file Save and Open commands and invokes redirection calls<br> to either the Database or the external storage system.<br> <br> &#8658; The (supposedly) newer solution is Microsoft's <B>RBS</B> (Remote Blob Storage), which can be used with Sharepoint 2010<br> and (as of) SQL Server 2008 R2. Standardly, it uses the "FILESTREAM" feature of SQL2008 to interface to the filesystem.<br> So, this is not using third-party software, it's explicitly from Microsoft and <B>needs</B> to be used in conjunction with FILESTREAM.<br> But an option to use RBS in conjunction with a third party provider, and not using FILESTREAM exists as well.<br> <br> &#8658; RBS with a 3rd party storage provider and 3rd party software, not using the FILESTREAM feature.<br> <br> &#8658; SharePoint "File Share Library" is another solution. It is a customized SharePoint Document Library<br> that retains much of all functions of any other regular <B>Document Library</B>.<br> Its additional features include automatically mapping the library to a network drive and displaying<br> the files and folder structure on the network file share "as if" they are in the library.<br> <br> For example, the "Bamboo File Share Library" or the "DocAve File Share Connector" are both able<br> to store on a File Share directly.<br> <br> In all four cases, on any Server in the SharePoint Farm, additional software needs to be installed.<br> <br> <br> Fig 2: High level overview implementation external storage: EBS, RBS, SharePoint File Share Library:<br> <br> <img src="sharepoint2.jpg" align="centre"/> <br> <br> <br> <h3>3. How to manipulate / differentiate storage of small content and large content? :</h3> Actually, there are two questions:<br> <br> <B> - What are the options to store files (all, or just a part) on a filesystem/SAN.<br> - How to manipulate / differentiate storage of <B>small content</B> and <B>large content</B> on filesystems/SAN.<br> </B> <br> For now, this is only some sort of braindump:<br> <br> Per default, a by Sharepoint provisioned database, will only delivery a Primary filegroup, consisting<br> of just one file (the primary file). Ofcourse, a transaction log will be created too. For now, we leave<br> the transaction log out of the discussion (since we are interested in storage of data).<br> Furthermore, the "Content" databases are our primary focus (which is certainly incomplete, since search databases<br> etc.. are very important too).<br> <br> The <I>only</I> question here is: can we somehow force the system to store smaller objects on some storage<br> facility (whether internal or external) and larger objects on some other storage facility<br> (whether internal in the database or external on some filesystem). <br> This could be an interesting question, if critical business documents are (say) less than 100MB,<br> while much less critical documents (say) are greater than 100MB (e.g. maps), or greater 20MB or so, and could possibly<br> be stored on somewhat less expensive hardware, with a somewhat lower backup frequency.<br> <br> Can we possibly use multiple database files, or FileGroups, to achive that objective?<br> Or must we use EBS, RBS or "File Share Library"?<br> <br> It's critical to know that we can ajust EBS/RBS to map files to database storage, <B>or</B><br> external storage, by criteria as the <B>filetype</B> (say .pdf), or <B>size</B> (say >100MB)<br> <br> As another point in our investigation, we will list all options for just storing files on some filesystem,<br> and not inside the database.<br> <br> <TABLE border=3> <TR> <TD>1. Default: SharePoint provisioned</TD> <TD><B>Using EBS/RBS/SharePoint File Share Library:NO</B> <br>Just one primary file, for example<br> <br> K:\mssql\primary_data1.mdf<br> <br> No differentiation is possible (unless you use EBS/RBS to store blobs on filesystems)</TD> </TR> <TR> <TD>2. By DBA provisioned database</TD> <TD><B>Using EBS/RBS/SharePoint File Share Library:NO</B><br> The Sharepoint Admin is able to <B>(re-)attach</B> the database to a WebApp/Collection.<br> The DBA has created the Primary Filegroup to include multiple datafiles like for example:<br> <br> K:\mssql\primary_data1.mdf<br> L:\mssql\primary_data2.ndf<br> M:\mssql\primary_data3.ndf<br> <br> No differentiation is possible (unless you use EBS/RBS to store blobs on filesystems)<br> At least a certain gain in performance is expected (due to multiple spindles, if used).</TD> </TR> <TR> <TD>3. By DBA provisioned database</TD> <TD><B>Using EBS/RBS/SharePoint File Share Library:NO</B><br> The Sharepoint Admin is able to <B>(re-)attach</B> the database to a WebApp/Collection.<br> The DBA has created the Primary Filegroup to as for example:<br> <br> K:\mssql\primary_data1.mdf<br> <br> The DBA has created a second (and possibly more) Filegroup (FG1) to include muliple files<br> as for example:<br> <br> L:\mssql\FG1_data1.ndf<br> M:\mssql\FG1_data2.ndf<br> N:\mssql\FG1_data3.ndf<br> <br> Directly after creation, the DBA assigns FG1 to be the "default" Filegroup.<br> <br> No differentiation is possible (unless you use EBS/RBS to store blobs on filesystems)<br> At least a certain gain in performance is expected (due to multiple spindles, if used).</TD> </TR> <TR> <TD>4. Default: SharePoint provisioned</TD> <TD><B>Using EBS/RBS:YES</B><br> <br> Independed of database layout of files:<br> <br> Differentiation is possible (using EBS/RBS criteria filetype/size)</TD> </TR> <TR> <TD>5. -Default: SharePoint provisioned<br> OR<br> -by DBA provisioned database</TD> <TD><B>Using EBS/RBS/SharePoint File Share Library:NO</B><br> <br> The Sharepoint Admin carefully assigns a specific databases to specific WebApps/Collections,<br> while keeping in mind (if possible) which site is for large objects (and thus using it's<br> specific database), and which is for ciritical business documents (and thus using it's<br> own specific database also)<br> <br> In cooperation with DBA and Storage Admins, LUNS can be assigned in accordance with<br> critical apps (critical storage/high backup freq) and less critical apps (less expensive<br> storage and lower backup freq.)<br> <br> Differentiation is possible.</TD> </TR> <TR> <TD>6. -Default: SharePoint provisioned<br> OR<br> -by DBA provisioned database</TD> <TD><B>Using EBS/RBS:NO</B><br> <br> <B>Using SharePoint File Share Library:YES</B><br> <br> The Sharepoint Admin carefully assigns a specific Libary to specific WebApps/Collections.<br> This library uses a file share using the "SharePoint File Share Library"<br> <br> The DBA creates a database, or the default SharePoint provisioned database is used.<br> <br> Storage of files ouside the database is possible, for this specific Library type.<br> But no differntiation on small-large files, unless the Library usage is coupled to<br> specific purposes.</TD> </TR> </TABLE> <br> <br> <h3>4. Some quick notes on specific Sharepoint objects:</h3> <font face="arial" size=2 color="blue"> <B>4.1 Short description Sharepoint Databases:</B><br> <font face="arial" size=2 color="black"> <br> See section 1.<br> <br> <B>4.2 Relation of Web application, to Site collection, and content database:</B><br> <br> <font face="arial" size=2 color="blue"> Web Application (WebApp) > Site Collection = Top Level site + optional subsites<br> <font face="arial" size=2 color="black"> <br> A Web Application hosts Site Collection(s). Sites in a site collection, share administrative settings<br> <br> When you create a WebApp, you specify:<br> - Name of the IIS website (toplevel site)<br> - port, path of the virtual direcory, the url, the security settings etc..<br> - Database Server and database name.<br> <br> From the Adminatrative webpage, the Admin can create the above from an easy to use Admin console.<br> Note that the relation of the choice of the content database, relates to the WebApp/Site collection.<br> <br> Per default, a Content database is created for each Web Application.<br> <br> Also, directly after creation of the WebApp, it is possible to attach the WebApp to another database<br> of your choice (the former will be deleted, and the new one populated with standard sharepoint objects).<br> You can do that using powershell, or simply by using the webbased Administration Console.<br> <br> <font face="arial" size=2 color="blue"> <B>4.3 Libraries and Lists:</B><br> <font face="arial" size=2 color="black"> <br> On a site, you can show documents in an organized way, using libraries.<br> For example, you can create a library of common documents for a project, and project team members<br> can use their client tools to find documents, edit them, and checkin/checkout those files.<br> Once created, a library may somewhat show "the look and feel" of a fileshare, but it is not.<br> The files are either stored in the SQL Server content database, or, if using EBS/RBS, on an external storage system.<br> <br> Each library displays a list of files and descriptions about the files, which helps users to use the files,<br> and to work together. So, the sharepoint Admin controls how documents are viewed, tracked, managed, and created.<br> Also, you can use workflows to enhance collaboration on documents in libraries.<br> <br> <font face="arial" size=2 color="blue"> <B>4.4 Sharepoint services:</B><br> <font face="arial" size=2 color="black"> <br> This section just serves to "get an idea" about the different Sharepoint services.<br> <br> The term "service" in a Sharepoint environment, often refers to the specific apllications that support<br> al sorts of functionality in this environment.<br> Besides those services, in a Windows environment we can expect to find some <B>true background "services"</B><br> as well. These then are the services you can see, and control, using various Windows admin tools like<br> using the following commands:<br> <br> C:\TEMP> sc query > listing.txt<br> <br> Or by using the applet:<br> <br> C:\TEMP> services.msc <br> The first command, just prints a listing of all installed services to a text file (listing.txt).<br> In a Sharepoint 7 environment, we should expect at least the following services:<br> <font face="courier" size=2 color="black"> <br> SERVICE_NAME: OSearch<br> DISPLAY_NAME: Office SharePoint Server Search<br> <br> SERVICE_NAME: SPAdmin<br> DISPLAY_NAME: Windows SharePoint Services Administration<br> <br> SERVICE_NAME: SPSearch<br> DISPLAY_NAME: Windows SharePoint Services Search<br> <br> SERVICE_NAME: SPTimerV3<br> DISPLAY_NAME: Windows SharePoint Services Timer<br> <br> SERVICE_NAME: SPTrace<br> DISPLAY_NAME: Windows SharePoint Services Tracing<br> <br> <font face="arial" size=2 color="black"> Depending on the size of the installation (what features are active), this list might be somewhat longer<br> as to include also "loadbalancer services" and "single-signon services" (and possibly others as well).<br> <br> If you would take a quick look at figure 1 again, your configuration might consist of 3 tiers:<br> 1. Webservers with IIS and al sorts of Sharepoint related information under "wwwroot".<br> 2. Sharepoint as the middle tier.<br> 3. SQL Server as the backend database engine.<br> <br> However, often (1) and (2) are often combined on the same machine, while multiple of those machines<br> are collected in a "farm".<br> <br> Although you can control, and modify operations on the services by using Windows tools (like services.msc),<br> not all operations are allowed using the standard tools.<br> For example:<br> <br> -Starting and stopping a service is allowed using Windows tools (like using "net start" or "net stop").<br> -For adjusting account information for the services, you should use Sharepoint own tools like "stsadm" (Sharepoint7)<br> or powershell (Sharepoint 2010), or other Sharepoint "aware" means to change such critical data.<br> <br> The get a further idea of the "stack" of services on tiers (1) and (2),<br> take a look at the following example stop script (cmd) where we assume tiers (1)&(2) are on one machine:<br> <br> net stop w3svc<br> net stop iisadmin<br> net stop ssosrv<br> net stop DCLauncher<br> net stop DCLoadBalancer<br> net stop OSearch<br> net stop SPAdmin<br> net stop SPSearch<br> net stop SPTimerV3<br> net stop SPTrace<br> net stop SPWriter<br> iisreset /stop<br> <br> <br> <h3>5. Metadata, pointers, and BLOBs:</h3> Let's first see how SQL Server stores blobs inside the database. So, here we want to explore the default<br> situation where blobs (the documents) are fully stored inside the database.<br> <br> After that, we will try to explore how outline storage (on filesystems/SAN) is implemented.<br> <br> <font face="arial" size=2 color="blue"> <B>5.1 Blob metadata, with inline storage:</B><br> <font face="arial" size=2 color="black"> <br> <B><U>5.1.1 Metadata on documents (or blobs)</U></B><br> <br> In SQL 2K5/2K8, we can try a couple of standard queries, in order to find "alien" objects,<br> in this case, originating from Sharepoint.<br> Using the following query (in a Content database), we get an impression of objects (like tables, views, sp's)<br> which are not installed at a default installation of SQL Server. So, they are installed afterwards,<br> like the adding of database objects that results from a creation of a WebApp in Sharepoint.<br> <br> <font face="courier" size=2 color="blue"> select substring(name,1,20) as "Name", substring(type_desc,1,20) as "Type", create_date<br> from sys.all_objects where is_ms_shipped=0 and type_desc='VIEW' or type_desc='USER_TABLE'<br> and name not like '%CI_AS' order by name -- in my case I want to exclude some collation tables <br> <font face="arial" size=2 color="black"> <br> This produces (in my case) a list like:<br> <br> <TABLE border=1> <TR> <TD>Name</TD><TD>Type</TD> </TR> <TR> <TD>AllDocs</TD><TD>USER_TABLE</TD> </TR> <TR> <TD>AllDocStreams</TD><TD>USER_TABLE</TD> </TR> <TR> <TD>AllDocVersions</TD><TD>USER_TABLE</TD> </TR> <TR> <TD>AllLinks</TD><TD>USER_TABLE</TD> </TR> <TR> <TD>AllLists</TD><TD>USER_TABLE</TD> </TR> <TR> <TD>AllUserData</TD><TD>USER_TABLE</TD> </TR> <TR> <TD>AllLists</TD><TD>USER_TABLE</TD> </TR> <TR> <TD>..</TD><TD>..</TD> </TR> <TR> <TD>Docs</TD><TD>VIEW</TD> </TR> <TR> <TD>DocStreams</TD><TD>VIEW</TD> </TR> <TR> <TD>DocVersions</TD><TD>VIEW</TD> </TR> <TR> <TD>..</TD><TD>..</TD> </TR> <TR> <TD>SiteQuota</TD><TD>USER_TABLE</TD> </TR> <TR> <TD>Sites</TD><TD>USER_TABLE</TD> </TR> <TR> <TD>..</TD><TD>..</TD> </TR> <TR> <TD>Workflow</TD><TD>USER_TABLE</TD> </TR> </TABLE> <br> <I>The above listing, is just a small sample (partial listing)..</I><br> <br> With not too much effort, we can browse through the datamodel of these tables and views.<br> Then, after some investigation, we can clearly see that the "Alldocs" and "AlldocStreams" tables,<br> gives us all we need to know about "id's" (of blobs), the "content" of the blob, the "size", the associated<br> "site_id" which uses this blob, and much more.<br> Joining these tables with the AllLists view, will reveal the list or library (and other information) too.<br> <br> You can try for example the following three queries to obtain lots of information about the document storage<br> within a content database:<br> <br> <font face="courier" size=2 color="blue"> USE [YourContentDatabase]<br> GO<br> <br> -- Query 1:<br> <br> SELECT AllLists.tp_Title AS 'List Name',<br> AllDocs.LeafName AS 'File Name',<br> AllDocs.DirName AS 'URL',<br> AllDocStreams.Content AS 'Document Content (Binary)',<br> AllDocStreams.Size AS 'Size'<br> FROM <B>AllDocs</B> <br> JOIN <B>AllDocStreams</B> <br> ON <br> AllDocs.Id=AllDocStreams.Id <br> JOIN <B>AllLists</B> <br> ON<br> AllLists.tp_id = AllDocs.ListId<br> ORDER BY AllDocStreams.Size DESC<br> <br> -- Query 2:<br> <br> SELECT AllDocStreams.Id, AllDocStreams.[Content], AllDocStreams.Size, AllDocs.Version, AllDocs.TimeLastModified, <br> AllDocs.CheckoutUserId, AllDocs.CheckoutDate, AllDocs.IsCurrentVersion, AllDocs.DirName, <br> AllDocs.LeafName, AllDocs.[Level]<br> FROM AllDocs INNER JOIN<br> AllDocStreams ON AllDocStreams.Id = AllDocs.Id AND AllDocs.[Level] = AllDocStreams.[Level]<br> WHERE AllDocs.TimeLastModified > getdate() - 30 -- use here as getdate()- number_of_days_ago<br> ORDER by AllDocs.TimeLastModified<br> <br> -- Query 3: Suppose you want information on .doc, .xls, and .pdf files:<br> <br> SELECT [AllDocs].[DirName], [AllDocs].[LeafName], [AllDocStreams].[Content]<br> from [AllDocs],[AllDocStreams] where (LeafName like  %.doc or LeafName like  %.xls or LeafName like  %.pdf )<br> and [AllDocStreams].[Content] is not NULL and [AllDocs].[Id] = [AllDocStreams].[Id] <br> <font face="arial" size=2 color="black"> <br> You can query on specific Webs and Sites as well, where you are not focusing on document information,<br> but you for example just want to obtain lists of Webapps and sites. For example:<br> <br> <font face="courier" size=2 color="blue"> SELECT Id, OwnerId, TimeCreated, RootWebId FROM Sites<br> <font face="arial" size=2 color="black"> <br> - The <B>"AllDocs"</B> Table regsiters lot's of metadata about documents, like document id, site id, Webid,<br> TimeLastWritten, Version, and much more.<br> - The <B>"AllDocStreams"</B> Table registers all document id's, associated site id's, blob contents,<br> document sizes in bytes, Parent id's (The identifier of the document's parent container).<br> - The <B>"AllUserData"</B> Table registers data for any item associated with any list- and document librarylists.<br> - The <B>"AllLists"</B> Table contains information of SharePoint lists such as list s name,<br> list s description and the list s item count, and more.<br> <br> <br> <font face="arial" size=2 color="blue"> <B>5.2 Blob inline storage (No EBS/RBS):</B><br> <font face="arial" size=2 color="black"> <br> It's well known that SQL Server is optimized for ascii oriented storage (typical administrtive data).<br> Don't forget that BLOBs and relational database data are very different entities.<br> Microsoft itself refers to this kind of content, as <B>unstructured data</B>, as opposed to the more structured,<br> relational data normally stored in a database.<br> <br> However, various datatypes are available for storage of large text bloks, XML, and "binary" data.<br> <br> <B>binary datatypes:</B><br> <br> - image: up to 2GB, e.g.: to store pdf, word files, images, or other binary data<br> - varbinary(max): up to 2G, e.g.: to store pdf, word files, images, or other binary data<br> The storage size is the actual length of the data entered + 2 bytes.<br> - binary: Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000.<br> <br> Or we can use FILESTREAM Storage. FILESTREAM is a storage attribute of the existing VARBINARY(MAX) data type.<br> FILESTREAM alters how the BLOB data is stored in the <B>file system</B> rather than in the SQL Server data files.<br> However, the Filegroup supporting the FILESTREAM data, is just a database file(s) on the same LUN(s)/Disk(s)<br> as the other regular database files.<br> <br> While the "image" datatype was the way to go in SQL Server 2000, it will be depreciated in newer SQL versions.<br> So, in SQL 2K5/2K8, image is still available, but the "varbinary" is the best type for storage of any type of blob.<br> <br> Not using RBS, the BLOB is registered as stored in the Content Database in the <B>AllDocStreams</B> table,<br> in a varbinary field called <B>Content</B> (in Sharepoint7 it was the image datatype).<br> Each document version counts as a record, and even the Contents field is replicated, even if only a metadata<br> change occurred. This is quite inefficient indeed.<br> <br> The following figure illustrates the storage of blobs inside SQL Server (no EBS/RBS).<br> <br> Fig 3: High level overview Inline blob storage (no EBS/RBS):<br> <br> <img src="sharepoint3.jpg" align="centre"/> <br> <br> <B>Extracting files:</B><br> <br> It's instructive, as a sort of exercise, to extract all files to the filesystem. Although it maybe viewed as pretty useless,<br> it's still informative to see how it can be done, using only simple sql tools and methods.<br> It must be said that it's certainly against the policy of Microsoft, to directly interface to the Content database<br> in this way.<br> <br> <U>Method 1:</U><br> <br> <font face="courier" size=2 color="blue"> USE WSS_Content -- name of your content database<br> GO<br> <br> DECLARE <br> &nbsp @Command VARCHAR(4000),<br> &nbsp @FileID VARCHAR(128),<br> &nbsp @MyFilename nvarchar(max)<br> <br> DECLARE cur1 CURSOR FOR <br> SELECT Id FROM [WSS_Content].[dbo].[AllDocStreams]<br> <br> OPEN cur1<br> FETCH NEXT FROM cur1 INTO @FileID<br> <br> WHILE (@@FETCH_STATUS = 0) <br> BEGIN<br> <br> &nbsp SELECT @MyFilename = LeafName from [WSS_Content].[dbo].[AllDocs] WHERE Id = @FileID<br> <br> &nbsp SET @Command = 'bcp "SELECT Content from [WSS_Content].[dbo].[AllDocStreams] WHERE Id = ''' +<br> &nbsp @FileID + '''" queryout "C:\SQLExtract\' +<br> &nbsp @MyFilename+'" -T -n -Slocalhost'<br> <br> &nbsp EXEC xp_cmdshell @Command<br> <br> &nbsp FETCH NEXT FROM cur1 INTO @FileID<br> <br> END <br> <br> CLOSE cur1<br> DEALLOCATE cur1<br> <br> <font face="arial" size=2 color="black"> <br> <br> <font face="arial" size=2 color="blue"> <B>5.2 Documents on outline storage (filesystems/SAN):</B><br> <font face="arial" size=2 color="black"> <br> <br> <br> <h3>6. Other Storage pointers:</h3> <font face="arial" size=2 color="blue"> <B>6.1 Growth and Planning sizing:</B><br> <font face="arial" size=2 color="black"> <br> SharePoint uses:<br> <br> <ul> <li>Recycle bin</li> <li>Versioning</li> <li>Search and index information of Content</li> <li>MetaData</li> <li>Regular Content (in DB or SAN or Share)</li> </ul> For Database storage of blobs, studies have shown that a good rule of thumb for initial planning is: 3.5 x file system storage.<br> <br> <br> <font face="arial" size=2 color="blue"> <B>6.2 Backup / Recovery:</B><br> <font face="arial" size=2 color="black"> <br> Here, we are don't take into consideration specialized methods/tools like using "Data Protection Manager",<br> or any other 3rd party tools.<br> <br> <B><U>6.2.1 Full Backup / Recovery:</U></B><br> <br> This really means full backup, and NOT on the item level (document, site, library etc..).<br> <br> - Only SQL Storage: consistent model for backup/recovery. <br> Just all or nothing scenario. Possibly slow, but fully consistent.<br> <br> - EBS: SQL backup: consistent model for backup/recovery.<br> But Sync between SQL Storage + SAN Storage could be difficult.<br> Although a COM interface (called the EBS Provider) keeps these two stores in sync, it's only<br> at events where the COM interface recognizes file Save and Open commands and invokes redirection calls to the EBS<br> This then, is done with well-known clients. The EBS Provider thus ensures that the SQL Server content database<br> contains metadata references to their associated BLOB streams in the external BLOB store.<br> But this is only garanteed with using well-known clients.<br> <br> - RBS: SQL backup: consistent model for backup/recovery. <br> But Sync between SQL Storage + SAN Storage should not be so difficult. SAN storage = FILESTREAM Filegroup.<br> <br> - Shared Library (file share): SQL backup: consistent model for backup/recovery.<br> Sync between SQL Storage + File Share (SAN) Storage could be difficult.<br> <br> <br> <B><U>6.2.2 Item level Recovery:</U></B><br> <br> <h3>Still to do.....</h3> <br> <br> <br> <br> </body> </html>