Linking 2 SQL Databases

  • Hi All

    I am a complete novice so I apologise for any incorrect terminology.

    What I am trying to do is link 2 systems running different Security Access Control software's from different vendors both using SQL as the data pool.

    Ultimately what the end goal looks like is system A is the front end and the relevant changes and transaction information is transmitted and received between Systems A and B.

    The reason is to phase out system B eventually as the hardware is changed out over time for Hardware directly compatible with System A

    Anyone had any experience with a project like this before ? I am sure that it can be done

     

  • If your question is as simple as "can data be moved from one database to another in SQL Server?" the answer is yes and there are numerous ways of doing that. If you were hoping for a more detailed answer, please be more specific about what you want.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Hi Phil

    Thanks for the response, I guess at the moment I am trying to ascertain if its even possible.

    My understanding is that the tables in the SQL differ between the systems but ultimately from a users perspective when you add a card holder into system A it would appear in System B which from my understanding is through the backend via the SQL DB meaning that the user would not need to access System B at all.

    There is a lot more required but starting small as I believe if I could get this to work the rest would be relatively straightforward (well maybe not straightforward)

     

  • Are the databases on the same SQL Server instance?

    Does the movement of data need to be instant, or is a lag acceptable?

    Do you have any particular software tools in mind for implementing this?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Are the databases on the same SQL Server instance? No

    Does the movement of data need to be instant, or is a lag acceptable? Ideally instant but when you say lag a few seconds would not be a deal breaker

    Do you have any particular software tools in mind for implementing this? none at all

  • OK, is it fair to assume that you cannot, or should not, modify the design of either of these two databases?

    Is it one-way traffic (ie, from A to B but not from B to A)?

     

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I don't think modifying them is possible.

    Yes two way traffic either way

  • OK, then you have a detailed mapping exercise ahead of you, to work out exactly which data items go where and under what conditions.

    Hopefully, the systems contain DateCreated and DateModified (or similar) columns on the tables you are interested in synchronising, otherwise you may have no ability to detect incremental changes, and that would mean doing full reloads, blowing your target of a few seconds' lag out of the water.

    If there is any possibility that the same row could be updated in both systems, you will also have to think about how you would deal with that (the equivalent of a merge conflict in Git).

     

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 8 posts - 1 through 7 (of 7 total)

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