I've searched for information on this but so far have not been able to
find any advice.
We have several databases running on SQL Server 7.0 that are
essentially identical in structure but are used for different data.
There are several tables that are identical in all databases.
Currently, we make updates to those tables in one database and
propogate the new versions of those tables to the other databases.
The maintenance to keep after this is somewhat problematic in that the
user, or dba, has to initiate some action to apply the update to all
tables anytime a change is made. An idea I have to simplify this
process is create views that access the data in a central location.
I would create a database for these shared tables, and replace the
tables in each individual database with a view of the same name. The
other nice thing about this solution is it is transparent to
applications that rely on this data. I contemplated using the shared
database by making code changes to change all the references to those
tables to DB..table, but that would take considerable man hours. Can
anyone give me a reason why this would not be a good idea? Thanks.