SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication


Replication

Author
Message
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33030 Visits: 18560
Hany Helmy (1/22/2014)
Forgot it, so I had to make some RnD to get it right.


Nice approach. That's the kind of response I like to see.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

pchirags
pchirags
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2369 Visits: 615
nice and easy..
Thanks Steve.
PHYData DBA
PHYData DBA
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 537
Thanks for the easy question Steve.
Having worked a lot with all types of replication this might have been easier to answer for me that the 45% that have not found it so easy.
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14524 Visits: 12238
Interesting question. I thought it worked for all models, but ir's so long since I last looked at replication that I thought I might be wrong, so checked in MSDN. The page I checked was The Transaction Log, which seems to say that log records are retained until the data has been replicated only in full recovery model, which seems to me to imply that transactional replication will not work in either simple or bulk-logged model, so (thinking that MSDN was more likely to have that right than my memory) I picked that answer - the wrongest one of the three.

Is that page wrong, or do the other recovery models have some method of delaying the discard of log records pending replication other than by setting log_reuse_wait to 6 in the affected records? Or perhaps in this question "replication works" means "snapshot replication works" (snapshot uses value 7, not value 6, to delay truncation)?

Tom

Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11180 Visits: 12004
L' Eomot Inversé (1/23/2014)
Interesting question. I thought it worked for all models, but ir's so long since I last looked at replication that I thought I might be wrong, so checked in MSDN. The page I checked was The Transaction Log, which seems to say that log records are retained until the data has been replicated only in full recovery model, which seems to me to imply that transactional replication will not work in either simple or bulk-logged model, so (thinking that MSDN was more likely to have that right than my memory) I picked that answer - the wrongest one of the three.

Is that page wrong, or do the other recovery models have some method of delaying the discard of log records pending replication other than by setting log_reuse_wait to 6 in the affected records? Or perhaps in this question "replication works" means "snapshot replication works" (snapshot uses value 7, not value 6, to delay truncation)?


When replication is used, the log is not reused even when you are in simple recovery. Once replication has catched up, it will be available for reuse. (In full recovery, log records will not be reused until both the log backup and replication have catched up).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Revenant
Revenant
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7431 Visits: 4865
Simple, straightforward, nice. Thanks, Steve!
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14524 Visits: 12238
Hugo Kornelis (1/23/2014)
L' Eomot Inversé (1/23/2014)
Interesting question. I thought it worked for all models, but ir's so long since I last looked at replication that I thought I might be wrong, so checked in MSDN. The page I checked was The Transaction Log, which seems to say that log records are retained until the data has been replicated only in full recovery model, which seems to me to imply that transactional replication will not work in either simple or bulk-logged model, so (thinking that MSDN was more likely to have that right than my memory) I picked that answer - the wrongest one of the three.

Is that page wrong, or do the other recovery models have some method of delaying the discard of log records pending replication other than by setting log_reuse_wait to 6 in the affected records? Or perhaps in this question "replication works" means "snapshot replication works" (snapshot uses value 7, not value 6, to delay truncation)?


When replication is used, the log is not reused even when you are in simple recovery. Once replication has catched up, it will be available for reuse. (In full recovery, log records will not be reused until both the log backup and replication have catched up).

Yes, but how is release of the log records prevented? If the log_reuse_wait attribute in sys.databases is not set to 6 and there is no other value that indicates that transactional replication requires the log not to be truncated (value 7 is explicitly for snapshot creation, so not relevant to transactional replication except possibly while an initial snapshot is being created, and nothing else seems remotely relevant) it looks as if some method is used that doesn't use that attribute, so it seems that it can't be this attribute being nonzero that tells the system that it can't recover any virtual log files. Maybe simple and bulk logged recovery models set it to 1 when no VLF can be retrieved because of transactional replication?

Tom

Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11180 Visits: 12004
L' Eomot Inversé (1/24/2014)
Yes, but how is release of the log records prevented?

My guess is that the information on that page you used is incorrect. But I have never worked with a replicated database in simple recovery, and I am too lazy to set up a test scenario now.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14524 Visits: 12238
Hugo Kornelis (1/24/2014)
L' Eomot Inversé (1/24/2014)
Yes, but how is release of the log records prevented?

My guess is that the information on that page you used is incorrect. But I have never worked with a replicated database in simple recovery, and I am too lazy to set up a test scenario now.

Sounds like a reasonable guess to me. So maybe in future I'll trust my memory and not check in BOL even when it's something I last looked at a long time ago.

Tom

free_mascot
free_mascot
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4065 Visits: 2235
Good one! help to sharpen Replication skill... recalling old memories Smile

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search