MSSQL Replikation

[haentschman]

Hallöle...:P

Da ich Alleinunterhalter in der Firma bin, und nur auf meine eigenen Entscheidungen höre :lol:, wollte ich diesmal lieber noch andere Meinungen einholen...:zwinker:

Gegeben:

1. 1 DB Server in der Firma
2. DB Server in den verschieden Niederlassungen

Frage vorweg:
Die einzelnen Transanktionsmöglichkeiten habe ich schon ausprobiert...funktionieren alle.:zwinker: Ich habe aber verschiedene Konstellationen die eigentlich ein Mischen erfordern. Geht auch...nur welche sind die besseren für die Konstellation? Welche Fallstricke erwarten mich?
...wie macht man es richtig.:gruebel:

Konstellation 1:
1. Daten die "sofort" synchronisiert werden müssen. Bidirektional. - Mergereplikation (1 Minute Intervall) funktioniert
Info: Jeder Datensatz wird in einer Sperrtabelle "gesperrt" bevor er geöffnet wird. In dieser einen Minute könnte der Andere auch den DS öffen und schreiben. Wie oft kommt das vor? :gruebel:
vs.
1. Jede "Niederlassung" hat seinen Verleger, der Server hat seinen Verleger - Transaktionsreplikation noch nicht probiert
Info:
https://docs.microsoft.com/de-de/sql...l-server-ver15

Zitat:

Wenn Sie ein Abonnement unter Verwendung des vollqualifizierten Domänennamens (FQDN) zu einer bidirektionalen Veröffentlichung hinzufügen möchten, sollten Sie überprüfen, ob der Servername (@@SERVERNAME) des Abonnenten den FQDN zurückgibt. Wenn der Servername des Abonnenten den FQDN nicht zurückgibt, führen Änderungen durch den Abonnenten möglicherweise zu Primärschlüsselverstößen.

Konstellation 2:
1. "statische" Daten unidirektional zum Abonnenten - Transaktionsreplikation funktioniert
Option: ggf. Umstellung auf bidirektional

Danke für Infos... :wink:

[Bernhard Geyer]

Funktionieren werden alle.
Frage ist nur ob alle Anwendungen mitspielen.

Es ist immer problematisch wenn die Anwendung Autoinc-Felder nutzt.
Das zweite Problem sind immer Cascatierte Löschoperationen.

Da kann es leicht passieren das die Replikationen "die Krätsche machen".

[haentschman]

Zitat:

Frage ist nur ob alle Anwendungen mitspielen.

...überall meine Anwendungen. Allerdings Umbau von Desktopversion zu Filialsystem...mal eben so. :?

Zitat:

Es ist immer problematisch wenn die Anwendung Autoinc-Felder nutzt.

Code:

create table xxx(
  ID int identity(1,1) not null,

Die ID Felder sind AutoInc...:cry: Lassen wir auf uns zukommen. Da sind noch einige Leichen im Keller...:cry:
Was greift eher... die identity oder AutoInc? Bringt es was das auf NONE umzustellen?

Zitat:

Das zweite Problem sind immer Cascatierte Löschoperationen.

:? Im Moment habe ich eine Cascade die aber nie benutzt werden "sollte", weil diese Daten nicht gelöscht werden. Die Cascade ist nur für "Wartungen"...:zwinker:
Kann ich mal ausprobieren.

Danke

[Frickler]

Kann man bei Identity eine Art Startwert vorgeben? Dann addiere doch eine sehr hohe Zahl oben drauf, und zwar auf jede zu mergende Tabelle einen anderen. Ich nehme mal an, das sind BigInt, also 64 Bit Integer? Dann auf eine Tabelle 1.000.000.000.000 draufaddieren, bei der nächsten 2.000.000.000.000 usw. Die Ids zählen dann nicht 1, 2, 3, ... usw sondern halt 1.000.000.000.001, 1.000.000.000.002, 1.000.000.000.003, ...usw. - und es gibt keine Konflikte beim Mergen.

Wenns sich um Filialen handelt, wäre auch die Filial-ID möglich, vorausgesetzt, das ist ne Zahl...

[haentschman]

Zitat:

und es gibt keine Konflikte beim Mergen.

...dafür ist die Sperrtabelle da. Damit nur einer schreiben kann. Bei Mergerepikation könnte es vorkommen, das 2 dann den DS offen haben...

Zitat:

1.000.000.000.003, ...usw. - und es gibt keine Konflikte beim Mergen

:gruebel: Wenn 2 die 1.000.000.000.003 schreiben könnte das auch zu Konflikten führen.

[Bernhard Geyer]

Zitat:

Zitat von Frickler (Beitrag 1503129)

Kann man bei Identity eine Art Startwert vorgeben?

Kann man.

Wir hatten zwei CRM-Systeme, welche laut Feature-Matrix Replikation bei MS SQL Unterstützten.
Beide haben nach kurzer Zeit die Replikation nicht mehr unterstützt, da trotz dieser Möglichkeiten die ganzen Filialen doch irgendwann mal Replikations/Merge-Probleme zeigten.

[Bernhard Geyer]

Zitat:

Zitat von haentschman (Beitrag 1503131)

:gruebel: Wenn 2 die 1.000.000.000.003 schreiben könnte das auch zu Konflikten führen.

Filiale 1 bekommt Nummernbereich 1.000.000.000.000 bis 1.999....
Filiale 2 bekommt Nummernbereich 2.000.000.000.000 bis 2.999....

Theoretisch gibts keine Überschneidungen.
Praktisch sind bei uns 2 Hersteller von CRMs an genau diesem Problem gescheitert...

[haentschman]

Zitat:

Wenn 2 die 1.000.000.000.003 schreiben könnte das auch zu Konflikten führen.

...wenn aber 2 Niederlassungen oder der Hauptsitz gleichzeitig, was möglich ist, am Beleg arbeiten, hast du trotzdem evt. Konflikte...unabhängig von der ID des Datensatzes...weil die ID ist in diesem Falle gleich! :wink:

[Bernhard Geyer]

Zitat:

Zitat von haentschman (Beitrag 1503136)

...wenn aber 2 Niederlassungen oder der Hauptsitz gleichzeitig, was möglich ist, am Beleg arbeiten, hast du trotzdem evt. Konflikte...unabhängig von der ID des Datensatzes...weil die ID ist in diesem Falle gleich! :wink:

Gleiche schon existierende ID.
Das ist eigentlich immer das "kleinere Problem", da hierfür entweder die Anwendung eine Konflikt-Wizward anbieten muss/wird oder das über die DB-Tools der Hersteller läuft.

Unsere Anwendung läuft auch bei einem Kunden mit zwei großen Standorten mit Merge-Replikation.
Auch wenn unsere Anwendung nicht für Replikation ausgelegt ist, funktioniert es. Aber auch nur weil die Nummernbereiche getrennt sind und praktisch der o.g. Fall nicht vorkommen sollte. Und falls doch ist der en IT so fähig die Merge-Konflikte selbständig zu lösen.

[haentschman]

Hallöle...:P

Zitat:

die Anwendung eine Konflikt-Wizward anbieten muss

...ich stelle sicher, daß nur einer im "System" diesen DS schreiben kann. Alle anderen kennen die Sperre, weil sie ist auf der "Masterdatenbank" eingetragen. Jeder Client setzt beim Bearbeiten die Sperre "zentral"...und nimmt sie auch wieder raus.

Klar kann trotzdem noch einiges schiefgehen. :wink: Sowas wie: Sperre kann nicht entfernt werden...weil Internet kaputt. :stupid: Dafür kann man die Sperre im Notfalle manuell entfernen.

...aber wenigstens keine überschriebenen Daten. :thumb:

Danke an Alle...

[haentschman]

8-)
[neue Frage]
Ich habe auf dem Live System NUR eine Veröffentlichung erstellt. Der Snapshot wird aller 5 Minuten ausgeführt...ohne Probleme.
An dieser Veröffentlichung hängt noch kein Abonnent dran.

Fehler beim normalen Speichern (SQL) eines komplexen Objektes innerhalb einer Transaktion:

Zitat:

exception message : [FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Ein Rollback für SP_3 kann nicht ausgeführt werden. Es wurde keine Transaktion oder kein Sicherungspunkt mit diesem Namen gefunden.

vereinfacht:

Delphi-Quellcode:

begin
  Qry := CreateQuery;
  try
    Transaction := TFDTransaction.Create(nil);
    try
      Transaction.Connection := FConnection;
      Qry.Transaction := Transaction;

      Transaction.StartTransaction;
      try
        case Receipt.State of
          sdsModified:
          begin
            Qry.SQL.Text := GetSQLByName('XXX_UPDATE_ID');
            Qry.ParamByName('ID').AsInteger := Receipt.ID;
            FillParameters; 
            Qry.ExecSQL;
            WriteLists; //wieder SQL für Detail Objekte
          end;
        end;
        Transaction.Commit;
      except
        Transaction.Rollback;
      end;
    finally
      Transaction.Free;
    end;
  finally
    Qry.Free;
  end;
end;

PS: es gibt keine SP_3. In Fehlermeldungen später war es auch u.a. SP_2. :roll:

..nach Entfernung der Veröffentlichung wird wieder normal gespeichert. :?

Nach was muß ich suchen? :gruebel:

[rokli]

Neuer Gedanke
Status in den Tabellen:

Status = 0 > neuer Datensatz ... System1 darf verarbeiten

System 1 liest ein und setzt Status danach = 10
Status = 10 > System1 hat verarbeitet

System 2 liest ein und setzt Status danach = 20
Status = 20 > System2 hat verarbeiten

So weißt du immer, wer das schon bearbeitet hat und wer nicht ...

[haentschman]

Danke für deine Idee. :thumb:

Inzwischen habe ich herausgefunden, daß diese Meldung das Ergebnis einer vorgehenden Feldermeldung ist. :? Da muß ich weiter suchen... :wink:

[jobo]

Sowohl uns als auch Dir bleibt der SQL Code inkl. Parameter verborgen.

Code:

            Qry.SQL.Text := GetSQLByName('XXX_UPDATE_ID');
            Qry.ParamByName('ID').AsInteger := Receipt.ID;
            FillParameters;

Wie wäre es, das finale Statment oder Params getreten zu loggen und wenn einem der Fehler nicht ins Auge springt, dann zu Fuß auf die DB loslassen?

Wenn es kein SP2, SP3 gibt und das so schick dynamisch ist, klingt es ein wenig, als ob da Werte reinlaufen, die beim Parsen / Quoten versehentlich zu SQL werden, statt Parameter zu bleiben.

[haentschman]

Moin...

Zitat:

Wie wäre es, das finale Statment oder Params getreten zu loggen

PS: Das Statement funktioniert und ist hunderte Mal täglich im Einsatz. Mir war klar das das mit der "Aktivierung" der Replikation zu tun hat.

Zitat:

Sowohl uns als auch Dir bleibt der SQL Code inkl. Parameter verborgen.

...mir nicht. Euch wollte ich die 280 Zeilen Statement nicht antun. :zwinker:
Auszug:

Code:

update XXX set
   Nummer = :ZGIWB
  ,DatumBeleg = :OGMIT
...
  ,DATEV = :PDSOS
  where ID = :ID

Ich bin gerade dabei die DB anzupassen und den Quelltext zu korrigieren. Im Quelltext gab es eine Anweisung die die Replikationsspalte per Update geändert hat. Das ist nicht zulässig! Der obige Fehler ist u.a. das Ergebnis hieraus...:zwinker:

Trotzdem Danke...:cheers:

[jobo]

Na nichts zu danken, es war ja sowieso eher eine leise Kritik. Wie soll man an einem unbekannten Statement mit unbekannten Parametern und unbekanntem Modell einen DB-Fehler finden?

:cheers:

[haentschman]

[Update]
Das mit der "manuellen" Änderung der Replikationsspalte war die Ursache. :roll: Ich habe aufgehört die Referenzen zu zählen...:?
...jetzt klappt das auch mit dem "Nachbarn" = SQL. :P

[haentschman]

HINWEIS:
(durch Test verifiziert) :evil:

Durch die eingerichtete Replikation funktioniert

@@IDENTITY
SCOPE_IDENTITY()
GetLastAutoGenValue (benutzt imho SCOPE_IDENTITY())

als Value für den eingefügten Datensatz nicht mehr richtig! (In der Tabelle steht es richtig, aber die Rückgabe ist falsch)

Da die Replikation in der Session reichlich Werte neu anlegt, kommen nach dem Speichern komische ID als LastID raus. :evil:
Beispiel:
Nach Insert in der Tabelle ID=326236
Nach GetLastAutoGenValue ID = 56 :shock:
...Replikation gelöscht...es kommt wieder nach Insert in der Tabelle ID=326237 raus.

Die einzige Variante:
IDENT_CURRENT(tablename)

...:kotz:

Was kommt noch? :gruebel:

Zitat:

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

[jobo]

Ich habe nie verstanden, warum man diese Technik nutzt (oder sogar warum es sie gibt).
"Gib mir mal die letzte ID -von wem ist mir egal .. "
Es gibt auch glaub ich niemand, der das empfiehlt.

Es gibt m.E. in allen nennenswerten DB Engines eine RETURNING Clause oder Vergleichbares.
Bei MS SQL ist es OUTPUT.spaltenname [into @Variable].
Man bekommt also definierte Werte zurück oder schreibt sie in Variablen (innerhalb T-SQL).

Konkretes Beispiel

Delphi-Quellcode:

>select @@version;
>
>(No column name)
>--------------------------
>Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64) 
>
>create table person (
  person_id int identity(1,1),
  last_name varchar(255) NOT NULL,
  first_name varchar(255),
  age int);
>
>insert into person (first_name, last_name ) values('test 1','test'),('test 2','test'),('test 3','test');
>
>
>select * from person;
>
>person_id | last_name | first_name | age
>---------------------------------------------------
 1         | test      | test 1     | null     
 2         | test      | test 2     | null
 3         | test      | test 3     | null
>
>insert  person ( first_name, last_name) 
 OUTPUT INSERTED.person_id
 values('test 1','test');
>
>person_id | 
>------------
 4         |
>
>insert  person ( first_name, last_name) 
 OUTPUT INSERTED.person_id, INSERTED.first_name, INSERTED.last_name
 values('test 1','test');
>
>person_id | first_name | last_name 
>-----------------------------------
 5         | test 1     | test

Den Praxiseinsatz kenne ich nicht, da ich MS SQL nicht nutze. Nur von anderen Anbietern.
Im SP Bereich ist es sehr komfortabel einsetzbar.

Im (Delphi) Client muss man sich dran gewöhnen, ein Insert auf seine Rückgabewerte zu behandeln und ist glücklich.

Also vielleicht lohnt sich ein Test bei Dir oder dann der Umbau wenigstens der kritischen Stellen.

[haentschman]

Moin...8-)

@jobo: :cheer: ...mein geliebtes "returning" auch im MSSQL.

...ich habe mal was probiert (was funktioniert):

Variante 1:

1. Insert Statement absetzen (ExecSQL)
2. Query: 'select IDENT_CURRENT(Bla) as LastID' absetzen
3. LastID := Query.FieldByName(LastID).AsInteger

Variante 2:

1. "Script" mit Query.Open
2.

Code:

declare @LastID table (LastID int)

insert into Bla 
  (Blubb) 
OUTPUT INSERTED.ID into @LastID 
  values 
  (99)

select LastID from @LastID

3. LastID := Query.FieldByName('LastID').AsInteger (gleiche Query :wink:)

PS: nur OUTPUT INSERTED.ID läuft in einen Fehler:

Zitat:

Für die Bla-Zieltabelle der DML-Anweisung dürfen keine Trigger aktiviert sein, wenn die Anweisung eine OUTPUT-Klausel ohne INTO-Klausel enthält

Die Replikation legt aber reichlich Trigger an. :zwinker:


Welche Variante würdet ihr bevorzugen?

Imho ist die sicherste die Variante 2. :gruebel: Bedeutet aber auch, das alle insert Statements anpaßt werden müssen. (23 aktuell)

Danke...:wink:

[jobo]

Wenn es solche Einschränkungen bei OUTPUT gibt, würde ich es erstmal mir einer Replikationstabelle als Test ausprobieren, bevor ich alles umbaue.
Sieht irgendwie nach der Frage des kleineren Übels aus.

[haentschman]

Moin...8-)

Zitat:

bevor ich alles umbaue.

...bei mir war das zügig erledigt. :stupid: Ich habe mich für Variante 2 entschieden.

[Werbung ON]
Vieleicht hat mir geholfen daß ich die SQL in Dateien/Ressourcen gespeichert habe. :thumb: Damit gab es "quasi" keinen Umbau am Quelltext. :thumb: Siehe Bild...

Quelltext Old:

Delphi-Quellcode:

function TDatabaseBase.GetLastId: Integer;
begin
  Result := FConnection.GetLastAutoGenValue('');
end;
...
function TDatabase.Write(User: TSEAMUser): Integer;
var
  Qry: TFDQuery;
begin
  Result := -1;
  Qry := CreateQuery;
  try
    case User.State of
      sdsNew:
      begin
        Qry.SQL.Text := GetSQLByName('COM_INSERT_USERS');
...
        Qry.ParamByName('ACC').AsInteger := User.AccessCount;
        Qry.ExecSQL;
        User.ID := GetLastId;
        Result := User.ID;
      end;
      sdsModified:
      begin
        Qry.SQL.Text := GetSQLByName('COM_EDIT_USERS');
        Qry.ParamByName('ID').AsInteger := User.ID;
        Qry.ParamByName('ROI1').AsInteger := User.Role_1.ID;
...

Quelltext New:

Delphi-Quellcode:

function TDatabaseBase.InsertWithGetLastID(Query: TFDQuery): Integer;
begin
  Query.Open;
  Result := Query.FieldByName('LastID').AsInteger;
end;
...
function TDatabase.Write(User: TSEAMUser): Integer;
var
  Qry: TFDQuery;
begin
  Result := -1;
  Qry := CreateQuery;
  try
    case User.State of
      sdsNew:
      begin
        Qry.SQL.Text := GetSQLByName('COM_INSERT_USERS');
...
        Qry.ParamByName('ACC').AsInteger := User.AccessCount;
        User.ID := InsertWithGetLastID(Qry);

        Result := User.ID;
      end;
      sdsModified:
      begin
        Qry.SQL.Text := GetSQLByName('COM_EDIT_USERS');
        Qry.ParamByName('ID').AsInteger := User.ID;
        Qry.ParamByName('ROI1').AsInteger := User.Role_1.ID;
...

Danke nochmal. :wink:

[jobo]

Zitat:

Zitat von haentschman (Beitrag 1504534)

Moin...8-)

Zitat:

bevor ich alles umbaue.

...bei mir war das zügig erledigt. :stupid: Ich habe mich für Variante 2 entschieden.

[Werbung ON]
Vieleicht hat mir geholfen daß ich die SQL in Dateien/Ressourcen gespeichert habe. :thumb: Damit gab es "quasi" keinen Umbau am Quelltext. :thumb: Siehe Bild...
..

Gut! Geordnete Strukturen, Trennung von Persistenz, Verhalten und GUI machen eine solche Umstellung mit Sicherheit sauberer und robuster.
Mein Hinweis zum Umbau galt eher der Problematik eines dosierten Funktionstests bzw. dann auch Praxistests rund um die Replikation. Die "Randbedingungen" bzw. Einschränkungen bei der Funkiont OUTPUT scheinen mir entgegen meiner Empfehlung nicht besonders sexy. Und da fehlt mir selbst die Praxiserfahrung mit MS SQL. Wenn ich mich nicht irre, war die Doku, die ich nach Deiner Trigger Problematik angeschaut hatte, halbwegs aktuell (SQL Server 2019), aber voller Einschränkungen.
Ich hoffe es funktioniert im Realbetrieb besser als die IDENTITY Verfahren!

[Uwe Raabe]

Zitat:

Zitat von haentschman (Beitrag 1504534)

Vieleicht hat mir geholfen daß ich die SQL in Dateien/Ressourcen gespeichert habe.

Wenn man es genau nimmt, dann speichert derjenige, der das SQL z.B. im gleichnamigen TFDQuery-Property einträgt, das ja auch in einer Ressource:

Delphi-Quellcode:

{$R *.dfm}

:nerd:

[jobo]

Zitat:

Zitat von Uwe Raabe (Beitrag 1504641)

Zitat:

Zitat von haentschman (Beitrag 1504534)

Vieleicht hat mir geholfen daß ich die SQL in Dateien/Ressourcen gespeichert habe.

Wenn man es genau nimmt, dann speichert derjenige, der das SQL z.B. im gleichnamigen TFDQuery-Property einträgt, das ja auch in einer Ressource:
..

Let's get nerdy!
Uwe hat Recht, aber da würde ich anmerken, Ressource ist nicht gleich Ressource.
Wenn man ausschließlich mit Delphi arbeitet, spielt der Zugang und die Änderbarkeit der Ressourcen nicht so eine große Rolle. Im Gegenteil, manchmal ist ja die Möglichkeit, alles in einer Anwendung zu integrieren (und as is auszuliefern) auch sehr gewollt und mit der resultierenden Statik auch besonders robust.
Bedeutet aber auch, dass Ressourcenänderungen mit der Neukompilation und Verteilung verbunden sind (außer, man fährt da mehrgleisig, womit der Faktor Robustheit an der Stelle aber auch hops geht.)
Ich kenne die Komponenten von haentschan nicht, aber Zugängigkeit und Dynamik der (ausgelagerten) Ressourcen sehe ich jedoch als wichtigen Faktor. Und mal kurz durchgespielt: Wenn ich mit transparent zugängigen, externen Ressourcen loslege, ist es aus dieser Richtung auch nicht sehr aufwändig, bei Bedarf Ressourcen zu embedden, automatisert und mir klaren Verantwortlichkeiten. Da würde mir ein Ressourcenmanager wie von haentschman wahrscheinlich besser gefallen, als ein *.dfm.

[haentschman]

Zitat:

Ich kenne die Komponenten von haentschan nicht

...keine Komponenten. Einfach SQL Files die eingebunden werden.
Anleitung:
https://www.delphipraxis.net/49505-s...einbinden.html

Zitat:

besser gefallen, als ein *.dfm.

...sucht sich leichter und ist von der verwendeten Komponente nicht abhängig. :zwinker: