Microsoft SQL Server forum - Table Synchronization across databases on the same server

Author

Table Synchronization across databases on the same server

chandrub78@gmail.com

2005-10-27, 9:25 am

Hello Everyone
We have a third party tool which maintains its own custom
database,DB1 on SQL Server 2000 on Server A (Windows 2000). We built an
inhouse application which maintains a transactional database, DB2 also
on Server A derived from DB1. Since the DB1 is frequently updated the
synchronisation between the databases is currently maintained by using
views in DB2 which pulls in all the required data from across tables in
DB1.
But we found that the cost of employing views, referencing tables
across databases has made the inhouse application slower and so as a
apparent performance tuning measure we plan to maintain a subset of
data(for a specific time period) from DB1 in actual tables with same
schema in our transactional database DB2. But since the tables in DB1
are frequently updated during the day we are required to the
synchronise the corresponding tables in DB2 with their counterparts in
DB1. The only constraint in this synchronization being that the third
party custom database,DB1 cannot not be subjected to any changes (like
creation of triggers) as it is maintained by a different group of
personnel.
I read about Triggers,Maintenance
Jobs and Replication but not sure
which would suffice our needs.Are there any recommendations on how this
could be carried out? Any suggestion would be welcome.

Thanks
chandra

Erland Sommarskog

2005-10-27, 9:25 am

(chandrub78@gmail.com) writes:
> We have a third party tool which maintains its own custom
> database,DB1 on SQL Server 2000 on Server A (Windows 2000). We built an
> inhouse application which maintains a transactional database, DB2 also
> on Server A derived from DB1. Since the DB1 is frequently updated the
> synchronisation between the databases is currently maintained by using
> views in DB2 which pulls in all the required data from across tables in
> DB1.
> But we found that the cost of employing views, referencing tables
> across databases has made the inhouse application slower and so as a
> apparent performance tuning measure we plan to maintain a subset of
> data(for a specific time period) from DB1 in actual tables with same
> schema in our transactional database DB2. But since the tables in DB1
> are frequently updated during the day we are required to the
> synchronise the corresponding tables in DB2 with their counterparts in
> DB1. The only constraint in this synchronization being that the third
> party custom database,DB1 cannot not be subjected to any changes (like
> creation of triggers) as it is maintained by a different group of
> personnel.

Hm, wonder if transactional replication can be used here? Normally, when
you replicate a table, you replicate it all. Here you only want some
data to be replicated. But you should be able to set up filter for this,
I believe.

Anyway, I'm not very good at replication myself, I would encourage you to
pursue this in microsoft.public.sqlserver.replication.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links