September 5, 2003 at 3:41 am
I am not familiar with Replication and would really appreciate some help from anyone who is willing to give it.
The situation:
SQL SERVER 2000 (SP3), Windows2000Server
I have a few databases that contain a couple tables that are consistent between databases. Such as "database1", "database2", "database3" all contain a Customers table.
I would like to create a "Control" database containing a Customers table and when i update this table, it must also update the other 3 Customers tables.
The catch is, the 3 Customers tables contain some common columns with the same names(e.g. customer_code), some common columns with different names (e.g. customer_name vs customer_description) and some columns that aren't in the other Customers tables.
The questions are:
- is replication the best solution for this situation?
- is it possible to link columns (e.g. map customer_name to customer_description)?
- if so how?
- would this require on publication per subscription, or would it be possible with just one publication?
Thank you,
ryan.
September 5, 2003 at 9:05 am
It would seem to me that if you have all these databases on one machine that the overhead of replication would be overkill. Instead what I would do is decide what the "Master" table is and then use views in the other databases that alias the fields as needed and just point to the master table in the other database. This is quick, simple and doesn't require an agent to constantly update the data in the other databases. If the databases are on different machines you could still use this approach but at that point you may want to initiate replication. You just have to ask yourself how much lag time is OK and who has permissions to actually update the data. That should help you decide what form of replication you will want to use and how often to run the replication agent.
I hope this helps!
Gary Johnson
Microsoft Natural Language Group
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.
September 8, 2003 at 3:02 am
Thanks for your help and advice Gary.
My initial solution to our problem was to use views, but that was not desired by the powers that be.
That led me to look at replication, but, as you say:
quote:
...the overhead of replication would be overkill.
My next option, as i see it, would be to use triggers on the "Master" table to Insert, Update, Delete the other tables, which i am in the process of testing the feasability of this option at the moment.
Thanks again,
Ryan.
September 8, 2003 at 4:20 am
I'd use replication over triggers if possible, why write code when replication will manage it for you (and its been tested).
That said, I'd consider another conversation with whoever said no to views. The only time I wouldnt use a view would be if I needed a seriously different index plan or the data was on another server. In the case of a different server, if you use a view and the remote server is down, you're stuck. Replication offers some fault tolerance. Whether you use triggers or replication, you're essentially doing every transaction twice - that puts additional load on the box that you can avoid with a view. The other point is that if you replicate you impose some limits on your ability to easily change the table when you need to.
If you do use replication, you can do it with one publication, though in many cases it will make sense to have more. Suppose you have one table that is 1g, 4 more that are each 50mb each. If you put all in one pub and you need to change a column in one of the 50mb tables, you'll end up having to resnapshot the 1g table as well. I typically break large tables out into separate pubs, put all the small ones in one.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply