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,

EllenEllen K (ekaye2002@.yahoo.com) writes:
> 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.

A BLOB normally resides on its own page. However, this page is also
logged, else ROLLBACK TRANSACTION would be very tragic.

> If log shipping works by applying the transaction log to the standby
> database, then what happens to the BLOB data?

It's shipped as well.

> Related question, how does transactional replication work? Is it also
> based on the transaction log?

Yes. I don't remember the exact details, but basically log entries are
sent to the distributor which spreads them around. Note an important
feature of transacional replication is that what is a transaction in
the publisher, is also a transaction in the subscribers.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Thanks so much. So I think I can use log shipping for my availability
/ disaster recovery plan, the BLOB data was my only worry.

Ellen :)|||Ellen K (ekaye2002@.yahoo.com) writes:
> Thanks so much. So I think I can use log shipping for my availability
> / disaster recovery plan, the BLOB data was my only worry.

It goes without saying that you have not completed the work with setting
up a recovery plan, until you actually have simulated a disaster recovery.
(And depending on your availability requirements, is something that should
be exercised with some frequency.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Absolutely.

Thanks again. :)

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.googlegroups.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.googlegroups.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.

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.