Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008

By:   |   Comments (23)   |   Related: > FILESTREAM


Problem

SQL Server 2008 provides the capability of storing BLOBs (e.g. MP3, Word, Excel, PDF, etc.) in the NTFS file system rather than in a database file.  I am interested in how to configure the FILESTREAM technology in Management Studio.  Could you help us to understand the benefits and provide details on how to implement this new feature in SQL Server 2008?  Further, I am interested in how to use this functionality with the C# programming language.

Solution

A BLOB is a binary large object.  Video, audio, image and document files (e.g. MP3, Word, Excel, PDF, etc.) are all examples of BLOBs.  Applications often want to store and retrieve these various BLOBs together with the typical relational data.  SQL Server has supported storing BLOBs in the database for quite some time.  SQL Server 2008 provides a new option for storing BLOBs on the NTFS file system in lieu of in a database file.  According to Books on Line use FILESTREAM when:

  • You are storing BLOBs with an average size of 1 megabyte or more
  • Fast read access is important
  • You want to access BLOBs from your application's middle tier code

The advantages of using FILESTREAM are:

  • You store and retrieve your BLOBs together with your relational data in a single data store  
  • The BLOBs are included in database backups and restores
  • Inserting, updating and deleting BLOBs and your relational data happens in the same database transaction
  • The 2 GB max size for a varbinary(max) column doesn't apply; you are only limited by the available space on the NTFS file system
  • The SQL Server buffer pool memory is not used to operate on BLOBs; in previous versions large BLOBs could consume a lot of this memory
  • All BLOB access can be performed from .NET code, allowing your middle tier code to easily work with these objects
  • The NTFS file system can save and retrieve large BLOBs faster than SQL Server

There are several configuration steps that need to be performed in order to use FILESTREAM.  The main one is creating a filegroup specifically for storing BLOBs.  As BLOBs are inserted, updated or deleted, the action is transparently performed on the NTFS file system rather than in a database file.  You will typically perform these actions on the BLOBs from .NET code rather than T-SQL.

In this tip we are going to gain an understanding of FILESTREAM by walking through a simple code sample to demonstrate the steps to:

  • Configure FILESTREAM
  • Add a BLOB from T-SQL code
  • Add a BLOB from C# code

The demo code below was only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008. 

Configure FILESTREAM

There are a couple of configuration steps required in order to use the FILESTREAM feature:

  • Enable FILESTREAM for the database instance
  • Create a filegroup for BLOB storage
  • Create a table with a BLOB column

To enable FILESTREAM for the database instance, execute the system stored procedure sp_filestream_configure:

EXEC sp_filestream_configure 
  @enable_level = 3
, @share_name = N'FS';

The valid values for the @enable_level parameter are:

  • 0 = disabled (this is the default)
  • 1 = enabled only for T-SQL access
  • 2 = enabled for T-SQL access and local file system access
  • 3 = enabled for T-SQL access, local file system access, and remote file system access

The @share_name parameter is used to create a file share that can be used to access the BLOBs via the file system.

You can also enable FILESTREAM for the database instance from SQL Server Management Studio (SSMS).  Right click on the database instance (i.e. root node) in the Object Explorer, then select Properties from the context menu.  Click Advanced and set the Filestream Access Level to Full access enabled. 

serverproperties

You can examine the status of FILESTREAM support on the database instance by executing this query:

SELECT 
 SERVERPROPERTY ('FilestreamShareName') ShareName
,SERVERPROPERTY ('FilestreamConfiguredLevel') ConfiguredLevel
,SERVERPROPERTY ('FilestreamEffectiveLevel') EffectiveLevel

You should see the ConfiguredLevel =3 and EffectiveLevel = 3.  As of this writing (February 2008 CTP) changing the Filestream Access Level requires a restart of the database instance.  When you enable FILESTREAM you would see the ConfiguredLevel = the value you specified for the @enable_level parameter and EffectiveLevel would be 0.  Right click on the database instance in Object Explorer then select Restart from the context menu.

In order to store BLOBs on the NTFS file system you need to create a filegroup with the CONTAINS FILESTREAM attribute.  Execute the following script to add a filegroup to an existing database (change the database name and filename as appropriate):

ALTER DATABASE fs
ADD FILEGROUP fs_fg_filestream CONTAINS FILESTREAM
GO
ALTER DATABASE fs
ADD FILE
(
    NAME= 'fs_filestream',
    FILENAME = 'C:\db\fs'
)
TO FILEGROUP fs_fg_filestream
GO

Note that for a FILE that is added to a FILESTREAM filegroup, the FILENAME parameter is actually a path.  In the example above C:\db must already exist;  a folder named fs cannot already exist; it will be created under C:\db.

The final configuration step for FILESTREAM is to create a table to hold the BLOBs.  For example:

CREATE TABLE dbo.BLOB (
 ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
 BLOB VARBINARY(MAX) FILESTREAM NULL
)

To store a BLOB using FILESTREAM you must have a column of type VARBINARY(MAX) with the FILESTREAM attribute specified.  In addition the table must have a UNIQUEIDENTIFIER column with the ROWGUIDCOL attribute. 

Add a BLOB from T-SQL

To add a BLOB from T-SQL, you execute an INSERT statement, and simply CAST the BLOB contents to VARBINARY(MAX).  Since the BLOB column type is VARBINARY(MAX), selecting it will render in hexadecimal.  The PathName() function will return the UNC path to the BLOB file.  Notice that the last part of the path is the value of the UNIQUEIDENTIFIER column.

DECLARE @ID UNIQUEIDENTIFIER
SET @ID = NEWID()
INSERT INTO dbo.BLOB
 (ID, BLOB)
VALUES 
 (@ID, CAST('BLOB Placeholder' AS VARBINARY(MAX)))
SELECT ID, BLOB 
FROM dbo.BLOB
WHERE ID = @ID
SELECT BLOB.PathName() 
FROM dbo.BLOB
WHERE ID = @ID

 

results

You can use the T-SQL UPDATE statement to replace the contents of the BLOB.  You can use the T-SQL DELETE statement to delete it.  Even though you are issuing T-SQL commands, the action on the BLOB is being performed on the NTFS file system.

Add a BLOB from C#

Probably the most likely scenario for using FILESTREAM is where you will be manipulating the BLOBs from your application's middle tier code.  For demonstration purposes we'll create a simple example in C# that can save a BLOB and retrieve a BLOB.  To keep the demo code simple, we'll create a console application where you specify command line arguments; for example:

BLOB [ get | put ] filename [id]

  • Get is used to retrieve a BLOB; you must also specify id which is the UNIQUEIDENTIFIER that identifies the row to retrieve
  • Put is used to save a BLOB
  • Filename is the full path to the BLOB file; put will read the file from the file system and save it as a  BLOB in the database, get will retrieve the BLOB from the database using the id and write it out to this filename on the NTFS file system

Let's review a few snippets from the sample code (the full Visual Studio 2008 project is available here).  The declaration below is used to call the OpenSqlFilestream API function from C# code.  OpenSqlFilestream is not part of the .NET Framework, therefore you need the declaration below to call it from .NET code.  This function is used to both retrieve and save the BLOB.    Note that this API function is actually contained in sqlncli10.dll, which is the SQL 2008 Native Client DLL.

[DllImport("sqlncli10.dll", SetLastError = true, 
              CharSet = CharSet.Unicode)]
static extern SafeFileHandle OpenSqlFilestream(
        string FilestreamPath,
        UInt32 DesiredAccess,
        UInt32 OpenOptions,
        byte[] FilestreamTransactionContext,
        UInt32 FilestreamTransactionContextLength,
        Int64 AllocationSize);

The main parameters for OpenSqlFilestream are as follows (you can review the complete description in Books on Line here):

  • FilestreamPath is the path to the BLOB; call the PathName() function on the actual BLOB column in the database table to get this path.
  • DesiredAccess is a hexadecimal value that specifies whether you want to read, read and write, or write the BLOB.
  • FilestreamTransactionContext is the transaction context; you get this by executing the T-SQL function GET_FILESTREAM_TRANSACTION_CONTEXT().  This function is new to SQL Server 2008.

Next we are going to write two stored procedures that we will call from our C# code to add and retrieve BLOBs.

