Log Shipping and BLOB data

[Ellen K]

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

[Erland Sommarskog]

Re: Log Shipping and BLOB data

Ellen K (ekaye2002@yaho o.com) writes:[color=blue]
> 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.[/color]

A BLOB normally resides on its own page. However, this page is also
logged, else ROLLBACK TRANSACTION would be very tragic.
[color=blue]
> If log shipping works by applying the transaction log to the standby
> database, then what happens to the BLOB data?[/color]

It's shipped as well.
[color=blue]
> Related question, how does transactional replication work? Is it also
> based on the transaction log?[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ellen K]

Re: Log Shipping and BLOB data

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 :)

[Erland Sommarskog]

Re: Log Shipping and BLOB data

Ellen K (ekaye2002@yaho o.com) writes:[color=blue]
> Thanks so much. So I think I can use log shipping for my availability
> / disaster recovery plan, the BLOB data was my only worry.[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ellen K]

Re: Log Shipping and BLOB data

Absolutely.

Thanks again. :)