December 7, 2015 at 9:42 am
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
December 7, 2015 at 9:48 am
Have you considered merge replication?
John
December 7, 2015 at 10:14 am
KSQLDBA (12/7/2015)
I have 2 diffenrent databases accessed by 2 different teams: Web Support Team access DB1 And Customer Services DB2I 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?
😎
December 7, 2015 at 10:23 am
because of the size and performance issues they want to keep them separated
December 7, 2015 at 10:36 am
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?
December 7, 2015 at 10:45 am
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
December 16, 2015 at 2:24 pm
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.
December 16, 2015 at 2:58 pm
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
December 16, 2015 at 7:15 pm
Are these two databases on the same server\instance?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2015 at 7:47 pm
KSQLDBA (12/16/2015)
but Merge replication and service broker update only similar database tableMy 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.
December 21, 2015 at 6:49 am
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
December 21, 2015 at 7:56 am
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
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply