SMSQL and MS SQL Filestream databases

[rdenyer001]

First off I am not a SQLdatabase person.

Our SQL programmers, (who are not storage people), asked me to create a new LUN on our SQL 2008 Server to hold "filestream" data.

From a SQL point of veiw every thing is working fine, BUT  SMSQL 5.0R1  wount run as it feels the configuration is invalid.

Data LUN  is  Drive E

Log LUN is  Drive  F

SnapInfo LUN  is Drive H

New Filestream data LUN is drive I

When trying to run the configuration wizard  I get this error;

The database cold not be migrated to the LUNs [E] and and [I], because LUNs [E] and [F] are currently shared by databases in a mulitiple LUN configuration. To proceed, click "reconfigure" to specify a different database path.

Choose from either of the following actions:

1. Migrate the databases to the LUNs [E] and [F].

2. Migrate the databases to a different LUN altogether

I have tried creating a new LUN [J]  and migrating [I] to [J] and this does not work either.

The only thing that seems to want to work is to migrate  [I] into  [E], and our SQL people are not kean on this as they wanted to keep the filestream data seperate from the "normal"  SQL databases files.

I have updated to SMSQL 5.1  - but have the same problem still.

Anyone got  suggestions  ?

Regards,

Richard

[reena]

Hi Richard,

This is happening coz the LUN I: is actually containing the filestream data as part of the database sitting on the LUN E: and both these E: and I: are sharing the log LUN F:. Per SMSQL this is an invalid configuration. For the sharing model, it wants the databases together in a LUN and the logs in a LUN, but if the databases are split between two LUNs and their logs are still on a same LUN, then Config Wizard would flag the error.

Check this KB article, hope this helps.

https://kb.netapp.com/support/index?page=content&id=2015362

Reena

[rdenyer001]

Thanks Reena,

So would my confuguration  work if  I created a seperate  log LUN for  the filestream databases ?

Regards,

Richard

[reena]

Richard,

So here's the thing.

1. Config 1:
   
   • DB1 + Log1 +FS1 in same LUN - supported
     
2. Config 2:
   
   • DB1 (dedicated LUN)
     
   • Log1 (dedicated LUN)
     
   • FS1 (dedicated LUN)   - Supported
     
3. Config 3:
   
   • DB1 + DB2 (shared LUN)
     
   • Log1 + Log2 (shared LUN)
     
   • FS1 + FS2 (shared LUN) - Supported
     
4. Config 4:
   
   • DB1 + DB2 (shared LUN)
     
   • Log1 + Log2 (shared LUN)
     
   • FS1 (dedicated LUN)  - not supported
     

Hope this clarifies.

Reena

[rdenyer001]

Thanks Reena,

So we have Option number 3

but we stil get this message

The database cold not be migrated to the LUNs [E] and and [I], because LUNs [E] and [F] are currently shared by databases in a mulitiple LUN configuration. To proceed, click "reconfigure" to specify a different database path.

Choose from either of the following actions:

1. Migrate the databases to the LUNs [E] and [F].

2. Migrate the databases to a different LUN altogether

But  we have quite a few more DBs   and not all of them have  file stream data

Regards,

Richard

[reena]

Yes.. that’s where the problem is. You should move out all those databases and their logs from these shared LUNs with the Databases + FS to some other LUNs where they could share them. The idea is to have even distribution of the identical structure in the LUNs. DBs with FS and DBs w/o FSs cannot live in same shared LUNs.

Reena

[qzhang]

We don't support option 3. For configuration where multiple databases sharing LUN(s), everything from the database include filestrem should be on those LUN(s). SMSQL only supports two types of multiple dbs sharing LUN(s) configuration, one is sharing one LUN, another one is sharing two LUNs. That means SMSQL doesn't support multiple dbs sharing 3 LUNs, 4 LUNs, etc (but single db does). So in your case, you have a configuration that multiple dbs is sharing 2 LUNs (E and F), So everything including FS should be on those two LUNs. If you want filestream on a separate LUN, you can move DB+LOG to E:\, FS to F:\.

Thanks, Qing

[rdenyer001]

So it looks like the bottom line is that  although  Microsoft SQL is quiet happy with the way our SQL DBA  has structure his data, SnapManager for SQL is not so happy.

We were under the  impression that  the whole idea of "FileStreams" was that it enabled you to  seperate your  "blob"  type data  from you DB files

This is from the Micosoft site

Much data is unstructured, such as text documents, images, and videos. This unstructured data is often stored outside the database, separate from its structured data. This separation can cause data management complexities. Or, if the data is associated with structured storage, the file streaming capabilities and performance can be limited.

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

By the looks of what is recommended here the only way we will get SMSQL to work is  to place it all on the same LUN 

[qzhang]

The reason SMSQL cannot run on any db layout is that it has to consider not only backup, but also how to perform fast restore. Fast restore is at the LUN level. If database files are spread everywhere, restore will not be fast, and you will not be taking advantage of snapshot backup.

Thanks,

-Qing

[qzhang]

Clarification on my earlier statement that "If database files are spread everywhere, restore will not be fast", I meant if database files are spread everywhere AND files from different databases reside on same LUN(s), restore will not be fast since you will have to use copy method to perform a restore. Otherwise restoring one database will also interrupt the service of other database(s) if fast LUN restore is used in such configuration. You can have database files spread everywhere on multiple LUNs/volumes and still do fast restore, as long as they are not sharing LUNs with other database files.

Thanks, Qing