Simultaneously transaction Update on different Database

  • I have 2 diffenrent databases accessed by 2 different teams: Web Support Team access DB1 And Customer Services DB2

    I want be able to synchronously update a some table column in DB2 of any changes like Insert, Create, Update, Delete... happening on DB1 and vice versa.

    The tables of the databases are not designed architecturally the same way.

    DB1 is uses by application A and DB2 is used by different application B but both have some common fields info like example customer ID in DB1 is name Client in DB2, comment in DB1 is Note in DB2 and the field are also different...

    "So the actual update will be done on the table field value level base on some kind of mapping I guess"

    The " value in the fields" is the only similarity

    I want to be able to synchronize this data if they are update respectively in either one of the Databases

    Is there Any immediate solution out there For this kind of operation?

    I personally Was thinking about some triggers that may be able to do the job. but if you have any Idea please let me know

    Thanks again in advance.

    KSQLDBA

  • Have you considered merge replication?

    John

  • KSQLDBA (12/7/2015)


    I have 2 diffenrent databases accessed by 2 different teams: Web Support Team access DB1 And Customer Services DB2

    I want be able to simultaneously update DB2 of any changes like Insert, Create, Update, Delete... happening on DB1 in timely manner and vice versa.

    Is there Any immediate solution out there For this kind of operation?

    I personally Was thinking about some triggers that may be able to do the job. but if you have any Idea please let me know

    Thanks again in advance.

    KSQLDBA

    Quick question, wouldn't it be easier to merge these into one database?

    😎

  • because of the size and performance issues they want to keep them separated

  • KSQLDBA (12/7/2015)


    because of the size and performance issues they want to keep them separated

    So now we know that there are performance issues, at least we have a slightly better picture although most of the details are still missing.

    😎

    Quick questions

    1. What is an acceptable update latency?

    2. What is the update frequency?

    3. What is the typical update footprint?

    4. Are constraints or Identity generated values an issue?

  • The tables of the databases are not designed architecturally the same way.

    DB1 is uses by application A and DB2 is used by different application 2

    No major DB issue

    need

  • Merge replication may work. Depends on how different the tables are and if they meet the requirements for merge replciation.

    Otherwise, I'd go with service broker.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • but Merge replication and service broker update only similar database table

    My data are field base in another words I will say the similarity is only on the "value in the fields" as the architecture design is completely different

    I want to be able to synchronize this data if they are update respectively in either one of the Databases

  • Are these two databases on the same server\instance?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • KSQLDBA (12/16/2015)


    but Merge replication and service broker update only similar database table

    My data are field base in another words I will say the similarity is only on the "value in the fields" as the architecture design is completely different

    I want to be able to synchronize this data if they are update respectively in either one of the Databases

    Service broker can do anything with data you tell it to do. There is no requirement that either end be similar in any way.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • The main choices are Triggers, Merge Replication, Microsoft Replication Objects,, Service Broker, and Distributed Transactions.

    Triggers have one advantage, in that you do not need to change any existing code and probably do not need to change any operational procedures. However, triggers enforce synchronous commit on source and target, and have been known to give locking issues when transaction volumes build up.

    Distributed transactions would require code changes in both applications, so that both applications update both databases. Again, they require synchronous commits.

    Merge Replication will add a whole new set of skill requirements to your DBAs if you have not used this before. It might be the right answer for you, but it will need a sizable investment in training and time before you have the skills needed to deploy this in Production.

    Replication Objects can give you a fine degree of control on what data gets moved, but you need to write all the supporting code. There are some sites that give you model code, but ultimately you have a solution that is unique to you and therefor needs a high risk score.

    Service Broker is more flexible than Merge Replication, in that you can control exactly what gets updated via program code. However, it still needs a similar investment in skills and training as Merge Replication, as well as changes in DBA operational procedures.

    These choices give different options for simplicity, risk and resilience. If you already have SB or Merge Replication in place then the risk of using these for your problem is much reduced as you already have the skills.

    If this was my problem and transaction volumes were measured in tran/minute rather than tran/second I would probably recommend starting with Triggers because of their simplicity and low cost, but be prepared to move to Service Broker if locking issues could not be solved. But I do not know the full details of your environment so you need to decide what is best for you.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • To be honest, I use a combination of triggers and staging tables with a job to move the data from the staging tables to their targets in a sort of homegrown replication. I don't like what Replication does to the log files and backups. Service Broker seems nice but I've not worked with it much. Seems like I won't be able to because the folks at work put the squash on that idea (probably because they spent a huge amount of money on something else to do the job).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply