Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Replication Expand / Collapse
Author
Message
Posted Wednesday, January 22, 2014 7:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:49 PM
Points: 17,967, Visits: 15,979
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
Post #1533624
Posted Wednesday, January 22, 2014 9:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:49 AM
Points: 1,848, Visits: 587
nice and easy..
Thanks Steve.
Post #1533727
Posted Wednesday, January 22, 2014 10:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:07 AM
Points: 348, Visits: 311
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.
Post #1533757
Posted Thursday, January 23, 2014 1:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:45 AM
Points: 7,860, Visits: 9,606
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
Post #1534234
Posted Thursday, January 23, 2014 1:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 6,098, Visits: 8,365
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
Post #1534243
Posted Thursday, January 23, 2014 1:40 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
Simple, straightforward, nice. Thanks, Steve!
Post #1534250
Posted Friday, January 24, 2014 11:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:45 AM
Points: 7,860, Visits: 9,606
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
Post #1534608
Posted Friday, January 24, 2014 11:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 6,098, Visits: 8,365
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
Post #1534614
Posted Saturday, January 25, 2014 12:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:45 AM
Points: 7,860, Visits: 9,606
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
Post #1534738
Posted Monday, January 27, 2014 1:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
Good one! help to sharpen Replication skill... recalling old memories :)

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1534883
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse