?
Solved

Triggers on replicated SQL-server database

Posted on 2013-01-21
4
Medium Priority
?
325 Views
Last Modified: 2013-01-28
I am using sql-server 2005. My setup is like this:

Server A ----> Server B.

Server A is replicated to server B synchronously. (No data needs to come back from server B) I am using the built in transcription replication.


I have written triggers that need to fire when data is modified on Server A. I only need to read the data generated by these triggers on server B.

The triggers involve some complex views and will be quite taxing on server A. Server A is mission critical, server B is not.  I want there to be minimal impact on Server A.

Is it possible to have triggers fire on Server B when the transactions from Server A change things, as opposed to have them fire on Server A when the tables are first modified?

To illustrate:

SCENARIO ONE:

a. User modifies table x on Server A.
b. an update to table x causes a trigger to fire which records data in table y.
c. Server A replicates the data in table x and table y to server B.

SCENARIO TWO:

a. User modifies tabel x on Server A.
b. Server A replicates the data in table x to server B.
c. An update trigger on Server B fires when it detects that the replication transaction has changed the data in table x.
d Server B records data in table y.


I want Scenario Two, can it be done?
0
Comment
Question by:rdun25
[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
  • 2
  • 2
4 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 38803616
Hi,

The thing to be careful of is what is the replication synchronisation set to? Is it delete table and create a new one? (this will delete your triggers) or truncate and insert records?

I can't see why scenario two would work.

The catch for me is that you said the replication is synchronous - doesn't that mean that before ServerA commits a transaction, it sees that it committed in ServerB first? Therefore anything you do by way of a trigger on either server will affect performance.

Do you need a trigger? Can it be done on ServerB with a regularly scheduled job?

Regards
  David
0
 

Author Comment

by:rdun25
ID: 38807873
Thank you for your response. I am sorry, I may be confusing terms here (I've only had cursory experience with SQL).

The replication from Server A to Server B is a transactional replication. So any changes to Server A are replicated to Server B. I am using the built in Replication system.

Could you elaborate on  how a scheduled job would work? I need to record if any changes have been made to several tables in the database. At the moment there are UPDATE, CREATE and DELETE triggers which record the Primary Key of the altered table and a timestamp.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 38811355
Hi,

I understand the inbuilt transactional replication, as I used it extensively on a previous job.

Scheduled Job. What I'm asking here, is there any way to identify new data without the use of a trigger. Can you have a separate table with the id's of the rows already processed. Then a select to find the new ones is

select t.*
from dbo.sometable t
left outer join idtable i
    on i.id = t.id
where
    i.id is null

So instead of a trigger, write a stored procedure to do what you need, and then in SQL Agent create a job with the job-step which calls the procedure, and schedule it every couple of minutes.

HTH
  David
0
 

Author Closing Comment

by:rdun25
ID: 38829365
Thanks, that's brilliant. Probably be better and less demanding than triggers.
0

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

649 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