Konuyu Oyla:
  • Derecelendirme: 0/5 - 0 oy
  • 1
  • 2
  • 3
  • 4
  • 5
SQL VeriTabanı Kopyalama Hk.
#1
Merhaba,

Lokalda çalışan ve anlık olarak veri girişi olan bir veritabanını sunucu tarafında bulunan MS SQL Server 2008'e nasıl kopyalayabilirim. Lokalde çalışan veri tabanında bulunan tablo alanları,view ler,procedure ler ve trigerler 2 aya bir güncelleme yapılmakta. Yapılan bu güncellemeler ile birlikte ve içindeki veriler ile birlikte günlük olarak nasıl kopyalanabilir? Yardımlarınız için şimdiden teşekkür ederim.
Cevapla
#2
Merhaba
Replication konusunu araştırın derim.

http://www.veritabani.gen.tr/2016/11/01/...ion-nedir/
Cevapla
#3
(07-09-2023, Saat: 12:00)serdar Adlı Kullanıcıdan Alıntı: Merhaba
Replication konusunu araştırın derim.

http://www.veritabani.gen.tr/2016/11/01/...ion-nedir/

Bunu daha önce araştırdım yalnız veri tabanı sürekli güncelleniyor yani yeni tablolar, tablaya yeni alanlar ekleniyor. Sürekli günellenen bir veritabanına yapılması nasıl olur. Örneğin Deneme tablasounda A ve B alanları var bu şekliyle replikasyon yaptık aradan 2 ay geçti aynı tabloya C alanı eklendi sunucu tarafına A ve B alanlarımı replike edilie yoksa A,B ve C alanları mı? Bu konu beni düşündürüyor çünkü bunu ile alakalı bir bilgiye rastlayamadım
Cevapla
#4

unit uCheckDataChangeAndSyncThread;



interface

uses
  System.SysUtils,
  System.Classes,
  UniProvider,
  ODBCUniProvider,
  AccessUniProvider,
  SQLServerUniProvider,
  Data.DB,
  Uni,
  CRBatchMove,
  uUtils.LogClass;

type
  TMonitorDataChangeThread = class(TThread)
  private
    FSourceDatabaseConnectString: string;
    FTargetDatabaseConnectString: string;
    FSourceTableName: string;
    FTargetTableName: string;
    FAdditionalFieldsToFilter: TArray<string>;
    FAdditionalValuesToFilter: TArray<string>;
    FSourceDB: TUniConnection;
    FTargetDB: TUniConnection;
    FBatchMove: TCRBatchMove;
    FSourceQuery: TUniQuery;
    FTargetQuery: TUniQuery;
    FMetaDataIndexName: TUniMetaData;
    FMetaDataColumnName: TUniMetaData;
    FSourceDateTimeFieldname: string;
    FTargetDateTimeFieldname: string;
    FTargetLastRecordValue: string;
    FOldDaysToIncludeInCheck: Word;
    FComInitialized: Boolean;
    FLastDate: TDateTime;
    FActive: Boolean;
    FCheckFrequency: Word;
    FLog: TLog;
    FLogDir: string;
    FLogFilename: string;
    FLoggingActive: Boolean;
    FFormatSettings: TFormatSettings;
    procedure SetLogDir(const Value: string);
    procedure SetLogFilename(const Value: string);
    procedure SetLoggingActive(const Value: Boolean);
    procedure DoDBConnection(DB: TUniConnection; const ConnectionString: string);
    function GetPrimaryKeyFieldname(DB: TUniConnection; const Tablename: string; out FieldName: string): Boolean;
    function GetDateTimeFormatted(const ProviderName: string; const ADate: TDateTime): string;
    function PrepareSourceQuery(): Boolean;
    function PrepareTargetQuery(): Boolean;
    function GetTargetLastDate(): Boolean;
    function DeleteMissingFromTarget(): Boolean;
  protected
    procedure Execute(); override;
  public
    constructor Create(); reintroduce; overload;
    destructor Destroy(); override;
    procedure DoTerminate(); reintroduce;

    property SourceDatabaseConnectString: string read FSourceDatabaseConnectString write FSourceDatabaseConnectString;
    property TargetDatabaseConnectString: string read FTargetDatabaseConnectString write FTargetDatabaseConnectString;
    property SourceTablename: string read FSourceTableName write FSourceTableName;
    property TargetTablename: string read FTargetTableName write FTargetTableName;
    property AdditionalFieldsToFilter: TArray<string> read FAdditionalFieldsToFilter write FAdditionalFieldsToFilter;
    property AdditionalValuesToFilter: TArray<string> read FAdditionalValuesToFilter write FAdditionalValuesToFilter;
    property SourceDateTimeFieldname: string read FSourceDateTimeFieldname write FSourceDateTimeFieldname;
    property TargetDateTimeFieldname: string read FTargetDateTimeFieldname write FTargetDateTimeFieldname;
    property OldDaysToIncludeInCheck: Word read FOldDaysToIncludeInCheck write FOldDaysToIncludeInCheck;
    property CheckFrequency: Word read FCheckFrequency write FCheckFrequency;
    property LogDirectory: string read FLogDir write SetLogDir;
    property LogFilename: string read FLogFilename write SetLogFilename;
    property LoggingActive: Boolean read FLoggingActive write SetLoggingActive;
    property Active: Boolean read FActive;
  end;


implementation


uses
  ActiveX,
  System.Diagnostics,
  DateUtils;


//==============================================================================
{ TMonitorDataChangeThread }
//==============================================================================
//------------------------------------------------------------------------------
constructor TMonitorDataChangeThread.Create();
begin
  inherited Create(True);
  FreeOnTerminate := True;

  FLog := TLog.Create(nil);
  FSourceDB := TUniConnection.Create(nil);
  FTargetDB := TUniConnection.Create(nil);
  FSourceQuery := TUniQuery.Create(nil);
  FTargetQuery := TUniQuery.Create(nil);
  FMetaDataIndexName := TUniMetaData.Create(nil);
  FMetaDataColumnName := TUniMetaData.Create(nil);
  FBatchMove := TCRBatchMove.Create(nil);
  FBatchMove.FieldMappingMode := mmFieldName;
  FBatchMove.Mode := bmAppendUpdate;

  FSourceQuery.Connection := FSourceDB;
  FTargetQuery.Connection := FTargetDB;
  FBatchMove.Source := FSourceQuery;
  FBatchMove.Destination := FTargetQuery;

  FActive := False;
  FCheckFrequency := 10;
  FLogDir := ExtractFileDir(ParamStr(0)) + PathDelim + 'SyncLog';
  SetLogFilename('yyyy-mm-dd".log"');
  SetLoggingActive(True);

  FFormatSettings := TFormatSettings.Create('en-US');

  FSourceTableName := EmptyStr;
  FTargetTableName := EmptyStr;
  FTargetLastRecordValue := EmptyStr;
  FSourceDateTimeFieldname := EmptyStr;
  FTargetDateTimeFieldname := EmptyStr;
  FOldDaysToIncludeInCheck := 0;
end;


//------------------------------------------------------------------------------
destructor TMonitorDataChangeThread.Destroy();
begin
  FLog.Free();
  FMetaDataIndexName.Free();
  FMetaDataColumnName.Free();
  FBatchMove.Free();
  FTargetQuery.Free();
  FSourceQuery.Free();
  FSourceDB.Free();
  FTargetDB.Free();

  inherited;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.DoDBConnection(DB: TUniConnection; const ConnectionString: string);
begin
  DB.ConnectString := ConnectionString;
  try
    DB.Open();
  except
    on E: EUniError do
    begin
      FLog.Log('DoDBConnection(): ' + E.Message);
    end;
  end;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.GetPrimaryKeyFieldname(DB: TUniConnection; const Tablename: string; out FieldName: string): Boolean;
var
  IndexName: string;
begin
  FMetaDataIndexName.Close();
  FMetaDataIndexName.Connection := DB;
  FMetaDataIndexName.MetaDataKind := 'Indexes';
  FMetaDataIndexName.Restrictions.AddPair('TABLE_NAME', Tablename);
  FMetaDataIndexName.Filter := 'UNIQUE=1';
  try
    FMetaDataIndexName.Open();
    FMetaDataIndexName.Filtered := True;
    case FMetaDataIndexName.RecordCount of
      0:
      begin
        FLog.Log('GetPrimaryKeyFieldname(): Cannot identify index name');
        Exit(False);
      end;
      1:
      begin
        IndexName := FMetaDataIndexName.FieldByName('index_name').AsString;
        FMetaDataIndexName.Close();
      end;

      else
      begin
        FLog.Log('GetPrimaryKeyFieldname(): There are more than one unique index in table "%s". We cannot identify primary key');
        Exit(False);
      end;
    end;
  except
    on E: EUniError do
    begin
      FLog.Log('GetPrimaryKeyFieldname()-1: ' + E.Message);
      Exit(False);
    end;
  end;

  FMetaDataColumnName.Close();
  FMetaDataColumnName.Connection := DB;
  FMetaDataColumnName.MetaDataKind := 'IndexColumns';
  FMetaDataColumnName.Restrictions.AddPair('TABLE_NAME', Tablename);
  FMetaDataColumnName.Filter := 'index_name=' + QuotedStr(IndexName);
  try
    FMetaDataColumnName.Open();
    FMetaDataColumnName.Filtered := True;
    case FMetaDataColumnName.RecordCount of
      0:
      begin
        FMetaDataColumnName.Close();
        FLog.Log('GetPrimaryKeyFieldname(): Cannot identify column name');
        Exit(False);
      end;

      else
      begin
        FMetaDataColumnName.First();
        FieldName := EmptyStr;
        while not FMetaDataColumnName.Eof do
        begin
          FieldName := FieldName + FMetaDataColumnName.FieldByName('column_name').AsString + ';';
          FMetaDataColumnName.Next();
        end;
        FMetaDataColumnName.Close();
        if FieldName.Length > 0 then
          SetLength(FieldName, FieldName.Length-1);
        Exit(True);
      end;
    end;
  except
    on E: EUniError do
    begin
      FLog.Log('GetPrimaryKeyFieldname()-2: ' + E.Message);
      Exit(False);
    end;
  end;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.GetDateTimeFormatted(const ProviderName: string; const ADate: TDateTime): string;
begin
  if SameText(ProviderName, 'Access') then
    Exit(FormatDateTime('"#"mm/dd/yyyy hh:nn:ss"#"', FLastDate, FFormatSettings))
  else
  if SameText(ProviderName, 'SQL Server') then
    Exit(FormatDateTime('yyyy-mm-dd hh:nn:ss', FLastDate, FFormatSettings));
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.GetTargetLastDate(): Boolean;
var
  QueryText: string;
  I: Integer;
begin
  QueryText := 'select max(' + AnsiQuotedStr(FTargetDateTimeFieldname, '"') + ') from ' + AnsiQuotedStr(FTargetTableName, '"');
  if Length(FAdditionalFieldsToFilter) > 0 then
  begin
    QueryText := QueryText + ' where ';
    for I := Low(FAdditionalValuesToFilter) to High(FAdditionalValuesToFilter) do
      QueryText := QueryText + AnsiQuotedStr(FAdditionalFieldsToFilter[I], '"') + ' = ' + QuotedStr(FAdditionalValuesToFilter[I]) + ' and ';
    SetLength(QueryText, QueryText.Length - 5); // remove additional " and "
  end;

  FTargetQuery.SQL.Text := QueryText;
  try
    FTargetQuery.Open();
    if FTargetQuery.Fields[0].IsNull then
      FLastDate := 0
    else
    begin
      FLastDate := FTargetQuery.Fields[0].AsDateTime;
    end;
    FTargetQuery.Close();
  except
    on E: EUniError do
    begin
      FLog.Log('GetTargetLastDate(): ' + E.Message);
      Exit(False);
    end;
  end;

  Result := True;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.PrepareSourceQuery(): Boolean;
var
  QueryText: string;
  I: Integer;
begin
  if not Assigned(FSourceQuery) then
  begin
    FLog.Log('PrepareSourceQuery(): SourceQuery is not assigned! Exiting.');
    Exit(False);
  end;

  QueryText := 'select * from ' + AnsiQuotedStr(FSourceTableName, '"');
  QueryText := QueryText + ' where ' + AnsiQuotedStr(FSourceDateTimeFieldname, '"') + ' > ' + GetDateTimeFormatted(FSourceDB.ProviderName, FLastDate);
  for I := Low(FAdditionalValuesToFilter) to High(FAdditionalValuesToFilter) do
    QueryText := QueryText + ' and ' + AnsiQuotedStr(FAdditionalFieldsToFilter[I], '"') + ' = ' + QuotedStr(FAdditionalValuesToFilter[I]);

  FSourceQuery.SQL.Text := QueryText;
  try
    FSourceQuery.Open();
  except
    on E: EUniError do
    begin
      FLog.Log('PrepareSourceQuery(): ' + E.Message);
      Exit(False);
    end;
  end;

  Result := True;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.PrepareTargetQuery(): Boolean;
var
  QueryText: string;
  I: Integer;
begin
  if not Assigned(FTargetQuery) then
  begin
    FLog.Log('PrepareTargetQuery(): TargetQuery is not assigned! Exiting.');
    Exit(False);
  end;

  QueryText := 'select * from ' + AnsiQuotedStr(FTargetTableName, '"');
  QueryText := QueryText + ' where ' + AnsiQuotedStr(FTargetDateTimeFieldname, '"') + ' > ' + QuotedStr(GetDateTimeFormatted(FTargetDB.ProviderName, FLastDate));
  for I := Low(FAdditionalValuesToFilter) to High(FAdditionalValuesToFilter) do
    QueryText := QueryText + ' and ' + AnsiQuotedStr(FAdditionalFieldsToFilter[I], '"') + ' = ' + QuotedStr(FAdditionalValuesToFilter[I]);

  FTargetQuery.SQL.Text := QueryText;
  try
    FTargetQuery.Open();
  except
    on E: EUniError do
    begin
      FLog.Log('PrepareTargetQuery(): ' + E.Message);
      Exit(False);
    end;
  end;

  Result := True;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.DeleteMissingFromTarget(): Boolean;
var
  Count: Integer;
begin
  if not Assigned(FSourceQuery) then
  begin
    FLog.Log('DeleteMissingFromTarget(): SourceQuery is not assigned! Exiting.');
    Exit(False);
  end;

  if not FSourceQuery.Active then
  begin
    FLog.Log('DeleteMissingFromTarget(): SourceQuery is not open! Exiting.');
    Exit(False);
  end;

  if not Assigned(FTargetQuery) then
  begin
    FLog.Log('DeleteMissingFromTarget(): TargetQuery is not assigned! Exiting.');
    Exit(False);
  end;

  if not FTargetQuery.Active then
  begin
    FLog.Log('DeleteMissingFromTarget(): TargetQuery is not open! Exiting.');
    Exit(False);
  end;

  Count := 0;
  FTargetQuery.Last();
  while not FTargetQuery.Bof do
  begin
    if not FSourceQuery.Locate(FSourceDateTimeFieldname, FTargetQuery.FieldByName(FTargetDateTimeFieldname).AsDateTime, []) then
    begin
      FLog.Log('DeleteMissingFromTarget(): Deleting record: ' + FormatDateTime('yyyy-mm-dd hh:nn:ss', FTargetQuery.FieldByName(FTargetDateTimeFieldname).AsDateTime) + '...');
      try
        FTargetQuery.Delete();
        Inc(Count);
      except
        on E: EUniError do
        begin
          FLog.Log('DeleteMissingFromTarget(): Failed to delete record. ERROR: ' + E.Message);
        end;
      end;
    end
    else
      FTargetQuery.Prior();
  end;
  FLog.Log('Deleted record count: ' + Count.ToString());

  Result := True;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.Execute();
label
  UpdateIt;

var
  Timing: TStopwatch;
  InitialRun: Boolean;
  Updated: Boolean;
  Mode: string;
begin
  FComInitialized := Succeeded(CoInitialize(nil));

  if FSourceDatabaseConnectString = EmptyStr then
  begin
    FLog.Log('ERROR: SourceDatabaseConnectString is empty.');
    Exit();
  end;

  if FTargetDatabaseConnectString = EmptyStr then
  begin
    FLog.Log('ERROR: TargetDatabaseConnectString is empty.');
    Exit();
  end;

  if FSourceTableName = EmptyStr then
  begin
    FLog.Log('ERROR: SourceTablename is empty.');
    Exit();
  end;

  if FTargetTableName = EmptyStr then
  begin
    FLog.Log('ERROR: TargetTablename is empty.');
    Exit();
  end;

  if Length(FAdditionalFieldsToFilter) <> Length(FAdditionalValuesToFilter) then
  begin
    FLog.Log('ERROR: AdditionalFieldsToFilter <> AdditionalValuesToFilter.');
    Exit();
  end;

  if FSourceDateTimeFieldname = EmptyStr then
  begin
    FLog.Log('ERROR: SourceDateTimeFieldname is empty.');
    Exit();
  end;

  if FTargetDateTimeFieldname = EmptyStr then
  begin
    FLog.Log('ERROR: TargetDateTimeFieldname is empty.');
    Exit();
  end;

  FActive := True;
  InitialRun := True; // used for initial (very first time after thread starts) trigger of event
  Timing := TStopwatch.StartNew();
  while not Terminated do
  begin
    if Terminated then Break;
    if ((Timing.ElapsedMilliseconds / 1000) >= FCheckFrequency) or (InitialRun) then
    begin
      InitialRun := False;
      Timing.Stop();
      FLog.Log('TMonitorDataChangeThread.Execute(): Starting batch operation...');
      FLog.IncIndent();
      try
        FLog.Log('Checking SourceDB connection...');
        if not FSourceDB.Connected then
          DoDBConnection(FSourceDB, FSourceDatabaseConnectString);

        FLog.Log('SourceDB connection: ' + BoolToStr(FSourceDB.Connected, True));
        if not FSourceDB.Connected then
          Continue;

        FLog.Log('Checking TargetDB connection...');
        if not FTargetDB.Connected then
          DoDBConnection(FTargetDB, FTargetDatabaseConnectString);

        FLog.Log('TargetDB connection: ' + BoolToStr(FTargetDB.Connected, True));
        if not FTargetDB.Connected then
          Continue;

{
        if FSourcePrimaryKeyFieldname = EmptyStr then
          if not GetPrimaryKeyFieldname(FSourceDB, FSourceTableName, FSourcePrimaryKeyFieldname) then
            Continue;

        if FTargetPrimaryKeyFieldname = EmptyStr then
          if not GetPrimaryKeyFieldname(FTargetDB, FTargetTableName, FTargetPrimaryKeyFieldname) then
            Continue;
}

        FLog.Log('Reading last record date on Target...');
        if not GetTargetLastDate() then
          Continue;
        FLog.Log('Insert check date: ' + DateToISO8601(FLastDate));

        FBatchMove.Mode := bmAppend;
        Updated := False;
        Mode := 'insert';
UpdateIt:
        FLog.Log(Format('Preparing source query for %s...', [Mode]));
        if not PrepareSourceQuery() then
          Continue;

        FLog.Log(Format('Preparing target query for %s...', [Mode]));
        if not PrepareTargetQuery() then
          Continue;

        FLog.Log(Format('Starting %s operation...', [Mode]));
        try
          FBatchMove.Execute();
          FLog.Log('Modified record count: ' + FBatchMove.ChangedCount.ToString());
          FLog.Log('Moved (Inserted & Updated) record count: ' + FBatchMove.MovedCount.ToString());
        except
          on E: EUniError do
          begin
            FLog.Log('TMonitorDataChangeThread.Execute(): ' + E.Message);
          end;
        end;

        if (FOldDaysToIncludeInCheck > 0) and not Updated then
        begin
          FLastDate := IncDay(FLastDate, -FOldDaysToIncludeInCheck);
          FLog.Log('Update check date: ' + DateToISO8601(FLastDate));
          FBatchMove.Mode := bmUpdate;
          Updated := True;
          Mode := 'update';
          goto UpdateIt;
        end;

        // Bu noktada artık Query eski kayıtlar ile açılmış oluyor
        if (FOldDaysToIncludeInCheck > 0) then
        begin
          Mode := 'identifying missing records';
          FLog.Log(Format('Preparing source query for %s...', [Mode]));
          if not PrepareSourceQuery() then
            Continue;

          FLog.Log(Format('Preparing target query for %s...', [Mode]));
          if not PrepareTargetQuery() then
            Continue;

          FLog.Log('Starting deleting missing records from target...');
          DeleteMissingFromTarget();
        end;
      finally
        FSourceDB.Close();
        FTargetDB.Close();
        Timing := TStopwatch.StartNew();
        FLog.DecIndent();
        FLog.Log('TMonitorDataChangeThread.Execute(): Finished batch operation...');
      end;
    end;
    TThread.Sleep(100);
  end;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.SetLogDir(const Value: string);
begin
  FLog.BaseDir := Value;
  FLogDir := Value;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.SetLogFilename(const Value: string);
begin
  FLog.LogFilename := Value;
  FLogFilename := Value;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.SetLoggingActive(const Value: Boolean);
begin
  FLog.LoggingActive := Value;
  FLoggingActive := Value;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.DoTerminate();
begin
  if FComInitialized then CoUninitialize();
  inherited;
end;

end.


glagherMerhaba,

