Intrebari

Re: Oglindire tabela pe servere diferite

B_gd_n[ ]Sahlean — Sat, 10 Nov 2007 19:42:54 GMT

Pe server-ul unde este baza de date sursă:

CREATE DATABASE PseudoSincronizareSursa
GO

USE PseudoSincronizareSursa
GO

CREATE TABLE Produs
(
    CodProdus INT PRIMARY KEY,
    DenumireProdus VARCHAR(50) NOT NULL,
    Stare ROWVERSION NOT NULL --ROWVERSION <=> TIMESTAMP
)
GO

CREATE UNIQUE INDEX idx_produs_stare
ON Produs (Stare)
GO

/*
--Ai posibilitatea sa se defineste un index care sa includa toate campurile
--care trebuiesc exportate din sursa in destinatie
CREATE INDEX idx_produs_stare
ON Produs (Stare,CodProdus,DenumireProdus)
*/

INSERT INTO Produs (CodProdus,DenumireProdus) VALUES (1,'Mere')
INSERT INTO Produs (CodProdus,DenumireProdus) VALUES (2,'Portocale')
INSERT INTO Produs (CodProdus,DenumireProdus) VALUES (3,'Lamai')
INSERT INTO Produs (CodProdus,DenumireProdus) VALUES (4,'Pere')
GO

Pe fiecare server unde există câte o bază de date destinaţie:

CREATE DATABASE PseudoSincronizareDestinatie
GO

USE PseudoSincronizareDestinatie
GO

CREATE TABLE Produs
(
    CodProdus INT PRIMARY KEY,
    DenumireProdus VARCHAR(50) NOT NULL,
    Stare BINARY(8) NOT NULL
)
GO

EXEC sp_addlinkedserver
    @server = 'LINK_SQLSERVER_SURSA',
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = 'OOSQL\SQLEXPRESS',
    @catalog = 'PseudoSincronizareSursa'
GO

--activezi optiunea RPC OUT pentru serverul "linked"
EXEC master.dbo.sp_serveroption
    @server='LINK_SQLSERVER_SURSA',
    @optname='rpc out',
    @optvalue='true'
GO

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'LINK_SQLSERVER_SURSA',
    @useself = 'TRUE',
    @locallogin = 'oosql\bgdn'
GO

CREATE PROCEDURE spSincronizare
AS
    DECLARE @tbl TABLE ( CodProdus INT, DenumireProdus VARCHAR(50), Stare BINARY(8) )

    DECLARE @max ROWVERSION
    SET @max = ISNULL( (SELECT MAX(Stare) FROM Produs) , 0x0 )

    INSERT INTO @tbl (CodProdus, DenumireProdus, Stare)
    SELECT CodProdus, DenumireProdus, Stare
    FROM LINK_SQLSERVER_SURSA.PseudoSincronizareSursa.dbo.Produs
    WHERE Stare > @max

    UPDATE Produs
    SET Produs.DenumireProdus = T.DenumireProdus, Produs.Stare = T.Stare
    FROM @tbl AS T
    WHERE Produs.CodProdus = T.CodProdus

    INSERT INTO Produs (CodProdus, DenumireProdus, Stare)
    SELECT CodProdus, DenumireProdus, Stare
    FROM @tbl AS T
    WHERE T.CodProdus NOT IN (SELECT CodProdus FROM Produs)
GO

În mod asemănător cu exemplul anterior, pentru sincronizare apelezi procedura stocată
EXEC spSincronizare

În continuare, poti adăuga şi modifica date în baza de date sursă:

INSERT INTO Produs (CodProdus,DenumireProdus) VALUES (5,'Soareci optici')
UPDATE Produs SET DenumireProdus = 'Ala bala portocala' WHERE CodProdus = 2

si sa realizezi sincronizarea din nou
EXEC spSincronizare

Observaţii:

[1] În tabela sursă se defineşte un câmp Stare de tip timestamp / rowversion. Valorile acestui câmp sunt coduri unice generate automat de server crescător (conform BOL). Avantajul acestui tip de date - în acest caz - vine din faptul că în momentul modificării unei înregistrări în baza de date sursă (bd PseudoSincronizareSursa) se modifică automat şi valoarea din câmpul timestamp / rowversion Stare, generându-se un nou cod pentru înregistrarea respectivă.

[2] Plecându-se de la valoarea maximă rowversion/timestamp din campul Stare a tabelei destinaţie Produs (bd PseudoSincronizareDestinatie) se pot filtra datele din tabela sursă Produs (bd PseudoSincronizareSursa). Daca în baza de date sursă se modifică/inserează 100 de înregistrari dintr-un total de 10.000 prin această filtrare, server-ul destinaţie primeşte de server-ul sursă doar 1% din înregistrări (adică 100 de înregistrări) spre deosebire de implementarea anterioară în care server-ul destinaţie primeşte de la serverul sursă toate înregistrarile: 100% (adică 10000). Lucru acesta se poate observa analizând planurile de execuţie.

[3] Soluţia se aplică doar pentru inserarea şi modificarea produsele, dar se poate adapta şi pentru cazul în care se doreşte ştergerea de produse.

[4] Parţial - doar pentru sincronizarea produselor noi inserate - soluţia poate fi aplicată daca există doar un câmp numeric IDENTITY care nu contine valori nule.

Re: Oglindire tabela pe servere diferite

alinescoo — Fri, 09 Nov 2007 09:21:43 GMT

Multumesc mult pentru solutie si mai ales pentru timpul necesar acordat pentru crearea solutiei.

Sunt perfect convins, asa o sa fac. Intr-adevar, celelalte servere au linked pe server 1, nu m-am exprimat eu bine

Re: Oglindire tabela pe servere diferite

rsocol — Thu, 08 Nov 2007 18:18:32 GMT

Bogdan,

Sunt complet de acord.

Răzvan

Re: Oglindire tabela pe servere diferite

B_gd_n[ ]Sahlean — Thu, 08 Nov 2007 17:29:25 GMT

Fara suparare, dar uptate-ul ar putea fi costisitor mai ales daca:

[1] sunt multe produse

[2] conexiunea dintre cele 2 servere este lenta.

