alex via SQLMonster.com (fo***@SQLMonster.com) writes:
Ok, sorry about my explanation .
I have two sql servers that runs on different machines
each of sql servers have database (there is not same database) however
there is table (suppose myTable) that is same to two databases and i need
to synchronize between them in this case e.g.
sqlServer1 myTable1
sqlServer2 myTable2
in case some data is changed in [sqlServer1 myTable1] i need that
[sqlServer2 myTable2] will changed too according to [sqlServer1 myTable1]
and not vice versa i.e i need that [sqlServer2 myTable2] will ALLWAYS have
data like [sqlServer1 myTable1]
One method is to set up replication, although a replication topology
for one single table is probably an overkill. Also, that would require
the tables to have the same metadata, and your initial post indicated
that they have different columns.
Another method is to set up SqlServer2 as a linked server on
SqlServer1, and then have a trigger that replicates the data to
the table on SqlServer2. If the network is not always up, or if it is
slow, this can cause updates on Server1 to be slow.
Yet another possibility is to set up Server1 as a linked server on Server2,
and regularly poll, for instance from SQL Server Agent. If the table
is moderate in size, it might be feasible to simply truncate the
table, and then load the data over. Of course, this won't do, if the
table is has many rows.
Yet a possibiliy is a combination of the last two: you have a trigger
which writes to a table local to Server1, and then Server2 pulls the
chagens from this table, and then deletes the rows in the table. Here
some care needs to be taken so that you don't miss any updates, but
also you don't lock the source tables for updates while fetching.
Which of these to choose depends on your exact requirements, which
privileges you can run this under, which possibilities you have to
set up linked servers and create triggers etc.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
