reading data from replication SQL database

Posted on 2016-07-21
Medium Priority
Last Modified: 2016-08-11
Hi ,

I need to ask one of your expert that if there is possibility that DEAD LOCK condition happen on a replicated SQL server database?

I mean will the Database fall over due to dead lock condition and will interrupt the main production server?
Question by:ken hanse
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 27

Expert Comment

ID: 41725258
If it is a merge replication I think it could be possible. In it is a transnational replication then deadlocks that happen on the publisher(source) cannot cause interruptions on subscriber(target) database.
LVL 27

Expert Comment

by:Chris Luttrell
ID: 41732339
Just from Google searches, Yes, deadlocks can happen.  Have only done done one way Publish/Subscribe myself.

Author Comment

by:ken hanse
ID: 41738382
Just to clarify the answers, if I read the data on replicated data source on my ETL, can the DEAD LOCK condition could  occurred in the sources database if I read/write happened in a same time frame?
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

LVL 27

Expert Comment

ID: 41739150
What do you mean by "replicated data source"? Please use the correct terminology. In a replication process the source server/database is called publisher and the target server/database is called subscriber.

What ETL are you talking about? You use some third party process ETL on top of replication?

Author Comment

by:ken hanse
ID: 41746649
Hi Zberteoc,

I'm a ETL developer, I connect to the Data sources and bring the data into Data warehouse.

For the Data source, I use the replicated data source which is subscriber. My ETL connect to the subscriber.

Hope this will clear your answer.
LVL 27

Accepted Solution

Zberteoc earned 2000 total points
ID: 41747433
SQL server is pretty smart and efficient when applies the transactions through replication but in a scenario when a large transaction is replicated, lots of rows, it will create locks on the subscriber. One work around is to use the NOLOCK hint in your ETL queries done against your data source.

SELECT ... from YouTable WITH(NOLOCK) ...

Author Closing Comment

by:ken hanse
ID: 41753416
This is a best solution.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question