CREATE PROCEDURE dbo.stp_AddBLOB
AS
BEGIN
  DECLARE @ID UNIQUEIDENTIFIER
  SET @ID = NEWID()
  INSERT INTO dbo.BLOB
    (ID, BLOB)
  VALUES 
    (@ID, CAST('' AS VARBINARY(MAX)))
  SELECT ID, BLOB.PathName()
  FROM dbo.BLOB
  WHERE ID = @ID
END
GO
CREATE PROCEDURE dbo.stp_GetBLOB
@ID UNIQUEIDENTIFIER
AS
BEGIN
  SELECT BLOB.PathName()
  FROM dbo.BLOB
  WHERE ID = @ID
END
GO

To add a BLOB we will execute the stored procedure dbo.stp_AddBLOB.  This procedure inserts a row into our BLOB table with an empty BLOB column, then returns a result set with the UNIQUEIDENTIFIER value for the row and the path to the BLOB.  The path is what we need to pass to the OpenSqlFilestream API function in order to read or write the BLOB from our C# code.

To retrieve a BLOB we will call the stored procedure dbo.stp_GetBLOB.  It requires the UNIQUEIDENTIFIER value for the row as a parameter then returns the path for the BLOB.

Let's review the key points in the C# code that saves and retrieves the BLOBs.  The partial code snippet below saves a BLOB:

  // (1) read in the file to be saved as a blob in the database
  FileStream input = new FileStream(filename, FileMode.Open, 
                                         FileAccess.Read);
  byte[] buffer = new byte[(int)input.Length];
  input.Read(buffer, 0, buffer.Length);
  // (2) insert empty blob in the database
  cn = GetConnection();
  tx = cn.BeginTransaction();
  cmd = new SqlCommand("dbo.stp_AddBLOB", cn, tx);
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  SqlDataReader r = cmd.ExecuteReader(
      System.Data.CommandBehavior.SingleRow);
  r.Read();
  string id = r[0].ToString();
  string path = r[1].ToString();
  r.Close();
  // (3) get the transaction context
  cmd2 = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", 
                cn, tx);
  Object obj = cmd2.ExecuteScalar();
  byte[] txCtx = (byte[])obj;
  // (4) open the filestream to the blob
  SafeFileHandle handle = OpenSqlFilestream(
      path,
      DESIRED_ACCESS_WRITE,
      SQL_FILESTREAM_OPEN_NO_FLAGS,
      txCtx,
      (UInt32)txCtx.Length,
      0);
  // (5) open a Filestream to write the blob
  FileStream output = new FileStream(
      handle,
      FileAccess.Write,
      buffer.Length,
      false);
  output.Write(
      buffer,
      0,
      buffer.Length);
      output.Close();

The numbered comments in the above code identify the key points for discussion:

  1. Read the filename specified on the command line into a byte array.

  2. Invoke the dbo.stp_AddBLOB stored procedure to add an empty BLOB to the database and return the path to the BLOB.  Note that this call is made inside of a transaction.

  3. Get the transaction context.  This value will be passed as a parameter to the OpenSqlFilestream function call, allowing it to participate in the current transaction.

  4. Call the OpenSqlFilestream function to create the handle necessary to write the BLOB to the NTFS file system.

  5. Create a FileStream object which will use the handle from step 4 and actually write the BLOB to the NTFS file system.  The FileStream object is part of the .NET Framework and is used for reading and writing files.

The code snippet below is used to retrieve a BLOB based on the value of the UNIQUEIDENTIFIER column in the row:

  // (1) retrieve path of BLOB
  cn = GetConnection();
  tx = cn.BeginTransaction();
  cmd = new SqlCommand("dbo.stp_GetBLOB", cn, tx);
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@ID", new System.Guid(blobID));
  SqlDataReader r = cmd.ExecuteReader(
     System.Data.CommandBehavior.SingleRow);
  r.Read();
  string path = r[0].ToString();
  r.Close();
  // (2) get the transaction context
  cmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()",
               cn, tx);
  Object obj = cmd.ExecuteScalar();
  byte[] txCtx = (byte[])obj;
  // (3) open the filestream to the blob
  SafeFileHandle handle = OpenSqlFilestream(
      path,
      DESIRED_ACCESS_READ,
      SQL_FILESTREAM_OPEN_NO_FLAGS,
      txCtx,
      (UInt32)txCtx.Length,
      0);
  // (4) open a Filestream and read the blob
  FileStream blob = new FileStream(
      handle,
      FileAccess.Read);
  byte[] buffer = new byte[(int)blob.Length];
  blob.Read(buffer, 0, buffer.Length);
  blob.Close();
  if (handle != null && !handle.IsClosed)
      handle.Close();
  // (5) write out the blob to a file
  FileStream output = new FileStream(
      filename,
      FileMode.CreateNew);
  output.Write(buffer, 0, buffer.Length);
  output.Close();

