February 25, 2011 at 1:12 pm
Everyone,
My experience with replication is very limited, so I wanted to get some advice/perspective from some folks who have the experience.
Our developers have been tinkering around with their server for a new product we're developing. They set up replication on a single table in a database. This publisher has 3 subscribers. All 3 subscribers are on the same server, just the same table in 3 different databases. The source table in the publisher will receive some form of DML statement maybe a half dozen times per month. Of the 3 subscribers, 2 of them could accomplish the same purpose with a view, although I am not a fan of cross-database views.
The 3rd subscriber is a different scenario. Right now, the subscriber database is set up to store information for all of our clients. In the future, it's possible (and I thought this was supposed to be the current design) clients could be broken in different databases. If this separation happens, the subscriber should only have data from the publisher relevant to the clients in that database.
My question is, is using replication the preferred choice for data synchronization for this design/situation? Personally, I think it's overkill, but I happened to stumble upon this design when I was asked to perform another task on this server. Our developers have been doing their own thing and have not discussed any of their work with anyone outside of the 3-4 people working on it. They do not have a DBA/Data Architect on the team/project, so I have some hesitations with some of their work.
I'm curious to hear your thoughts....thanks....
February 25, 2011 at 1:27 pm
I don't know if I would use the word "overkill" but I would say it seems a bit odd of a use of a replication, even thought it may work well enough. My concern with this design is that there are additional complexities which really aren't necessary for a few extra inserts into other tables in other databases from my perspective.
It definitely will support what you are describing though. For the third database if you needed to filter what is being replicated there you could add that filter in the publication.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 25, 2011 at 1:38 pm
I agree with that is is an "odd" choice. Setting up and maintaining replication in one environment may not be too bad...but throw in the portability issue and you multiply that times the number of environments you have from DEV to STAGE to PRODUCTION and the fun begins.
Can you go to the dev team and ask if a couple DML TRIGGERs would get them where they need to be? Cross-database calls are not ideal for portability however I would choose that design over replication any day.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply