Replikation, Stored Procedures

[Thomas2]

Hallo,

folgendes Szenario:
Quellserver A
Zielserver B

A fügt immer neue Datensätze in seine DB ein. Der Zugriff ist für B nur über Views und Stored Procedures möglich.
B möchte immer ein aktuelles Abbild haben und weitere Relationen in seiner Datenbank hinzufügen und mit den Daten von A verknüpfen. Daher soll eine Transaktionsreplikation eingerichtet werden.
Ist dies soweit i.O. und brauchbar?

Die Frage ist auch, wie und ob es irgendwie möglich ist, den Output den die Stored Procedures erzeugen immer aktuell zu halten. Und das möglichst bequem und transaktionssicher

Vielen Dank

[O. Helper]

Hallo Thomas2,

um klar zu sagen, ob es soweit i.O. und brauchbar ist, sind die Informationen noch zu dürftig; wir kennen die Rahmenbedingungen und Anforderungen nicht.

Grundsätzlich ist es so, das man im Server B den Server A als Verbindungsserver (Linked Server) einrichten kann.
Dann kann man aus Server B über den voll-qualifizierten Namen auch Daten aus Server A selektieren, z.B.:
[highlight=SQL]SELECT *
FROM ServerA.Datenbank1.Schema.TabelleX
INNER JOIN ServerB.Datenbank2.Schema.TabelleY[/highlight]
Heisst also, die Daten können auf Server A verbleiben und sind trotzdem fürServer B selektierbar.

Mit einer Replikation kann man
- Verringerung des Traffics
- Redundanz / Ausfallsicherheit
erreichen, da die Daten dann zusätzlich auf Server B vorliegen. Fällt Netzwerk wie WAN aus, kann man über Server B weiterarbeiten.

Stored Procedures erzeugen immer aktuell

Was genau meinst Du damit? SP auf welchem Server / welche Daten?
Wenn es SP auf Server B ist, die die Live-Daten von Server A selektiert sind sie aktuell; bei einer Replikationslösung eben mit stand der letzten Replikation.

[Thomas2]

Ja, den Verbindungsserver habe ich eingerichtet und er funktioniert auch soweit. Die Stored Procedures befinden sich auf dem Quellserver A. Diese liefern Daten die B in eigenen Tabellen abspeichern möchte. Diese Daten sind nur per SP erreichbar, da die Rechte auf dem Quellserver stark eingeschränkt sind.
Nun soll auf B immer der Output der SPs von A in Tabellen abgespeichert werden. Bei einigen SP wächst die Ergebnismenge immer nur an, d.h. man müsste diese irgendwie effizient abspeichern. Ein reines abspeichern der gesamten Ergebnismenge ist zu langsam.
Einige Ergebnisse von SP sind tagesaktuell, d.h. es sind keine alten Ergebnisse vorhanden. Hier soll immer nur der neuste Output gesichert werden.
Ideen/Vorschläge wie man dies möglichst gut realisiert?

Vielen Dank für die Bemühungen

[O. Helper]

Man könnte über ein TimeStamp Feld (kein Datum-Uhrzeit, sondern eine fortlaufende Nummer = RowVersion), alle Datensätze ermitteln, die sein dem "letzten" mal geändert/neu eingefügt wurde.
Nur wenn man das manuell abgleichen will, ist das sehr mühselig, insbesondere bei vielen Tabellen; jedes mal prüfen, ist es ein INSERT/UPDATE etc. und vor allem, wie erkennt man DELETE?

Von daher ist die Replikation ein geeignetes und praktische Mittel, das der MS SQL auch noch von Haus mitbringt.
Bisher habe ich nur mit MERGE und der SNAPSHOT Replikation gearbeitet, die Transaktionsreplikation wird aber genauso gut & (fast) einfach funktionieren.
Soweit ich weiß & gelesen habe, arbeitet es mit mit LOG + einer Queue und hängt sich nicht in die Transaktion; es wird also nie Probleme geben, wenn Server B mal nicht verfügbar ist.

Durch die TransRepl werden nur die Änderungen in Server A übertragen, hält den Traffic dadurch in Grenzen.
Wenn Server B mal eine Zeit lang nicht erreichbar ist (z.B. beim Patch einspielen), dann holt er alle Änderungen wieder nach, sobald er da ist (=> Queue).
Man kann in der Repl definieren, welche Tabellen repliziert werden, man muss also nicht alles übertragen.
Entgegen der MergeRepl werden keine weitere Felder in den Quelltabellen angelegt.

Du solltest Dir aber vorher unbedingt alles zum Thema Replikation in der BOL durchlesen, da es doch ein etwas komplexeres Thema ist.
Z.B. das schon erwähnte installieren von Patches ist so eine Sache; mit unter muss man die Replikation erst auflösen, überall das gleiche SP installieren und anschließen wieder die Replikation einrichten.
Und es wäre günstig, wenn Du es vorher etwas testen könntest; versuch macht kluch...
Als Abonnement könntest Du auch eine SQL Server 2005 Express Edition auf irgend einem Rechner nehmen; die Express kann man nur nicht als Verleger/Verteiler verwenden.

[Thomas2]

Danke für deine Ausführungen.

Die einzige offe Frage ist noch die Handhabung der Resultate der Stored Procedures. Da muss ich mir wohl noch irgendein Workaround einfallen lassen, in der Art wie du es schon kurz skizziert hast.
Bei der Replikation werden die Resultate der Stored Procedures soweit ich weiß nicht übermittelt, wäre auch irgendwie Quatsch. Nur hat jemand eine weitere konkrete. realisierbare und effiziente Idee wie man die Ergebnisse der SP komfortabel aktuell hält?

[O. Helper]

Bei der Replikation werden (nur) die Daten von den Tabellen übertragen; Ergebnisse von SPs sind dabei irrelevant.
Du hast dann in B den gleichen Datenbestand wie in A.
Die SPs kannst Du dann in Server B anlegen, die die Daten aus Server B selektieren; oder wie auch die Struktur bei Dir ist.

[Thomas2]

Nein, das kann ich leider nicht. Ich habe auf Quellserver A nur eingeschränkt Zugriff u.a. nicht auf alle benötigten Tabellen. Den Quellcode der SP kann ich ebenso nicht einsehen. Daran wird sich leider wahrscheinlich auch nichts ändern.

Weitere Frage: Kann ich eine Replikation machen und dann die replizierten Tabellen um eine Spalte ergänzen? Werden diese dann bei der nächsten Replikation überschrieben oder funktioniert diese dann nicht mehr, da die Spaltenanzahlen nicht überein stimmen?

Danke

[O. Helper]

Bei der Replikation kann man nicht nur definieren, welche Tabellen repliziert werden, sondern auch welche Felder. Ebenso kann man Filter setzen, um nur bestimmte Daten zu replizieren.
Beispiel wäre eine ERP DB, wo nur Daten eines bestimmten Mandanten repliziert werden sollen.

In der Zieltabelle kann man neue Felder anlegen. Man muss aber darauf achten, das sie Nullable sind oder zumindest einen Default haben; sonst rumst es bei neuen Datensätzen, denn der Verleger kennt logischer Weise die Felder nicht.

Wenn Du nicht der DBA von Server A bist, hast Du so oder so ein Problem. Eine Replikation, wenn es denn noch Thema ist und es dazu kommt, kann nur der A-DBA einrichten und nur er kann Server B als Abonennt zulassen. Dann kommt noch dazu, dass das Snapshot per Fileshare zur Verfügung gestellt werden muss, wo B darauf Zugriff hat, und und und....
Wenn Du die Definition von der SP nicht kennst & erfährts, hast Du ein weiteres Problem. Die SP kann ein simples SELECT machen oder es kann eine aufwendige Business Logic hinter stehen; oder das zu wissen, kannst Du es nicht exakt abbilden.

Alles im allen läuft es darauf hinaus, das Du Dich mit dem A-DBA abstimmen musst; hilft alles nichts.

Klappt das nicht, wird es kompliziert und aufwendig für Dich.

[Thomas2]

Hallo,

das ist eine komplizierte Vertragsgeschichte. Einen Replikationsserver muss die Firma liefern, die SP müssen und werden die aber nicht offen legen. D.h. es wird nichts anderes übrig bleiben die SP auf dem Quellserver A auszuführen und die Ergebnisse halbwegs synchron nach B zu kopieren.
Und dort fehlt mir der intelligente Ansatz um das effizient zu realisieren.

[O. Helper]

Hallo Thomas,

das hast Du ein echtes sch... Problem und mein Mitgefühl deswegen.
Jetzt wird es für Dich nicht nur kompliziert, sondern auch aufwendig.
Das Bordmittel Replikation fällt damit aus, also musst Du alles von Hand machen.
Unabdingbar dafür ist, das die Quell-SP Dir auch den PrimaryKey mitliefert, sonst hast Du ganz verloren (man, bin ich heute wieder aufmunternd, ein echter Optimist ).

Ich hatte auch schon mal ein Script erstellt, mit dem man das dynamisch Handlen kann und es sogar hier gepostet; ich findest es momentan nur nicht wieder (Chaot der ich bin). Wird sich finden lassen, ich post es Dir noch mal.

Wie der Spass für Dich dann aussieht, habe ich mal als "kleines" Beispiel erstellt; wie erwähnt hast Du mein aufrichtest Mitgefühl in der Sache:
[highlight=SQL]-- Quelltabelle; kein Zugriff & unbekannt
CREATE TABLE #Quelle
(ID int NOT NULL IDENTITY (1, 1),
Feld1 varchar(50) NOT NULL,
Feld2 varchar(50) NULL,
CONSTRAINT PK_Quelle PRIMARY KEY CLUSTERED(ID)
);
GO
-- Und etwas füllen
INSERT INTO #Quelle VALUES ('F1 Wert 1', 'F2 Wert 1');
INSERT INTO #Quelle VALUES ('F1 Wert 2', 'F2 Wert 2');
GO
-- Hier soll es letztendlich hin
CREATE TABLE #Ziel
(ID int NOT NULL IDENTITY (1, 1),
Feld1 varchar(50) NOT NULL,
Feld2 varchar(50) NULL,
Feld3 varchar(50) NULL,
CONSTRAINT PK_Ziel PRIMARY KEY CLUSTERED(ID)
);
GO
-- Staging-Tabelle; muss dem Design der SP entsprechen
CREATE TABLE #Staging
(ID int NOT NULL, -- !Keine Identity!
Feld1 varchar(50) NOT NULL,
Feld2 varchar(50) NULL,
CONSTRAINT PK_Staging PRIMARY KEY CLUSTERED(ID)
);
GO
-- Die SP auf Server A; Design eigentlich unbekannt.
CREATE PROCEDURE #spQuelle
AS SELECT *
FROM #Quelle;
GO
-- Nun eine SP als ein MERGE alias UpSert anlegen
CREATE PROCEDURE #myMerge
AS
BEGIN
DELETE FROM #Staging;
-- Ergebnisse in die Staging-Tab übernehmen
INSERT INTO #Staging
EXEC #spQuelle;
-- Effizienz ist gefragt; also alles raus, was es nicht mehr gibt
DELETE FROM #Ziel
FROM #Ziel AS Z
LEFT JOIN #Staging AS S
ON Z.ID = S.ID
WHERE S.ID IS NULL;
-- Vorhandene DS Updaten
UPDATE #Ziel
SET Feld1 = S.Feld1,
Feld2 = S.Feld2
FROM #Ziel AS Z
INNER JOIN #Staging AS S
ON Z.ID = S.ID
WHERE Z.Feld1 <> S.Feld1
OR Z.Feld2 <> S.Feld2;
-- Nun neue DS
INSERT INTO #Ziel (Feld1, Feld2)
SELECT S.Feld1, S.Feld2
FROM #Staging AS S
LEFT JOIN #Ziel AS Z
ON S.ID = Z.ID
WHERE Z.ID IS NULL;
END;
GO
-- Ersten Sync ausführen
EXEC #myMerge;
-- Eigene Daten ins Feld 3
UPDATE #Ziel
SET Feld3 = 'Ich gehöre mir!'
WHERE ID = 1;
GO
-- Nun Neu- & Änderungen
INSERT INTO #Quelle VALUES ('F1 Wert 3', 'F2 Wert 3');
UPDATE #Quelle
SET Feld1 = 'F1 Wert1 Ich bin geändert'
WHERE ID = 1;
GO
-- Neuen Sync ausführen
EXEC #myMerge;
GO

-- Ergebnis prüfen
SELECT * FROM #Ziel;
GO
DROP PROCEDURE #spQuelle;
DROP PROCEDURE #myMerge;
DROP TABLE #Staging;
DROP TABLE #Quelle;
DROP TABLE #Ziel;[/highlight]
Und bevor einer fragt; Nein, Langeweile habe ich kein, nur ausnahmsweise mal Urlaub und sowieso Interesse an allen Problemen rund um MSSQL; ich könnte ja mal die gleichen bekommen.

[Thomas2]

Hallo,

danke für deine bisherige Hilfe! Hast du zufällig den Code gefunden oder meintest du den geposteten? Werde diese Woche mich wieder der Arbeit widmen (müssen) und benötige daher jeden möglichen Input.

Danke

[O. Helper]

Der gepostete war nur ein Beispiel, wie es aussehen könnte.

Das andere Script, was ich meinte, habe ich auch wieder gefunden:
http://entwickler-forum.de/showpost....51&postcount=6
Das bezieht sich aber auf eine andere Tabelle aus Quelle, keine Stored Procedure; da musst Du es etwas umbasteln müssen.

[Thomas2]

Hallo,

ist es auch möglich die ganze Geschichte über einen SSH-Tunnel zu erledigen oder muss man in derselben Domäne o.ä. sein?

Den Snapshot kann man ja soweit ich das gesehen habe als spezifische Datei per Hand einladen.

Danke

[O. Helper]

Hallo Thomas,

wie die Kommunikation selbst abläuft, ist für den SQL Server transparent (= egal), hauptsache sie funktioniert.

Das Snapshot kann man manuell übertragen. Ich habe es, mangels Filezugriff sogar mal so gemacht, das ich aus der Quelldatenbank nur ein Create-Script erstellt habe und das dann von Hand auf dem Abonnenten ausgeführt habe.