SQL Server FileStream details,

SQL Server FileStream details,

FILESTREAM is a new feature in SQL Server 2008. It allows you to store and enlarge object data in the form of an independent file, instead of storing all data in a data file. In the past, there were two methods to manage files in the business system. One was to save the files to the file system on the server, and the database only saved the path of the file, when using this file, the application connects to the Server to read the file; the other is to save the file as varbinary (max) or image data type to SQL Server. SQL Server 2008 provides FILESTREAM, which combines the advantages of these two methods.

FILESTREAM makes the SQL Server database engine and the NTFS file system a whole. A Transact-SQL statement can insert, update, query, search, and backup FILESTREAM data. FILESTREAM uses the NT system cache to cache file data. This helps reduce any impact of FILESTREAM data on database engine performance. Because the SQL Server Buffer Pool is not used, the memory can be used for query processing.

In the past, we had two methods for file management:

1. The database only saves the path of the file. The specific file is stored on the file server (NFS). during use, the file is read from the file server by programming;

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

There are problems with the above two file storage methods: the first method is affected by I/O because it will access the disk, and the performance is not very good, and file backup is not good; the second method solves the problem of file backup (Database Backup), but because the number of bytes in the field is too large, it will also affect the database and its performance is also very low.

Microsoft introduced a new method in SQL Server 2008-FileStream, which is not a new data type, but a technology, it makes the SQL Server database engine and the NTFS file system a whole. It combines the advantages of the above two methods: FileStream uses the NT System to cache file data, you can use the Transact-SQL statement to insert, update, query, search, and back up file data.

1. FileStream Configuration

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

 

Right-click Properties, switch to the FILESTREAM tab, and select the following configuration

 

2. Open SQL Server and configure the following:

You can also run the following script:

Exec sp_configure filesteam_access_level, 2RECONFIGURE 

Restart SQL Server Service

 

Ii. instance display

Create a FileStream file/group

--Create filestreamgroup ALTER DATABASE [Archive]ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM GO--Create filestream and association with filestreamgroup aboveALTER DATABASE [Archive]ADD FILE ( NAME = N'FileStream', FILENAME = N'D:\Company\Data\SQL Server\FileStream') TO FILEGROUP [FileStreamGroup]GO

Create a test table (Note: if the table contains the FILESTREAM column, each row must have a unique row ID)

--Create tableCREATE TABLE Archive.dbo.Attachment (  [ID] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL PRIMARY KEY,  [FileName] NVARCHAR(100) NULL,  [CreateUser] NVARCHAR(100) NULL,  [CreateDatetime] DATETIME NULL,  [Content] VARBINARY(MAX) FILESTREAM NULL )FILESTREAM_ON [FileStreamGroup] 

Insert some test data

 --Insert some recordsINSERT INTO Attachment VALUES (NEWID(),'File Name 1','shg.cpan', GETDATE(),NULL),(NEWID(),'File Name 1','shg.cpan', GETDATE(),CAST('' AS VARBINARY(MAX))),(NEWID(),'File Name 1','shg.cpan', GETDATE(),CAST('This is a attachment, which contains all introduction for filestream' 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();}

Retrieve Data

SELECT DATALENGTH(CONTENT)/(1024.0 * 1024.0) AS MB,* FROM ATTACHMENT 　

Result

 

File System

　　

You can use the Excel software to open this file.

Iii. Notes

　　Note the following:

• Not all file storage systems are suitable for FileStream. If the average size of the stored file objects is larger than 1 MB, use FileStream. Otherwise, varbinary (max) is used for small file objects) BLOB storage in the database usually provides better streaming performance;
• FileStream can be used on the faulty Cluster (Failover Cluster), but the FileStream file group must be located on the shared disk resources;
• FILESTREAM compatibility with other SQL Server features: https://msdn.microsoft.com/zh-cn/library/bb895334 (v = SQL .105). aspx

Articles you may be interested in:

• SQL Server's FileStream and FileTable in-depth analysis
• SQL Server misunderstanding: 18th-day discussion about FileStream storage, garbage collection, and other
• Introduction to FileStream in SQL Server 2008