Log Shipping with Filestream data?

Question

Log Shipping with Filestream data?

david_wendelken

SSCrazy

Points: 2388
More actions
September 27, 2010 at 1:59 pm

#238785

I'm trying to set up a disaster recover setup so a copy of the databaes is available if the primary site is burned up, flooded out, etc.
As I understand it so far, filestream data contains pointers to files stored in the operating system. Is that correct?
If so, if I set up log-shipping between my local database and a remote, off-site database, I'll be happily shipping pointers to a file that won't be accessible after my primary site burns down, 'cause that drive just got burned up with all the rest of th primary site equipment. Is that correct?
In theory, the file pointers entered via user action could point to ANYWHERE on our network, couldn't they? Do application environments like CRM and SHAREPOINT have the ability to limit what directories the filestream data can be placed in, or do we have to rely upon the discipline of individual developers/vendors?
Are there existing utilities that are smart enough to find those files and copy them to a backup device when they are changed? Is the pointer data in the filestream data accessible enough for someone to write such a utility if they wanted to?
Or is my understanding gained from my preliminary research into these topics misguided, and none of this is a problem? Here's hoping I'm wrong about this... 😉

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

The filestream pointers are pointing to the filegroup, rather than the actual path on the drive. The filegroup, like any other, can be pointed wherever you want when you restore the database and prep it for log shipping.

Inserting the actual data is still a logged operation, so it should be created on the log shipping destination server too.

david_wendelken SSCrazy Points: 2388 More actions · Answer 2

Derrick Smith (9/27/2010)
Inserting the actual data is still a logged operation, so it should be created on the log shipping destination server too.

I haven't started working with filestream data yet, so I'm still fuzzy on the details.

That sounds like we define a filegroup or groups to hold the filestream data for a given database? Which means that the files have a limited set of directories that they can be in?

And, if there is any sort of validation mechanism in place when the insert happens on the secondary device, that would mean the files would have to be there first, before the transaction was entered into the database? Is there such a validation check?

Do the transaction log shipping mechanisms built into SS 2008 handle copying those files at the right time?

NickDBA Hall of Fame Points: 3792 More actions · Answer 3

We use LogShipping as part of DR running SQL 2K8. There have been rumblings from some quaters to use filestreaming for document storage. If so, then having read this post / answers will help. 🙂

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 4

Hi All

I'm agree with 'Derrick Smith, B'coz in Log shipping with File stream, when my file get deleted from Disk path then we can restore the latest transaction shipped on Secondary server its get all the file recovered.

So in my opinion its not a headache to copy the File stream documents with an extra effort.

Ali
MCTS SQL Server2k8