469,950 Members | 1,871 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

How to synchronize 2 tables on different sql servers

Hi, anybody can help me.
How can i synchronize 2 tables on 2 different sql servers 2000

i mean TABLE1(col1, col2, col3, col4) and
TABLE1(col1, col2, col3, col4, col5, col6)
the first 3 colums are the same in rwo tables.

Thanks

--
Message posted via http://www.sqlmonster.com
Jul 23 '05 #1
6 9310
alex via SQLMonster.com (fo***@nospam.SQLMonster.com) writes:
Hi, anybody can help me.

How can i synchronize 2 tables on 2 different sql servers 2000

i mean TABLE1(col1, col2, col3, col4) and
TABLE1(col1, col2, col3, col4, col5, col6)
the first 3 colums are the same in rwo tables.


Could you be more specific on what you mean. Do you want to synchronize
data or metadata? And what should the synchronization result in?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Thanks for replay, i want to synchronize
data between two tables? i don't know how to acomplish that.
Maybe u can suggest me some stuff to read about topic???
P.S. i had read your articles about Error Handling very helpful.

thanks

--
Message posted via http://www.sqlmonster.com
Jul 23 '05 #3
alex via SQLMonster.com (fo***@SQLMonster.com) writes:
Thanks for replay, i want to synchronize
data between two tables? i don't know how to acomplish that.
Maybe u can suggest me some stuff to read about topic???


Well, if you know you are in A and want to go B, I might be able to
help you, if you tell me where A and B are.

But if you don't know where you want to go, then I cannot help you with
that part. "Synchronize the tables" could mean a lot of things, and I'm
not active in this newsgroup to play guessing games.

Please give an example of what you have, and where you want to arrive.
Then we might be able to suggest something.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
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]

--
Message posted via http://www.sqlmonster.com
Jul 23 '05 #5
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
Jul 23 '05 #6
thanks, this more that i expect.

--
Message posted via http://www.sqlmonster.com
Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by gene.ellis | last post: by
2 posts views Thread by Pascal Polleunus | last post: by
3 posts views Thread by 2401 members, members can post | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.