Tag Archives: file

FILESTREAM with SharePoint 2010

​Author: Bill Baer, Senior Technical Product Manager, Microsoft Corporation – SharePoint

FILESTREAM is a new feature in SQL Server 2008 that enables the storage of unstructured data on a NTFS file system. FILESTREAM (local or remote) is supported in SharePoint 2010 as one mechanism of reducing capital expenditures through enabling the storage of large binary unstructured data on content addressable or commodity storage.

Unstructured Data
Unstructured data refers to information that does not adhere to a defined model or does not fit well into relational tables in SharePoint unstructured data can refer to Microsoft Office document file formats, video, audio, and related file types.
 
Structured Data
Structured data in SharePoint refers to the metadata associated with its corresponding unstructured data or BLOB. Relational databases are most often cited as examples of structured data.
 
SQL Server and Unstructured Data Storage
There are three (3) approaches to storing unstructured data with SQL Server, RBS, SQL BLOB, and FILESTREAM:
  • Remote BLOB Storage (RBS) in which SharePoint relies on a new layer in SQL Server to read or update BLOB data stored outside of the database on separate BLOB Stores (file system or dedicated BLOB stores)
  • SQL BLOB which refers to traditional BLOB storage with SharePoint, BLOB data is stored along side the structured metadata in the Content Database
  • FILESTREAM
 
FILESTREAM Overview
FILESTREAM is implemented on the varbinary(max) datatype instructing the database engine to store unstructured data on the file system through a FILESTREAM filegroup that contains file system directories instead of the files themselves also known as data containers. Data containers are the interface between database engine storage and file system storage. varbinary is the binary data type designation for binary large objects stored in SharePoint 2010 content databases and refers to variable-length binary data. (MAX) refers to a value that max indicates that the maximum storage size is 2^31-1 bytes or otherwise 2GB. Remote BLOB Storage does not provide a mechanism to exceed to the 2GB file size limit imposed by SharePoint.
 
In SharePoint 2010 remote BLOB data is referenced by a unique identifier in content databases configured for RBS (see illustration).
 
 
 

 
 
FILESTREAM offers several benefits as related to performance 1) FILESTREAM uses the NT system cache for caching file data reducing the effect that FILESTREAM data has on Database Engine performance and 2) the SQL Server buffer pool is not used; therefore, memory is available for query processing.
 
FILESTREAM provides optimum value in scenarios where SharePoint is used to storage large BLOB data such as video files that will benefit from FILESTREAM or BLOB data that exceeds 1MB.
 
Special Considerations
 
FILESTREAM and Business Continuity Management
 
Database mirroring does not support FILESTREAM since a FILESTREAM filegroup cannot be created on the principal server and database mirroring cannot be configured for a database that contains FILESTREAM filegroups. If the FILESTREAM provider is used to store BLOB data locally (within the same content database) the database cannot be configured for database mirroring.
 
If the FILESTREAM provider is configured to store the BLOB data within a separate SQL database or when using a 3rd party BLOB store, the content database can be mirrored; however, database mirroring will apply only to the content database and not the BLOB data. The BLOB data needs to be handled separately and kept in sync with the associated metadata (content database). For FILESTREAM BLOB databases, this can be done through log shipping.
 
To learn about the differences between FILESTREAM and SQL Server Remote BLOB Store see also http://blogs.msdn.com/b/sqlrbs/archive/2009/11/18/sql-server-remote-blob-store-and-filestream-feature-comparison.aspx.
 
FILESTREAM and the Office Web Applications Cache
 
The Office Web Applications cache is used by Word and PowerPoint Web Applications to create a version of a document requested for viewing through the browser improving performance and reducing resource consumption on server machines by making cached versions of a document or presentation available in cases where there are multiple requests for the same document. 
 
The Office Web Applications cache occurs in two (2) distinct tiers, on the server file system and within a “specialized” site collection hosted on a per Web application basis. Document or presentation requests made through the Office Web Applications are served through both caches as the images are rendered for client consumption. Both cache locations are used by all site collections within a Web application where the Office Web Applications features activated.
 
Content databases where FILESTREAM is configured will store the cached versions of the document or presentation in the configured provider location.  It is recommended to configure the Office Web Applications cache on a database that is not configured for FILESTREAM.
 
Configuring FILESTREAM with SharePoint 2010
The instructions that follow are designed to be used on a single-server deployment for demonstration purposes and implements the local FILESTREAM Provider.
 
Enable FILESTREAM on the target SQL Server Instance
 
1.       On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 (R2), point to Configuration Tools, and then click SQL Server Configuration Manager.
2.       In the list of services, right-click SQL Server Services, and then click Open.
3.       In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4.       Right-click the instance and then click Properties.
5.       In the SQL Server Properties dialog box, click the FILESTREAM tab.
6.       Select the Enable FILESTREAM for Transact-SQL access check box.
7.       If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
8.       If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
9.       Click Apply.
10.    Click Start, click All Programs, click Microsoft SQL Server 2008 (R2), and then click SQL Server Management Studio.
11.    In SQL Server Management Studio, click New Query to display the Query Editor.
12.    In Query Editor, enter the following Transact-SQL code:
 
EXEC sp_configure filestream_access_level, 2
 
In Query Editor, enter the following Transact-SQL code:
 
RECONFIGURE
Provision the RBS Data Store
 
1.       Click Start, click All Programs, click Microsoft SQL Server 2008 (R2), and then click SQL Server Management Studio.
2.       Expand Databases.
3.       Select the content database for which you want to create a BLOB store, and then click New Query.
4.       In SQL Server Management Studio, click New Query to display the Query Editor.
5.       In Query Editor, enter the following Transact-SQL code:
 
use [Database Name]
 
if not exists (select * from sys.symmetric_keys where name = N’##MS_DatabaseMasterKey##’)create master key encryption by password = N’Admin Key Password !2#4′
 
In Query Editor, enter the following Transact-SQL code:
 
use [Database Name]
 
if not exists (select groupname from sysfilegroups where groupname=N’RBSFilestreamProvider’)alter database [Database Name] add filegroup RBSFilestreamProvider contains filestream
 
In Query Editor, enter the following Transact-SQL code:
 
use [Database Name]
 
alter database [Database Name] add file (name = RBSFilestreamFile, filename = ‘c:\BlobStore’) to filegroup RBSFilestreamProvider
Install the Microsoft SQL Server 2008 R2 Remote Blob Store
 
Download the x64 package for the Microsoft SQL Server 2008 R2 Remote Blob Store from the Microsoft SQL Server 2008 R2 Feature Pack at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52&displaylang=en.
 
Open a Command Prompt with Administrator permissions and execute the following command to install RBS.MSI downloaded in the previous step:
 
msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="<Database Name>" DBINSTANCE="<Instance Name>" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider_1
 
NOTE
Specify the full path to RBS.MSI in the above state, i.e. C:\Users\Administrator\Desktop\RBS.MSI. Replace the values for DBNAME and DBINSTANCE to match your environment.
 
Enable Remote BLOB Storage
1.       On the Start menu, click All Programs.
2.       Click Microsoft SharePoint 2010 Products.
3.       Click SharePoint 2010 Management Shell.
4.       In the SharePoint 2010 Management Shell, enter the following Windows PowerShell statement to set the content database to be configured:
$database=Get-SPContentDatabase –Identity “Database Name”
In the SharePoint 2010 Management Shell, enter the following Windows PowerShell statement to gets the object that holds settings that determine how the content database uses Microsoft SQL Server Remote Blob Storage:
$rbs=$database.RemoteBlobStorageSettings
In the SharePoint 2010 Management Shell, enter the following Windows PowerShell statement to determine if RBS has been installed for the selected content database:
$rbs.Installed()
NOTE
The result of $rbs.Installed() should be True, if the result is False, verify RBS.MSI has been installed successfully by reviewing rbs_install_log.txt. Ensure the install statement was running In the SharePoint 2010 Management Shell, enter the following Windows PowerShell statement to enable RBS for the selected content database:
 
$rbs.Enable()
In the SharePoint 2010 Management Shell, enter the following Windows PowerShell statements to set the RBSprovider for the selected content database:
$rbs.SetActiveProviderName($rbs.GetProviderNames()[0])
 
$rbs
 

NOTE
The result of $rbs should be:
Table 1

 
Appendix for Table 1
 
Enabled specifies whether or not RBS has been enabled for the selected content database.
 
ActiveProviderName is name of the SQL Remote Blob Storage provider new files will be stored in. This will be null if new files will not be stored using SQL Remote Blob storage.
 
MinimumBlobStorageSize refers to the minimum size a BLOB may be to be considered RBS storage worthy, BLOB data exceeding the specified MinimumBlobStorageSize will be stored in the RBS data store.
 
FILESTREAM performance data shows BLOB data exceeding 1MB provides the most efficient streaming performance. See also http://msdn.microsoft.com/en-us/library/cc949109(SQL.100).aspx.
 

 
To configure the MinimumBlobStorageSize:
1.       On the Start menu, click All Programs.
2.       Click Microsoft SharePoint 2010 Products.
3.       Click SharePoint 2010 Management Shell.
4.       In the SharePoint 2010 Management Shell, enter the following Windows PowerShell statements to configure the MinimumBlobStorageSize at 1MB:
$database = Get-SPContentDatabase “Database Name”
 
$database.RemoteBlobStorageSettings.MinimumBlobStorageSize=1048576
 
$database.Update()
UpgradePersistedProperties specifies the collection of field names and values for fields that were deleted or changed.
 
Validate Installation
To validate the FILESTREAM configuration and RBS installation:
 
1.       Click Start, click All Programs, click Microsoft SQL Server 2008, and then click SQL Server Management Studio.
2.       Expand Databases.
3.       Select the content database for which you want to create a BLOB store, and then click New Query.
4.       In SQL Server Management Studio, click New Query to display the Query Editor.
5.       In Query Editor, enter the following Transact-SQL code:
USE [Database Name]
SELECT * FROM dbo.DatabaseInformation
Confirm that both the RBSCollectionId and RBSProvider rows are available.
 
Test the RBS Data Store
1.       Select a desired Document Library on a site in the configured content database.
2.       Upload a file that is greater than 1 MB.
3.       On the computer that contains the RBS data store, click Start, and then click Computer.
4.       Browse to the RBS data store directory.
5.       Browse to the file list and open the folder that has the most recent modified date (other than $FSLOG). In that folder, open the file that has the most recent modified date. Verify that this file has the same size and contents as the file that you uploaded. If it does not, ensure that RBS is installed and enabled correctly.
The data store directory structure will appear similar to that in the following diagram.
 

 
NOTE
In the event error "The URL ‘<Document Library>/File’ is invalid. It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web.” is displayed when uploading documents greater than the configured MinimumBlobStorageSize open SQL Server Configuration Manager and enable Enable FILESTREAM for file I/O streaming access and restart the SQL Server (MSSQLSERVER) service.
 
Additional Resources

 

 

Read More

SP1 updates for SharePoint Workspace 2010

SharePoint Workspace capacity limits have increased for SP1. The approximate upper limit for documents that can be stored in the Ofice Document Cache before you can start to see significant impact on memory and performance, has changed from approximately 1,800 to 10,000 documents, depending on file size, type, and contents.

And, an update has been applied to Alternate Access Mapping handling as well.

For details, see:
http://technet.microsoft.com/en-us/library/ee649106.aspx

 


Read More

Visual Studio 2008 extensions for Windows SharePoint Services (VSEWSS) v1.3 Released

The VSeWSS v1.3 is now released and is available for download here. You should uninstall the VSeWSS 1.3 CTP and install this version. There is no difference to the project file format so Visual Studio 2008 projects using the VSeWSS format should not require any upgrading from the CTP.

The tools are available in 9 languages including English, Japanese, German, French, Spanish, Italian, Korean, Chinese (Simplified), and Chinese (Traditional). They are intended to be used with the Visual Studio 2008 install of the same language.

Please note: These are the development tools for SharePoint 2007 and require Visual Studio 2008. The development tools for SharePoint 2010 are included with Visual Studio 2010. You can upgrade your VSeWSS projects to Visual Studio 2010 and SharePoint Server 2010 using an upgrade tool available here.

New features in v1.3 over the v1.2 are:

  • Runs on x64 bit machines
  • Fast deployment commands
  • WSP View improvements
  • Command line build support
  • Solution Generator supports Publishing Sites
  • New RootFiles templates
  • Web part rename assistance
  • CopyLocal support for adding dependent DLLs
  • Conflict resolution dialog for deployments

These features were all available in the CTP. More details of the new features, and other important installation tips are in the release notes on the download page. Please review the release notes.

The best place to get assistance in using VSeWSS 1.3 is in the SharePoint 2007 Development MSDN Forum.


Read More

File Shares and SharePoint. Still a Hot Topic.

I’m sometimes reminded that there is still a lot of debate over how to position file shares and SharePoint in an organization. There are still many people drinking the file share Kool-Aid and that’s fine. I blogged about this a little over a year ago and it generated more than 5000 views and 8 comments/trackbacks which is about 8 more than usual. Apparently, Joel has to talk about this a lot as he posted about this again recently. (I would link you there, but his new blog is down again. Somebody please fix this! Joel’s blog is too important to be down. I joked with him when he was putting together his new site that he needed a hot standby failover solution.)
Anyways, after I posted my latest entry on geo-redundancy in SharePoint I’ve been enjoying some great debate through comments with TBA. I thought that conversation would be interesting for the rest of you. Here’s it is:
# re: More Clarification Needed? Geographic Separation of SharePoint Farm Components.
What strikes me is that due to these limitations, SharePoint cannot be easily configured to replace DFS for file storage ! SharePoint is marketed as the “file server of the future” yet it lacks the DFS’s feature of maintaining local copies of files in environments that span continents/remote locations.
If I am to store all my files in SharePoint I have to store them all in one primary data center. Obviously users from different continents are better off having the data locally…. I would think this will be the major upgrade to the next SharePoint…
Monday, April 07, 2008 12:41 PM by TBA

# re: More Clarification Needed? Geographic Separation of SharePoint Farm Components.
SharePoint and file shares co-exist, not replace one another. Each have their own merits. SharePoint makes traditional file share data usable. DFS is one of the few technologies that allow multi-master replication. You are right that users prefer data to be local for performance reasons. However, http traffic is much better than CIFS over the WAN and SharePoint supports numerous acceleration vendors to make consolidated deployments seem local.

http://blogs.msdn.com/mikewat/archive/2006/12/09/file-shares-vs-sharepoint.aspx

Monday, April 07, 2008 12:52 PM by Michael Watson

# re: More Clarification Needed? Geographic Separation of SharePoint Farm Components.
You say “SharePoint makes traditional share data usable”. Ok, I have 250 Gigs of project “Delta” files located on a file share that is DFS replicated for fault-toleration and localization. I have people using that data from all-over the world. Due to SharePoint’s architectural limitation ( lack of file replication support ) I can’t migrate out of DFS.
Right, I have created a team-space in SharePoint called “Delta” – great – the team members now can use discussions / calendars / etc. However the 250 Gigs of related-files are still in DFS and people cannot use Sharepoint’t s doc management features due to this. The only way around this would be to ask people move files between SharePoint ans DFS which is silly and is up to them really – leaving you out of control.
So how exactly is SharePoint making my data usable , again ?
Tuesday, April 08, 2008 4:46 AM by TBA

# re: More Clarification Needed? Geographic Separation of SharePoint Farm Components.
Thanks for the great debate. It brings the blog to life.
Your file share data in SharePoint becomes “usable” because of the rich metadata definition, context, and most importantly, search. File Share data exists in its raw form without much context. It becomes an island, unknown outside of its most ardent users. It will most likely be duplicated by others since they are unaware it exists.
To improve upon the file share experience while still enjoying its benefits, you could link to the existing data in DFS from SharePoint or simply index the content for search. However, most organizations will prefer to simply migrate the collaboration-ready data to SharePoint. Since data usually has a preferred location it should be moved to the nearest regional SharePoint farm. This ensures the primary users of that data enjoy great performance while still providing access to users outside of the region. If the scenarios truly require it, consider using network acceleration technologies for remote collaborators or one of the SharePoint data replication solutions. We have a lot of great partners in this space and the solutions are probably much cheaper than most realize.
The bottom-line is SharePoint is part of a robust information architecture that improves upon the traditional file share experience with rich metadata, better context, and consolidated and scoped search. While there are certain scenarios were file shares are still the a great solution I’m confident that an organization will be best served by moving the majority of its collaboration to SharePoint.
Tuesday, April 08, 2008 1:43 PM by Michael Watson…(read more)
Read More

MOSS export list SSL issue

Problem: I can’t export a list to a excel spreadsheet file from a SharePoint 2007/WSS3.0 site is using SSL.  SP2010 exports https lists correctly. 
Initial Hypothesis: File is being exported however, when opening the *.iqy file, the error “Excel cannot connect to the SharePoint list.” pops up. 

Resolution: This is a bug with 2 workarounds,

  • Option1: From excel connect to the list. or
  • Option 2: Save the *.iqy file, open it using notepad, chnage the url to use https instead of http (it occurs twice in the iqy file.

Read More

Word 2002: The Complete Reference

Master the powerful new features in Microsoft Word 2002. This complete reference provides comprehensive coverage of the changes and updates to Word, focusing on the new formatting and proofing options, enhanced graphics capabilities, new file search methods, and customization options, including new speech recognition capabilities. You’ll also get an introduction to VBA and will learn to write macros to automate repetitive Word tasks.

Buy Now!

List Price: $ 43.95
Price: $ 19.95

Adding a Calendar View to a file from ONET.XML

Pretty simple, but I always seem to forget…when adding a page to a new site in a Feature, you need the following markup to add a calendar view of an Events list in a ListView web part:

<View List="Lists/Calendar" BaseViewID="2" WebPartZoneID="Top" WebPartOrder="0" DefaultView="TRUE" Type="Calendar" />

Read More

GlobalSCAPE® Announces New Capabilities for Its Enterprise Managed File Transfer Solution

SAN ANTONIO, Texas–(BUSINESS WIRE)–GlobalSCAPE, Inc. (NYSE Amex: GSB), a leading developer of secure information exchange solutions, today announced new capabilities for its award-winning managed file transfer (MFT) solution, Enhanced File Transfer Server® (EFT Server). This new version of EFT Server brings additional security and visibility features, along with improved workflow tools, to …
Read Full Story
Source: Business Wire
Date Published: 30th March 11

GlobalSCAPE® Announces New Capabilities for Its Enterprise Managed File Transfer Solution

GlobalSCAPE, Inc. , a leading developer of secure information exchange solutions, today announced new capabilities for its award-winning managed file transfer solution, Enhanced File Transfer Server® .
Read Full Story
Source: Business Wire via Yahoo! Finance
Date Published: 30th March 11

Is it possible to convert an Access file into a web database?

In MS Access 2010, is it possible to, given a database created as a desktop database, switch the view to be as though you clicked the “Web Database” option at the beginning?

Say the database passes the web compatibility test, is it possible to switch to be creation of a web database?

I know that if the database is Web compatible, you can still publish to a SharePoint site, but when you’re creating it as a Web database, many of the options which are not compatible with the Web are excluded. Especially when designing forms and macros.

So yeah, is there a way to switch afterthefact?

I’ve looked, and can’t see such an option, and I couldn’t find anything on google about the matter at all.

thanks

Chosen Answer:

Yes you can – if you want to be limited to running on Microsoft servers and limit your users to using Microsoft browsers (and only certain versions). If you want to run a real database on a real website that’s of use to real users, you copy the data to a MySQL database and write the site as a real website. Without using anything that Microsoft produces (except, maybe, Windows and Notepad).
by: Colanth
on: 12th March 11