This would mean you could batch up the changes, mean updates to the original table would be done quicker, would deal better with lost connectivity between the two servers. However, you have less guarantee that the updates would actually be applied to the server, and you have the added complexity of a sql job that must be run.
So it's a tradeoff. And once you start writing and maintaining this sort of logic you realise that's why MS wrote replication stuff, so you don't have to. Two-way sync with triggers is tricky, because the triggers will fire each other. You will have to control this somehow, for example with special values. Otherwise, you will get strange locking errors.
DBMS can't help you very much with linked servers. Bad queries usually just hang and timeout when there is type mismatch etc. Transactions with multiple writes in the trigger OR in the query launching the trigger cause deadlocks easily.
I remember one integration that I had to rewrite completely when a legacy app caused deadlocks with a trigger. If the schemas are different, like usually is the case with application integration EAI , you might consider:. Now, th eonly thing would be how the linked server is setup between and , you would have to try that out first. Stack Overflow for Teams — Collaborate and share knowledge with a private group.
Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Trigger to update data on another sql server Ask Question.
Asked 12 years, 10 months ago. Active 12 years, 3 months ago. Performs insert, update, or delete operations on a target table based on the results of a join with a source table. Arthur My Blog. Now i have one question , How will i move select data to Execute Sql task so that i will update it.
Sign in. United States English. Ask a question. Quick access. Search related threads. Actually I don't like to use a syncing method which might be a black box like replication because I don't want the SQL Server specific tables to be blocked while I'm updating them and syncing them with the server. Remember that every several minutes I must send several table changes from client to the server and fetch also two table changes from server.
I have found a method which is strange but new. Is that possible that I log all executed for specific preferred stored procedures in client and send them with their parameters in a. The same will happen on the server and sent to the client.
Do you think that this is a simple yet useful method or not? EDIT: Remember that this is a real-time synchronization and this makes it special. It means when the client user is using the table, the synchronization process with server must happen every several minutes so none of the tables must be locked. You said you need a high performance solution which runs often minimum all 2 minutes and you need a good approach which should be fast without locking.
But you don't want a blackbox system. Instead of a blackbox system, which is used on millions of installations with good results, you try to invent the wheel again and build your own solution?
Hm, sounds a bit weird. This means, if you change a table and add a new column, CDC will watch the table but ignore all changes to the new column. In fact it only records NULL as value before and value after. You need to reinitialize it after DDL -Changes to a watched table.
Well these are my 2 cents. Hopefully you have a good overview and maybe you found one solution which works for you.
Microsoft Sync Framework - seems to me more fit for smaller databases of mobile apps. It adds quite a lot of tables to your database and it is not as efficient as replication. As it is implemented outside SQL Server as a component, it will be more difficult to configure.
I have no experience with it, only tried it and decided not to use it. Database change tracking. It is a built-in SQL Server function that does for you change tracking including inserts, updates and deletes.
Everything else like sending and applying changes, solving conflicts etc. CDC as mentioned in Ionic's answer - I have no experience with it, as it is available only in Enterprise or Developer editions. Using your own trick with logging executed stored procedures - depends on the nature of your database application very much.
But when the procedures get little different, there you can get a big mess in data. And how would you deal with conflicts? From your question it seems that you need to sync just few tables and not the whole big databases. For this purpose you should analyze your needs in more detail than you have specified in the question, like:.
If you eventually find out, that deletes and conflicts are not your problem and that your structure will not change a lot, you can consider writing your own logic, but it can easily grow to rows of code. I successfully solved the syncronization process by capturing the executed stored procedures not as a bunch but one by one which worked great in my case. Since integrity and everything is carefully considered, the system has been working real-time up to now.
I had a similar challenge trying to distribute data across different servers and solved it by using third party tools Diff for schema changes and DataDiff for data changes sync and following PowerShell script required to automate the process:.
This method schedules comparison between two databases and synchronizes found changes in real time. Here are some articles offering step by step instructions:. I asked this question several years ago and I solved it finally and used the solution in many projects. If your tables are exactly the same whether the same server or not , Use Transnational Replication. It is very easy to setup and it will not take more than an hour for initial setup. However it may needs some tips to better debug and understand the mechanism which you can find out on many website.
If your destination tables have extra columns than your source tables, I definitely recommend Triggers unless you have so many transactions in a second or your server is weak.
0コメント