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

Transactions in Transactional Replication Expand / Collapse
Author
Message
Posted Thursday, April 3, 2008 12:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, Visits: 32
Comments posted to this topic are about the item Transactions in Transactional Replication


Paul Ibison
Paul.Ibison@replicationanswers.com
Post #478977
Posted Thursday, April 3, 2008 7:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 3:02 AM
Points: 49, Visits: 102
Great article, thanks!



Post #479182
Posted Thursday, April 3, 2008 9:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 30, 2014 2:57 AM
Points: 503, Visits: 611
Another reason for being very careful with the MaxCmdsInTran parameter is that it can cause SQL Server to fail with error 9003. Basically, SQL Server gets its LSNs in a mess. You can recover from it with removing replication and putting it back on but only by deleting rows from msrepl_transactions and inserting what SQL Server "expects" to find there.
Post #479306
Posted Thursday, April 3, 2008 1:50 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 15, 2014 9:47 AM
Points: 226, Visits: 162
We are considering setting up a Transactional Replication and have some questions you could possible help us with.

When continuously, how often does the log reader agent update? Does it start as soon as the last Transaction is completed? If you don't set a scheduled time does that mean you are in the continuous mode?

We plan to have the subscriber as read only, is there a way to guarantee this beyond setting role\user permissions restrictions?

Eventually we want to break the replication and use the subscriber as the active database. What would be the best way to do this and have the most up to date information on the subscriber at time of separation?
Post #479521
Posted Friday, April 4, 2008 1:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 30, 2014 2:57 AM
Points: 503, Visits: 611
You are correct. When setting up replication you have the choice to run them either on a schedule or continuously. Which you chose depends on a number of factors. We always run in continuous mode
because we replicate very large volumes of data and running on a schedule would introduce latency which we don't want. Prior to SQL Server 2005, if an agent failed for some reason, e.g. network loss, that was it. It was stopped. Therefore, I have had to develop a complete monitoring system to look for any failed agents and restart them. SQL Server 2005 retries, though I still need my monitoring code. So, if you can live with a bit of latency the scheduled run option may be a better bet for you.

In continuous mode, prior to SQL Server 2005, the Log Reader Agent scanned the log file every 10 seconds by default. In SQL Server 2005 this has changed to every 5 seconds. You can override this by creating your own profile and changing the value of the PollingInterval parameter. We leave it as it is.

On to your subscriber as read only question. We have a SQL Server login specifically for the replication agents. The login requires public access to master (as of SQL Server 2000) and then we give it db_owner access in the publisher and subscriber databases. Then, as you say, user permissions can be set to ensure read only access.

There's a lot around your last question so it's difficult to give more than a bit of a woolly answer. To ensure the subscriber is up to date you would have to stop writing at the publisher and make sure all transactions have replicated. The Replication Monitor (SQL Server 2005) or Enterprise Manager can be used to determine this. Slight gripe now. SQL Server 2005 Replication Monitor is a major step backwards. It's awful, though I seem to be alone in this judgement from articles I've read. Anyway,
once all the transactions have replicated just drop the subscription(s).

Presumably, whatever was updating the publisher will now be writing to the new active database to keep it up to date.

I waffled on a bit but I hope all that helps.
Post #479733
Posted Friday, April 4, 2008 2:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 18, 2009 5:13 AM
Points: 98, Visits: 72
Great article

I wanted to share a problem I had with Failing Transactions on the Subscriber. I had set the distribution agent subscription streams parameter to 4(no. of processors on the server)

BOL says
When you specify a value of 2 or greater for -SubscriptionStreams, the order in which transactions are received at the Subscriber may differ from the order in which they were made at the Publisher. If this behavior causes constraint violations during synchronization, you should use the NOT FOR REPLICATION option to disable the enforcement of constraints during synchronization. For more information, see How to: Control the Behavior of Triggers and Constraints During Synchronization (Replication Transact-SQL Programming).

I found that the order the transactions were arriving at the publisher caused a deadlock and so some of the transactions were killed at the subscriber leaving inconsistent data. i think this is the only scenario where the transaction commited on a publisher are not replicated to the subscriber in a consistent state.
Post #479760
Posted Friday, April 4, 2008 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 3:02 AM
Points: 49, Visits: 102
mdowns (4/4/2008)
Prior to SQL Server 2005, if an agent failed for some reason, e.g. network loss, that was it. It was stopped. Therefore, I have had to develop a complete monitoring system to look for any failed agents and restart them. SQL Server 2005 retries, though I still need my monitoring code.


Instead of the "complete monitoring system", you could simply add another schedule for the existing job of every one minute. This way it would restart automatically if it fails.

The continuous/non-continuous setting is done by simply adding/removing the "-continuous" flag in the agent. Can be done regardless of the scheduling.




Post #480035
Posted Friday, April 4, 2008 9:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 30, 2014 2:57 AM
Points: 503, Visits: 611
meird

We did consider such things but with 439 Log Reader Agents and 2445 Distribution Agents and given that we originally delivered this on SQL Server 7 we decided against it. SQL Server 7 was nowhere near as flexible as 2000/2005 and all too often upgrades to our software required subscriptions and publications to be dropped and re-created which would mean having to put back in the job step you describe. Too much of an administrative overhead back then.
Post #480053
Posted Monday, April 7, 2008 4:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
Great article! Demystifies some of the worries that I've had about replication.


Post #480676
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse