Updating tables between two Microsft SQL Server databases on separate servers

Currently, I'm working on setting up a second SQL Server database (we'll call B) on a separate server but on the same network as a master one (we'll call A).

B

A

For security purposes, I need to automatically update specific information between the two based on certain table events, to which database B will share with a Web Application for external clients to utilize. For example, when certain tables in B have new records inserted, that information needs to also be inserted into A; whereas if those same tables are updated in A, they need to be updated in B. In both cases, never the other way around.

I've read that Triggers are used to accomplish this, but would be a poor choice to only use them in my case for possible consistency and scalability issues. Instead, I should use a trigger to simply copy that data to a temporary table, to which a stored procedure, run in the background periodically via SQL Server Agent, can transfer that data if a new record/job has been added.

My questions are:

Would it be best to have the stored procedure check if there exists at least one record, then transfer and delete that record from the temporary table once done?

Should the temporary table have other external data (such as table name, trigger event, etc.)

If more tables and events ever needed to be considered in the future, would it be a good idea to maintain one stored procedure and one temporary table, where the stored procedure could determine where to transfer the data based on the external data, or would multiple stored procedures and temporary tables per table / event / trigger be best?

Is this a bad idea and should replication or linked servers be used instead, even if the table schemas between the two might not match?

Sorry for being so long winded. Any and all help is much appreciated!

Voted to close, because I think it belongs on dba.stackexchange.com instead. There's lots of architectural components to this, and it warrants some specialized knowledge instead of specific programming advice.
– Stuart Ainsworth
Jul 2 at 22:28

That being said, another option to consider is change tracking: docs.microsoft.com/en-us/sql/relational-databases/track-changes/…
– Stuart Ainsworth
Jul 2 at 22:36

What happens when the same info is changed in both A and B - who wins? There are many solutions, but basically the low maintenance out of the box solution (i.e .merge replication) probably won't cut it. You're probably best off building the custom queue solution that you describe, but make no mistake these are not "temp" tables, they are legitimate queue tables
– Nick.McDermaid
Jul 2 at 23:15

it was buried in his original answer, but rows are ALWAYS inserted first on B, and ALWAYS updated on A, and NEVER the other way around. based on that logic, there won't be collisions.
– Stuart Ainsworth
Jul 3 at 1:05

I didn't see that - thanks for clarifying. I think it's important for the OP to be absolutely sure that in two standalone systems, that it's going to work this way. Sometime people think it works that way when really there is no guarantee that it does.
– Nick.McDermaid
Jul 4 at 4:22

1 Answer
1

Bidirectional syncing will be a nightmare, not that you can always avoid it. Two options that I can see:

Queuing service is a great idea that I hadn't considered, but you'd have to commit to ALWAYS using that service when writing data. That may not always be feasible.
– Stuart Ainsworth
Jul 2 at 23:07

Yeah, it's a non-trivial code change. Another awful thing you could do is create a hashcode on the entire row, and then have a batch process look for changes between the two servers. Not real-time, but you could have it run every few minutes, depending on the size of your data.
– Russell Fox
Jul 2 at 23:20

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Fjhtyj