The numbered comments in the above code identify the key points for discussion:

  1. Invoke the stored procedure dbo.stp_GetBLOB to get the path for the BLOB identified by the blobID which is a UNIQUEIDENTIFIER passed in on the command line.  Note that this call is made inside of a transaction.

  2. Get the transaction context.  This value will be passed as a parameter to the OpenSqlFilestream function call, allowing it to participate in the current transaction.

  3. Call the OpenSqlFilestream function to create the handle necessary to read the BLOB from the NTFS file system.

  4. Create a FileStream object which will use the handle from step 3 and actually read the BLOB from the NTFS file system.  The FileStream object is part of the .NET Framework and is used for reading and writing files.

  5. Write the BLOB out to a file.  This is done to validate that we have successfully retrieved the BLOB.

Next Steps
  • Download a copy of the latest Community Technology Preview of SQL Server 2008 from this site.  The above examples were created using the February, 2008 CTP.
  • Download a copy of the sample code here and experiment with FILESTREAM. 
  • Review the SQL Server 2008 Books on Line content for FILESTREAM for additional information.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, July 8, 2020 - 6:10:14 AM - javed Back To Top (86103)

very helpful


Wednesday, November 5, 2014 - 1:06:23 PM - nofunwithfilestream Back To Top (35191)

We enabled Filestream on an existing server and an existing database. We altered an existing table and added columns specific to Filestream. The result was a new DocGUID column with GUIDs on all rows in the altered table and NULL on all rows for the new varbinary (filestream) column.

All code samples I have seen show how to insert a new record in this table and populate the varbinary column and we have that working.

The problem is trying to update the varbinary (filestream) column on a record that already exists. The code samples  show getting the path info from the new column, then creating a SqlFileStream object and writing to it. The path returns NULL on all pre-existing records.

How do I update the existing records with the new file/blob?

Thanks!


Sunday, May 11, 2014 - 5:49:05 AM - HdW Back To Top (30723)

Thanks! There doesn't seem to be a problem with 2012. 
 

 


Friday, May 9, 2014 - 8:44:55 AM - Ray Barley Back To Top (30700)

I tried with SQL Server 2012 and I could have 2 filestream columns in a table.  Here is my sample code:

 

CREATE TABLE dbo.BLOB2 (
 ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
 BLOB1 VARBINARY(MAX) FILESTREAM NULL,
 BLOB2 VARBINARY(MAX) FILESTREAM NULL
)

DECLARE @ID UNIQUEIDENTIFIER
SET @ID = NEWID()
INSERT INTO dbo.BLOB2
 (ID, BLOB1, BLOB2)
VALUES
 (@ID, CAST('BLOB1 Placeholder' AS VARBINARY(MAX)), CAST('BLOB2 Placeholder' AS VARBINARY(MAX)))
SELECT ID, BLOB1, BLOB2
FROM dbo.BLOB2
WHERE ID = @ID
SELECT BLOB1.PathName(),  BLOB2.PathName()
FROM dbo.BLOB2
WHERE ID = @ID


Friday, May 9, 2014 - 7:21:14 AM - HdW Back To Top (30698)

Have you ever managed to put more than 1 filestream column into one table? For migration purposes I need to put 6 objects (filestream instead of image) into the table (per record, that is).  I used sql  server 2008, and it seemed impossible. Or could it be that 2012 does offer that functionality and 2008 doesn't?

 

Cheers,

 

HdW


Friday, August 23, 2013 - 2:13:33 PM - Ray Barley Back To Top (26463)

What are you doing that you get the error 87?  Be very specific.  Are you configuring, running a SQL command, ?


Thursday, August 22, 2013 - 3:41:21 PM - Teophile LONTSIE Back To Top (26440)

Hi

I have done every thing that is asked. FilestreamConfigureLevel and FilestreamEffectivelevell are 3

But I receive invalid Handle, last error gives error 87 : The parameter is Incorrect.

I have change different configuration from Win 7 to Win 8.

The last think to test is SQL Server because, I test SQL server 2008 Pack 1

I have the same error.

Please I really need help.

Kindly

 


Wednesday, March 14, 2012 - 2:28:56 PM - Mark Back To Top (16398)

I wish this article be in MSDN, thank you for good examples and excellent explanation!

 


Thursday, February 9, 2012 - 11:57:44 AM - Karthik & Siva Back To Top (15962)

You are great. This code helped us lot. Thanks.


Monday, February 6, 2012 - 12:58:59 PM - lee suko Back To Top (15914)

Store image in a database - C# source code

http://net-informations.com/csprj/dataset/cs-insert-image.htm

 

lee.

 

 


Tuesday, December 30, 2008 - 2:40:37 AM - manish Back To Top (2474)

Thank you so much Raybarley, It worked for me,

 

 


Friday, December 26, 2008 - 6:22:15 AM - raybarley Back To Top (2449)
Open SQL Server Configuration Manager, click on SQL Server Services (tree view on left side of window) then right click SQL Server (MSSQLSERVER) in the list of services, and select Properties. You will see a FILESTREAM tab. Click that tab then make sure to check Allow remote clients to have streaming access to FIELSTREAM data. You may need to restart the SQL Server service for this to take effect. However after checking allow remote clients the query you note above will return 3 for both the Configured Level and Effective Level. I checked this out in the release version of SQL Server 2008. I no longer have any of the CTP versions. It is possible that something changed between CTP and release.

Wednesday, December 24, 2008 - 10:51:03 PM - manish Back To Top (2444)

I enabled FILESTREAM for the database instance from SQL Server Management Studio (SSMS) and then restarted the server.

But still this following query

SELECT SERVERPROPERTY ('FilestreamShareName') ShareName,SERVERPROPERTY ('FilestreamConfiguredLevel') ConfiguredLevel

,SERVERPROPERTY ('FilestreamEffectiveLevel') EffectiveLevel 

results as

ConfiguredLevel  EffectiveLevel
2                       2

 Where as it sould be ConfiguredLevel =3 and EffectiveLevel = 3, I don;t know where I am missing any thing, Please advice if any one faced this too

 

 


Thursday, August 28, 2008 - 5:43:07 AM - raybarley Back To Top (1712)

There has been a change to the way you enable FILESTREAM since I did this tip which was based on the February CTP.  You should take a look at the following blog entry on the SQL Server Storage blog:

http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/03/filestream-configuration-and-setup-changes-in-sql-server-2008-february-ctp.aspx

It sounds like the part you still need to do is this:

EXEC sp_configure ‘filestream_access_level’, ‘[level_value]’

RECONFIGURE

·         Click Execute.

Here, [level_value] can be:

0 Disables FILESTREAM support for this instance.

1 Enables FILESTREAM for Transact-SQL access.

2 Enables FILESTREAM for Transact-SQL and Win32 streaming access.

 


Thursday, August 28, 2008 - 5:18:34 AM - culminIT Back To Top (1711)

Thanks for all that information, very helpful.  I just have a small problem.  I didn't enable filestream during installation, and know I'm struggling to get it 100%.  All my settings are as you describe.  In SQL Server Configuration Manager, I have enabled FILESTREAM up to level 'Allow remote clients to have streaming access to FILESTREAM Data'.  The Filestream access level under Advanced properties on the instance is set to 'Full access enabled'.  I have even managed to create a filestream filegroup on my database, and created a table with a filestream field to which I can add data.

 My problem is that I don't have the seemingly important stored procedure 'sp_filestream_configure'.  I also read to type 'net share' in the cmd prompt.  My share name states 'caching disabled', i.s.o. 'SQL Server FILESTREAM share'.  I have run the script suggested on: http://www.codeplex.com/SQLSrvEngine/Wiki/View.aspx?title=FileStreamEnable&referringTitle=Home but that didn't solve the problem.  Any other suggestions?


Wednesday, July 2, 2008 - 7:51:17 AM - mikebee27 Back To Top (1338)

Ray, thank you so much.  This worked perfectly.


Sunday, June 29, 2008 - 4:09:40 AM - raybarley Back To Top (1295)

 I backed up the database used in the article then restored it to a different database on the same SQL Server 2008 instance (I only have 1 right now, running in a Virtual PC).

Backup command: backup database sql2008_fs to disk = 'c:\temp\sql2008_fs.bak' 

View the backup contents command: restore filelistonly from disk = 'c:\temp\sql2008_fs.bak'

Partial Output :

 LogicalName     PhysicalName
--------------- ---------------------------------------------------------------------------------------
sql2008_fs    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\sql2008_fs.mdf
sql2008_fs_log    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\sql2008_fs_log.ldf
fs_filestream    C:\db\sql2008_fs

Note that the fs_filestream logical name is the filegroup setup for filestream.

Then I did a restore to another database name, moving the files to a different location on the same server. 

 restore database sql2008_fs_2
from disk = 'c:\temp\sql2008_fs.bak'
with
move 'sql2008_fs' to 'c:\temp\sql2008_fs.mdf',
move 'sql2008_fs_log' to 'c:\temp\sql2008_fs_log.ldf',
move 'fs_filestream' to 'c:\temp\sql2008_fs'

 I opened the new database; it looks the same as the original.

 


Friday, June 27, 2008 - 10:56:48 AM - mikebee27 Back To Top (1280)

Thanks for the quick response.


Friday, June 27, 2008 - 10:48:51 AM - raybarley Back To Top (1279)
You should be able to move the database by doing a backup and a restore.
Enable FILESTREAM on the target database (command example in the article).
Backup the source database; e.g. BACKUP DATABASE <sourcedbname> TO DISK = '<path>\sourcedbname.bak'
Restore the database backup to the target: e.g. RESTORE DATABASE <sourcedbname> FROM DISK = '<path>\sourcedbname.bak'

If you need to restore the database files to a different location than they
exist in the source, check the MOVE option in the RESTORE command;
see http://technet.microsoft.com/en-us/library/ms186858(SQL.100).aspx

Use RESTORE FILELISTONLY FROM DISK = '<path>\sourcedbname.bak' to get a look at the
the full path of the files in the source database.  Without the MOVE
option they will be restored to exactly the same path.

Partial example of RESTORE FILELISTONLY output for a backup of the "sample" database:

LogicalName    PhysicalName
sample         C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sample.mdf
sample_log     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sample_log.ldf

If you wanted to restore to a different location on a different server you would
run this from the target server:

RESTORE DATABASE sample
WITH MOVE sample TO 'C:\newdir\sample.mdf, sample_log TO 'C:\newdir\sample_log.ldf'

I don't know what you will see that's different if you do a RESTORE FILELISTONLY
on a database backup that has FILESTREAM enabled.


Friday, June 27, 2008 - 8:19:22 AM - mikebee27 Back To Top (1272)

Do you have any information - links, experience, advice, etc. - on how to move a SQL 2008 Filestream database to another server?  I have used this article's information to develop a document versioning application on my local machine w/ the 2008 Feb. CTP, and now it's time to move it to a public development server, and I can't get it over there to save my life without completely recreating it from scratch.  Any ideas would be appreciated.


Friday, May 2, 2008 - 6:41:27 AM - LeeFAR Back To Top (938)

Thanks for the reply.  Your response was as I thought.


Thursday, May 1, 2008 - 7:12:05 PM - raybarley Back To Top (933)

The general recommendation for a SQL Server FILE is that you use locally attached storeage or SAN.  However you can use trace flag 1807 to use a mapped or UNC location; see this KB article: http://support.microsoft.com/kb/304261 for the details.

I haven't seen anything definitive on whether this applies to the FILESTREAM filegroup.

The account that the SQL Server service runs as automatically gets permission to access the FILESTREAM container.

 

 


Thursday, May 1, 2008 - 10:51:35 AM - LeeFAR Back To Top (931)

When you create the filegroup, can this be a UNC or does it have to be a drive letter?  If the drive is not on the physcial server, then I would assume the service account running the DB Engine service would need rights to the remote server or is it a different account?

Thanks for the good info.  Glad to see this feature in 2008.















get free sql tips
agree to terms