Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dpnadam
Starting Member
29 Posts |
 Posted - 2011-02-03 : 05:44:42
|
| HiI have a scenario that is new to me. I basically have anywhere between 2-4 databases that need to be synchronized with a master database. So for example, say I have 3 databases for cars of different makes (Ford, Honda, Toyota). The master database (Cars) will hold details of all the cars. The three child databases are managed by their own piece of software (software is the same for each). The master database by another piece of software.When an entry is inserted, updated, deleted in one of the child databases I need it to be reflected in the master database. When an entry is inserted, updated, deleted in the master database I need the record to be inserted/updated/deleted in the appropriate child database. Is this possible with a trigger or should I be looking at an entirely new approach?Any examples are much appreciated.Thanks-------Create table code--Create 3 databases and insert table below into each. Changed Ford to different makes.CREATE TABLE [dbo].[Ford]( [ID] [int] NOT NULL, [Model] [varchar](20) NULL, [Horsepower] [int] NULL, [Doors] [int] NULL,PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]--Insert into master databaseCREATE TABLE [dbo].[Cars]( [ID] [int] NOT NULL, [Make] [varchar](10) NULL, [Model] [varchar](20) NULL, [Horsepower] [int] NULL, [Doors] [int] NULL,PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-03 : 07:41:00
|
| You can do it with triggers but there are some possible pitfalls and the biggest one is that a trigger is synchronous, meaning that if you start a transaction to change a record in DB3 then the record must also be written in the master database befor the scope of the transaction ends. This can be very timeconsuming, but if the db's are on the same server this might not be a big issue... Triggers also have a tendency to flood a system with activity, and run more frequently than intended. It will take a lot of discipline to implement this using triggers. Another issue is that you need to make sure there are no eternal trigger loops created. A better solution (in my opinion) would be to keep track of when records are changed in each database and the run scheduled syncs.- LumbagoMy blog-> www.thefirstsql.com |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-02-03 : 07:56:20
|
I am not sure but would merge-replication be a solution?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
dpnadam
Starting Member
29 Posts |
Posted - 2011-02-03 : 08:41:44
|
quote:
Originally posted by Lumbago
You can do it with triggers but there are some possible pitfalls and the biggest one is that a trigger is synchronous, meaning that if you start a transaction to change a record in DB3 then the record must also be written in the master database befor the scope of the transaction ends. This can be very timeconsuming, but if the db's are on the same server this might not be a big issue... Triggers also have a tendency to flood a system with activity, and run more frequently than intended. It will take a lot of discipline to implement this using triggers. Another issue is that you need to make sure there are no eternal trigger loops created. A better solution (in my opinion) would be to keep track of when records are changed in each database and the run scheduled syncs.- LumbagoMy blog-> www.thefirstsql.com
Thanks Lumbago. The databases will be on the same server. The problem with running scheduled syncs is that any change needs to be reflected instantly really. Any other thoughts?Obviously I want to avoid eternal trigger loops or unecessary trigger runs. May be I should be looking at a different approach? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
|
|
dpnadam
Starting Member
29 Posts |
Posted - 2011-02-03 : 08:47:34
|
quote:
Originally posted by webfred
I am not sure but would merge-replication be a solution?
Thanks Webfred, I'm taking a look at this now. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-03 : 08:50:22
|
| I think the Service Broker also could be an option in these kinds of situations. It's sort of a message queuing system where delivery is guaranteed but it's asynchronous so scope is returned to the initiating transaction immediately. But if all databases are on the same server triggers might be the best and most efficient way actually.- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
|
|
|
|
|
Synchronize multiple databases with a trigger?