Repet: este doar o părere.
In ceea ce priveste implementarea ta, o solutie cred ca au fi ca in procedura SincronizareProduse sa fie inserate intr-o tabela temporara (#prod) produsele de pe server1 iar UPDATE-ul si INSERT-ul sa se realizeze plecand de la datele din tabela temporara. Pe de alta parte, exista posibilitatea ca optimizarile realizate de SQL Server sa faca inutila tabela temporara ...

Re: Oglindire tabela pe servere diferite

rsocol — Thu, 08 Nov 2007 16:48:00 GMT

Presupun că pe server-ele linked1, linked2, etc, utilizatorii nu pot modifica/adăuga produse (pot să aleagă doar din cele existente atunci când fac facturi, etc), nu-i aşa?

Pe de altă parte, mi s-ar părea mai normal ca server-ele secundare să aibă un linked server către server-ul principal (nu invers, cum ai propus tu). Dar dacă trebuie să fie invers (din motive de securitate), nu-i nicio problemă (se modifică în mod corespunzător numele tabelelor). Să luăm un exemplu. Pe server1, avem:

CREATE TABLE Produse (
      ID_Produs int IDENTITY PRIMARY KEY,
      Denumire nvarchar(50) NOT NULL UNIQUE,
      UM varchar(5) NULL
)

INSERT INTO Produse (Denumire, UM) VALUES ('Mere', 'KG')
INSERT INTO Produse (Denumire, UM) VALUES ('Pere', 'KG')
INSERT INTO Produse (Denumire, UM) VALUES ('Capsator', 'BUC')

Pe linked1, linked2, etc, avem:

CREATE TABLE Produse (
      ID_Produs int PRIMARY KEY,
      Denumire nvarchar(50) NOT NULL UNIQUE,
      UM varchar(5) NULL,
      StocMinim numeric(15,3) NULL
)

Pentru început, să presupunem că avem toate datele din server1:

INSERT INTO Produse (ID_Produs, Denumire, UM)
SELECT ID_Produs, Denumire, UM FROM server1.YourDB.dbo.Produse

şi avem în plus ceva informaţii completate pe coloana StocMinim:

UPDATE Produse SET StocMinim=10 WHERE Denumire='Mere'

Atunci creăm urmatoarea procedură:

CREATE PROCEDURE SincronizareProduse
AS
UPDATE Produse SET Denumire=n.Denumire, UM=n.UM
FROM Produse v INNER JOIN server1.YourDB.dbo.Produse n ON v.ID_Produs=n.ID_Produs
WHERE v.Denumire<>n.Denumire
      OR v.UM<>n.UM
      OR v.UM IS NULL AND n.UM IS NOT NULL
      OR v.UM IS NOT NULL AND n.UM IS NULL

INSERT INTO Produse (ID_Produs, Denumire, UM)
SELECT ID_Produs, Denumire, UM FROM server1.YourDB.dbo.Produse
WHERE ID_Produs NOT IN (SELECT ID_Produs FROM Produse)

Şi să zicem că între timp se întâmplă nişte modificări pe server1:

UPDATE Produse SET Denumire='Mere ionatane' WHERE Denumire='Mere'

INSERT INTO Produse (Denumire, UM) VALUES ('Mere golden', 'KG')

Atunci când executăm:

EXEC SincronizareProduse

Ar trebui ca datele modificate să se transfere de pe server. Ce probleme pot apare:

1. dacă în linked1 utilizatorii ar fi adăugat un alt produs, "Perforator", să zicem, care are ID-ul 4, atunci când apelăm procedura de sincronizare denumirea acestui produs s-ar modifica fiind acum "Mere ionatane" (şi dacă aveam o factură de vânzare pentru 3 buc Perforator, atunci aceasta devine 3 kg Mere ionatane)

2. dacă în linked1 utilizatorii au adăugat un alt produs numit "Mere ionatane" (în afară de produsul numit "Mere"), atunci când apelăm procedura de sincronizare vom obţine eroare "Violation of unique key ...", pentru că se încearcă modificarea denumirii produsului "Mere" în "Mere ionatane",
deşi există deja un alt produs cu acest nume.

Dacă se stabileşte de la început regula că în celelalte servere utilizatorii nu modifică datele din tabela Produse, atunci aceste probleme sunt excluse. Dacă este nevoie ca utilizatorii să adauge sau să modifice date în tabela Produse şi pe celelate server-e, atunci s-ar putea face sincronizarea folosind în join coloana Denumire în loc de ID, dar şi aşa ar putea apare probleme (de exemplu dacă s-au folosit unităţi de măsură diferite).

Răzvan

Re: Oglindire tabela pe servere diferite

ggciubuc — Thu, 08 Nov 2007 15:54:18 GMT

Pentru asta rezolvarea ar fi astfel :

un trigger pune intr-o tabela codurile(valorile cheii primare) tabelei vizate detinute de liniile care au fost inserate sau modificate ( se utilizeaza tabela inserted); pe urma la apasarea butonului respectiv acel query va fi un join intre tabela cu coduri si tabela vizata (relatie 1 la 1 facuta prin cheia primara); in continuare se sterg (delete from ...) liniile din tabelele cu coduri pentru a fi pregatita pentru urmatoarea operatie

Re: Oglindire tabela pe servere diferite

alinescoo — Thu, 08 Nov 2007 14:27:48 GMT

Dar chestia cu insert din view... nu stiu cum sa am in view doar codurile noi sau cele modificate... tinand cont ca nu am flag sau altceva... si nici nu as vrea sa modific nimic pe server1... la toate linked server se pot face orice modificari...

Interesanta chestia cu service brooker... analizez sa vad despre ce e vorba.

Re: Oglindire tabela pe servere diferite

ggciubuc — Thu, 08 Nov 2007 11:32:59 GMT

Chestia asta poate fi implementata asincron si automatizat prin tehnologia service broker in SQL 2005 sau replicare

Insa daca e vorba de o lansare operatie la nivel aplicatie se face un "insert into linkserver.tabel select * from viewCuDateleFiltrate", adica se lanseaza aceasta instructiune prin acel buton; sigur ramane aspectul administrativ care trebuie impus intr-o procedura: la ora 8 in fiecare zi cineva apasa acel buton.

Daca se doreste automatizare se poate apela la ce am spus mai sus.

Oglindire tabela pe servere diferite

alinescoo — Thu, 08 Nov 2007 09:05:13 GMT

V-as ruga sa va dati cu parerea in urmatoarea situatie... cum s-ar putea face cel mai optimizat:

Am un server principal - server1 - pe care am tabela products - code, name... etc. Am mai multe servere linked catre: el linked1, linked2, linked3 in diferite locatii pe care am o tabela products dar care mai are in plus alte campuri fata de cele de pe server1. As vrea ca la CEREREA unui user care are acces pe un server linked sa se oglindeasca produsele noi sau produsele modificate in tabela locala de pe linked. Adica, pe server 1 am adaugat un produs nou - il adaug si pe linked... am modificat o denumire... o modific si pe linked. Insa nu just in time, ci la apasarea unui buton dintr-o aplicatie. Vad ce produse sunt noi, care sunt modificate si le inserez/updatez...

Ei, cum s-ar face cel mai bine asta ?