December 18, 2002 at 2:29 am
I have a web content management system that has three databases. Each database is used for a different stage in the web publication process.
There are 3 particular tables that need to be exact copies of each other in each of the databases.
All data is maintained in the first database.
My initial thought was to have DB1 as the publication and DB2 and DB3 as subscribers.
The Internet Service Provider (ISP) said that this was overkill and they preferred us to use triggers to maintain the databases.
Having thought about it my preference is still for replication.
I understand the ISP's points
What are your thoughts on this?
December 18, 2002 at 4:38 am
I think your ISP will not guarantee the replication. Personally the more control you have the better as they will generally guarantee uptime and backups, no more.
Trigger are simple and mistakes like TRUNCATE TABLE when should not will not be sent to the other where they can be with replication, you can also control with a trigger when to fire and what to do. So a DELETE may mark the record in the other DBs but not actually delete and you can find out who issued by building into the trigger.
If the code is acting flaky you can fix it yourself and no need to worry when they can talk to you.
December 18, 2002 at 4:43 am
I agree that replication makes sense. It solves the problem cleanly and with less work. The downside to replication is schema changes become harder. Not impossible, just harder. One advantage you might discuss is validation. Transactional repl has the ability to do it for you, if you're writing triggers you'd also have to write the code to do a compare to check for missing rows/incorrect data.
Andy
December 18, 2002 at 7:16 am
Maybe another approach. Eliminate the exact copies, and construct a fourth database that contains the three tables that are the same in a single database.
Of course, you will have to take availability and locking into account in this case.
And don't forget the location of the different databases as a possible limiting factor...
Edited by - NPeeters on 12/18/2002 07:17:43 AM
December 18, 2002 at 3:36 pm
I would go with Replication. Having written a home brew replication engine I can tell you that you don't want to do it! Your needs are exactly what replication is designed for. Writing "A few simple triggers" won't cover what happens when two databases change the same record in the table. Replication does. As Andy stated the biggest drawbacks you will have with replication is doing a schema change. However this can still be accomplished without too much trouble. One caviat... I would recommend you stay away from having a managed identity field. I feel it is more work than it's worth. Especially since you have to have a unique identifier field on the table anyway. Why not use it where you would use the identity field. Sure it takes more space in the child table but much less troubles down the road!
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply