At work we're going to store media for our clients such as video, HD photos and audio, I suggested using the SQL FILESTREAM to store such media.
One of the main concerns about this approach is what happens if the FILEGROUP gets corrupted?
Any other concerns we should take into account?
Please read the CIL.
One of the main concerns about this approach is what happens if the FILEGROUP gets corrupted?
A: If Filestream Filegroup corrupted, then you database will be in "recovery pending"
status, just like other filegroup corrupted. But almost only one kind of corruption
of FileStream FileGroup can happen, which is hdr corrupted, which is kind of
dictionary file, saving file header information.
•Would this affect the Database or its functioning?
A: Yes, this will leave your database not functional.
•Will there be any warning the data might be corrupted?
A: No, if just data is corrupted in your BLOB files, there is no warning.
Actually those just binary files, I can't think a scenario it can be corrupted.
•Does backing up a database with corrupted FILEGROUPS raise any warning? Or does it just backup normally?
A: If the Filestream FileGroup is corrupted, your database is not functional,
you have to restore the database.
•If a backup can be made with corrupted data, What about the Restore?
A: Backup can't be made with corrupted data, maybe dirty data possible,
but dirty data still is legal data. When I say dirty data, for example,
someone has permission to your filestream in SQL Server will also have the
permission to your BLOB files, they can directly update the BLOB file content,
which may involve some dirty data.
As of other concerns, you need to do full backup/Differential backup/Tranlog backup
of your database regularly, just in case disaster happens. Filegroup backup will not
help you.