reading data from replication SQL database

Posted on 2016-07-21
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 26

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 26

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?
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

LVL 26

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 26

Accepted Solution

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) ...

Author Closing Comment

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

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

696 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