Solved

reading data from replication SQL database

Posted on 2016-07-21
7
109 Views
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?
0
Comment
Question by:ken hanse
[X]
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
7 Comments
 
LVL 27

Expert Comment

by:Zberteoc
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.
0
 
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.
0
 

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?
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 27

Expert Comment

by:Zberteoc
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?
0
 

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.
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 500 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) ...
1
 

Author Closing Comment

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

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a 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.

707 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