MSSQLTips

This post is related to this tip: Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008

http://www.mssqltips.com/tip.asp?tip=1489

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.

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.

 

 

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

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.

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.

Thanks for the quick response.

 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.

 

Ray, thank you so much.  This worked perfectly.

 

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?

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.

 

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

 

 

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.

Thank you so much Raybarley, It worked for me,