਍㰀栀攀愀搀㸀ഀഀ Albert van der Sel : A few simple notes on Sharepoint Storage ਍㰀⼀栀攀愀搀㸀ഀഀ ਍ ഀഀ ਍ ഀഀ

A few simple notes on Sharepoint / SQL Server Storage

਍ ഀഀ ਍ ഀഀ Version : 0.9
਍䐀愀琀攀  㨀 ㄀㈀⼀　㌀⼀㈀　㄀㈀㰀戀爀㸀ഀഀ By : Albert van der Sel
਍䘀漀爀 眀栀漀  㨀 䘀漀爀 愀渀礀漀渀攀 眀栀漀 氀椀欀攀猀 猀漀洀攀 猀椀洀瀀氀攀 渀漀琀攀猀 愀戀漀甀琀 匀栀愀爀攀瀀漀椀渀琀Ⰰ 昀漀挀甀猀攀搀 漀渀 匀儀䰀 匀攀爀瘀攀爀 猀琀漀爀愀最攀㰀戀爀㸀ഀഀ Status : Working document (not ready)
਍㰀栀爀⼀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍㰀栀㌀㸀㄀⸀ 䜀氀漀戀愀氀 倀甀爀瀀漀猀攀 漀昀 猀漀洀攀 漀昀 琀栀攀 洀愀椀渀 匀栀愀爀攀瀀漀椀渀琀 搀愀琀愀戀愀猀攀猀㨀㰀⼀栀㌀㸀ഀഀ ਍ ഀഀ ਍㰀吀䄀䈀䰀䔀 戀漀爀搀攀爀㴀㌀㸀ഀഀ ਍ ഀഀ ਍      㰀吀刀㸀ഀഀ SharePoint_AdminContent_GUID ਍            㰀吀䐀㸀䌀攀渀琀爀愀氀 䄀搀洀椀渀椀猀琀爀愀琀椀漀渀 挀漀渀琀攀渀琀 搀愀琀愀戀愀猀攀⸀ 吀栀攀 挀漀渀琀攀渀琀 搀愀琀愀戀愀猀攀 椀猀 甀猀攀搀 琀漀 猀琀漀爀攀 愀氀氀ഀഀ SharePoint Services content related to Administration. ਍      㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀匀栀愀爀攀倀漀椀渀琀开䌀漀渀昀椀最㰀⼀吀䐀㸀ഀഀ The configuration database contains config info: metadata on databases, and all of the IIS Web sites, Web applications, Web Part packages, and site templates. ਍㰀⼀吀䐀㸀ഀഀ ਍ ഀഀ ਍ ഀഀ ਍            㰀吀䐀㸀圀匀匀开䌀漀渀琀攀渀琀㰀⼀吀䐀㸀ഀഀ 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. ਍           䈀礀 搀攀昀愀甀氀琀Ⰰ 愀 猀椀渀最氀攀 挀漀渀琀攀渀琀 搀愀琀愀戀愀猀攀 椀猀 挀爀攀愀琀攀搀 昀漀爀 攀愀挀栀 圀攀戀 愀瀀瀀氀椀挀愀琀椀漀渀⸀㰀⼀吀䐀㸀ഀഀ ਍ ഀഀ ਍ ഀഀ ਍            㰀吀䐀㸀圀匀匀开匀攀愀爀挀栀㰀⼀吀䐀㸀ഀഀ This database is used for the search services. It contains the information ਍              琀栀愀琀 椀猀 爀攀焀甀椀爀攀搀 昀漀爀 猀攀愀爀挀栀椀渀最 挀漀渀琀攀渀琀⸀㰀⼀吀䐀㸀ഀഀ ਍ ഀഀ ਍ ഀഀ ਍            㰀吀䐀㸀匀栀愀爀攀搀匀攀爀瘀椀挀攀猀开䐀䈀㰀⼀吀䐀㸀ഀഀ If Shared Services are used, each SSP uses its own database. This one is used for configuration settings. ਍                 䤀琀 洀愀礀 戀攀 爀攀氀愀琀攀搀 琀漀 甀猀攀爀 瀀爀漀昀椀氀攀猀Ⰰഀഀ memberships, audiences, calculations, administration etc.. ਍      㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀匀栀愀爀攀搀匀攀爀瘀椀挀攀猀开匀匀倀㰀⼀吀䐀㸀ഀഀ Stores the content for this SSP website. ਍      㰀⼀吀刀㸀ഀഀ ਍ ഀഀ ਍ ഀഀ ਍ ഀഀ ਍ ഀഀ ਍ ഀഀ
਍㰀戀爀㸀ഀഀ

2. Global Architecture Sharepoint:

