exec sp_configure filestream_access_level, 2;
reconfigure;
go
-- 윈도우 에서 \\127.0.0.1 로 share 폴더를 억세스 할 수 있음
create database FileTableDemo;
go
alter database FileTableDemo
add filegroup FileStreamGroup1 contains filestream;
alter database FileTableDemo
add file (
name = FileStream1,
filename = 'l:\FileStreamFileGroup1'
) to filegroup FileStreamGroup1;
alter database FileTableDemo set filestream ( directory_name = 'FileTableDemo' ); -- < sub folder 가 생김
alter database FileTableDemo set filestream( non_transacted_access = full );
use FileTableDemo;
go
create table MyFileTable as filetable;
select * from MyFileTable
With SQL Server Denali CTP3 now available, I'm starting to explore some of the new Database Engine capabilities. One that caught my attention is the FileTable.
The FileTable builds upon FileStream storage which allows BLOB data to be stored as individual files separate from a database's data files. In SQL Server 2008 (and 2008 R2), interactions with the FileStream files took place either through T-SQL or through code which engaged with OpenSqlFileStream API. This was an interesting direction for applications with large volumes of BLOB data which needed to be accessed quickly, but it wasn't the most accessible implementation. (For more info on FileStream storage including its benefits and trade-offs, check out this excellent white paper which includes best practices for FileStream configuraiton which you will want to employ.)
The FileTable addresses this by making the Windows share associated with the externally stored files directly engageable and by allowing the share to employ a familiar, hiearchical folder structure. (The folder structure is represented by the underutilized hierarchyid data type first introdued in SQL Server 2008. If you aren't familiar with it, check it out.) This, along with the semantic search capabilities of Denali, will be what make FileStream take off for database developers.
However, there are quite a number of steps involved with configuring a SQL Server instance and database before you can create your first FileTable, and if you are not familiar with the setup of FileStream storage, these steps can be challenging to perform. Having stumbled a couple times myself before finally getting it right, I figured document the steps involved with enabling the environment and creating a simple FileTable in SQL Server Denali CTP3.
1. Enable the FileStream driver on the SQL Server instance.
a. Open SQL Server Configuration Manager.
b. Navigate to the SQL Server Services folder in the left-hand pane.
c. In the right-hand pane, right-click the Database Engine instance on which you want to enable FileStream support and select Properties.
d. In the Properties dialog, navigate to the FILESTREAM tab.
e. Check the Enable FILESTREAM for Transact-SQL access and Enable FILESTREAM for file I/O access options.
NOTE You can also change the name of the Windows share, but for the purpose of these instructions, I'll leave it as its default, i.e. the name of the instance.
f. Check the Allow remote client access option for the purposes of this post. For production deployments, this may or may not be the right option for you and requires a consideration of the accessibility and security trade-offs.
i. Click the Apply button and then close the Properties dialog and SQL Server Configuration Manager.
At this point, you can see that the server now exposes a Windows share using the name of the Database Engine instance. You can see this by opening Windows Explorer and navigating to \\127.0.0.1. If you enabled remote access on the FileStream share (in Step 1f), you can access the share but it will be empty at this point. (In SQL Server 2008 and 2008 R2, attempting to access the share in this manner would result in an error regardless of the remote access setting.)
2. Enable SQL Server to use the FileStream driver.
a. Open SQL Server Management Studio and cancel the Connect to Server dialog.
b. Click the New Query button and connect to the SQL Server Database Engine instance you configured in Step 1.
c. In the query window, execute the following statements:
exec sp_configure filestream_access_level, 2;
reconfigure;
NOTE The filestream_access_level configuration setting set in Step 2d takes one of three values: 0, 1, or 2. The default value, 0, disables FileStream support. The value 1 enables T-SQL only access to FileStream files, and the value 2 enables both T-SQL and Win32 (direct) access to the files.
With FileStream now configured at the instance-level, you can now setup a new (example) database employing FileStream storage.
3. Create a new database employing a FileStream file group.
a. In the query window used in Step 2d (or a new one), execute the following statement to create a new database:
create database FileTableDemo;
b. In that same query window, execute the following statement to add a FileStream file group to the database:
alter database FileTableDemo
add filegroup FileStreamGroup1 contains filestream;
c. In that same query window, execute the following statement to define the storage associated with the FileStream file group added in the previous step:
alter database FileTableDemo
add file (
name = FileStream1,
filename = 'D:\Temp\FileStreamFileGroup1'
) to filegroup FileStreamGroup1;
NOTE Steps 3a, b, and c could be combined into a step using a single CREATE DATABASE statement. It's been decomposed into smaller steps here for greater transparency.
This last step deserves a bit more discussion. The path to the FileStream storage, as identified in the filename setting, must be a valid path on the SQL Server system up to the last folder in the path. In other words, D:\Temp must exist for the previous statement to work. However, the last folder, FileStreamFileGroup1 in the case of the previous statement, must not exist. That folder will be created by SQL Server upon statement execution.
With the database in place, you now need to configure how the database will present and control interactions with the FileStream data.
4. Configure how the database handles access to the FileStream data.
a. In the query windows from previous steps, define the name of the Share subfolder through which FileStream files associated with this database will be shared:
alter database FileTableDemo set filestream ( directory_name = 'FileTableDemo' );
b. In the query window, execute the following statement to enable direct read and write access to the FileStream data:
alter database FileTableDemo set filestream( non_transacted_access = full );
NOTE Options for non_transacted_access are off, read_only, and full. Off disables non-transactional access so that data in the FileTableDemo subfolder will not be accessible. Read_only provides read-only access to files (outside a transaction as handled through the OpenSqlFileStream API). Full allows files to be read and written through the subfolder.
If you accessed the share earlier, you might wish to review it again now. The instance's share at \\127.0.0.1 now has a FileTableDemo subfolder per Step 4a.
With all this in place, you can now define a filetable in the database.
5. Create a FileTable within the FileTableDemo database by executing the following statement in the query window from the previous step:
use FileTableDemo;
go
create table MyFileTable as filetable;
NOTE The CREATE TABLE statement used in the last step is very simplistic compared to what is possible with the structure of a FileTable but it provides a good starting point for working with this new feature. For more info on the broader syntax on the CREATE TABLE ... AS FILETABLE statement, please see this document.
With a FileTable now defined within the FileTableDemo database, you can now reconnect to \\127.0.0.1 and access the instance share. The FileTableDemo subfolder defined in Step 4a now has its own subfolder, MyFileTable, named for the FileTable created in Step 5. As the database has been configured to allow full non-transactional access to this folder, you can drag and drop files within the FileTable folder and even create subfolders under it. All of these will be reflected in the MyFileTable table when you interact with it using T-SQL. As you use T-SQL to insert, update, and delete data in the FileTable table, those changes will be reflected in the associated folder on the Windows share. Pretty cool stuff!
http://blog.tallan.com/2011/08/22/using-sqlfilestream-with-c-to-access-sql-server-filestream-data/
Using SqlFileStream with C# to Access SQL Server FILESTREAM Data
FILESTREAM is a powerful feature in SQL Server that stores varbinary(max) column data (BLOBs) in the file system (where BLOBs belongs) rather than in the database’s structured file groups (where BLOBs kill performance). This feature was first introduced in SQL Server 2008, and is now being expanded with the new FileTable feature coming in SQL Server 2012 (code-named “Denali”). This post contains abbreviated updated FILESTREAM coverage that I’ll be adding to Tallan’s new book Programming SQL Server 2012 (to be published, hopefully, shortly after the first quarter of 2012).
If you’re not already familiar with FILESTREAM, you can get the necessary background by reading these two articles: Introducing FILESTREAM and Enabling and Using FILESTREAM. In this post, I’ll show you how to use the SqlFileStream class to achieve high-performance streaming of SQL Server FILESTREAM data in your .NET applications (the code is shown in C#, but can be written in VB .NET as well).
What Is SqlFileStream?
SqlFileStream is a class in the .NET Framework (.NET 3.5 SP1 and higher) that wraps the OpenSqlFilestream function exposed by the SQL Server Native Client API. This lets you stream BLOBs directly between SQL Server and your .NET application (written in C# or VB .NET). SqlFileStream is always used within a transaction. You create a SqlFileStream object when you are ready to store and retrieve BLOBs from varbinary(max) FILESTREAM columns. Just at that point in time, SQL Server will “step aside” and let you stream directly against the server’s file system—a native environment optimized for streaming. This provides you with a streaming “tunnel” between your application and SQL Server’s internally-managed file system. Using SqlFileStream will give your application lightning-fast BLOB performance. Let’s dive in!
Creating the Database
Before getting started, be sure that FILESTREAM is enabled for remote file system access at both the Windows Service and SQL Server instance levels (as explained in Enabling and Using FILESTREAM). Then create a FILESTREAM-enabled database as follows (be sure to create the directory, C:\DB in this example, before creating the database):
01 |
CREATE DATABASE PhotoLibrary |
03 |
(NAME = PhotoLibrary_data, |
04 |
FILENAME = 'C:\DB\PhotoLibrary_data.mdf'), |
05 |
FILEGROUP FileStreamGroup CONTAINS FILESTREAM |
06 |
(NAME = PhotoLibrary_blobs, |
07 |
FILENAME = 'C:\DB\Photos') |
09 |
(NAME = PhotoLibrary_log, |
10 |
FILENAME = 'C:\DB\PhotoLibrary_log.ldf') |
Next, use the database and create a table for BLOB storage as follows:
4 |
CREATE TABLE PhotoAlbum( |
5 |
PhotoId int PRIMARY KEY, |
6 |
RowId uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID(), |
7 |
Description varchar(max), |
8 |
Photo varbinary(max) FILESTREAM DEFAULT(0x)) |
In this table, the Photo column is declared as varbinary(max) FILESTREAM, and will hold pictures that will be stored in the file system behind the scenes. (Refer to Enabling and Using FILESTREAM for a complete explanation of the varbinary(max) FILESTREAM and ROWGUIDCOL columns.) Notice the default value we’ve established for the BLOB column. The value 0x represents a zero-length binary stream, which is different than NULL. Think of it as the difference between a zero-length string and a null string in .NET; the two are not the same. Similarly, you won’t be able to use SqlFileStream against NULL instances of varbinary(max) FILESTREAM columns, and you’ll soon see why.
Writing SqlFileStream Code
Start Visual Studio, create a new Class Library project, and add a PhotoData class as follows:
003 |
using System.Data.SqlClient; |
004 |
using System.Data.SqlTypes; |
005 |
using System.Drawing; |
007 |
using System.Transactions; |
009 |
namespace PhotoLibraryApp |
011 |
public class PhotoData |
013 |
private const string ConnStr = |
014 |
"Data Source=.;Integrated Security=True;Initial Catalog=PhotoLibrary;"; |
016 |
public static void InsertPhoto |
017 |
(int photoId, string desc, string filename) |
019 |
const string InsertTSql = @" |
020 |
INSERT INTO PhotoAlbum(PhotoId, Description) |
021 |
VALUES(@PhotoId, @Description); |
022 |
SELECT Photo.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() |
024 |
WHERE PhotoId = @PhotoId"; |
029 |
using (TransactionScope ts = new TransactionScope()) |
031 |
using (SqlConnection conn = new SqlConnection(ConnStr)) |
035 |
using (SqlCommand cmd = new SqlCommand(InsertTSql, conn)) |
037 |
cmd.Parameters.Add("@PhotoId", SqlDbType.Int).Value = photoId; |
038 |
cmd.Parameters.Add("@Description", SqlDbType.VarChar).Value = desc; |
039 |
using (SqlDataReader rdr = cmd.ExecuteReader()) |
042 |
serverPath = rdr.GetSqlString(0).Value; |
043 |
serverTxn = rdr.GetSqlBinary(1).Value; |
047 |
SavePhotoFile(filename, serverPath, serverTxn); |
053 |
private static void SavePhotoFile |
054 |
(string clientPath, string serverPath, byte[] serverTxn) |
056 |
const int BlockSize = 1024 * 512; |
058 |
using (FileStream source = |
059 |
new FileStream(clientPath, FileMode.Open, FileAccess.Read)) |
061 |
using (SqlFileStream dest = |
062 |
new SqlFileStream(serverPath, serverTxn, FileAccess.Write)) |
064 |
byte[] buffer = new byte[BlockSize]; |
066 |
while ((bytesRead = source.Read(buffer, 0, buffer.Length)) > 0) |
068 |
dest.Write(buffer, 0, bytesRead); |
077 |
public static Image SelectPhoto(int photoId, out string desc) |
079 |
const string SelectTSql = @" |
083 |
GET_FILESTREAM_TRANSACTION_CONTEXT() |
085 |
WHERE PhotoId = @PhotoId"; |
091 |
using (TransactionScope ts = new TransactionScope()) |
093 |
using (SqlConnection conn = new SqlConnection(ConnStr)) |
097 |
using (SqlCommand cmd = new SqlCommand(SelectTSql, conn)) |
099 |
cmd.Parameters.Add("@PhotoId", SqlDbType.Int).Value = photoId; |
101 |
using (SqlDataReader rdr = cmd.ExecuteReader()) |
104 |
desc = rdr.GetSqlString(0).Value; |
105 |
serverPath = rdr.GetSqlString(1).Value; |
106 |
serverTxn = rdr.GetSqlBinary(2).Value; |
110 |
photo = LoadPhotoImage(serverPath, serverTxn); |
119 |
private static Image LoadPhotoImage(string filePath, byte[] txnToken) |
123 |
using (SqlFileStream sfs = |
124 |
new SqlFileStream(filePath, txnToken, FileAccess.Read)) |
126 |
photo = Image.FromStream(sfs); |
Let’s explain the code in detail. We’ll start at the top with some required namespace inclusions. The two using statements to take notice of are System.Data.SqlTypes and System.Transactions. The System.Data.SqlTypes namespace defines the SqlFileStream class that we’ll be using to stream BLOBs. No special assembly reference is required to use this class, because it is provided by the System.Data.dll assembly that our project is already referencing (Visual Studio set this reference automatically when it created our project). The System.Transactions namespace defines the TransactionScope class that lets us code implicit transactions against the database. This class is provided by the System.Transactions.dll assembly, which is not referenced automatically. You’ll need to add a reference to it now, or the code will not compile. Right-click the project in Solution Explorer and choose Add Reference. In the Add Reference dialog, click the .NET tab, and scroll to find the System.Transactions component. Then double-click it to add the reference.
At the top of the class, we define a connection string as a hard-coded constant named ConnStr. This is just for demonstration purposes; a real-world application would store the connection string elsewhere (such as in a configuration file, possibly encrypted), but we’re keeping our example simple.
Streaming Into SQL Server
The first method defined in the class is InsertPhoto, which accepts a new photo integer ID, string description, and full path to an image file to be saved to the database. Notice that the InsertTSql string constant defined at the top of the method specifies an INSERT statement that includes the PhotoId and Description columns, but not the actual Photo BLOB column itself. Instead, the INSERT statement is followed immediately by a SELECT statement that retrieves two pieces of information we’ll use to stream the BLOB into Photo column much more efficiently than using ordinary T-SQL—namely, a logical UNC path name to the file and the transactional context token. These are the two values needed to use SqlFileStream, and you’re about to see how exactly. But all we’ve done so far is define a constant holding two T-SQL statements. The constant is followed by two variables declarations serverPath and serverTxn that will receive the two special values when we later execute those T-SQL statements.
The method then creates and enters a new TransactionScope block. This does not actually begin the database transaction (we’ve not even connected to the database yet), but rather declares that all data access within the block (and in any code called from within the block) must participate in a database transaction. Inside the TransactionScope block, the code creates and opens a new SqlConnection. Being the first data access code inside the TransactionScope block, this also implicitly begins the database transaction. Next, it creates a SqlCommand object associated with the open connection and prepares its command text to contain our T-SQL statements (the INSERT followed by the SELECT).
Invoking the ExecuteReader method executes the T-SQL statements and returns a reader from which we can retrieve the values returned by the SELECT statement. The transaction is still pending at this time. Our INSERT statement does not provide a value for RowId and instead allows SQL Server to automatically generate and assign a new uniqueidentifier ROWGUID value by default just like before when we used T-SQL to insert the first two rows. We’ve also pointed out that no value is provided for the Photo column—and this is exactly how the default 0x value that we defined earlier for the Photo column comes into play (we said we’d come back to it, and here we are).
Although the row has been added by the INSERT statement, it will rollback (disappear) if a problem occurs before the transaction is committed. Because we didn’t provide a BLOB value for the Photo column in the new row, SQL Server honors the default value 0x that we established for it in the CREATE TABLE statement for PhotoAlbum. This represents a zero-length binary stream, which is completely different than NULL. Being a varbinary(max) column decorated with the FILESTREAM attribute, an empty file gets created in the file system that SQL Server associates with the new row. At the same time, SQL Server initiates an NTFS file system transaction over this new empty file and synchronizes it with the database transaction. So just like the new row, the new file will disappear if the database transaction does not commit successfully.
Immediately following the INSERT statement, the SELECT statement returns Photo.PathName and GET_FILESTREAM_TRANSACTION_CONTEXT. What we’re essentially doing with the WHERE clause in this SELECT statement is reading back the same row we have just added (but not yet committed) to the PhotoAlbum table in order to reference the BLOB stored in the new file that was just created (also not yet committed) in the file system.
The value returned by Photo.PathName is a fabricated path to the BLOB for the selected PhotoId. The path is expressed in UNC format, and points to the network share name established for the server instance when we first enabled FILESTREAM (this is MSSQLSERVER in our example, as shown in Figure 9-1). It is not a path the file’s physical location on the server, but rather contains information SQL Server can use to derive the file’s physical location. For example, you’ll notice that it always contains the GUID value in the uniqueidentifier ROWGUIDCOL column of the BLOB’s corresponding row. We retrieve the path value from the reader’s first column and store it in the serverPath string variable.
We just explained how SQL Server initiated an NTFS file system transaction over the FILESTREAM data in the new row’s Photo column when we started our database transaction. The GET_FILESTREAM_TRANSACTION_CONTEXT function returns a handle to that NTFS transaction (if you’re not inside a transaction, this function will return NULL and your code won’t work). We obtain the transaction context, which is returned by the reader’s second column as a SqlBinary value, and store it in the byte array named serverTxn.
Armed with the BLOB path reference in serverPath and the transaction context in serverTxn, we have what we need to create a SqlFileStream object and perform direct file access to stream our image into the Photo column. We close the reader, terminate its using block, then terminate the enclosing using block for the SqlConnection as well. This would normally close the database connection implicitly, but that gets deferred in this case because the code is still nested inside the outer using block for the TransactionScope object. So the connection is still open at this time, and the transaction is still pending. It is precisely at this point that we call the SavePhotoFile method to stream the specified image file into the Photo column of the newly inserted PhotoAlbum row, overwriting the empty file just created by default. When control returns from SavePhotoFile, the TransactionScope object’s Complete method is invoked and its using block is terminated, signaling the transaction management API that everything worked as expected. This implicitly commits the database transaction (which in turn commits the NTFS file system transaction) and closes the database connection.
The SavePhotoFile method reads from the source file and writes to the database FILESTREAM storage in 512 KB chunks at a time using ordinary .NET streaming techniques. The method begins by defining a BlockSize integer constant that is set to a reasonable value of 512 KB. Picture files larger than this will be streamed to the server in 512 KB blocks at a time. The local source image file (in clientPath) is then opened on an ordinary read-only FileStream object.
Then the destination file is opened by passing the two special values (serverPath and serverTxn), along with a FileAccess.Write enumeration requesting write access, into the SqlFileStream constructor. Like the source FileStream object, SqlFileStream inherits from System.IO.Stream, so it can be treated just like any ordinary stream. Thus, you attain write access to the destination BLOB on the database server’s NTFS file system. Remember that this output file is enlisted in an NTFS transaction and nothing you stream to it will be permanently saved until the database transaction is committed by the terminating TransactionScope block, after SavePhotoFile completes. The rest of the SavePhotoFile method implements a simple loop that reads from the source FileStream and writes to the destination SqlFileStream, one 512 KB block at a time until the entire source file is processed, and then it closes both streams.
Streaming Out From SQL Server
The rest of the code contains methods to retrieve existing photos and stream their content from the file system into an Image object for display. You’ll find that this code follows the same pattern as the last, only now we’re performing read access.
The SelectPhoto method accepts a photo ID and returns the string description from the database in an output parameter. The actual BLOB itself is returned as the method’s return value in a System.Drawing.Image object. We populate the Image object with the BLOB by streaming into it from the database server’s NTFS file system using SqlFileStream. Once again, we start things off by entering a TransactionScope block and opening a connection. We then execute a simple SELECT statement that queries the PhotoAlbum table for the record specified by the photo ID and returns the description and full path to the image BLOB, as well as the FILESTREAM transactional context token. And once again we use the path name and transactional context with SqlFileStream to tie into the server’s file system in the LoadPhotoImage method.
Just as when we were inserting new photos (only this time using FileAccess.Read instead of FileAccess.ReadWrite), we create a new SqlFileStream object from the logical path name and transaction context. We then pull the BLOB content directly from the NTFS file system on the server into a new System.Drawing.Image object using the static Image.FromStream method against the SqlFileStream object. The populated image can then be passed back up to a Windows Forms application, where it can be displayed using the Image property of a PictureBox control.
Or, to stream a photo over HTTP from a simple ASP.NET service:
03 |
using System.Data.SqlClient; |
04 |
using System.Data.SqlTypes; |
06 |
using System.Transactions; |
09 |
namespace PhotoLibraryHttpService |
11 |
public partial class PhotoService : Page |
13 |
private const string ConnStr = |
14 |
"Data Source=.;Integrated Security=True;Initial Catalog=PhotoLibrary"; |
16 |
protected void Page_Load(object sender, EventArgs e) |
18 |
int photoId = Convert.ToInt32(Request.QueryString["photoId"]); |
24 |
const string SelectTSql = @" |
25 |
SELECT Photo.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() |
27 |
WHERE PhotoId = @PhotoId"; |
29 |
using (TransactionScope ts = new TransactionScope()) |
31 |
using (SqlConnection conn = new SqlConnection(ConnStr)) |
38 |
using (SqlCommand cmd = new SqlCommand(SelectTSql, conn)) |
40 |
cmd.Parameters.Add("@PhotoId", SqlDbType.Int).Value = photoId; |
42 |
using (SqlDataReader rdr = cmd.ExecuteReader()) |
45 |
serverPath = rdr.GetSqlString(0).Value; |
46 |
serverTxn = rdr.GetSqlBinary(1).Value; |
51 |
this.StreamPhotoImage(serverPath, serverTxn); |
57 |
private void StreamPhotoImage(string serverPath, byte[] serverTxn) |
59 |
const int BlockSize = 1024 * 512; |
60 |
const string JpegContentType = "image/jpeg"; |
62 |
using (SqlFileStream sfs = |
63 |
new SqlFileStream(serverPath, serverTxn, FileAccess.Read)) |
65 |
byte[] buffer = new byte[BlockSize]; |
67 |
Response.BufferOutput = false; |
68 |
Response.ContentType = JpegContentType; |
69 |
while ((bytesRead = sfs.Read(buffer, 0, buffer.Length)) > 0) |
71 |
Response.OutputStream.Write(buffer, 0, bytesRead); |
Conclusion
The OpenSqlFilestream function provides native file streaming capabilities between FILESTREAM storage in the file system managed by SQL Server and any native-code (e.g., C++) application. SqlFileStream provide a managed code wrapper around OpenSqlFilestream that simplifies direct FILESTREAM access from .NET applications (e.g., C# and VB .NET). This post explained how this API works in detail, and showed the complete data access code that uses SqlFileStream for both reading and writing BLOBs to and from SQL Server. That’s everything you need to know to get the most out of FILESTREAM. I hope you enjoyed it!
