March 12, 2010 at 4:21 am
Hello All,
I have an application which deals with binary data. Currently it has been implemented with a standard methodology of keeping file path in the DB and the actual file outside the DB.
However this requires us to rely on two teams for backups (in our org windows file system back is done by a different team from SQL DBA team).
This has obvious problems in keeping two backups in sync and upto date.
I thought about using FileStream, but one of the crippling limitations of FileStream is that Replication is not supported (which is the form of HA we were using for our solution because few minutes of downtime is OK for us).
The documentation says Log Shipping is supported, but I don't understand that how come mirroring doesn't work and log shipping does? (from my understanding mirroring is a more sophisticated/automated form of log shipping).
Has anyone used Log Shipping with FileStream Database ... does it work?
March 12, 2010 at 4:40 am
Read the White Paper:
FILESTREAM Storage in SQL Server 2008
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 5:30 am
Hello,
FS property is actually fits very well with your backup including files on file system.
But I do not know how it works with log shipping, replication, etc.
But I can suggest you LiteSpeed SQL Server tool in order to backup your db with file folders. There is an attach file option.
You can download its evaluation version and try it.
I hope that helps,
Eralper
March 12, 2010 at 5:55 am
Eralper (3/12/2010)
...SPAM...Eralper
Please do not post advertisements.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 5:59 am
Hello Paul,
I'm sorry you thought that to be spam.
Because I gave the name of a tool that I downloaded, tried and found useful for backing up a SQL database with the file folders.
It is a very common scenario that you keep files in system folders instead of database itself.
This is actually what is FileStream is for.
I posted my experience with that tool only. That is not a spam.
March 12, 2010 at 7:19 am
Erlaper,
Can you please resend your experiences to me directly on my mail bi.dw.architect@gmail.com
Edit: As far as the light speed is concerned, I have heard about it, but in my company procurement is so bureaucratic that I don't want to go that way
March 12, 2010 at 7:54 am
To answer the question of why it supports Log Shipping but not Mirroring is probably more down to the QA teams at Microsoft rather than the architecture not supporting it.
I believe as well that FILESTREAM has it's own transaction log and although it's included in the output from BACKUP LOG in the same file, it may be architecturally quite different to the traditional transaction log and not very well suited to constantly outputting to a network stream rather than periodically to disk.
Not sure how nicely 2gb+ files would work with max safety mode either!
March 12, 2010 at 8:41 am
> I believe as well that FILESTREAM has it's own transaction log and although it's included in the output from BACKUP LOG in > the same file, it may be architecturally quite different to the traditional transaction log and not very well suited to
> constantly outputting to a network stream rather than periodically to disk.
Ah and here is the fundamental problem. If the architecture of the File Stream log is such that it is unsuitable for continuously be written over the network? then how will log shipping work?
Mirroring and Log shipping are not very different because both of them send the log continuously. So if one is not suitable then the other would automatically become unsuitable.
This is what I don't understand....
March 12, 2010 at 9:02 am
No, that's not the case.
Log shipping consists of 3 basic operations. Backup, Copy and Restore.
By default data is backed up every 15 minutes and there are jobs automatically created that copy it to the standby and restore the log.
See the below for more information:
http://msdn.microsoft.com/en-us/library/ms187103.aspx
Mirroring is a different beast. All transactional data is streamed as it occurs to the mirror server and either syncronously or asyncronously applied.
See the below for an overview:
http://msdn.microsoft.com/en-us/library/ms189852.aspx
I think providing this level of real time processing to FILESTREAM would have too many overheads as it would constantly be mining for differences between before/after copies of files
March 12, 2010 at 9:17 am
Eralper (3/12/2010)
I'm sorry you thought that to be spam. I posted my experience with that tool only. That is not a spam.
It looked like spam to me - and a bit naughty posting competing products to a site sponsored by redgate. Just sayin'.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 9:44 am
Paul White (3/12/2010)
Eralper (3/12/2010)
I'm sorry you thought that to be spam. I posted my experience with that tool only. That is not a spam.It looked like spam to me - and a bit naughty posting competing products to a site sponsored by redgate. Just sayin'.
Actually, it happens frequently, but usually othr competing products are mentioned as well and the individual encouraged to try them all and select the one that best fits their needs/requirements/price/etc.
March 12, 2010 at 6:55 pm
Lynn Pettis (3/12/2010)
Paul White (3/12/2010)
Eralper (3/12/2010)
I'm sorry you thought that to be spam. I posted my experience with that tool only. That is not a spam.It looked like spam to me - and a bit naughty posting competing products to a site sponsored by redgate. Just sayin'.
Actually, it happens frequently, but usually othr competing products are mentioned as well and the individual encouraged to try them all and select the one that best fits their needs/requirements/price/etc.
Good point. To explain further, I read the offending post as saying "I don't know the answer to your question, but download this unrelated product instead."
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply