[All databases I am referring to here reside on a SQL Server 2000
machine.]
In the organisation where I work we currently have a public sanitised
version of our existing HR personnel database. This is very widely
used as the main source of employee info by many internal bespoke
applications.
For most of the tables, the structure of the published out table is
exactly the same as in the master personnel database. These are
obviosuly very specific to the old HR application. Currently these
tables are published out via the use of triggers and SQL Server
replication.
We are just in the process of replacing our HR system, and as such the
underlying structure of the data held will change considerably.
However, due to the number of applications which now use this public
database it is a requirement to not only publish out the same data,
but publish out this data in the same format of the previous HR
system.
Mapping the individual (useful) columns shouldn't be too difficult and
all the rest (non-used) columns can pretty much be NULL'd out. What
my problem is is this:
I have started to map individual tables and mostly their content is
now derived from more than just 1 table. How would you go about
ensuring that when a change is made in the master table(s) that it
then correctly updates the downstream table?
[For ease of understanding from here on, database A is the NEW master,
and database B is the existing sanitised public database.]
My thoughts on this are:
1) Re-create B's tables with the use of views. (1 view per table)
2) Add triggers to all of A's tables that contain data that will be
published out. When data in these tables is changed they mark which
records were affected.
3) Schedule a job to run periodically that refreshes B's tables using
A's views. Only refresh the records that have changed by seeing which
records have been marked by the triggers.
Or...
Would it be possible to set up a DTS package, or replication to do a
similar thing?
The way I have done this in the past was to use the process I have
outlined above, but this was for a much simpler process and only 1
table. Since this will be on a larger scale (although not much, now
about 15 or so downstream tables) is there a better way of achieving
this?
Any thoughts gratefully recieved!
Thanks in advance,
Jonathan.
