?
Solved

reading data from replication SQL database

Posted on 2016-07-21
7
Medium Priority
?
129 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
  • 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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

Author Closing Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

593 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