਍ ഀഀ ਍ ഀഀ Fig 1: High level overview Sharepoint, focused on storage models:
਍㰀戀爀㸀ഀഀ
਍㰀椀洀最 猀爀挀㴀∀猀栀愀爀攀瀀漀椀渀琀㄀⸀樀瀀最∀ 愀氀椀最渀㴀∀挀攀渀琀爀攀∀⼀㸀ഀഀ
਍一漀琀攀 昀爀漀洀 昀椀最甀爀攀 ㄀Ⰰ 琀栀愀琀 匀栀愀爀攀瀀漀椀渀琀 愀瀀瀀猀 挀愀渀 猀琀漀爀攀 搀漀挀甀洀攀渀琀猀 搀椀爀攀挀琀氀礀 椀渀 琀栀攀 搀愀琀愀戀愀猀攀Ⰰ 漀爀Ⰰ 眀椀琀栀 愀搀搀椀琀椀漀渀愀氀㰀戀爀㸀ഀഀ software, to an external filesystem (like on a SAN).
਍倀攀爀 搀攀昀愀甀氀琀Ⰰ 匀栀愀爀攀瀀漀椀渀琀 眀椀氀氀 猀琀漀爀攀 搀漀挀甀洀攀渀琀猀 椀渀猀椀搀攀 琀栀攀 戀愀挀欀攀渀搀 匀儀䰀 匀攀爀瘀攀爀 ∀䌀漀渀琀攀渀琀∀ 搀愀琀愀戀愀猀攀⠀猀⤀⸀㰀戀爀㸀ഀഀ
਍䠀漀眀攀瘀攀爀Ⰰ 攀猀瀀攀挀椀愀氀氀礀 愀琀 氀愀爀最攀 瘀漀氀甀洀攀猀 漀昀 搀漀挀甀洀攀渀琀猀Ⰰ 匀儀䰀 匀攀爀瘀攀爀 猀琀漀爀愀最攀 椀猀 渀漀琀 漀瀀琀椀洀椀稀攀搀 昀漀爀 猀琀漀爀愀最攀㰀戀爀㸀ഀഀ of files, as internal (inline) "blobs".Especially when Sharepoint is integrated with Office programs,
਍漀渀 愀渀礀 ∀猀愀瘀攀∀ 愀挀琀椀漀渀Ⰰ 愀 戀氀漀戀 眀椀氀氀 戀攀 猀琀漀爀攀搀 椀渀 琀栀攀 挀漀渀琀攀渀琀 搀愀琀愀戀愀猀攀⠀猀⤀Ⰰ 眀栀椀挀栀 眀椀氀氀 椀渀昀氀愀琀攀 琀栀漀猀攀 搀愀琀愀戀愀猀攀猀 爀愀瀀椀搀氀礀⸀㰀戀爀㸀ഀഀ
਍䤀昀 礀漀甀 眀愀渀琀 ⠀漀爀 渀攀攀搀 琀漀⤀Ⰰ 礀漀甀 挀愀渀 挀栀漀漀猀攀 琀漀 猀琀漀爀攀 琀栀漀猀攀 昀椀氀攀猀 漀渀 愀 昀椀氀攀猀礀猀琀攀洀Ⰰ 氀椀欀攀 氀漀挀愀氀 猀琀漀爀愀最攀Ⰰ 漀爀 愀 匀䄀一⸀㰀戀爀㸀ഀഀ This is called "external storage of files", or "outline" storage. Technically, we then should not speak
਍漀昀 ∀戀氀漀戀猀∀ 愀渀礀洀漀爀攀Ⰰ 戀攀挀愀甀猀攀 琀栀攀 漀戀樀攀挀琀猀 愀爀攀 樀甀猀琀 昀椀氀攀猀 漀渀 愀 昀椀氀攀猀礀猀琀攀洀Ⰰ 眀栀椀氀攀 匀儀䰀 匀攀爀瘀攀爀 漀渀氀礀 猀琀漀爀攀猀 ∀洀攀琀愀搀愀琀愀∀㰀戀爀㸀ഀഀ about the documents (like a pointer on how to locate the file: see section 5).
਍㰀戀爀㸀ഀഀ Four solutions exists (up to now):
਍ഀഀ ਍ഀഀ
਍㰀䈀㸀ഀഀ -EBS or "External Blob Storage", using a 3rd party "storage provider"
਍㰀戀爀㸀ഀഀ -RBS or "Remote Blob Storage", from MS (default solution is in conjuction with SQL Filestream)
਍㰀戀爀㸀ഀഀ -RBS using a 3rd party "storage provider" and "Third Party" software (I/O directly to Storage, not using SQL Filestream)
਍㰀戀爀㸀ഀഀ -Special "Third Party" Software, that couples a Sharepoint library to a File Share.
਍㰀⼀䈀㸀ഀഀ ਍㰀戀爀㸀ഀഀ
਍☀⌀㠀㘀㔀㠀㬀 吀栀攀 漀氀搀攀爀 猀漀氀甀琀椀漀渀 椀猀 䴀椀挀爀漀猀漀昀琀✀猀 㰀䈀㸀䔀䈀匀㰀⼀䈀㸀 ⠀䔀砀琀攀爀渀愀氀 䈀氀漀戀 匀琀漀爀愀最攀⤀Ⰰ 眀栀椀挀栀 挀愀渀 戀攀 甀猀攀搀 眀椀琀栀㰀戀爀㸀ഀഀ Sharepoint 2007 & 2010. However, you need a thirdparty "storage provider".
਍䔀䈀匀 ∀猀椀琀猀∀ 挀氀漀猀攀 漀渀 琀栀攀 氀漀眀攀爀 猀琀愀挀欀 漀昀 匀栀愀爀攀瀀漀椀渀琀 愀渀搀 渀攀攀搀猀 攀砀琀攀渀猀椀漀渀猀 椀渀 漀爀搀攀爀 琀漀 ∀琀愀氀欀∀ 琀漀㰀戀爀㸀ഀഀ to the storage system. EBS is only available in conjunction with a third party storage provider.
਍吀栀攀 琀攀爀洀 䔀䈀匀 椀猀 樀甀猀琀 愀 最攀渀攀爀椀挀 渀愀洀攀 昀漀爀 愀渀礀 瀀爀漀瘀椀搀攀爀 琀栀愀琀 氀攀琀 猀栀愀爀攀瀀漀椀渀琀 洀椀搀搀氀攀眀愀爀攀 匀攀爀瘀攀爀猀 琀愀氀欀㰀戀爀㸀ഀഀ to remote storage (iSCSI, FC SAN).
਍䤀琀 眀漀爀欀猀 愀氀漀渀最猀椀搀攀 ⠀瀀愀爀愀氀氀攀氀⤀ 琀漀 琀栀攀 䐀愀琀愀戀愀猀攀⸀ 䴀攀琀愀搀愀琀愀 椀猀 愀氀眀愀礀猀 猀琀漀爀攀搀 椀渀 琀栀攀 搀愀琀愀戀愀猀攀Ⰰ 愀渀搀 礀漀甀㰀戀爀㸀ഀഀ can "tune" (on basis of size, or type of file) what get's on the filesystem, and what gets into the database.
਍㰀戀爀㸀ഀഀ To coordinate the two data stores, the "core" is a COM interface, ISPExternalBinaryProvider,
਍眀栀攀爀攀 猀攀洀愀渀琀椀挀猀 愀爀攀 椀洀瀀氀攀洀攀渀琀攀搀 琀漀 爀攀挀漀最渀椀稀攀 昀椀氀攀 匀愀瘀攀 愀渀搀 伀瀀攀渀 挀漀洀洀愀渀搀猀 愀渀搀 椀渀瘀漀欀攀猀 爀攀搀椀爀攀挀琀椀漀渀 挀愀氀氀猀㰀戀爀㸀ഀഀ to either the Database or the external storage system.
਍ഀഀ
਍☀⌀㠀㘀㔀㠀㬀 吀栀攀 ⠀猀甀瀀瀀漀猀攀搀氀礀⤀ 渀攀眀攀爀 猀漀氀甀琀椀漀渀 椀猀 䴀椀挀爀漀猀漀昀琀✀猀 㰀䈀㸀刀䈀匀㰀⼀䈀㸀 ⠀刀攀洀漀琀攀 䈀氀漀戀 匀琀漀爀愀最攀⤀Ⰰ 眀栀椀挀栀 挀愀渀 戀攀 甀猀攀搀 眀椀琀栀 匀栀愀爀攀瀀漀椀渀琀 ㈀　㄀　㰀戀爀㸀ഀഀ and (as of) SQL Server 2008 R2. Standardly, it uses the "FILESTREAM" feature of SQL2008 to interface to the filesystem.
਍匀漀Ⰰ 琀栀椀猀 椀猀 渀漀琀 甀猀椀渀最 琀栀椀爀搀ⴀ瀀愀爀琀礀 猀漀昀琀眀愀爀攀Ⰰ 椀琀✀猀 攀砀瀀氀椀挀椀琀氀礀 昀爀漀洀 䴀椀挀爀漀猀漀昀琀 愀渀搀 㰀䈀㸀渀攀攀搀猀㰀⼀䈀㸀 琀漀 戀攀 甀猀攀搀 椀渀 挀漀渀樀甀渀挀琀椀漀渀 眀椀琀栀 䘀䤀䰀䔀匀吀刀䔀䄀䴀⸀㰀戀爀㸀ഀഀ But an option to use RBS in conjunction with a third party provider, and not using FILESTREAM exists as well.
਍㰀戀爀㸀ഀഀ ⇒ RBS with a 3rd party storage provider and 3rd party software, not using the FILESTREAM feature.
਍㰀戀爀㸀ഀഀ ⇒ SharePoint "File Share Library" is another solution. It is a customized SharePoint Document Library
਍琀栀愀琀 爀攀琀愀椀渀猀 洀甀挀栀 漀昀 愀氀氀 昀甀渀挀琀椀漀渀猀 漀昀 愀渀礀 漀琀栀攀爀 爀攀最甀氀愀爀 㰀䈀㸀䐀漀挀甀洀攀渀琀 䰀椀戀爀愀爀礀㰀⼀䈀㸀⸀㰀戀爀㸀ഀഀ Its additional features include automatically mapping the library to a network drive and displaying
਍琀栀攀 昀椀氀攀猀 愀渀搀 昀漀氀搀攀爀 猀琀爀甀挀琀甀爀攀 漀渀 琀栀攀 渀攀琀眀漀爀欀 昀椀氀攀 猀栀愀爀攀 ∀愀猀 椀昀∀ 琀栀攀礀 愀爀攀 椀渀 琀栀攀 氀椀戀爀愀爀礀⸀㰀戀爀㸀ഀഀ
਍䘀漀爀 攀砀愀洀瀀氀攀Ⰰ 琀栀攀 ∀䈀愀洀戀漀漀 䘀椀氀攀 匀栀愀爀攀 䰀椀戀爀愀爀礀∀ 漀爀 琀栀攀 ∀䐀漀挀䄀瘀攀 䘀椀氀攀 匀栀愀爀攀 䌀漀渀渀攀挀琀漀爀∀ 愀爀攀 戀漀琀栀 愀戀氀攀㰀戀爀㸀ഀഀ to store on a File Share directly.
਍ഀഀ ਍㰀戀爀㸀ഀഀ In all four cases, on any Server in the SharePoint Farm, additional software needs to be installed.
਍㰀戀爀㸀ഀഀ
਍䘀椀最 ㈀㨀 䠀椀最栀 氀攀瘀攀氀 漀瘀攀爀瘀椀攀眀 椀洀瀀氀攀洀攀渀琀愀琀椀漀渀 攀砀琀攀爀渀愀氀 猀琀漀爀愀最攀㨀 䔀䈀匀Ⰰ 刀䈀匀Ⰰ 匀栀愀爀攀倀漀椀渀琀 䘀椀氀攀 匀栀愀爀攀 䰀椀戀爀愀爀礀㨀㰀戀爀㸀ഀഀ
਍㰀椀洀最 猀爀挀㴀∀猀栀愀爀攀瀀漀椀渀琀㈀⸀樀瀀最∀ 愀氀椀最渀㴀∀挀攀渀琀爀攀∀⼀㸀ഀഀ
਍㰀戀爀㸀ഀഀ
਍㰀栀㌀㸀㌀⸀ 䠀漀眀 琀漀 洀愀渀椀瀀甀氀愀琀攀 ⼀ 搀椀昀昀攀爀攀渀琀椀愀琀攀 猀琀漀爀愀最攀 漀昀 猀洀愀氀氀 挀漀渀琀攀渀琀 愀渀搀 氀愀爀最攀 挀漀渀琀攀渀琀㼀 㨀㰀⼀栀㌀㸀ഀഀ ਍䄀挀琀甀愀氀氀礀Ⰰ 琀栀攀爀攀 愀爀攀 琀眀漀 焀甀攀猀琀椀漀渀猀㨀㰀戀爀㸀ഀഀ
਍㰀䈀㸀ഀഀ - What are the options to store files (all, or just a part) on a filesystem/SAN.
਍ⴀ 䠀漀眀 琀漀 洀愀渀椀瀀甀氀愀琀攀 ⼀ 搀椀昀昀攀爀攀渀琀椀愀琀攀 猀琀漀爀愀最攀 漀昀 㰀䈀㸀猀洀愀氀氀 挀漀渀琀攀渀琀㰀⼀䈀㸀 愀渀搀 㰀䈀㸀氀愀爀最攀 挀漀渀琀攀渀琀㰀⼀䈀㸀 漀渀 昀椀氀攀猀礀猀琀攀洀猀⼀匀䄀一⸀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀 ഀഀ For now, this is only some sort of braindump:
਍㰀戀爀㸀ഀഀ ਍倀攀爀 搀攀昀愀甀氀琀Ⰰ 愀 戀礀 匀栀愀爀攀瀀漀椀渀琀 瀀爀漀瘀椀猀椀漀渀攀搀 搀愀琀愀戀愀猀攀Ⰰ 眀椀氀氀 漀渀氀礀 搀攀氀椀瘀攀爀礀 愀 倀爀椀洀愀爀礀 昀椀氀攀最爀漀甀瀀Ⰰ 挀漀渀猀椀猀琀椀渀最㰀戀爀㸀ഀഀ of just one file (the primary file). Ofcourse, a transaction log will be created too. For now, we leave
਍琀栀攀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最 漀甀琀 漀昀 琀栀攀 搀椀猀挀甀猀猀椀漀渀 ⠀猀椀渀挀攀 眀攀 愀爀攀 椀渀琀攀爀攀猀琀攀搀 椀渀 猀琀漀爀愀最攀 漀昀 搀愀琀愀⤀⸀㰀戀爀㸀ഀഀ Furthermore, the "Content" databases are our primary focus (which is certainly incomplete, since search databases
਍攀琀挀⸀⸀ 愀爀攀 瘀攀爀礀 椀洀瀀漀爀琀愀渀琀 琀漀漀⤀⸀㰀戀爀㸀ഀഀ
਍吀栀攀 㰀䤀㸀漀渀氀礀㰀⼀䤀㸀 焀甀攀猀琀椀漀渀 栀攀爀攀 椀猀㨀 挀愀渀 眀攀 猀漀洀攀栀漀眀 昀漀爀挀攀 琀栀攀 猀礀猀琀攀洀 琀漀 猀琀漀爀攀 猀洀愀氀氀攀爀 漀戀樀攀挀琀猀 漀渀 猀漀洀攀 猀琀漀爀愀最攀㰀戀爀㸀ഀഀ facility (whether internal or external) and larger objects on some other storage facility
਍⠀眀栀攀琀栀攀爀 椀渀琀攀爀渀愀氀 椀渀 琀栀攀 搀愀琀愀戀愀猀攀 漀爀 攀砀琀攀爀渀愀氀 漀渀 猀漀洀攀 昀椀氀攀猀礀猀琀攀洀⤀⸀ഀഀ
਍吀栀椀猀 挀漀甀氀搀 戀攀 愀渀 椀渀琀攀爀攀猀琀椀渀最 焀甀攀猀琀椀漀渀Ⰰ 椀昀 挀爀椀琀椀挀愀氀 戀甀猀椀渀攀猀猀 搀漀挀甀洀攀渀琀猀 愀爀攀 ⠀猀愀礀⤀ 氀攀猀猀 琀栀愀渀 ㄀　　䴀䈀Ⰰ㰀戀爀㸀ഀഀ while much less critical documents (say) are greater than 100MB (e.g. maps), or greater 20MB or so, and could possibly
਍戀攀 猀琀漀爀攀搀 漀渀 猀漀洀攀眀栀愀琀 氀攀猀猀 攀砀瀀攀渀猀椀瘀攀 栀愀爀搀眀愀爀攀Ⰰ 眀椀琀栀 愀 猀漀洀攀眀栀愀琀 氀漀眀攀爀 戀愀挀欀甀瀀 昀爀攀焀甀攀渀挀礀⸀㰀戀爀㸀ഀഀ
਍䌀愀渀 眀攀 瀀漀猀猀椀戀氀礀 甀猀攀 洀甀氀琀椀瀀氀攀 搀愀琀愀戀愀猀攀 昀椀氀攀猀Ⰰ 漀爀 䘀椀氀攀䜀爀漀甀瀀猀Ⰰ 琀漀 愀挀栀椀瘀攀 琀栀愀琀 漀戀樀攀挀琀椀瘀攀㼀㰀戀爀㸀ഀഀ Or must we use EBS, RBS or "File Share Library"?
਍㰀戀爀㸀ഀഀ It's critical to know that we can ajust EBS/RBS to map files to database storage, or
਍攀砀琀攀爀渀愀氀 猀琀漀爀愀最攀Ⰰ 戀礀 挀爀椀琀攀爀椀愀 愀猀 琀栀攀 㰀䈀㸀昀椀氀攀琀礀瀀攀㰀⼀䈀㸀 ⠀猀愀礀 ⸀瀀搀昀⤀Ⰰ 漀爀 㰀䈀㸀猀椀稀攀㰀⼀䈀㸀 ⠀猀愀礀 㸀㄀　　䴀䈀⤀㰀戀爀㸀ഀഀ
਍ ഀഀ As another point in our investigation, we will list all options for just storing files on some filesystem,
਍愀渀搀 渀漀琀 椀渀猀椀搀攀 琀栀攀 搀愀琀愀戀愀猀攀⸀㰀戀爀㸀 ഀഀ ਍㰀戀爀㸀ഀഀ ਍ ഀഀ ਍ ഀഀ ਍            㰀吀䐀㸀㄀⸀ 䐀攀昀愀甀氀琀㨀 匀栀愀爀攀倀漀椀渀琀 瀀爀漀瘀椀猀椀漀渀攀搀㰀⼀吀䐀㸀ഀഀ ਍      㰀吀刀㸀ഀഀ ਍            㰀吀䐀㸀㰀䈀㸀唀猀椀渀最 䔀䈀匀⼀刀䈀匀⼀匀栀愀爀攀倀漀椀渀琀 䘀椀氀攀 匀栀愀爀攀 䰀椀戀爀愀爀礀㨀一伀㰀⼀䈀㸀㰀戀爀㸀ഀഀ The Sharepoint Admin is able to (re-)attach the database to a WebApp/Collection.
਍                吀栀攀 䐀䈀䄀 栀愀猀 挀爀攀愀琀攀搀 琀栀攀 倀爀椀洀愀爀礀 䘀椀氀攀最爀漀甀瀀 琀漀 椀渀挀氀甀搀攀 洀甀氀琀椀瀀氀攀 搀愀琀愀昀椀氀攀猀 氀椀欀攀 昀漀爀 攀砀愀洀瀀氀攀㨀㰀戀爀㸀ഀഀ
਍                 䬀㨀尀洀猀猀焀氀尀瀀爀椀洀愀爀礀开搀愀琀愀㄀⸀洀搀昀㰀戀爀㸀ഀഀ L:\mssql\primary_data2.ndf
਍                 䴀㨀尀洀猀猀焀氀尀瀀爀椀洀愀爀礀开搀愀琀愀㌀⸀渀搀昀㰀戀爀㸀ഀഀ
਍                 一漀 搀椀昀昀攀爀攀渀琀椀愀琀椀漀渀 椀猀 瀀漀猀猀椀戀氀攀 ⠀甀渀氀攀猀猀 礀漀甀 甀猀攀 䔀䈀匀⼀刀䈀匀 琀漀 猀琀漀爀攀 戀氀漀戀猀 漀渀 昀椀氀攀猀礀猀琀攀洀猀⤀㰀戀爀㸀ഀഀ At least a certain gain in performance is expected (due to multiple spindles, if used). ਍       㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀㌀⸀ 䈀礀 䐀䈀䄀 瀀爀漀瘀椀猀椀漀渀攀搀 搀愀琀愀戀愀猀攀㰀⼀吀䐀㸀ഀഀ ਍      㰀吀刀㸀ഀഀ ਍            㰀吀䐀㸀㰀䈀㸀唀猀椀渀最 䔀䈀匀⼀刀䈀匀㨀夀䔀匀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
਍                䤀渀搀攀瀀攀渀搀攀搀 漀昀 搀愀琀愀戀愀猀攀 氀愀礀漀甀琀 漀昀 昀椀氀攀猀㨀㰀戀爀㸀ഀഀ
਍                 䐀椀昀昀攀爀攀渀琀椀愀琀椀漀渀 椀猀 瀀漀猀猀椀戀氀攀 ⠀甀猀椀渀最 䔀䈀匀⼀刀䈀匀 挀爀椀琀攀爀椀愀 昀椀氀攀琀礀瀀攀⼀猀椀稀攀⤀㰀⼀吀䐀㸀ഀഀ ਍      㰀吀刀㸀ഀഀ ਍            㰀吀䐀㸀㰀䈀㸀唀猀椀渀最 䔀䈀匀⼀刀䈀匀⼀匀栀愀爀攀倀漀椀渀琀 䘀椀氀攀 匀栀愀爀攀 䰀椀戀爀愀爀礀㨀一伀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
਍                吀栀攀 匀栀愀爀攀瀀漀椀渀琀 䄀搀洀椀渀 挀愀爀攀昀甀氀氀礀 愀猀猀椀最渀猀 愀 猀瀀攀挀椀昀椀挀 搀愀琀愀戀愀猀攀猀 琀漀 猀瀀攀挀椀昀椀挀 圀攀戀䄀瀀瀀猀⼀䌀漀氀氀攀挀琀椀漀渀猀Ⰰ㰀戀爀㸀ഀഀ while keeping in mind (if possible) which site is for large objects (and thus using it's
਍                猀瀀攀挀椀昀椀挀 搀愀琀愀戀愀猀攀⤀Ⰰ 愀渀搀 眀栀椀挀栀 椀猀 昀漀爀 挀椀爀椀琀椀挀愀氀 戀甀猀椀渀攀猀猀 搀漀挀甀洀攀渀琀猀 ⠀愀渀搀 琀栀甀猀 甀猀椀渀最 椀琀✀猀㰀戀爀㸀ഀഀ own specific database also)
਍                㰀戀爀㸀ഀഀ In cooperation with DBA and Storage Admins, LUNS can be assigned in accordance with
਍                挀爀椀琀椀挀愀氀 愀瀀瀀猀 ⠀挀爀椀琀椀挀愀氀 猀琀漀爀愀最攀⼀栀椀最栀 戀愀挀欀甀瀀 昀爀攀焀⤀ 愀渀搀 氀攀猀猀 挀爀椀琀椀挀愀氀 愀瀀瀀猀 ⠀氀攀猀猀 攀砀瀀攀渀猀椀瘀攀㰀戀爀㸀ഀഀ storage and lower backup freq.)
਍                 㰀戀爀㸀ഀഀ Differentiation is possible. ਍      㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀㘀⸀ ⴀ䐀攀昀愀甀氀琀㨀 匀栀愀爀攀倀漀椀渀琀 瀀爀漀瘀椀猀椀漀渀攀搀㰀戀爀㸀ഀഀ OR
਍                ⴀ戀礀 䐀䈀䄀 瀀爀漀瘀椀猀椀漀渀攀搀 搀愀琀愀戀愀猀攀㰀⼀吀䐀㸀ഀഀ ਍㰀⼀吀䄀䈀䰀䔀㸀ഀഀ
਍㰀戀爀㸀ഀഀ

