January 27, 2015 at 1:04 pm
Prod database "Mother" Copy of database "Father"
Here's my scenario I need help with: I have a database that has a table called User_Group table on our Production db "mother". The dev team wants me to add a trigger to add those changes into the same table on another database "Father"(on another server). The the remote database is "Father" and when the so when records get changed on "Mother" they are copied into there?
Does that make sense?
Any ideas?
From what I gather:
I guess they want it realtime. So when an update, insert, delete is performed on the table, it will update the dev "father" database. Sounds more for change tracking and recovery incase someone changes something they need a way to revert back?
Explanation on why not replication from them: I suggested replication but they do not want that. Because they already have replication on another server I guess strictly used for reporting. So they don't want to use that server. The trigger would be getting copied to their development database.
January 27, 2015 at 1:33 pm
jcosley (1/27/2015)
Prod database "Mother" Copy of database "Father"
Here's my scenario I need help with: I have a database that has a table called User_Group table on our Production db "mother". The dev team wants me to add a trigger to add those changes into the same table on another database "Father"(on another server). The the remote database is "Father" and when the so when records get changed on "Mother" they are copied into there?
Does that make sense?
Any ideas?
From what I gather:
I guess they want it realtime. So when an update, insert, delete is performed on the table, it will update the dev "father" database. Sounds more for change tracking and recovery incase someone changes something they need a way to revert back?
Explanation on why not replication from them: I suggested replication but they do not want that. Because they already have replication on another server I guess strictly used for reporting. So they don't want to use that server. The trigger would be getting copied to their development database.
Replication is overkill for simply copying changes over to another table.
But you need to know exactly what they want/need here. Is it truly "real-time" activity? If so that will involve a linked-server DML operation in the context of the same transaction. That can get onerous from a performance/throughput perspective. I note that this does NOT happen 'by itself'. You need to set everything up and code it to make it happen, including proper error handling, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 27, 2015 at 1:36 pm
Linked servers are already setup between these two SQL servers. What they want is when it's updated on production, it gets updated on Development as well.
January 27, 2015 at 1:51 pm
You have to be careful with something like this. You also have to ask what you want to happen if your change cannot be replicated to Father. Do you want to block the change from happening on Mother, or do you want the change to happen on Mother and figure out how to get your change over to Father.
They way I would handle something like this would be to record changes in your Mother User_Group table to someplace local on Mother. Then I would have a SQL Agent job that reads your change table and does the update on Father. Then you don't have to worry about breaking production if your link to the dev database goes down.
Just throwing this out there, because it's good to keep in mind...
Remember that triggers have to be coded to handle multiple records at once.
So you would have an AFTER INSERT, UPDATE, DELETE trigger that writes to what is essentially and audit table and then a job the reads from your audit table to make your changes to your dev environment.
January 27, 2015 at 1:55 pm
Hey thanks for your input. I just passed your post onto that developer and I'll see what they say. You make some very good points.
January 27, 2015 at 1:57 pm
Then you simply reissue the DML activity against the linked-server object inside the trigger. Note I have not tried to do this as a proof that it is actually possible inside a trigger. But you can do that very easily on your system.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 27, 2015 at 2:00 pm
They simply want it for change tracking only.
January 27, 2015 at 2:54 pm
So I convinced them to do something like this: http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/ Copy the updates to an audit table, then do a sql agent job to copy that ? Thoughts?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply