Match table data on Linkedserver servers

Question

I have 3 Linkedserver servers (the 3 having the same tables with the same fields).
Server 1: First server
Table: Test

Server 2: Second server
Table: Test

Server 3: Third Server
Table: Test

Select to return the "Test" table fields in the "First Server" table":

SELECT id, username, password FROM OPENQUERY(Primeiro_Servidor, 'SELECT id, username, password FROM Teste')

I would like to take table "Test" from server 1 for example, and replicate all of its data in table "Test" from servers 2 and 3 (checking if id exists. if it exists, do an update, if it does not exist, insert). Could someone tell me how to do this ?

Answer 1

A typical replication solution involves using two commands in the same transaction, one to update existing lines in the remote table and one to add the missing lines in the remote table:

-- código #1
BEGIN TRANSACTION;

-- atualiza informações existentes no servidor remoto (R)
UPDATE R
  set username= L.username,
      password= L.password 
  from Segundo_Servidor.banco.esquema.Teste as R
       inner join Teste as L on R.id = L.id
  where L.username <> R.username
        or L.password <> R.password;

-- acrescenta informações inexistentes no servidor remoto (R)
INSERT into Segundo_Servidor.banco.esquema.Teste (id, username, password)
  SELECT L.id, L.username, L.password
    from Teste as L
    where not exists (SELECT * 
                        from Segundo_Servidor.banco.esquema.Teste as R
                        where R.id = L.id);

COMMIT;

The above code is executed on the local server (L), that is, the first server. That is, it pushes the data for the remote server (R).

Another solution is also possible, with replication code running on each remote server, pulling the data:

-- código #2
BEGIN TRANSACTION;

-- atualiza informações existentes no servidor local
UPDATE L
  set username= PS.username,
      password= PS.password 
  from Primeiro_Servidor.banco.esquema.Teste as PS
       inner join Teste as L on PS.id = L.id
  where L.username <> PS.username
        or L.password <> PS.password;

-- acrescenta informações inexistentes no servidor local
INSERT into Teste (id, username, password)
  SELECT PS.id, PS.username, PS.password
    from Primeiro_Servidor.banco.esquema.Teste as PS
    where not exists (SELECT * 
                        from Teste s L
                        where PS.id = L.id);

COMMIT;

The advantage of the #2 code is that it can be turned into a procedure stored on the main server and run remotely with EXECUTE AT from the main server. That is, a single version of the code, maintained on the main server but run on each remote server from the main server:

-- código #3
EXECUTE (...) AT Segundo_Servidor;
EXECUTE (...) AT Terceiro_Servidor;

---

Meanwhile, be pushing or pulling the data, this type of replication generates excessive traffic on the network, because to know if the line exists or not on the other server, in practice the contents of the table (remote or local, depending on whether pushing or pulling) traffic through the network.

There are ways to optimize the above codes. But the suggestion is to evaluate the implementation of some kind of automatic replication provided by SQL Server.

PS: I have not tested the solutions proposed above; I hope they contain no error.

Answer 2

You can make an insert straight from a select of course but pq do this if vc have a free tool to make data comparison from MS SQL Server?

Any version of Visual Studio has a tool for data comparison and schema that can connect to two bases and compare the differences.

Select two databases that can be on two different servers...

You choose the tables that will be compared, note that to be able to compare record-to-record the table needs to have a PK or at least a single key (well need to know anyway which record to compare with which).

You can even apply the differences by matching the data or generate a script that will do this.

Besides, there’s a lot documentation on the web of how to use this feature. Even if you don’t have the Visual Studio (that any developer working with . Net will have) installed, the download of this version I showed you is free.