SQL Server 2008's Filestream location?

You can't. Afaik filestream data is stored localy and SQL will refuse to read/write from/to an UNC Your full backups will contain the entire filestream data. Unmanageable?

Definetly a very serious challenge My question would be what is the benefit you want to extract from the filestream? The usual benefits come from BLOB integration with database operations while keeping availability for Win32 file handle based operations: Even though FILESTREAM technology has many attractive features, it may not be the optimal choice in all situations. As mentioned earlier, the size of the BLOB data and the access patterns are the most significant factors when deciding whether to store the BLOB data wholly within the database or by using FILESTREAM Size affects the following: Efficiency with which the BLOB data can be accessed using either storage mechanism.As mentioned earlier, streaming access of large BLOB data is more efficient using FILESTREAM, but partial updates are (potentially much) slower Efficiency of backing up the combined structured and BLOB data using either storage mechanism.

A backup that combines SQL Server database files and a large number of FILESTREAM files will be slower than a backup of just SQL Server database files of an equivalent total size. This is because of the extra overhead of backing up each NTFS file (one per FILESTREAM data value). This overhead becomes more noticeable when the FILESTREAM files are smaller (as the time overhead becomes a larger percentage of the total time to backup per MB of data) From a pure performance point of view, there are many steps you can do on a file system level to improve performance.

What is you current problem, why is your system throughput affected by the media size? It means you have a somewhere a choke point of contention, perhaps a directory enumeration, or some other barrier that causes you to scale the response time with the media size. Your access to the media should be O(1), maybe O(logn),b ut definetely not O(n) I'd recommend you go over the SQL White Paper FILESTREAM Storage in SQL Server 2008 from where I found my quote about use cases.

You can't. Afaik filestream data is stored localy and SQL will refuse to read/write from/to an UNC. Your full backups will contain the entire filestream data.

Unmanageable? Definetly a very serious challenge. My question would be what is the benefit you want to extract from the filestream?

The usual benefits come from BLOB integration with database operations while keeping availability for Win32 file handle based operations: Even though FILESTREAM technology has many attractive features, it may not be the optimal choice in all situations. As mentioned earlier, the size of the BLOB data and the access patterns are the most significant factors when deciding whether to store the BLOB data wholly within the database or by using FILESTREAM. Size affects the following: Efficiency with which the BLOB data can be accessed using either storage mechanism.As mentioned earlier, streaming access of large BLOB data is more efficient using FILESTREAM, but partial updates are (potentially much) slower.

Efficiency of backing up the combined structured and BLOB data using either storage mechanism. A backup that combines SQL Server database files and a large number of FILESTREAM files will be slower than a backup of just SQL Server database files of an equivalent total size. This is because of the extra overhead of backing up each NTFS file (one per FILESTREAM data value).

This overhead becomes more noticeable when the FILESTREAM files are smaller (as the time overhead becomes a larger percentage of the total time to backup per MB of data). From a pure performance point of view, there are many steps you can do on a file system level to improve performance. What is you current problem, why is your system throughput affected by the media size?

It means you have a somewhere a choke point of contention, perhaps a directory enumeration, or some other barrier that causes you to scale the response time with the media size. Your access to the media should be O(1), maybe O(logn),b ut definetely not O(n). I'd recommend you go over the SQL White Paper FILESTREAM Storage in SQL Server 2008, from where I found my quote about use cases.

I've read through the white page numerous times, but, quite frankly, some of it goes over my head because it deals with areas of which I'm not familiar (eg - backups). Also, the white paper says The use of a file system filter driver also allows remote access to the FILESTREAM data through a UNC path, which indicates to me that storing filestream data on a different location other than the actual database server might be possible. Also, please see my edit in my original question; I hope it explains better why I am looking into using Filestream.

– Jagd Feb 9 '10 at 0:43 The system driver allows access through an UNC to the filestream from other location. In other words, it allows you to share the filestream. It does not allows you to store the fielstream on a UNC.

Note that at 7Tb, 'local' should probably mean some sort of SAN utility. – Remus Rusanu Feb 9 '10 at 1:10 1 About your edit: sounds to me that one aspect of the problem is that you don't have metadata in a relational format about your files, and you need to look into the file system for it. Can you keep metadata in tables, or it will quickly drift out of sync with the filesystem?

– Remus Rusanu Feb 9 '10 at 1:12 that is exactly the problem! I am unsure how to keep the metadata in sync with what is on the disk. I could probably come up with something if I had to though.

I've lent a bit of thought to the problem last night after reading your answers, and now I'm tossing around the idea of just keeping the files on disk and storing their metadata within the database. – Jagd Feb 9 '10 at 16:39.

I'm going to have to disagree with @RemusRusanu on the UNC issue. Although, @RemusRusanu makes some good points on why you would choose to use a filestream. Anyway, You can uses UNC's for filestreams -- it wouldn't be of much use otherwise.

Currently, I build a site that is using the UNC feature for servers in a web farm to read files from a SQL Filestream. A few points on using UNC Filestreams ... Access to UNC's is gated by SQL server. WTF?

The point of the file stream is to merge the benefits of the file system (good streaming) and the benefits of SQL Server (good meta data, transactions and query~ability). How does SQL ensure the file access is transactional? You have to open the transaction and inside the transaction ask SQL Server for a file handle.

Said another way, you can't just navigate to the Filestream UNC from windows explorer. If you're storing your binary in SQL server, then generally ~1.2MB is the break point where you should favor filestream over VarBinary. Here MS suggest 1 MB, but there is another in research paper I can't locate at the moment that suggested 1.2 is the break even point.

Enabling UNC access requires a distributed transaction, so both the SQL server and the consumer of the UNC path need distributed transactions enabled. Following is a code snippet that shows how to retrieve a handle to a Filestream. There is one big caveat: the transaction isn't closed in this snippet.

You will need to read the binary, then close the transaction. Leaving open transactions is clearly a no-no. Public FileStream GetStream(string FilePath){ FileStream FStream = null; Conn = new SqlConnection( MyConnectionStringHere ); Conn.Open(); txn = Conn.

BeginTransaction(); using (SqlCommand cmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", Conn, txn)){ Object obj = cmd.ExecuteScalar(); TransContext = (byte)obj; } SafeFileHandle SHandle = NativeSqlClient. GetSqlFilestreamHandle (FilePath, NativeSqlClient.DesiredAccess. Read, TransContext); FStream = new FileStream(SHandle, FileAccess.

Read); return FStream; }.

I believe that you may have misunderstood my question with regards to the UNC. I was simply inquiring as to why the Filestream White Paper seemed to indicate that a filestream could be stored on a different server from where the SQL Server 2008 database resides. The White Paper states, "The use of a file system filter driver also allows remote access to the FILESTREAM data through an UNC path."

Remus cleared this up for me by explaining that this simply means the filestream can be shared (thusly accessed) on your network by an UNC path. – Jagd Oct 19 '10 at 18:24 (cont. ) - In other words, I never had problems accessing the filestream itself programmatically through the Win 32 API.

Although, I do believe your example is somewhat unconventional. I prefer to use the SqlFileStream libraries when reading from or writing to it. – Jagd Oct 19 '10 at 18:27 Also, I didn't give you the -1.

I'm not sure who did. I don't see anything incorrect with your answer; I just think you misunderstood the guts of what Remus and I were getting at. – Jagd Oct 19 '10 at 18:30.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.