SQL database not accessible during transactional replication

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SS2K5 Replication » SQL database not accessible during...

15 posts, Page 1 of 2

1 2 »»

SQL database not accessible during transactional replication

Rate Topic

Display Mode

Topic Options

Author

Message

p.megens

Posted Wednesday, March 03, 2010 12:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 4:33 AM
Points: 6, Visits: 31

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

Roy Ernest

Posted Wednesday, March 03, 2010 6:21 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:24 AM
Points: 3,280, Visits: 6,619

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

p.megens

Posted Wednesday, March 03, 2010 6:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 4:33 AM
Points: 6, Visits: 31

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

Roy Ernest

Posted Wednesday, March 03, 2010 7:02 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:24 AM
Points: 3,280, Visits: 6,619

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

Steve Jones - SSC Editor

Posted Wednesday, March 03, 2010 7:49 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

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

p.megens

Posted Wednesday, March 03, 2010 10:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 4:33 AM
Points: 6, Visits: 31

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

Steve Jones - SSC Editor

Posted Wednesday, March 03, 2010 10:48 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

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

p.megens

Posted Wednesday, March 03, 2010 11:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 4:33 AM
Points: 6, Visits: 31

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

Steve Jones - SSC Editor

Posted Wednesday, March 03, 2010 11:25 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

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

Roy Ernest

Posted Wednesday, March 03, 2010 11:32 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:24 AM
Points: 3,280, Visits: 6,619

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 »

15 posts, Page 1 of 2

1 2 »»

Permissions