SQL Server FileStream detailed _mssql

FileStream is a new feature in SQL Server 2008 that allows large object data to be stored as separate files, rather than saving all data to a data file as usual. In the past, there were two ways to manage the files of a business system, one of which was to save the file to the server file system, where only the path to the file was saved, and the application was connected to the server to read the file when it was used, and the other was to varbinary the file (max) or the image data type is saved to SQL Server. And SQL Server 2008 provides FILESTREAM, combining the advantages of both approaches.

FileStream makes the SQL Server database engine and the NTFS file system a whole. Transact-SQL statements can insert, UPDATE, query, search, and back up FileStream data. FileStream uses the NT system cache to cache file data. This helps reduce any impact that FILESTREAM data may have on the performance of the database engine. Because the SQL Server buffer pool is not used, this memory can be used for query processing.

In the past, we have two ways of managing file management:

1. The database only saves the path of the file, the specific file is saved on the file server (NFS), and when used, the programming implementation reads the file from the file server;

2. Save the file directly to the varbinary (max) or image data type in the database.

The above two files are stored in a problematic way: the first method is not very well affected by I/O because it accesses the disk, and is not good for file backup; The second method solves the problem of file backup (database backup), but because the number of bytes in the field is too large, it can affect the database itself. Performance is also very low.

Microsoft has launched a new approach to SQL Server 2008-FileStream, which is not a new data type, but a technology that makes the SQL Server database engine and the NTFS file system a whole, It combines the advantages of the above two approaches: FileStream uses NT systems to cache file data, and operations on file data can be inserted, updated, queried, searched, and backed up using Transact-SQL statements.

First, FILESTREAM configuration

1. Configure SQL Server Installation instance: Start-> all Programs-> Microsoft SQL Server 2008 R2-> Configuration Tools-> SQL Server Configuration Manager

Right-click the property, switch to the FileStream tab, and check the following configuration

2. Open SQL Server and configure the following

The above can also be executed by using the following script:

Exec sp_configure filesteam_access_level, 2

Finally restart the SQL Server Service

Second, the example shows

Create FileStream type files/groups

--create filestreamgroup 
ALTER DATABASE [Archive]
ADD FILEGROUP [filestreamgroup] CONTAINS FILESTREAM 
Go
--create FileStream and association with Filestreamgroup above
ALTER DATABASE [Archive]
ADD FILE (NAME = n ' FileStream ', FILENAME = n ' D:\Company\Data\SQL server\filestream ') to file GROUP [Filestreamgroup] Go

Create a test table (note: If the table contains FileStream columns, each row must have a unique row ID)

--create table
Create table Archive.dbo.Attachment (
  [ID] [uniqueidentifier] ROWGUIDCOL not NULL PRIMARY key,< C11/>[filename] NVARCHAR (MB) null,
  [CreateUser] NVARCHAR (m) null,
  [createdatetime] DATETIME null,
  [Content] VARBINARY (MAX) FILESTREAM NULL 
)

Insert some test data

 --insert Some records
Insert into attachment VALUES 
(NEWID (), ' File Name 1 ', ' Shg.cpan ', GETDATE (), NULL),
( NEWID (), ' File Name 1 ', ' Shg.cpan ', GETDATE (), CAST (' as VARBINARY (MAX) '),

Insert some data from the foreground

using (SqlConnection conn = new SqlConnection ("server=10.7.15.172;database=archive;uid=sa;pwd=1234; Connect timeout=180 "))
{
  Conn. Open ();
  using (SqlCommand cmd = conn. CreateCommand ())
  {
    string id = Guid.NewGuid (). ToString ();
    Cmd. CommandText = "INSERT into attachment VALUES (' + ID +" ', ' File Name 2 ', ' Shg.cpan ', ' "+ DateTime.Now +" ', @content) ";
    SqlParameter param = new SqlParameter ("@content", SqlDbType.VarBinary, 1000000000);
    Param. Value = File.readallbytes (@ "D:\Folder\131 u_ex151207.log");
    Cmd. Parameters.Add (param);
    Cmd. ExecuteNonQuery ();
  }
  Conn. Close ();
}

Retrieving data

SELECT datalength (CONTENT)/(1024.0 * 1024.0) as mb,* from attachment

Results

File system

　　

The above files are uploaded real files, but no suffix, if renamed plus suffix, you can read, such as the last one is an Excel file, plus. xls, you can use Excel software to open this file

III. Matters of note

　　Please note the following items:

• Not all file stores are suitable for use with FileStream, and if the stored file objects are larger than 1MB to consider using FileStream, for smaller file objects, a varbinary (max) BLOB is typically stored in the database with more excellent flow performance ；
FileStream can be used on a failed cluster (Failover Cluster), but the FILESTREAM filegroup must be on a shared disk resource at this time;
FileStream compatibility with other SQL Server features: https://msdn.microsoft.com/zh-cn/library/bb895334 (v=sql.105). aspx