log shipping and BLOB data

Hi all,
It was my understanding (Please correct me if I'm wrong on this!) that
BLOB data actually reside on their own separate pages and a BLOB field
only holds a pointer to the location of the actual data, therefore the
BLOB data per se would not get written to the log, only the pointer
would be written.
If log shipping works by applying the transaction log to the standby
database, then what happens to the BLOB data?
Related question, how does transactional replication work? Is it also
based on the transaction log?
TIA,
Ellen
> It was my understanding (Please correct me if I'm wrong on this!) that
> BLOB data actually reside on their own separate pages and a BLOB field
> only holds a pointer to the location of the actual data, therefore the
> BLOB data per se would not get written to the log, only the pointer
> would be written.
Text, ntext and image data may be stored on separate pages, depending on the
'text in row' table option. The default is to store only the text pointer.
However, regardless of the setting, data are written to the log

> Related question, how does transactional replication work? Is it also
> based on the transaction log?
Yes, transactional replication uses log data. See the Books Online for a
thorough description.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1118177847.585875.143740@.g44g2000cwa.googlegr oups.com...
> Hi all,
>
> It was my understanding (Please correct me if I'm wrong on this!) that
> BLOB data actually reside on their own separate pages and a BLOB field
> only holds a pointer to the location of the actual data, therefore the
> BLOB data per se would not get written to the log, only the pointer
> would be written.
>
> If log shipping works by applying the transaction log to the standby
> database, then what happens to the BLOB data?
>
> Related question, how does transactional replication work? Is it also
> based on the transaction log?
>
> TIA,
>
> Ellen
>
|||Thanks very much. I'm not sure where I got the idea that the BLOB data
wouldn't be logged, but it's been bothering me as I try to come up with
an availability / disaster recovery plan because the whole raison
d'=EAtre of one of our databases is to store BLOB data and although log
shipping otherwise seemed like the best choice, I was very worried
about this point. So thanks for putting my mind at ease.
|||I should add that my comments are applicable to SQL Server 2000. In older
SQL Server versions, client apps could specify the NO LOG option on
WRITETEXT and UPDATETEXT statements if the 'select into/bulk copy' database
option was on and this would break the log backup sequence. This isn't an
issue in SQL Server 2000 when the database recovery model is FULL or
BULK_LOGGED (a requirement for log shipping).
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1118194929.139819.255330@.g43g2000cwa.googlegr oups.com...
Thanks very much. I'm not sure where I got the idea that the BLOB data
wouldn't be logged, but it's been bothering me as I try to come up with
an availability / disaster recovery plan because the whole raison
d'tre of one of our databases is to store BLOB data and although log
shipping otherwise seemed like the best choice, I was very worried
about this point. So thanks for putting my mind at ease.
|||We are using SQL Server 2000, no problem. But thanks for the
additional information.