Geek Answers Handbook

SQL Server 2008 Filter Location

I have about 7 terabytes of various media files (pdf, jpg, tiff) that are currently located on a very powerful file server. I am considering moving data to SQL Server 2008 and using the Filestream attribute to help me manage the data. I want to do this because I have web pages that manage this media and they (web pages) are getting slower and slower as more funds are added to the file server daily.

EDIT: Web pages are slow because many of them generate reports that reflect the various details of the file server and what is stored on it. In fact, web pages comb thousands of folders and files to create reports on what they contain. Some web pages allow users to manage folders and files and move them to different places. So, in a nutshell, I'm looking for a faster way to manage these files. It would also allow me to maintain metadata about these files in the database, thereby allowing me to query the database for this information, rather than combing it through a file server.

My problems:

1) I made a proof of concept and confirmed that I can create a local thread for the SQL Server 2008 database, and I successfully read and wrote media to it. However, I have yet to figure out how to use UNC as a filter. . In other words, the database is hosted on MySQLDB08, and my files are stored on TheFileServer01. I read it, but I haven't got it yet. Any help on this would be greatly appreciated!

2) Since I have 7 terabytes (and growth) of media, will my backups be unmanageable due to their size? Is this something that can dissuade me from using Filestream?

Any suggestions or help would be greatly appreciated!

+5
source share
2 answers
  • . afaik , SQL / / UNC.
  • . ? .

, ? BLOB , Win32:

, FILESTREAM , . , BLOB , BLOB FILESTREAM.

:

  • , BLOB . , BLOB FILESTREAM, ( ) .
  • BLOB- . , SQL Server FILESTREAM , SQL Server . NTFS ( FILESTREAM). , FILESTREAM ( ).

, . , ? , - , , - , . O (1), O (logn), b ut O (n).

SQL FILESTREAM Storage SQL Server 2008, .

+5

@RemusRusanu UNC. , @RemusRusanu , .

, UNC - . , UNC - SQL Filestream.

UNC...

  • UNC SQL. WTF? ( ) SQL Server ( , ). SQL ? SQL Server .

  • , Filestream UNC Windows.

  • SQL-, ~ 1.2MB - , filestream VarBinary. MS 1 , , , 1.2 .

  • UNC- , SQL- UNC .

, , Filestream. : . , . -.

    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;
    }
+1

More articles:


All Articles