By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,436 Members | 2,958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,436 IT Pros & Developers. It's quick & easy.

Table Synchronization across databases on the same server

P: n/a
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

Oct 26 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(ch********@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, es****@sommarskog.se

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

Oct 26 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.