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 12»»

SQL database not accessible during transactional replication Expand / Collapse
Author
Message
Posted Wednesday, March 3, 2010 12:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:45 AM
Points: 8, Visits: 38
Hello,

I've setup transactional replication between two SQL servers.

The publication database is on a standalone server and the subscription runs in a live environment where the database has to be accessible during replication. Both are connected accross an internet connection. The subscription has some stored procedures that are used by a website.

During replication I notice that the subscription database is sometimes not reachable, timeouts appear and it looks like it's locked by the replication. This causes a lot of errors on the stored procedures that run in the database, needed for the website. Total replication time is about 2 hours because there's a relatively slow connection between the two servers (different countries).

My opinion was that the subscription should be completely accessible during replication, but it seems it's not. As I look at the subscription server I see no high load or high memoy usage that can cause the problems.

I can't hardly find any information on this subject on the internet.
Can anyone please help me how to solve this?

best regards,
Jens
Post #875727
Posted Wednesday, March 3, 2010 6:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:27 PM
Points: 3,309, Visits: 6,702
One thing you can do is to set up SnapShot Isolation. (Row Level version) This means that the select queries (Stored procs) from your website will be able to read the last committed data. It will not have to wait for the Replication to release lock before your Stored procs can read the data. It is a simple set up.
I did write an article regarding this. Check here.
I did not understand one thing though. You stated it takes 2 Hrs for the replication to complete. Does the updates that happen in publisher comes in Batches of Hundreds of thousands of records?



-Roy
Post #875898
Posted Wednesday, March 3, 2010 6:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:45 AM
Points: 8, Visits: 38
Hello Roy,

Yes indeed, the publisher will have around 100000 to 400000 updates and new records with each replication. In total the table has around 4 million records.

I thought this would be no problem because replication was made for this, but it looks if the table is locked during replication.

Thanks for your article, I will read it and hope it gives me some more information.
Post #875922
Posted Wednesday, March 3, 2010 7:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:27 PM
Points: 3,309, Visits: 6,702
Replication is designed to do this pretty fast. But since you have latency, it takes more time to get this done. Any data manipulation will need a lock on the target (Subscriber).
The article will give you one way of mitigating the timeout issue. There might be other ways as well.


-Roy
Post #875943
Posted Wednesday, March 3, 2010 7:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:06 PM
Points: 33,169, Visits: 15,304
I'm confused what you mean by the subscriber is not accessible? Replication runs continuously, so is the subscriber never accessible? Or do you have scheduled subscription pull/push?

I haven't seen SQL Server not work during these times. When it's not accessible, do you mean you cannot connect? Or some stored procedures have long runtimes? Can you narrow down more what's happening?

When transaction replication runs, it doesn't necessarily stop access to your data. It runs log records back against your database, just like another user that's connected to the server.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #875983
Posted Wednesday, March 3, 2010 10:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:45 AM
Points: 8, Visits: 38
In our situation replication doesn't run continuously. It's scheduled for the night and it runs a few times a week. To my opinion that's better because also the data in the publisher is updated only a few times every week.

And yes you're right, SQL server is working and some data can be retrieved from the tables. But in most cases it takes a very long time for (some relatively simple) stored procedures to run. This will end up with a timeout error on the website.

I hope the problem is a bit more clear now.
Post #876143
Posted Wednesday, March 3, 2010 10:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:06 PM
Points: 33,169, Visits: 15,304
So here's what I suggest. When things are slow, check for locking/blocking. I would be concerned that someone has not setup transactional replciation and instead setup some snapshot replication, or that they are dropping and adding back data.

Are there lots of changes on the publisher?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #876180
Posted Wednesday, March 3, 2010 11:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:45 AM
Points: 8, Visits: 38
Hello Steve,

Could it be a good idea to use Snapshot Isolation (Row Level version) as Roy proposes?

Yes there are around 100000 to 400000 changes on the publisher each time before it's replicated. Replication is indeed setup as transactional replication, so no snapshot replication.
Post #876193
Posted Wednesday, March 3, 2010 11:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:06 PM
Points: 33,169, Visits: 15,304
I would think snapshot isolation would help, but I'm not a replication expert. With that many changes coming through, I suspect you are going to have issues. It's like packing that many transactions from clients into a small window.

Have you considered using more regular replication?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #876220
Posted Wednesday, March 3, 2010 11:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:27 PM
Points: 3,309, Visits: 6,702
Continuous replication would have been a better option. But still if you are going to have bulk updates you will still have the issues. Another thing you might want to look at is to see what kind of WAIT Types are happening while the Bulk Updates are being replicated.
This might be showing an underlying problem in your Server set up.


-Roy
Post #876233
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse