SQL Server 2012 introduces two new features that make it both easier and more valuable to store your documents and files in the database:
This article captures a timed walkthrough of the steps required to configure a SQL Server 2012 document storage solution from scratch. It includes a screen shot of every step.
We'll also see how quickly this solution can be completed.
Back to the top
In SQL Server Configuration Manager, enable FILESTREAM on the instance and specify a folder name for the instance-level share. By default, the share has the same name as the instance.
In SSMS, check the corresponding instance-level server properties.
Let's open the instance-level share in Windows Explorer to check it.
Create a new database for this demo. We'll name it EndToEndFileSearch.
Enable non-transactional access to support FileTables in this database, and specify a folder name for the database-level share. By default, the share has the same name as the database.
Add a FILESTREAM filegroup to the new database. We'll call it EndToEndFileSearch.
Add a file to the new FILESTREAM filegroup. We'll call it EndToEndFileSearch_FS.
Let's open the database-level share in Windows Explorer to check it.
Create a new FileTable. By default, the folder name for the table-level share is the same as the name of the table itself. A FileTable has a fixed schema, so you don't have to specify a list of columns.
This action scripts a CREATE TABLE statement template to a new query window.
Fill in parameter values for the template, and run the script to create the new FileTable.
Here's the newly-created FileTable in SSMS Object Explorer.
Let's open the table-level share in Windows Explorer to check it.
In Windows Explorer, let's drag and drop 137 files and folders totaling 105 MB into the new FileTable. (You cannot convert an existing file system folder to a FileTable.) This corpus of documents includes all white papers published to the MSDN Library by
the SQL Server team.
Let's query the FileTable to confirm that the documents are now stored in SQL Server.
Launch the Full-Text Indexing Wizard for the new FileTable.
Select the system-defined unique index on the FileTable's rowguidcol as the unique index to be used by the full-text index. This selection corresponds to the
KEY INDEX clause in the Transact-SQL
CREATE FULLTEXT INDEX statement.
Select the file_stream column that contains the documents as the column to be indexed. Specify the
file_type column as the type column, and check the box under Statistical Semantics to enable Semantic Search.
Enable automatic change-tracking.
Create a new full-text catalog for the new database and set it as the default catalog. This catalog will be used for the new full-text index. (The catalog is only a logical container.)
Finish the wizard and finish creating the full-text and semantic indexes.
Query two dynamic management views to check the status of full-text and semantic indexing.
Indexing is finished! (When you specify automatic population, the status of the indexing remains at "Starting" while waiting to process more updates.)
First, let's query the full-text index for a list of white papers that mention "SSIS" and sort them in descending order by rank.
Now, let's query the semantic index for a list of white papers in which "ETL" is a key phrase and sort them in descending order by score.
Four minutes and twenty seconds.
How long did all this take? Less than 5 minutes!
For more information about these new features in SQL Server 2012, see:
Also, see this blog post: