Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL database not accessible during transactional replication


SQL database not accessible during transactional replication

Author
Message
p.megens
p.megens
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 6852
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
p.megens
p.megens
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 6852
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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36282 Visits: 18752
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
My Blog: www.voiceofthedba.com
p.megens
p.megens
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36282 Visits: 18752
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
My Blog: www.voiceofthedba.com
p.megens
p.megens
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36282 Visits: 18752
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
My Blog: www.voiceofthedba.com
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 6852
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
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