4. Some quick notes on specific Sharepoint objects:

਍ ഀഀ ਍ ഀഀ ਍㰀䈀㸀㐀⸀㄀ 匀栀漀爀琀 搀攀猀挀爀椀瀀琀椀漀渀 匀栀愀爀攀瀀漀椀渀琀 䐀愀琀愀戀愀猀攀猀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ See section 1.
਍㰀戀爀㸀ഀഀ 4.2 Relation of Web application, to Site collection, and content database:
਍㰀戀爀㸀ഀഀ ਍圀攀戀 䄀瀀瀀氀椀挀愀琀椀漀渀 ⠀圀攀戀䄀瀀瀀⤀ 㸀 匀椀琀攀 䌀漀氀氀攀挀琀椀漀渀 㴀 吀漀瀀 䰀攀瘀攀氀 猀椀琀攀 ⬀ 漀瀀琀椀漀渀愀氀 猀甀戀猀椀琀攀猀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ A Web Application hosts Site Collection(s). ਍匀椀琀攀猀 椀渀 愀 猀椀琀攀 挀漀氀氀攀挀琀椀漀渀Ⰰ 猀栀愀爀攀 愀搀洀椀渀椀猀琀爀愀琀椀瘀攀 猀攀琀琀椀渀最猀㰀戀爀㸀ഀഀ
਍圀栀攀渀 礀漀甀 挀爀攀愀琀攀 愀 圀攀戀䄀瀀瀀Ⰰ 礀漀甀 猀瀀攀挀椀昀礀㨀㰀戀爀㸀ഀഀ - Name of the IIS website (toplevel site)
਍ⴀ 瀀漀爀琀Ⰰ 瀀愀琀栀 漀昀 琀栀攀 瘀椀爀琀甀愀氀 搀椀爀攀挀漀爀礀Ⰰ 琀栀攀 甀爀氀Ⰰ 琀栀攀 猀攀挀甀爀椀琀礀 猀攀琀琀椀渀最猀 攀琀挀⸀⸀㰀戀爀㸀ഀഀ - Database Server and database name.
਍㰀戀爀㸀ഀഀ From the Adminatrative webpage, the Admin can create the above from an easy to use Admin console.
਍一漀琀攀 琀栀愀琀 琀栀攀 爀攀氀愀琀椀漀渀 漀昀 琀栀攀 挀栀漀椀挀攀 漀昀 琀栀攀 挀漀渀琀攀渀琀 搀愀琀愀戀愀猀攀Ⰰ 爀攀氀愀琀攀猀 琀漀 琀栀攀 圀攀戀䄀瀀瀀⼀匀椀琀攀 挀漀氀氀攀挀琀椀漀渀⸀㰀戀爀㸀ഀഀ
਍倀攀爀 搀攀昀愀甀氀琀Ⰰ 愀 䌀漀渀琀攀渀琀 搀愀琀愀戀愀猀攀 椀猀 挀爀攀愀琀攀搀 昀漀爀 攀愀挀栀 圀攀戀 䄀瀀瀀氀椀挀愀琀椀漀渀⸀㰀戀爀㸀ഀഀ
਍䄀氀猀漀Ⰰ 搀椀爀攀挀琀氀礀 愀昀琀攀爀 挀爀攀愀琀椀漀渀 漀昀 琀栀攀 圀攀戀䄀瀀瀀Ⰰ 椀琀 椀猀 瀀漀猀猀椀戀氀攀 琀漀 愀琀琀愀挀栀 琀栀攀 圀攀戀䄀瀀瀀 琀漀 愀渀漀琀栀攀爀 搀愀琀愀戀愀猀攀㰀戀爀㸀ഀഀ of your choice (the former will be deleted, and the new one populated with standard sharepoint objects).
਍夀漀甀 挀愀渀 搀漀 琀栀愀琀 甀猀椀渀最 瀀漀眀攀爀猀栀攀氀氀Ⰰ 漀爀 猀椀洀瀀氀礀 戀礀 甀猀椀渀最 琀栀攀 眀攀戀戀愀猀攀搀 䄀搀洀椀渀椀猀琀爀愀琀椀漀渀 䌀漀渀猀漀氀攀⸀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ 4.3 Libraries and Lists:
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍伀渀 愀 猀椀琀攀Ⰰ 礀漀甀 挀愀渀 猀栀漀眀 搀漀挀甀洀攀渀琀猀 椀渀 愀渀 漀爀最愀渀椀稀攀搀 眀愀礀Ⰰ 甀猀椀渀最 氀椀戀爀愀爀椀攀猀⸀㰀戀爀㸀ഀഀ For example, you can create a library of common documents for a project, and project team members
਍挀愀渀 甀猀攀 琀栀攀椀爀 挀氀椀攀渀琀 琀漀漀氀猀 琀漀 昀椀渀搀 搀漀挀甀洀攀渀琀猀Ⰰ 攀搀椀琀 琀栀攀洀Ⰰ 愀渀搀 挀栀攀挀欀椀渀⼀挀栀攀挀欀漀甀琀 琀栀漀猀攀 昀椀氀攀猀⸀㰀戀爀㸀ഀഀ Once created, a library may somewhat show "the look and feel" of a fileshare, but it is not.
਍吀栀攀 昀椀氀攀猀 愀爀攀 攀椀琀栀攀爀 猀琀漀爀攀搀 椀渀 琀栀攀 匀儀䰀 匀攀爀瘀攀爀 挀漀渀琀攀渀琀 搀愀琀愀戀愀猀攀Ⰰ 漀爀Ⰰ 椀昀 甀猀椀渀最 䔀䈀匀⼀刀䈀匀Ⰰ 漀渀 愀渀 攀砀琀攀爀渀愀氀 猀琀漀爀愀最攀 猀礀猀琀攀洀⸀㰀戀爀㸀ഀഀ
਍䔀愀挀栀 氀椀戀爀愀爀礀 搀椀猀瀀氀愀礀猀 愀 氀椀猀琀 漀昀 昀椀氀攀猀 愀渀搀 搀攀猀挀爀椀瀀琀椀漀渀猀 愀戀漀甀琀 琀栀攀 昀椀氀攀猀Ⰰ 眀栀椀挀栀 栀攀氀瀀猀 甀猀攀爀猀 琀漀 甀猀攀 琀栀攀 昀椀氀攀猀Ⰰ㰀戀爀㸀ഀഀ and to work together. So, the sharepoint Admin controls how documents are viewed, tracked, managed, and created.
਍䄀氀猀漀Ⰰ 礀漀甀 挀愀渀 甀猀攀 眀漀爀欀昀氀漀眀猀 琀漀 攀渀栀愀渀挀攀 挀漀氀氀愀戀漀爀愀琀椀漀渀 漀渀 搀漀挀甀洀攀渀琀猀 椀渀 氀椀戀爀愀爀椀攀猀⸀㰀戀爀㸀ഀഀ ਍ ഀഀ ਍㰀戀爀㸀ഀഀ ਍㰀䈀㸀㐀⸀㐀 匀栀愀爀攀瀀漀椀渀琀 猀攀爀瘀椀挀攀猀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ 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
਍愀氀 猀漀爀琀猀 漀昀 昀甀渀挀琀椀漀渀愀氀椀琀礀 椀渀 琀栀椀猀 攀渀瘀椀爀漀渀洀攀渀琀⸀㰀戀爀㸀ഀഀ Besides those services, in a Windows environment we can expect to find some true background "services"
਍愀猀 眀攀氀氀⸀ 吀栀攀猀攀 琀栀攀渀 愀爀攀 琀栀攀 猀攀爀瘀椀挀攀猀 礀漀甀 挀愀渀 猀攀攀Ⰰ 愀渀搀 挀漀渀琀爀漀氀Ⰰ 甀猀椀渀最 瘀愀爀椀漀甀猀 圀椀渀搀漀眀猀 愀搀洀椀渀 琀漀漀氀猀 氀椀欀攀㰀戀爀㸀ഀഀ 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).
਍䤀渀 愀 匀栀愀爀攀瀀漀椀渀琀 㜀 攀渀瘀椀爀漀渀洀攀渀琀Ⰰ 眀攀 猀栀漀甀氀搀 攀砀瀀攀挀琀 愀琀 氀攀愀猀琀 琀栀攀 昀漀氀氀漀眀椀渀最 猀攀爀瘀椀挀攀猀㨀㰀戀爀㸀ഀഀ ਍ ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍匀䔀刀嘀䤀䌀䔀开一䄀䴀䔀㨀 伀匀攀愀爀挀栀㰀戀爀㸀ഀഀ DISPLAY_NAME: Office SharePoint Server Search
਍㰀戀爀㸀 ഀഀ SERVICE_NAME: SPAdmin
਍䐀䤀匀倀䰀䄀夀开一䄀䴀䔀㨀 圀椀渀搀漀眀猀 匀栀愀爀攀倀漀椀渀琀 匀攀爀瘀椀挀攀猀 䄀搀洀椀渀椀猀琀爀愀琀椀漀渀㰀戀爀㸀ഀഀ
਍匀䔀刀嘀䤀䌀䔀开一䄀䴀䔀㨀 匀倀匀攀愀爀挀栀㰀戀爀㸀ഀഀ DISPLAY_NAME: Windows SharePoint Services Search
਍㰀戀爀㸀 ഀഀ SERVICE_NAME: SPTimerV3
਍䐀䤀匀倀䰀䄀夀开一䄀䴀䔀㨀 圀椀渀搀漀眀猀 匀栀愀爀攀倀漀椀渀琀 匀攀爀瘀椀挀攀猀 吀椀洀攀爀㰀戀爀㸀ഀഀ
਍匀䔀刀嘀䤀䌀䔀开一䄀䴀䔀㨀 匀倀吀爀愀挀攀㰀戀爀㸀ഀഀ DISPLAY_NAME: Windows SharePoint Services Tracing
਍㰀戀爀㸀ഀഀ ਍䐀攀瀀攀渀搀椀渀最 漀渀 琀栀攀 猀椀稀攀 漀昀 琀栀攀 椀渀猀琀愀氀氀愀琀椀漀渀 ⠀眀栀愀琀 昀攀愀琀甀爀攀猀 愀爀攀 愀挀琀椀瘀攀⤀Ⰰ 琀栀椀猀 氀椀猀琀 洀椀最栀琀 戀攀 猀漀洀攀眀栀愀琀 氀漀渀最攀爀㰀戀爀㸀ഀഀ 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:
਍㄀⸀ 圀攀戀猀攀爀瘀攀爀猀 眀椀琀栀 䤀䤀匀 愀渀搀 愀氀 猀漀爀琀猀 漀昀 匀栀愀爀攀瀀漀椀渀琀 爀攀氀愀琀攀搀 椀渀昀漀爀洀愀琀椀漀渀 甀渀搀攀爀 ∀眀眀眀爀漀漀琀∀⸀㰀戀爀㸀ഀഀ 2. Sharepoint as the middle tier.
਍㌀⸀ 匀儀䰀 匀攀爀瘀攀爀 愀猀 琀栀攀 戀愀挀欀攀渀搀 搀愀琀愀戀愀猀攀 攀渀最椀渀攀⸀㰀戀爀㸀ഀഀ
਍䠀漀眀攀瘀攀爀Ⰰ 漀昀琀攀渀 ⠀㄀⤀ 愀渀搀 ⠀㈀⤀ 愀爀攀 漀昀琀攀渀 挀漀洀戀椀渀攀搀 漀渀 琀栀攀 猀愀洀攀 洀愀挀栀椀渀攀Ⰰ 眀栀椀氀攀 洀甀氀琀椀瀀氀攀 漀昀 琀栀漀猀攀 洀愀挀栀椀渀攀猀㰀戀爀㸀ഀഀ are collected in a "farm".
਍㰀戀爀㸀ഀഀ Although you can control, and modify operations on the services by using Windows tools (like services.msc),
਍渀漀琀 愀氀氀 漀瀀攀爀愀琀椀漀渀猀 愀爀攀 愀氀氀漀眀攀搀 甀猀椀渀最 琀栀攀 猀琀愀渀搀愀爀搀 琀漀漀氀猀⸀㰀戀爀㸀ഀഀ For example:
਍㰀戀爀㸀ഀഀ -Starting and stopping a service is allowed using Windows tools (like using "net start" or "net stop").
਍ⴀ䘀漀爀 愀搀樀甀猀琀椀渀最 愀挀挀漀甀渀琀 椀渀昀漀爀洀愀琀椀漀渀 昀漀爀 琀栀攀 猀攀爀瘀椀挀攀猀Ⰰ 礀漀甀 猀栀漀甀氀搀 甀猀攀 匀栀愀爀攀瀀漀椀渀琀 漀眀渀 琀漀漀氀猀 氀椀欀攀 ∀猀琀猀愀搀洀∀ ⠀匀栀愀爀攀瀀漀椀渀琀㜀⤀㰀戀爀㸀ഀഀ 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),
਍琀愀欀攀 愀 氀漀漀欀 愀琀 琀栀攀 昀漀氀氀漀眀椀渀最 攀砀愀洀瀀氀攀 猀琀漀瀀 猀挀爀椀瀀琀 ⠀挀洀搀⤀ 眀栀攀爀攀 眀攀 愀猀猀甀洀攀 琀椀攀爀猀 ⠀㄀⤀☀⠀㈀⤀ 愀爀攀 漀渀 漀渀攀 洀愀挀栀椀渀攀㨀㰀戀爀㸀ഀഀ
਍渀攀琀 猀琀漀瀀 眀㌀猀瘀挀㰀戀爀㸀ഀഀ net stop iisadmin
਍渀攀琀 猀琀漀瀀 猀猀漀猀爀瘀㰀戀爀㸀ഀഀ net stop DCLauncher
਍渀攀琀 猀琀漀瀀 䐀䌀䰀漀愀搀䈀愀氀愀渀挀攀爀㰀戀爀㸀ഀഀ net stop OSearch
਍渀攀琀 猀琀漀瀀 匀倀䄀搀洀椀渀㰀戀爀㸀ഀഀ net stop SPSearch
਍渀攀琀 猀琀漀瀀 匀倀吀椀洀攀爀嘀㌀㰀戀爀㸀ഀഀ net stop SPTrace
਍渀攀琀 猀琀漀瀀 匀倀圀爀椀琀攀爀㰀戀爀㸀ഀഀ iisreset /stop
਍㰀戀爀㸀ഀഀ
਍㰀栀㌀㸀㔀⸀ 䴀攀琀愀搀愀琀愀Ⰰ 瀀漀椀渀琀攀爀猀Ⰰ 愀渀搀 䈀䰀伀䈀猀㨀㰀⼀栀㌀㸀ഀഀ ਍䰀攀琀✀猀 昀椀爀猀琀 猀攀攀 栀漀眀 匀儀䰀 匀攀爀瘀攀爀 猀琀漀爀攀猀 戀氀漀戀猀 椀渀猀椀搀攀 琀栀攀 搀愀琀愀戀愀猀攀⸀ 匀漀Ⰰ 栀攀爀攀 眀攀 眀愀渀琀 琀漀 攀砀瀀氀漀爀攀 琀栀攀 搀攀昀愀甀氀琀㰀戀爀㸀ഀഀ 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:
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍㰀䈀㸀㰀唀㸀㔀⸀㄀⸀㄀ 䴀攀琀愀搀愀琀愀 漀渀 搀漀挀甀洀攀渀琀猀 ⠀漀爀 戀氀漀戀猀⤀㰀⼀唀㸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
਍䤀渀 匀儀䰀 ㈀䬀㔀⼀㈀䬀㠀Ⰰ 眀攀 挀愀渀 琀爀礀 愀 挀漀甀瀀氀攀 漀昀 猀琀愀渀搀愀爀搀 焀甀攀爀椀攀猀Ⰰ 椀渀 漀爀搀攀爀 琀漀 昀椀渀搀 ∀愀氀椀攀渀∀ 漀戀樀攀挀琀猀Ⰰ㰀戀爀㸀 ഀഀ in this case, originating from Sharepoint.
਍唀猀椀渀最 琀栀攀 昀漀氀氀漀眀椀渀最 焀甀攀爀礀 ⠀椀渀 愀 䌀漀渀琀攀渀琀 搀愀琀愀戀愀猀攀⤀Ⰰ 眀攀 最攀琀 愀渀 椀洀瀀爀攀猀猀椀漀渀 漀昀 漀戀樀攀挀琀猀 ⠀氀椀欀攀 琀愀戀氀攀猀Ⰰ 瘀椀攀眀猀Ⰰ 猀瀀✀猀⤀㰀戀爀㸀ഀഀ which are not installed at a default installation of SQL Server. So, they are installed afterwards,
਍氀椀欀攀 琀栀攀 愀搀搀椀渀最 漀昀 搀愀琀愀戀愀猀攀 漀戀樀攀挀琀猀 琀栀愀琀 爀攀猀甀氀琀猀 昀爀漀洀 愀 挀爀攀愀琀椀漀渀 漀昀 愀 圀攀戀䄀瀀瀀 椀渀 匀栀愀爀攀瀀漀椀渀琀⸀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ select substring(name,1,20) as "Name", substring(type_desc,1,20) as "Type", create_date
਍昀爀漀洀 猀礀猀⸀愀氀氀开漀戀樀攀挀琀猀 眀栀攀爀攀 椀猀开洀猀开猀栀椀瀀瀀攀搀㴀　 愀渀搀 琀礀瀀攀开搀攀猀挀㴀✀嘀䤀䔀圀✀ 漀爀 琀礀瀀攀开搀攀猀挀㴀✀唀匀䔀刀开吀䄀䈀䰀䔀✀㰀戀爀㸀ഀഀ and name not like '%CI_AS' order by name -- in my case I want to exclude some collation tables
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍吀栀椀猀 瀀爀漀搀甀挀攀猀 ⠀椀渀 洀礀 挀愀猀攀⤀ 愀 氀椀猀琀 氀椀欀攀㨀㰀戀爀㸀ഀഀ
਍㰀吀䄀䈀䰀䔀 戀漀爀搀攀爀㴀㄀㸀ഀഀ ਍            㰀吀䐀㸀一愀洀攀㰀⼀吀䐀㸀㰀吀䐀㸀吀礀瀀攀㰀⼀吀䐀㸀ഀഀ ਍            㰀吀刀㸀ഀഀ ਍            㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀䄀氀氀䐀漀挀匀琀爀攀愀洀猀㰀⼀吀䐀㸀㰀吀䐀㸀唀匀䔀刀开吀䄀䈀䰀䔀㰀⼀吀䐀㸀ഀഀ ਍            㰀吀刀㸀ഀഀ ਍            㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀䄀氀氀䰀椀渀欀猀㰀⼀吀䐀㸀㰀吀䐀㸀唀匀䔀刀开吀䄀䈀䰀䔀㰀⼀吀䐀㸀ഀഀ ਍            㰀吀刀㸀ഀഀ ਍            㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀䄀氀氀唀猀攀爀䐀愀琀愀㰀⼀吀䐀㸀㰀吀䐀㸀唀匀䔀刀开吀䄀䈀䰀䔀㰀⼀吀䐀㸀ഀഀ ਍            㰀吀刀㸀ഀഀ ਍            㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀⸀⸀㰀⼀吀䐀㸀㰀吀䐀㸀⸀⸀㰀⼀吀䐀㸀ഀഀ ਍            㰀吀刀㸀ഀഀ ਍            㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀䐀漀挀匀琀爀攀愀洀猀㰀⼀吀䐀㸀㰀吀䐀㸀嘀䤀䔀圀㰀⼀吀䐀㸀ഀഀ ਍            㰀吀刀㸀ഀഀ ਍            㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀⸀⸀㰀⼀吀䐀㸀㰀吀䐀㸀⸀⸀㰀⼀吀䐀㸀ഀഀ ਍            㰀吀刀㸀ഀഀ ਍            㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀匀椀琀攀猀㰀⼀吀䐀㸀㰀吀䐀㸀唀匀䔀刀开吀䄀䈀䰀䔀㰀⼀吀䐀㸀ഀഀ ਍            㰀吀刀㸀ഀഀ ਍            㰀⼀吀刀㸀ഀഀ ਍            㰀吀䐀㸀圀漀爀欀昀氀漀眀㰀⼀吀䐀㸀㰀吀䐀㸀唀匀䔀刀开吀䄀䈀䰀䔀㰀⼀吀䐀㸀ഀഀ ਍㰀⼀吀䄀䈀䰀䔀㸀ഀഀ ਍ ഀഀ ਍㰀戀爀㸀ഀഀ 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.
਍吀栀攀渀Ⰰ 愀昀琀攀爀 猀漀洀攀 椀渀瘀攀猀琀椀最愀琀椀漀渀Ⰰ 眀攀 挀愀渀 挀氀攀愀爀氀礀 猀攀攀 琀栀愀琀 琀栀攀 ∀䄀氀氀搀漀挀猀∀ 愀渀搀 ∀䄀氀氀搀漀挀匀琀爀攀愀洀猀∀ 琀愀戀氀攀猀Ⰰ㰀戀爀㸀ഀഀ gives us all we need to know about "id's" (of blobs), the "content" of the blob, the "size", the associated
਍∀猀椀琀攀开椀搀∀ 眀栀椀挀栀 甀猀攀猀 琀栀椀猀 戀氀漀戀Ⰰ 愀渀搀 洀甀挀栀 洀漀爀攀⸀㰀戀爀㸀ഀഀ Joining these tables with the AllLists view, will reveal the list or library (and other information) too.
਍㰀戀爀㸀ഀഀ ਍夀漀甀 挀愀渀 琀爀礀 昀漀爀 攀砀愀洀瀀氀攀 琀栀攀 昀漀氀氀漀眀椀渀最 琀栀爀攀攀 焀甀攀爀椀攀猀 琀漀 漀戀琀愀椀渀 氀漀琀猀 漀昀 椀渀昀漀爀洀愀琀椀漀渀 愀戀漀甀琀 琀栀攀 搀漀挀甀洀攀渀琀 猀琀漀爀愀最攀㰀戀爀㸀ഀഀ within a content database:
਍㰀戀爀㸀 ഀഀ ਍ ഀഀ USE [YourContentDatabase]
਍䜀伀㰀戀爀㸀ഀഀ
਍ⴀⴀ 儀甀攀爀礀 ㄀㨀㰀戀爀㸀ഀഀ
਍匀䔀䰀䔀䌀吀 䄀氀氀䰀椀猀琀猀⸀琀瀀开吀椀琀氀攀 䄀匀 ✀䰀椀猀琀 一愀洀攀✀Ⰰ㰀戀爀㸀ഀഀ AllDocs.LeafName AS 'File Name',
਍䄀氀氀䐀漀挀猀⸀䐀椀爀一愀洀攀 䄀匀 ✀唀刀䰀✀Ⰰ㰀戀爀㸀ഀഀ AllDocStreams.Content AS 'Document Content (Binary)',
਍䄀氀氀䐀漀挀匀琀爀攀愀洀猀⸀匀椀稀攀 䄀匀 ✀匀椀稀攀✀㰀戀爀㸀ഀഀ FROM AllDocs
਍䨀伀䤀一 㰀䈀㸀䄀氀氀䐀漀挀匀琀爀攀愀洀猀㰀⼀䈀㸀 㰀戀爀㸀ഀഀ ON
਍䄀氀氀䐀漀挀猀⸀䤀搀㴀䄀氀氀䐀漀挀匀琀爀攀愀洀猀⸀䤀搀 㰀戀爀㸀ഀഀ JOIN AllLists
਍伀一㰀戀爀㸀ഀഀ AllLists.tp_id = AllDocs.ListId
਍伀刀䐀䔀刀 䈀夀 䄀氀氀䐀漀挀匀琀爀攀愀洀猀⸀匀椀稀攀 䐀䔀匀䌀㰀戀爀㸀ഀഀ
਍ⴀⴀ 儀甀攀爀礀 ㈀㨀㰀戀爀㸀ഀഀ
਍匀䔀䰀䔀䌀吀 䄀氀氀䐀漀挀匀琀爀攀愀洀猀⸀䤀搀Ⰰ 䄀氀氀䐀漀挀匀琀爀攀愀洀猀⸀嬀䌀漀渀琀攀渀琀崀Ⰰ 䄀氀氀䐀漀挀匀琀爀攀愀洀猀⸀匀椀稀攀Ⰰ 䄀氀氀䐀漀挀猀⸀嘀攀爀猀椀漀渀Ⰰ 䄀氀氀䐀漀挀猀⸀吀椀洀攀䰀愀猀琀䴀漀搀椀昀椀攀搀Ⰰ 㰀戀爀㸀ഀഀ AllDocs.CheckoutUserId, AllDocs.CheckoutDate, AllDocs.IsCurrentVersion, AllDocs.DirName,
਍䄀氀氀䐀漀挀猀⸀䰀攀愀昀一愀洀攀Ⰰ 䄀氀氀䐀漀挀猀⸀嬀䰀攀瘀攀氀崀㰀戀爀㸀ഀഀ FROM AllDocs INNER JOIN
਍䄀氀氀䐀漀挀匀琀爀攀愀洀猀 伀一 䄀氀氀䐀漀挀匀琀爀攀愀洀猀⸀䤀搀 㴀 䄀氀氀䐀漀挀猀⸀䤀搀 䄀一䐀 䄀氀氀䐀漀挀猀⸀嬀䰀攀瘀攀氀崀 㴀 䄀氀氀䐀漀挀匀琀爀攀愀洀猀⸀嬀䰀攀瘀攀氀崀㰀戀爀㸀ഀഀ WHERE AllDocs.TimeLastModified > getdate() - 30 -- use here as getdate()- number_of_days_ago
਍伀刀䐀䔀刀 戀礀 䄀氀氀䐀漀挀猀⸀吀椀洀攀䰀愀猀琀䴀漀搀椀昀椀攀搀㰀戀爀㸀ഀഀ
਍ⴀⴀ 儀甀攀爀礀 ㌀㨀 匀甀瀀瀀漀猀攀 礀漀甀 眀愀渀琀 椀渀昀漀爀洀愀琀椀漀渀 漀渀 ⸀搀漀挀Ⰰ ⸀砀氀猀Ⰰ 愀渀搀 ⸀瀀搀昀 昀椀氀攀猀㨀㰀戀爀㸀ഀഀ
਍匀䔀䰀䔀䌀吀 嬀䄀氀氀䐀漀挀猀崀⸀嬀䐀椀爀一愀洀攀崀Ⰰ 嬀䄀氀氀䐀漀挀猀崀⸀嬀䰀攀愀昀一愀洀攀崀Ⰰ 嬀䄀氀氀䐀漀挀匀琀爀攀愀洀猀崀⸀嬀䌀漀渀琀攀渀琀崀㰀戀爀㸀ഀഀ from [AllDocs],[AllDocStreams] where (LeafName like ‘%.doc’ or LeafName like ‘%.xls’ or LeafName like ‘%.pdf’)
਍愀渀搀 嬀䄀氀氀䐀漀挀匀琀爀攀愀洀猀崀⸀嬀䌀漀渀琀攀渀琀崀 椀猀 渀漀琀 一唀䰀䰀 愀渀搀 嬀䄀氀氀䐀漀挀猀崀⸀嬀䤀搀崀 㴀 嬀䄀氀氀䐀漀挀匀琀爀攀愀洀猀崀⸀嬀䤀搀崀ᰀ㰠戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍夀漀甀 挀愀渀 焀甀攀爀礀 漀渀 猀瀀攀挀椀昀椀挀 圀攀戀猀 愀渀搀 匀椀琀攀猀 愀猀 眀攀氀氀Ⰰ 眀栀攀爀攀 礀漀甀 愀爀攀 渀漀琀 昀漀挀甀猀椀渀最 漀渀 搀漀挀甀洀攀渀琀 椀渀昀漀爀洀愀琀椀漀渀Ⰰ㰀戀爀㸀ഀഀ but you for example just want to obtain lists of Webapps and sites. For example:
਍㰀戀爀㸀ഀഀ ਍匀䔀䰀䔀䌀吀 䤀搀Ⰰ 伀眀渀攀爀䤀搀Ⰰ 吀椀洀攀䌀爀攀愀琀攀搀Ⰰ 刀漀漀琀圀攀戀䤀搀 䘀刀伀䴀 匀椀琀攀猀㰀戀爀㸀ഀഀ ਍ ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍ⴀ 吀栀攀 㰀䈀㸀∀䄀氀氀䐀漀挀猀∀㰀⼀䈀㸀 吀愀戀氀攀 爀攀最猀椀琀攀爀猀 氀漀琀✀猀 漀昀 洀攀琀愀搀愀琀愀 愀戀漀甀琀 搀漀挀甀洀攀渀琀猀Ⰰ 氀椀欀攀 搀漀挀甀洀攀渀琀 椀搀Ⰰ 猀椀琀攀 椀搀Ⰰ 圀攀戀椀搀Ⰰ㰀戀爀㸀ഀഀ TimeLastWritten, Version, and much more.
਍ⴀ 吀栀攀 㰀䈀㸀∀䄀氀氀䐀漀挀匀琀爀攀愀洀猀∀㰀⼀䈀㸀 吀愀戀氀攀 爀攀最椀猀琀攀爀猀 愀氀氀 搀漀挀甀洀攀渀琀 椀搀✀猀Ⰰ 愀猀猀漀挀椀愀琀攀搀 猀椀琀攀 椀搀✀猀Ⰰ 戀氀漀戀 挀漀渀琀攀渀琀猀Ⰰ㰀戀爀㸀 ഀഀ document sizes in bytes, Parent id's (The identifier of the document's parent container).
਍ⴀ 吀栀攀 㰀䈀㸀∀䄀氀氀唀猀攀爀䐀愀琀愀∀㰀⼀䈀㸀 吀愀戀氀攀 爀攀最椀猀琀攀爀猀 搀愀琀愀 昀漀爀 愀渀礀 椀琀攀洀 愀猀猀漀挀椀愀琀攀搀 眀椀琀栀 愀渀礀 氀椀猀琀ⴀ 愀渀搀 搀漀挀甀洀攀渀琀 氀椀戀爀愀爀礀氀椀猀琀猀⸀㰀戀爀㸀ഀഀ - The "AllLists" Table contains information of SharePoint lists such as list’s name,
਍氀椀猀琀ᤀ猠 搀攀猀挀爀椀瀀琀椀漀渀 愀渀搀 琀栀攀 氀椀猀琀ᤀ猠 椀琀攀洀 挀漀甀渀琀Ⰰ 愀渀搀 洀漀爀攀⸀㰀戀爀㸀ഀഀ
਍㰀戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ 5.2 Blob inline storage (No EBS/RBS):
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀 ഀഀ
਍䤀琀✀猀 眀攀氀氀 欀渀漀眀渀 琀栀愀琀 匀儀䰀 匀攀爀瘀攀爀 椀猀 漀瀀琀椀洀椀稀攀搀 昀漀爀 愀猀挀椀椀 漀爀椀攀渀琀攀搀 猀琀漀爀愀最攀 ⠀琀礀瀀椀挀愀氀 愀搀洀椀渀椀猀琀爀琀椀瘀攀 搀愀琀愀⤀⸀㰀戀爀㸀ഀഀ Don't forget that BLOBs and relational database data are very different entities.
਍䴀椀挀爀漀猀漀昀琀 椀琀猀攀氀昀 爀攀昀攀爀猀 琀漀 琀栀椀猀 欀椀渀搀 漀昀 挀漀渀琀攀渀琀Ⰰ 愀猀 㰀䈀㸀甀渀猀琀爀甀挀琀甀爀攀搀 搀愀琀愀㰀⼀䈀㸀Ⰰ 愀猀 漀瀀瀀漀猀攀搀 琀漀 琀栀攀 洀漀爀攀 猀琀爀甀挀琀甀爀攀搀Ⰰ㰀戀爀㸀ഀഀ 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
਍ⴀ 瘀愀爀戀椀渀愀爀礀⠀洀愀砀⤀㨀 甀瀀 琀漀 ㈀䜀Ⰰ 攀⸀最⸀㨀 琀漀 猀琀漀爀攀 瀀搀昀Ⰰ 眀漀爀搀 昀椀氀攀猀Ⰰ 椀洀愀最攀猀Ⰰ 漀爀 漀琀栀攀爀 戀椀渀愀爀礀 搀愀琀愀㰀戀爀㸀ഀഀ The storage size is the actual length of the data entered + 2 bytes.
਍ⴀ 戀椀渀愀爀礀㨀 䘀椀砀攀搀ⴀ氀攀渀最琀栀 戀椀渀愀爀礀 搀愀琀愀 眀椀琀栀 愀 氀攀渀最琀栀 漀昀 渀 戀礀琀攀猀Ⰰ 眀栀攀爀攀 渀 椀猀 愀 瘀愀氀甀攀 昀爀漀洀 ㄀ 琀栀爀漀甀最栀 㠀Ⰰ　　　⸀㰀戀爀㸀ഀഀ
਍伀爀 眀攀 挀愀渀 甀猀攀 䘀䤀䰀䔀匀吀刀䔀䄀䴀 匀琀漀爀愀最攀⸀ 䘀䤀䰀䔀匀吀刀䔀䄀䴀 椀猀 愀 猀琀漀爀愀最攀 愀琀琀爀椀戀甀琀攀 漀昀 琀栀攀 攀砀椀猀琀椀渀最 嘀䄀刀䈀䤀一䄀刀夀⠀䴀䄀堀⤀ 搀愀琀愀 琀礀瀀攀⸀㰀戀爀㸀ഀഀ FILESTREAM alters how the BLOB data is stored—in the file system rather than in the SQL Server data files.
਍䠀漀眀攀瘀攀爀Ⰰ 琀栀攀 䘀椀氀攀最爀漀甀瀀 猀甀瀀瀀漀爀琀椀渀最 琀栀攀 䘀䤀䰀䔀匀吀刀䔀䄀䴀 搀愀琀愀Ⰰ 椀猀 樀甀猀琀 愀 搀愀琀愀戀愀猀攀 昀椀氀攀⠀猀⤀ 漀渀 琀栀攀 猀愀洀攀 䰀唀一⠀猀⤀⼀䐀椀猀欀⠀猀⤀㰀戀爀㸀ഀഀ 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.
਍匀漀Ⰰ 椀渀 匀儀䰀 ㈀䬀㔀⼀㈀䬀㠀Ⰰ 椀洀愀最攀 椀猀 猀琀椀氀氀 愀瘀愀椀氀愀戀氀攀Ⰰ 戀甀琀 琀栀攀 ∀瘀愀爀戀椀渀愀爀礀∀ 椀猀 琀栀攀 戀攀猀琀 琀礀瀀攀 昀漀爀 猀琀漀爀愀最攀 漀昀 愀渀礀 琀礀瀀攀 漀昀 戀氀漀戀⸀㰀戀爀㸀ഀഀ
਍一漀琀 甀猀椀渀最 刀䈀匀Ⰰ 琀栀攀 䈀䰀伀䈀 椀猀 爀攀最椀猀琀攀爀攀搀 愀猀 猀琀漀爀攀搀 椀渀 琀栀攀 䌀漀渀琀攀渀琀 䐀愀琀愀戀愀猀攀 椀渀 琀栀攀 㰀䈀㸀䄀氀氀䐀漀挀匀琀爀攀愀洀猀㰀⼀䈀㸀 琀愀戀氀攀Ⰰ㰀戀爀㸀ഀഀ in a varbinary field called Content (in Sharepoint7 it was the image datatype).
਍䔀愀挀栀 搀漀挀甀洀攀渀琀 瘀攀爀猀椀漀渀 挀漀甀渀琀猀 愀猀 愀 爀攀挀漀爀搀Ⰰ 愀渀搀 攀瘀攀渀 琀栀攀 䌀漀渀琀攀渀琀猀 昀椀攀氀搀 椀猀 爀攀瀀氀椀挀愀琀攀搀Ⰰ 攀瘀攀渀 椀昀 漀渀氀礀 愀 洀攀琀愀搀愀琀愀㰀戀爀㸀ഀഀ 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):
਍㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ
਍㰀䈀㸀䔀砀琀爀愀挀琀椀渀最 昀椀氀攀猀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
਍䤀琀✀猀 椀渀猀琀爀甀挀琀椀瘀攀Ⰰ 愀猀 愀 猀漀爀琀 漀昀 攀砀攀爀挀椀猀攀Ⰰ 琀漀 攀砀琀爀愀挀琀 愀氀氀 昀椀氀攀猀 琀漀 琀栀攀 昀椀氀攀猀礀猀琀攀洀⸀ 䄀氀琀栀漀甀最栀 椀琀 洀愀礀戀攀 瘀椀攀眀攀搀 愀猀 瀀爀攀琀琀礀 甀猀攀氀攀猀猀Ⰰ㰀戀爀㸀ഀഀ it's still informative to see how it can be done, using only simple sql tools and methods.
਍䤀琀 洀甀猀琀 戀攀 猀愀椀搀 琀栀愀琀 椀琀✀猀 挀攀爀琀愀椀渀氀礀 愀最愀椀渀猀琀 琀栀攀 瀀漀氀椀挀礀 漀昀 䴀椀挀爀漀猀漀昀琀Ⰰ 琀漀 搀椀爀攀挀琀氀礀 椀渀琀攀爀昀愀挀攀 琀漀 琀栀攀 䌀漀渀琀攀渀琀 搀愀琀愀戀愀猀攀㰀戀爀㸀ഀഀ in this way.
਍㰀戀爀㸀ഀഀ Method 1:
਍㰀戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ ਍唀匀䔀 圀匀匀开䌀漀渀琀攀渀琀  ⴀⴀ 渀愀洀攀 漀昀 礀漀甀爀 挀漀渀琀攀渀琀 搀愀琀愀戀愀猀攀㰀戀爀㸀ഀഀ GO
਍㰀戀爀㸀 ഀഀ DECLARE
਍☀渀戀猀瀀 䀀䌀漀洀洀愀渀搀 嘀䄀刀䌀䠀䄀刀⠀㐀　　　⤀Ⰰ㰀戀爀㸀ഀഀ   @FileID VARCHAR(128),
਍☀渀戀猀瀀 䀀䴀礀䘀椀氀攀渀愀洀攀 渀瘀愀爀挀栀愀爀⠀洀愀砀⤀㰀戀爀㸀ഀഀ
਍䐀䔀䌀䰀䄀刀䔀 挀甀爀㄀ 䌀唀刀匀伀刀  䘀伀刀 㰀戀爀㸀           ഀഀ SELECT Id FROM [WSS_Content].[dbo].[AllDocStreams]
਍㰀戀爀㸀 ഀഀ OPEN cur1
਍䘀䔀吀䌀䠀 一䔀堀吀 䘀刀伀䴀 挀甀爀㄀ 䤀一吀伀 䀀䘀椀氀攀䤀䐀㰀戀爀㸀ഀഀ
਍圀䠀䤀䰀䔀 ⠀䀀䀀䘀䔀吀䌀䠀开匀吀䄀吀唀匀 㴀 　⤀ 㰀戀爀㸀ഀഀ BEGIN
਍㰀戀爀㸀 ഀഀ   SELECT @MyFilename = LeafName from [WSS_Content].[dbo].[AllDocs] WHERE Id = @FileID
਍㰀戀爀㸀 ഀഀ   SET @Command = 'bcp "SELECT Content from [WSS_Content].[dbo].[AllDocStreams] WHERE Id = ''' +
਍☀渀戀猀瀀 䀀䘀椀氀攀䤀䐀 ⬀ ✀✀✀∀ 焀甀攀爀礀漀甀琀 ∀䌀㨀尀匀儀䰀䔀砀琀爀愀挀琀尀✀ ⬀㰀戀爀㸀ഀഀ   @MyFilename+'" -T -n -Slocalhost'
਍㰀戀爀㸀 ഀഀ   EXEC xp_cmdshell @Command
਍㰀戀爀㸀 ഀഀ   FETCH NEXT FROM cur1 INTO @FileID
਍㰀戀爀㸀ഀഀ END
਍㰀戀爀㸀 ഀഀ CLOSE cur1
਍䐀䔀䄀䰀䰀伀䌀䄀吀䔀 挀甀爀㄀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍㰀戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ 5.2 Documents on outline storage (filesystems/SAN):
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍ഀഀ ਍ऀऀऀऀऀഀഀ ਍ ഀഀ ਍ഀഀ
਍㰀戀爀㸀ഀഀ
਍ ഀഀ ਍ ഀഀ

6. Other Storage pointers:

਍ ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀 ഀഀ 6.1 Growth and Planning sizing:
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍ ഀഀ ਍ ഀഀ SharePoint uses:
਍㰀戀爀㸀ഀഀ
    ਍㰀氀椀㸀刀攀挀礀挀氀攀 戀椀渀㰀⼀氀椀㸀ഀഀ
  • Versioning
    • ਍㰀氀椀㸀匀攀愀爀挀栀 愀渀搀 椀渀搀攀砀 椀渀昀漀爀洀愀琀椀漀渀 漀昀 䌀漀渀琀攀渀琀㰀⼀氀椀㸀ഀഀ
  • MetaData
    • ਍㰀氀椀㸀刀攀最甀氀愀爀 䌀漀渀琀攀渀琀 ⠀椀渀 䐀䈀 漀爀 匀䄀一 漀爀 匀栀愀爀攀⤀㰀⼀氀椀㸀ഀഀ
਍ ഀഀ ਍ ഀഀ 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:
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍䠀攀爀攀Ⰰ 眀攀 愀爀攀 搀漀渀✀琀 琀愀欀攀 椀渀琀漀 挀漀渀猀椀搀攀爀愀琀椀漀渀 猀瀀攀挀椀愀氀椀稀攀搀 洀攀琀栀漀搀猀⼀琀漀漀氀猀 氀椀欀攀 甀猀椀渀最 ∀䐀愀琀愀 倀爀漀琀攀挀琀椀漀渀 䴀愀渀愀最攀爀∀Ⰰ㰀戀爀㸀ഀഀ or any other 3rd party tools.
਍ ഀഀ
਍ഀഀ 6.2.1 Full Backup / Recovery:
਍㰀戀爀㸀ഀഀ ਍吀栀椀猀 爀攀愀氀氀礀 洀攀愀渀猀 昀甀氀氀 戀愀挀欀甀瀀Ⰰ 愀渀搀 一伀吀 漀渀 琀栀攀 椀琀攀洀 氀攀瘀攀氀 ⠀搀漀挀甀洀攀渀琀Ⰰ 猀椀琀攀Ⰰ 氀椀戀爀愀爀礀 攀琀挀⸀⸀⤀⸀㰀戀爀㸀ഀഀ
਍ ഀഀ - Only SQL Storage: consistent model for backup/recovery.
਍䨀甀猀琀 愀氀氀 漀爀 渀漀琀栀椀渀最 猀挀攀渀愀爀椀漀⸀ 倀漀猀猀椀戀氀礀 猀氀漀眀Ⰰ 戀甀琀 昀甀氀氀礀 挀漀渀猀椀猀琀攀渀琀⸀㰀戀爀㸀ഀഀ
਍ⴀ 䔀䈀匀㨀 匀儀䰀 戀愀挀欀甀瀀㨀 挀漀渀猀椀猀琀攀渀琀 洀漀搀攀氀 昀漀爀 戀愀挀欀甀瀀⼀爀攀挀漀瘀攀爀礀⸀㰀戀爀㸀ഀഀ But Sync between SQL Storage + SAN Storage could be difficult.
਍䄀氀琀栀漀甀最栀 愀 䌀伀䴀 椀渀琀攀爀昀愀挀攀 ⠀挀愀氀氀攀搀 琀栀攀 䔀䈀匀 倀爀漀瘀椀搀攀爀⤀ 欀攀攀瀀猀 琀栀攀猀攀 琀眀漀 猀琀漀爀攀猀 椀渀 猀礀渀挀Ⰰ 椀琀✀猀 漀渀氀礀㰀戀爀㸀ഀഀ at events where the COM interface recognizes file Save and Open commands and invokes redirection calls to the EBS
਍吀栀椀猀 琀栀攀渀Ⰰ 椀猀 搀漀渀攀 眀椀琀栀 眀攀氀氀ⴀ欀渀漀眀渀 挀氀椀攀渀琀猀⸀ 吀栀攀 䔀䈀匀 倀爀漀瘀椀搀攀爀 琀栀甀猀 攀渀猀甀爀攀猀 琀栀愀琀 琀栀攀 匀儀䰀 匀攀爀瘀攀爀 挀漀渀琀攀渀琀 搀愀琀愀戀愀猀攀㰀戀爀㸀ഀഀ contains metadata references to their associated BLOB streams in the external BLOB store.
਍䈀甀琀 琀栀椀猀 椀猀 漀渀氀礀 最愀爀愀渀琀攀攀搀 眀椀琀栀 甀猀椀渀最 眀攀氀氀ⴀ欀渀漀眀渀 挀氀椀攀渀琀猀⸀㰀戀爀㸀ഀഀ
਍ⴀ 刀䈀匀㨀 匀儀䰀 戀愀挀欀甀瀀㨀 挀漀渀猀椀猀琀攀渀琀 洀漀搀攀氀 昀漀爀 戀愀挀欀甀瀀⼀爀攀挀漀瘀攀爀礀⸀ 㰀戀爀㸀ഀഀ 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.
਍匀礀渀挀 戀攀琀眀攀攀渀 匀儀䰀 匀琀漀爀愀最攀 ⬀ 䘀椀氀攀 匀栀愀爀攀 ⠀匀䄀一⤀ 匀琀漀爀愀最攀 挀漀甀氀搀 戀攀 搀椀昀昀椀挀甀氀琀⸀㰀戀爀㸀ഀഀ
਍㰀戀爀㸀ഀഀ 6.2.2 Item level Recovery:
਍㰀戀爀㸀ഀഀ

Still to do.....

਍㰀戀爀㸀ഀഀ
਍ഀഀ ਍ഀഀ ਍㰀戀爀㸀 ഀഀ
਍㰀⼀戀漀搀礀㸀ഀഀ
Using EBS/RBS/SharePoint File Share Library:NO ਍                 㰀戀爀㸀䨀甀猀琀 漀渀攀 瀀爀椀洀愀爀礀 昀椀氀攀Ⰰ 昀漀爀 攀砀愀洀瀀氀攀㰀戀爀㸀ഀഀ
਍                 䬀㨀尀洀猀猀焀氀尀瀀爀椀洀愀爀礀开搀愀琀愀㄀⸀洀搀昀㰀戀爀㸀ഀഀ
਍                 一漀 搀椀昀昀攀爀攀渀琀椀愀琀椀漀渀 椀猀 瀀漀猀猀椀戀氀攀 ⠀甀渀氀攀猀猀 礀漀甀 甀猀攀 䔀䈀匀⼀刀䈀匀 琀漀 猀琀漀爀攀 戀氀漀戀猀 漀渀 昀椀氀攀猀礀猀琀攀洀猀⤀㰀⼀吀䐀㸀ഀഀ
2. By DBA provisioned database
Using EBS/RBS/SharePoint File Share Library:NO
਍                吀栀攀 匀栀愀爀攀瀀漀椀渀琀 䄀搀洀椀渀 椀猀 愀戀氀攀 琀漀 㰀䈀㸀⠀爀攀ⴀ⤀愀琀琀愀挀栀㰀⼀䈀㸀 琀栀攀 搀愀琀愀戀愀猀攀 琀漀 愀 圀攀戀䄀瀀瀀⼀䌀漀氀氀攀挀琀椀漀渀⸀㰀戀爀㸀ഀഀ The DBA has created the Primary Filegroup to as for example:
਍                㰀戀爀㸀ഀഀ K:\mssql\primary_data1.mdf
਍                㰀戀爀㸀ഀഀ The DBA has created a second (and possibly more) Filegroup (FG1) to include muliple files
਍                 愀猀 昀漀爀 攀砀愀洀瀀氀攀㨀㰀戀爀㸀ഀഀ
਍                 䰀㨀尀洀猀猀焀氀尀䘀䜀㄀开搀愀琀愀㄀⸀渀搀昀㰀戀爀㸀ഀഀ M:\mssql\FG1_data2.ndf
਍                 一㨀尀洀猀猀焀氀尀䘀䜀㄀开搀愀琀愀㌀⸀渀搀昀㰀戀爀㸀ഀഀ ਍ ഀഀ ਍                 㰀戀爀㸀ഀഀ Directly after creation, the DBA assigns FG1 to be the "default" Filegroup.
਍                 㰀戀爀㸀ഀഀ No differentiation is possible (unless you use EBS/RBS to store blobs on filesystems)
਍                 䄀琀 氀攀愀猀琀 愀 挀攀爀琀愀椀渀 最愀椀渀 椀渀 瀀攀爀昀漀爀洀愀渀挀攀 椀猀 攀砀瀀攀挀琀攀搀 ⠀搀甀攀 琀漀 洀甀氀琀椀瀀氀攀 猀瀀椀渀搀氀攀猀Ⰰ 椀昀 甀猀攀搀⤀⸀㰀⼀吀䐀㸀ഀഀ
4. Default: SharePoint provisioned
5. -Default: SharePoint provisioned
਍                伀刀㰀戀爀㸀ഀഀ -by DBA provisioned database
Using EBS/RBS:NO
਍                㰀戀爀㸀ഀഀ Using SharePoint File Share Library:YES
਍                㰀戀爀㸀ഀഀ The Sharepoint Admin carefully assigns a specific Libary to specific WebApps/Collections.
਍                吀栀椀猀 氀椀戀爀愀爀礀 甀猀攀猀 愀 昀椀氀攀 猀栀愀爀攀 甀猀椀渀最 琀栀攀 ∀匀栀愀爀攀倀漀椀渀琀 䘀椀氀攀 匀栀愀爀攀 䰀椀戀爀愀爀礀∀㰀戀爀㸀ഀഀ
਍                吀栀攀 䐀䈀䄀 挀爀攀愀琀攀猀 愀 搀愀琀愀戀愀猀攀Ⰰ 漀爀 琀栀攀 搀攀昀愀甀氀琀 匀栀愀爀攀倀漀椀渀琀 瀀爀漀瘀椀猀椀漀渀攀搀 搀愀琀愀戀愀猀攀 椀猀 甀猀攀搀⸀㰀戀爀㸀ഀഀ
਍                 匀琀漀爀愀最攀 漀昀 昀椀氀攀猀 漀甀猀椀搀攀 琀栀攀 搀愀琀愀戀愀猀攀 椀猀 瀀漀猀猀椀戀氀攀Ⰰ 昀漀爀 琀栀椀猀 猀瀀攀挀椀昀椀挀 䰀椀戀爀愀爀礀 琀礀瀀攀⸀㰀戀爀㸀ഀഀ But no differntiation on small-large files, unless the Library usage is coupled to
਍                 猀瀀攀挀椀昀椀挀 瀀甀爀瀀漀猀攀猀⸀㰀⼀吀䐀㸀ഀഀ
AllDocsUSER_TABLE
AllDocVersionsUSER_TABLE
AllListsUSER_TABLE
AllListsUSER_TABLE
DocsVIEW
DocVersionsVIEW
SiteQuotaUSER_TABLE
....