Lokalda çalışan ve anlık olarak veri girişi olan bir veritabanını sunucu tarafında bulunan MS SQL Server 2008'e nasıl kopyalayabilirim. Lokalde çalışan veri tabanında bulunan tablo alanları,view ler,procedure ler ve trigerler 2 aya bir güncelleme yapılmakta. Yapılan bu güncellemeler ile birlikte ve içindeki veriler ile birlikte günlük olarak nasıl kopyalanabilir? Yardımlarınız için şimdiden teşekkür ederim.
Cevapla
#5
(09-09-2023, Saat: 21:58)erdogan Adlı Kullanıcıdan Alıntı:

unit uCheckDataChangeAndSyncThread;



interface

uses
  System.SysUtils,
  System.Classes,
  UniProvider,
  ODBCUniProvider,
  AccessUniProvider,
  SQLServerUniProvider,
  Data.DB,
  Uni,
  CRBatchMove,
  uUtils.LogClass;

type
  TMonitorDataChangeThread = class(TThread)
  private
    FSourceDatabaseConnectString: string;
    FTargetDatabaseConnectString: string;
    FSourceTableName: string;
    FTargetTableName: string;
    FAdditionalFieldsToFilter: TArray<string>;
    FAdditionalValuesToFilter: TArray<string>;
    FSourceDB: TUniConnection;
    FTargetDB: TUniConnection;
    FBatchMove: TCRBatchMove;
    FSourceQuery: TUniQuery;
    FTargetQuery: TUniQuery;
    FMetaDataIndexName: TUniMetaData;
    FMetaDataColumnName: TUniMetaData;
    FSourceDateTimeFieldname: string;
    FTargetDateTimeFieldname: string;
    FTargetLastRecordValue: string;
    FOldDaysToIncludeInCheck: Word;
    FComInitialized: Boolean;
    FLastDate: TDateTime;
    FActive: Boolean;
    FCheckFrequency: Word;
    FLog: TLog;
    FLogDir: string;
    FLogFilename: string;
    FLoggingActive: Boolean;
    FFormatSettings: TFormatSettings;
    procedure SetLogDir(const Value: string);
    procedure SetLogFilename(const Value: string);
    procedure SetLoggingActive(const Value: Boolean);
    procedure DoDBConnection(DB: TUniConnection; const ConnectionString: string);
    function GetPrimaryKeyFieldname(DB: TUniConnection; const Tablename: string; out FieldName: string): Boolean;
    function GetDateTimeFormatted(const ProviderName: string; const ADate: TDateTime): string;
    function PrepareSourceQuery(): Boolean;
    function PrepareTargetQuery(): Boolean;
    function GetTargetLastDate(): Boolean;
    function DeleteMissingFromTarget(): Boolean;
  protected
    procedure Execute(); override;
  public
    constructor Create(); reintroduce; overload;
    destructor Destroy(); override;
    procedure DoTerminate(); reintroduce;

    property SourceDatabaseConnectString: string read FSourceDatabaseConnectString write FSourceDatabaseConnectString;
    property TargetDatabaseConnectString: string read FTargetDatabaseConnectString write FTargetDatabaseConnectString;
    property SourceTablename: string read FSourceTableName write FSourceTableName;
    property TargetTablename: string read FTargetTableName write FTargetTableName;
    property AdditionalFieldsToFilter: TArray<string> read FAdditionalFieldsToFilter write FAdditionalFieldsToFilter;
    property AdditionalValuesToFilter: TArray<string> read FAdditionalValuesToFilter write FAdditionalValuesToFilter;
    property SourceDateTimeFieldname: string read FSourceDateTimeFieldname write FSourceDateTimeFieldname;
    property TargetDateTimeFieldname: string read FTargetDateTimeFieldname write FTargetDateTimeFieldname;
    property OldDaysToIncludeInCheck: Word read FOldDaysToIncludeInCheck write FOldDaysToIncludeInCheck;
    property CheckFrequency: Word read FCheckFrequency write FCheckFrequency;
    property LogDirectory: string read FLogDir write SetLogDir;
    property LogFilename: string read FLogFilename write SetLogFilename;
    property LoggingActive: Boolean read FLoggingActive write SetLoggingActive;
    property Active: Boolean read FActive;
  end;


implementation


uses
  ActiveX,
  System.Diagnostics,
  DateUtils;


//==============================================================================
{ TMonitorDataChangeThread }
//==============================================================================
//------------------------------------------------------------------------------
constructor TMonitorDataChangeThread.Create();
begin
  inherited Create(True);
  FreeOnTerminate := True;

  FLog := TLog.Create(nil);
  FSourceDB := TUniConnection.Create(nil);
  FTargetDB := TUniConnection.Create(nil);
  FSourceQuery := TUniQuery.Create(nil);
  FTargetQuery := TUniQuery.Create(nil);
  FMetaDataIndexName := TUniMetaData.Create(nil);
  FMetaDataColumnName := TUniMetaData.Create(nil);
  FBatchMove := TCRBatchMove.Create(nil);
  FBatchMove.FieldMappingMode := mmFieldName;
  FBatchMove.Mode := bmAppendUpdate;

  FSourceQuery.Connection := FSourceDB;
  FTargetQuery.Connection := FTargetDB;
  FBatchMove.Source := FSourceQuery;
  FBatchMove.Destination := FTargetQuery;

  FActive := False;
  FCheckFrequency := 10;
  FLogDir := ExtractFileDir(ParamStr(0)) + PathDelim + 'SyncLog';
  SetLogFilename('yyyy-mm-dd".log"');
  SetLoggingActive(True);

  FFormatSettings := TFormatSettings.Create('en-US');

  FSourceTableName := EmptyStr;
  FTargetTableName := EmptyStr;
  FTargetLastRecordValue := EmptyStr;
  FSourceDateTimeFieldname := EmptyStr;
  FTargetDateTimeFieldname := EmptyStr;
  FOldDaysToIncludeInCheck := 0;
end;


//------------------------------------------------------------------------------
destructor TMonitorDataChangeThread.Destroy();
begin
  FLog.Free();
  FMetaDataIndexName.Free();
  FMetaDataColumnName.Free();
  FBatchMove.Free();
  FTargetQuery.Free();
  FSourceQuery.Free();
  FSourceDB.Free();
  FTargetDB.Free();

  inherited;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.DoDBConnection(DB: TUniConnection; const ConnectionString: string);
begin
  DB.ConnectString := ConnectionString;
  try
    DB.Open();
  except
    on E: EUniError do
    begin
      FLog.Log('DoDBConnection(): ' + E.Message);
    end;
  end;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.GetPrimaryKeyFieldname(DB: TUniConnection; const Tablename: string; out FieldName: string): Boolean;
var
  IndexName: string;
begin
  FMetaDataIndexName.Close();
  FMetaDataIndexName.Connection := DB;
  FMetaDataIndexName.MetaDataKind := 'Indexes';
  FMetaDataIndexName.Restrictions.AddPair('TABLE_NAME', Tablename);
  FMetaDataIndexName.Filter := 'UNIQUE=1';
  try
    FMetaDataIndexName.Open();
    FMetaDataIndexName.Filtered := True;
    case FMetaDataIndexName.RecordCount of
      0:
      begin
        FLog.Log('GetPrimaryKeyFieldname(): Cannot identify index name');
        Exit(False);
      end;
      1:
      begin
        IndexName := FMetaDataIndexName.FieldByName('index_name').AsString;
        FMetaDataIndexName.Close();
      end;

      else
      begin
        FLog.Log('GetPrimaryKeyFieldname(): There are more than one unique index in table "%s". We cannot identify primary key');
        Exit(False);
      end;
    end;
  except
    on E: EUniError do
    begin
      FLog.Log('GetPrimaryKeyFieldname()-1: ' + E.Message);
      Exit(False);
    end;
  end;

  FMetaDataColumnName.Close();
  FMetaDataColumnName.Connection := DB;
  FMetaDataColumnName.MetaDataKind := 'IndexColumns';
  FMetaDataColumnName.Restrictions.AddPair('TABLE_NAME', Tablename);
  FMetaDataColumnName.Filter := 'index_name=' + QuotedStr(IndexName);
  try
    FMetaDataColumnName.Open();
    FMetaDataColumnName.Filtered := True;
    case FMetaDataColumnName.RecordCount of
      0:
      begin
        FMetaDataColumnName.Close();
        FLog.Log('GetPrimaryKeyFieldname(): Cannot identify column name');
        Exit(False);
      end;

      else
      begin
        FMetaDataColumnName.First();
        FieldName := EmptyStr;
        while not FMetaDataColumnName.Eof do
        begin
          FieldName := FieldName + FMetaDataColumnName.FieldByName('column_name').AsString + ';';
          FMetaDataColumnName.Next();
        end;
        FMetaDataColumnName.Close();
        if FieldName.Length > 0 then
          SetLength(FieldName, FieldName.Length-1);
        Exit(True);
      end;
    end;
  except
    on E: EUniError do
    begin
      FLog.Log('GetPrimaryKeyFieldname()-2: ' + E.Message);
      Exit(False);
    end;
  end;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.GetDateTimeFormatted(const ProviderName: string; const ADate: TDateTime): string;
begin
  if SameText(ProviderName, 'Access') then
    Exit(FormatDateTime('"#"mm/dd/yyyy hh:nn:ss"#"', FLastDate, FFormatSettings))
  else
  if SameText(ProviderName, 'SQL Server') then
    Exit(FormatDateTime('yyyy-mm-dd hh:nn:ss', FLastDate, FFormatSettings));
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.GetTargetLastDate(): Boolean;
var
  QueryText: string;
  I: Integer;
begin
  QueryText := 'select max(' + AnsiQuotedStr(FTargetDateTimeFieldname, '"') + ') from ' + AnsiQuotedStr(FTargetTableName, '"');
  if Length(FAdditionalFieldsToFilter) > 0 then
  begin
    QueryText := QueryText + ' where ';
    for I := Low(FAdditionalValuesToFilter) to High(FAdditionalValuesToFilter) do
      QueryText := QueryText + AnsiQuotedStr(FAdditionalFieldsToFilter[I], '"') + ' = ' + QuotedStr(FAdditionalValuesToFilter[I]) + ' and ';
    SetLength(QueryText, QueryText.Length - 5); // remove additional " and "
  end;

  FTargetQuery.SQL.Text := QueryText;
  try
    FTargetQuery.Open();
    if FTargetQuery.Fields[0].IsNull then
      FLastDate := 0
    else
    begin
      FLastDate := FTargetQuery.Fields[0].AsDateTime;
    end;
    FTargetQuery.Close();
  except
    on E: EUniError do
    begin
      FLog.Log('GetTargetLastDate(): ' + E.Message);
      Exit(False);
    end;
  end;

  Result := True;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.PrepareSourceQuery(): Boolean;
var
  QueryText: string;
  I: Integer;
begin
  if not Assigned(FSourceQuery) then
  begin
    FLog.Log('PrepareSourceQuery(): SourceQuery is not assigned! Exiting.');
    Exit(False);
  end;

  QueryText := 'select * from ' + AnsiQuotedStr(FSourceTableName, '"');
  QueryText := QueryText + ' where ' + AnsiQuotedStr(FSourceDateTimeFieldname, '"') + ' > ' + GetDateTimeFormatted(FSourceDB.ProviderName, FLastDate);
  for I := Low(FAdditionalValuesToFilter) to High(FAdditionalValuesToFilter) do
    QueryText := QueryText + ' and ' + AnsiQuotedStr(FAdditionalFieldsToFilter[I], '"') + ' = ' + QuotedStr(FAdditionalValuesToFilter[I]);

  FSourceQuery.SQL.Text := QueryText;
  try
    FSourceQuery.Open();
  except
    on E: EUniError do
    begin
      FLog.Log('PrepareSourceQuery(): ' + E.Message);
      Exit(False);
    end;
  end;

  Result := True;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.PrepareTargetQuery(): Boolean;
var
  QueryText: string;
  I: Integer;
begin
  if not Assigned(FTargetQuery) then
  begin
    FLog.Log('PrepareTargetQuery(): TargetQuery is not assigned! Exiting.');
    Exit(False);
  end;

  QueryText := 'select * from ' + AnsiQuotedStr(FTargetTableName, '"');
  QueryText := QueryText + ' where ' + AnsiQuotedStr(FTargetDateTimeFieldname, '"') + ' > ' + QuotedStr(GetDateTimeFormatted(FTargetDB.ProviderName, FLastDate));
  for I := Low(FAdditionalValuesToFilter) to High(FAdditionalValuesToFilter) do
    QueryText := QueryText + ' and ' + AnsiQuotedStr(FAdditionalFieldsToFilter[I], '"') + ' = ' + QuotedStr(FAdditionalValuesToFilter[I]);

  FTargetQuery.SQL.Text := QueryText;
  try
    FTargetQuery.Open();
  except
    on E: EUniError do
    begin
      FLog.Log('PrepareTargetQuery(): ' + E.Message);
      Exit(False);
    end;
  end;

  Result := True;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.DeleteMissingFromTarget(): Boolean;
var
  Count: Integer;
begin
  if not Assigned(FSourceQuery) then
  begin
    FLog.Log('DeleteMissingFromTarget(): SourceQuery is not assigned! Exiting.');
    Exit(False);
  end;

  if not FSourceQuery.Active then
  begin
    FLog.Log('DeleteMissingFromTarget(): SourceQuery is not open! Exiting.');
    Exit(False);
  end;

  if not Assigned(FTargetQuery) then
  begin
    FLog.Log('DeleteMissingFromTarget(): TargetQuery is not assigned! Exiting.');
    Exit(False);
  end;

  if not FTargetQuery.Active then
  begin
    FLog.Log('DeleteMissingFromTarget(): TargetQuery is not open! Exiting.');
    Exit(False);
  end;

  Count := 0;
  FTargetQuery.Last();
  while not FTargetQuery.Bof do
  begin
    if not FSourceQuery.Locate(FSourceDateTimeFieldname, FTargetQuery.FieldByName(FTargetDateTimeFieldname).AsDateTime, []) then
    begin
      FLog.Log('DeleteMissingFromTarget(): Deleting record: ' + FormatDateTime('yyyy-mm-dd hh:nn:ss', FTargetQuery.FieldByName(FTargetDateTimeFieldname).AsDateTime) + '...');
      try
        FTargetQuery.Delete();
        Inc(Count);
      except
        on E: EUniError do
        begin
          FLog.Log('DeleteMissingFromTarget(): Failed to delete record. ERROR: ' + E.Message);
        end;
      end;
    end
    else
      FTargetQuery.Prior();
  end;
  FLog.Log('Deleted record count: ' + Count.ToString());

  Result := True;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.Execute();
label
  UpdateIt;

var
  Timing: TStopwatch;
  InitialRun: Boolean;
  Updated: Boolean;
  Mode: string;
begin
  FComInitialized := Succeeded(CoInitialize(nil));

  if FSourceDatabaseConnectString = EmptyStr then
  begin
    FLog.Log('ERROR: SourceDatabaseConnectString is empty.');
    Exit();
  end;

  if FTargetDatabaseConnectString = EmptyStr then
  begin
    FLog.Log('ERROR: TargetDatabaseConnectString is empty.');
    Exit();
  end;

  if FSourceTableName = EmptyStr then
  begin
    FLog.Log('ERROR: SourceTablename is empty.');
    Exit();
  end;

  if FTargetTableName = EmptyStr then
  begin
    FLog.Log('ERROR: TargetTablename is empty.');
    Exit();
  end;

  if Length(FAdditionalFieldsToFilter) <> Length(FAdditionalValuesToFilter) then
  begin
    FLog.Log('ERROR: AdditionalFieldsToFilter <> AdditionalValuesToFilter.');
    Exit();
  end;

  if FSourceDateTimeFieldname = EmptyStr then
  begin
    FLog.Log('ERROR: SourceDateTimeFieldname is empty.');
    Exit();
  end;

  if FTargetDateTimeFieldname = EmptyStr then
  begin
    FLog.Log('ERROR: TargetDateTimeFieldname is empty.');
    Exit();
  end;

  FActive := True;
  InitialRun := True; // used for initial (very first time after thread starts) trigger of event
  Timing := TStopwatch.StartNew();
  while not Terminated do
  begin
    if Terminated then Break;
    if ((Timing.ElapsedMilliseconds / 1000) >= FCheckFrequency) or (InitialRun) then
    begin
      InitialRun := False;
      Timing.Stop();
      FLog.Log('TMonitorDataChangeThread.Execute(): Starting batch operation...');
      FLog.IncIndent();
      try
        FLog.Log('Checking SourceDB connection...');
        if not FSourceDB.Connected then
          DoDBConnection(FSourceDB, FSourceDatabaseConnectString);

        FLog.Log('SourceDB connection: ' + BoolToStr(FSourceDB.Connected, True));
        if not FSourceDB.Connected then
          Continue;

        FLog.Log('Checking TargetDB connection...');
        if not FTargetDB.Connected then
          DoDBConnection(FTargetDB, FTargetDatabaseConnectString);

        FLog.Log('TargetDB connection: ' + BoolToStr(FTargetDB.Connected, True));
        if not FTargetDB.Connected then
          Continue;

{
        if FSourcePrimaryKeyFieldname = EmptyStr then
          if not GetPrimaryKeyFieldname(FSourceDB, FSourceTableName, FSourcePrimaryKeyFieldname) then
            Continue;

        if FTargetPrimaryKeyFieldname = EmptyStr then
          if not GetPrimaryKeyFieldname(FTargetDB, FTargetTableName, FTargetPrimaryKeyFieldname) then
            Continue;
}

        FLog.Log('Reading last record date on Target...');
        if not GetTargetLastDate() then
          Continue;
        FLog.Log('Insert check date: ' + DateToISO8601(FLastDate));

        FBatchMove.Mode := bmAppend;
        Updated := False;
        Mode := 'insert';
UpdateIt:
        FLog.Log(Format('Preparing source query for %s...', [Mode]));
        if not PrepareSourceQuery() then
          Continue;

        FLog.Log(Format('Preparing target query for %s...', [Mode]));
        if not PrepareTargetQuery() then
          Continue;

        FLog.Log(Format('Starting %s operation...', [Mode]));
        try
          FBatchMove.Execute();
          FLog.Log('Modified record count: ' + FBatchMove.ChangedCount.ToString());
          FLog.Log('Moved (Inserted & Updated) record count: ' + FBatchMove.MovedCount.ToString());
        except
          on E: EUniError do
          begin
            FLog.Log('TMonitorDataChangeThread.Execute(): ' + E.Message);
          end;
        end;

        if (FOldDaysToIncludeInCheck > 0) and not Updated then
        begin
          FLastDate := IncDay(FLastDate, -FOldDaysToIncludeInCheck);
          FLog.Log('Update check date: ' + DateToISO8601(FLastDate));
          FBatchMove.Mode := bmUpdate;
          Updated := True;
          Mode := 'update';
          goto UpdateIt;
        end;

        // Bu noktada artık Query eski kayıtlar ile açılmış oluyor
        if (FOldDaysToIncludeInCheck > 0) then
        begin
          Mode := 'identifying missing records';
          FLog.Log(Format('Preparing source query for %s...', [Mode]));
          if not PrepareSourceQuery() then
            Continue;

          FLog.Log(Format('Preparing target query for %s...', [Mode]));
          if not PrepareTargetQuery() then
            Continue;

          FLog.Log('Starting deleting missing records from target...');
          DeleteMissingFromTarget();
        end;
      finally
        FSourceDB.Close();
        FTargetDB.Close();
        Timing := TStopwatch.StartNew();
        FLog.DecIndent();
        FLog.Log('TMonitorDataChangeThread.Execute(): Finished batch operation...');
      end;
    end;
    TThread.Sleep(100);
  end;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.SetLogDir(const Value: string);
begin
  FLog.BaseDir := Value;
  FLogDir := Value;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.SetLogFilename(const Value: string);
begin
  FLog.LogFilename := Value;
  FLogFilename := Value;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.SetLoggingActive(const Value: Boolean);
begin
  FLog.LoggingActive := Value;
  FLoggingActive := Value;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.DoTerminate();
begin
  if FComInitialized then CoUninitialize();
  inherited;
end;

end.


glagherMerhaba,

Lokalda çalışan ve anlık olarak veri girişi olan bir veritabanını sunucu tarafında bulunan MS SQL Server 2008'e nasıl kopyalayabilirim. Lokalde çalışan veri tabanında bulunan tablo alanları,view ler,procedure ler ve trigerler 2 aya bir güncelleme yapılmakta. Yapılan bu güncellemeler ile birlikte ve içindeki veriler ile birlikte günlük olarak nasıl kopyalanabilir? Yardımlarınız için şimdiden teşekkür ederim.

Teşekkür ederim.
Cevapla


Konu ile Alakalı Benzer Konular
Konular Yazar Yorumlar Okunma Son Yorum
  server programinda hani veritabani dogru olur. sadikacar60 4 674 30-01-2024, Saat: 21:06
Son Yorum: sadikacar60
  COZULDU veritabani prg yerine ne kullanabilirim. sadikacar60 8 1.258 29-01-2024, Saat: 18:41
Son Yorum: sadikacar60
  Veritabanı FireDAC, Zeos, UniDAC kıyaslaması nguzeller 0 485 08-07-2023, Saat: 00:28
Son Yorum: nguzeller
  Veritabanı - Bul Değiştir Frrst 3 1.005 17-01-2023, Saat: 01:27
Son Yorum: Abdullah ILGAZ
  Firebird Uzak Veritabanı Bağlantısı MEDCEZİR 4 2.305 11-09-2021, Saat: 08:42
Son Yorum: MEDCEZİR



Konuyu Okuyanlar: 1 Ziyaretçi