Problems with databases synchronization #871

MapInetum · 2022-11-15T11:27:57Z

MapInetum
Nov 15, 2022

Hi for all.
I will try to explain my case and put you in context with the problems I have when synchronizing my databases with Dotmim.Sync.

First, my infrastrucutre is a server database and multiple client databases for sync, all of thems in MS SQL Server.
I want use SQL Server ChangeTracking for do all syncs.
The data sync is Bidirectional and the first sync are with a lot of data (millinos registers).

I'm using v0.9.7 of framework with this SyncOptions:

BatchSize = 8192
BatchDirectory = Path.Combine(assemblyDirectoryName, "Temp"),
SnapshotsDirectory = Path.Combine(assemblyDirectoryName, "Snapshot"),
CleanFolder = true,
CleanMetadatas = true,
ConflictResolutionPolicy = ConflictResolutionPolicy.ClientWins,
DisableConstraintsOnApplyChanges = false,
ScopeInfoTableName = "SyncInfo",
DbCommandTimeout = 3600

My big problem is in the first sync. I think that the tables approvisioning is ok but when the sync is fisihed, the framework retrieve me this error:

2022-11-15 10:42:00.726 +00:00 [INF] StatesSyncJob - 0.00 %:	MAPRADO.Configuration:	[MAPRADO.Configuration] Session Begins. Id:0ff8f125-5f97-420d-9614-f9d34fa3d7fc. Scope name:EGYSO3-StatesSyncJob.
2022-11-15 10:42:01.878 +00:00 [INF] StatesSyncJob - 0.00 %:	MAPRADO.Configuration:	[MAPRADO.Configuration] Overriding operation from server. Operation: Normal.
2022-11-15 10:42:02.017 +00:00 [INF] StatesSyncJob - 0.00 %:	MAPRADO.Configuration:	Provisioned 2 Tables. Provision:Table, TrackingTable, StoredProcedures, Triggers.
2022-11-15 11:13:43.525 +00:00 [INF] StatesSyncJob - 55.00 %:	MAPRADO.Configuration:	[StateHistoryOnline] [Total] Upserts:8746996. Deletes:0. Total:8746996.
2022-11-15 11:13:43.526 +00:00 [INF] StatesSyncJob - 75.00 %:	MAPRADO.Configuration:	[Total] Upserts:8746996. Deletes:0. Total:8746996. [C:\MAPRADO\Services\MAPRADO.SyncServer\Temp\MAPRADO.Configuration_GETCHANGES_2022_11_15_02cxwhdyzpq3q]
2022-11-15 11:14:19.495 +00:00 [INF] StatesSyncJob - 100.00 %:	MAPRADO.Configuration:	[MAPRADO.Configuration] Session Ends. Id:0ff8f125-5f97-420d-9614-f9d34fa3d7fc. Scope name:EGYSO3-StatesSyncJob.
2022-11-15 11:14:19.503 +00:00 [ERR] StatesSyncJob - Error: This SqlTransaction has completed; it is no longer usable.

Can you give me any tips for complete these sync. Thanks!

Mimetis · 2022-11-15T11:37:48Z

Mimetis
Nov 15, 2022
Maintainer

can you try with MultipleActiveResultSets=False in your connection string (if you are using SQL Server)

Otherwise, are you able to reproduce the issue, easily, and share a sample repro?

12 replies

Mimetis Nov 15, 2022
Maintainer

without any data, will be hard to reproduce ;)

MapInetum Nov 15, 2022
Author

INSERTsToExample.zip

You can repeat the insert of StateHistoryOnline register N times, primary key GUID changing.

You must be clear about one thing, this only happens to me when I try to synchronize a large number of records (millions).

Thanks!! ;)

Mimetis Nov 16, 2022
Maintainer

Your script doesn't work, it does not insert millions of records, it does not create the client and server database correctly ...
I can't spend too much on each problem and issues, If I need to understand and modify all the scripts and code to be able to just reproduce an error.
Please do an effort to make a reproduction sample explicit and ready to execute.

For your case, I've edited your script as is (something you should have done):

USE [master]
GO
-- Server database
if (exists (select * from sys.databases where name = 'MAPRADO.Configuration'))
Begin
	ALTER DATABASE [MAPRADO.Configuration] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [MAPRADO.Configuration]
End
Create database [MAPRADO.Configuration]
GO
-- Client database
if (exists (select * from sys.databases where name = 'MAPRADO.Client'))
Begin
	ALTER DATABASE [MAPRADO.Client] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [MAPRADO.Client]
End
Create database [MAPRADO.Client]
Go
USE [MAPRADO.Client]
Go
ALTER DATABASE [MAPRADO.Client] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 14 DAYS, AUTO_CLEANUP = ON)

Go
USE [MAPRADO.Configuration]
GO
ALTER DATABASE [MAPRADO.Configuration] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 14 DAYS, AUTO_CLEANUP = ON)
Go
/****** Object:  Table [dbo].[Machines]    Script Date: 15/11/2022 15:19:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Machines](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Alias] [nvarchar](50) NULL,
	[Description] [nvarchar](500) NULL,
	[MachineTypeId] [uniqueidentifier] NOT NULL,
	[MachineSubtypeId] [uniqueidentifier] NULL,
	[FirmwareId] [uniqueidentifier] NULL,
	[InstallationId] [uniqueidentifier] NOT NULL,
	[MachineGroupId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Machines] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[StateHistoryOnline]    Script Date: 15/11/2022 15:19:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StateHistoryOnline](
	[Id] [uniqueidentifier] NOT NULL,
	[IsOnline] [bit] NOT NULL,
	[TimeStampUtcOn] [datetime2](7) NOT NULL,
	[TimeStampUtcOff] [datetime2](7) NULL,
	[StateId] [uniqueidentifier] NOT NULL,
	[StateValueId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_StateHistoryOnline] PRIMARY KEY CLUSTERED 
(
	[Id] ASC,
	[IsOnline] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[States]    Script Date: 15/11/2022 15:19:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[States](
	[Id] [uniqueidentifier] NOT NULL,
	[Mask] [int] NOT NULL,
	[Code] [int] NULL,
	[Description] [nvarchar](500) NULL,
	[FirmwareId] [uniqueidentifier] NOT NULL,
	[TagId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Tags]    Script Date: 15/11/2022 15:19:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tags](
	[Id] [uniqueidentifier] NOT NULL,
	[Expression] [nvarchar](max) NOT NULL,
	[Archive] [bit] NOT NULL,
	[Aggregate] [bit] NOT NULL,
	[IsFormula] [bit] NOT NULL,
	[IsEvent] [bit] NOT NULL,
	[IsWritable] [bit] NOT NULL,
	[MaxValue] [float] NULL,
	[MinValue] [float] NULL,
	[ValidationExpression] [nvarchar](50) NULL,
	[ScaleMultiplier] [float] NOT NULL,
	[TagTypeId] [uniqueidentifier] NOT NULL,
	[HistorizationId] [uniqueidentifier] NULL,
	[UnitMeasureId] [uniqueidentifier] NULL,
	[VariableTypeId] [uniqueidentifier] NOT NULL,
	[ServiceId] [uniqueidentifier] NOT NULL,
	[MachineId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Machines] ADD  CONSTRAINT [DF_Machines_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO
ALTER TABLE [dbo].[StateHistoryOnline] ADD  CONSTRAINT [DF_StateHistoryOnline_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO
ALTER TABLE [dbo].[StateHistoryOnline] ADD  CONSTRAINT [DF_StateHistoryOnline_IsOnline]  DEFAULT ((1)) FOR [IsOnline]
GO
ALTER TABLE [dbo].[States] ADD  CONSTRAINT [DF_States_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO
ALTER TABLE [dbo].[States] ADD  CONSTRAINT [DF_States_Mask]  DEFAULT ((0)) FOR [Mask]
GO
ALTER TABLE [dbo].[Tags] ADD  CONSTRAINT [DF_Tags_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO
ALTER TABLE [dbo].[Tags] ADD  CONSTRAINT [DF_Tags_Archive]  DEFAULT ((0)) FOR [Archive]
GO
ALTER TABLE [dbo].[Tags] ADD  CONSTRAINT [DF_Tags_Aggregate]  DEFAULT ((0)) FOR [Aggregate]
GO
ALTER TABLE [dbo].[Tags] ADD  CONSTRAINT [DF_Tags_IsFormula]  DEFAULT ((0)) FOR [IsFormula]
GO
ALTER TABLE [dbo].[Tags] ADD  CONSTRAINT [DF_Tags_IsEvent]  DEFAULT ((0)) FOR [IsEvent]
GO
ALTER TABLE [dbo].[Tags] ADD  CONSTRAINT [DF_Tags_IsWritable]  DEFAULT ((0)) FOR [IsWritable]
GO
ALTER TABLE [dbo].[Tags] ADD  CONSTRAINT [DF_Tags_ScaleMultiplier]  DEFAULT ((1)) FOR [ScaleMultiplier]
GO
ALTER TABLE [dbo].[StateHistoryOnline]  WITH CHECK ADD  CONSTRAINT [FK_StateHistoryOnline_States] FOREIGN KEY([StateId])
REFERENCES [dbo].[States] ([Id])
GO
ALTER TABLE [dbo].[StateHistoryOnline] CHECK CONSTRAINT [FK_StateHistoryOnline_States]
GO
ALTER TABLE [dbo].[Tags]  WITH CHECK ADD  CONSTRAINT [FK_Tags_Machines] FOREIGN KEY([MachineId])
REFERENCES [dbo].[Machines] ([Id])
GO
ALTER TABLE [dbo].[Tags] CHECK CONSTRAINT [FK_Tags_Machines]
GO
ALTER TABLE [dbo].[Machines]  WITH CHECK ADD  CONSTRAINT [MachineAliasNotBlankSpace] CHECK  ((NOT [Alias] like '% %'))
GO
ALTER TABLE [dbo].[Machines] CHECK CONSTRAINT [MachineAliasNotBlankSpace]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador único de la máquina' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nombre de la máquina' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Descripción de la máquina' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'Description'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador del tipo de máquina' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'MachineTypeId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador del firmware actual de la máquina' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'FirmwareId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador de la instalación a la que pertenece la máquina' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'InstallationId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador del grupo de máquinas' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'MachineGroupId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador único de la tag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nombre de la variable o expresión calculada' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'Expression'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag para activar el archivado de datos segundales' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'Archive'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag para activar el archivado de datos agregados' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'Aggregate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag para indicar si la tag es una expresión calculada' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'IsFormula'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag para indicar si la tag genera eventos' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'IsEvent'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Umbral máximo para los valores de la tag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'MaxValue'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Umbral mínimo para los valores de la tag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'MinValue'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Factor de escalado para los valores de la tag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'ScaleMultiplier'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador del tipo de tag (alarma, estado, valor, etc.)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'TagTypeId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador de la configuración de historización para la tag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'HistorizationId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador de las unidades de medida de la tag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'UnitMeasureId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador del tipo de variable' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'VariableTypeId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador del servicio asociado a la tag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'ServiceId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador de la máquina asociada a la tag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tags', @level2type=N'COLUMN',@level2name=N'MachineId'
GO


Declare @cpt int = 0;
Declare @maxCpt int = 5000000
while (@cpt < @maxCpt)
Begin
	Declare @machineId uniqueidentifier = newid();
	Declare @tagId uniqueidentifier = newid();
	Declare @stateId uniqueidentifier = newid();
	Declare @stateHistoryOnlineId uniqueidentifier = newid();

	INSERT INTO Machines (Id, Name, Alias, Description, MachineTypeId, MachineSubtypeId, FirmwareId, InstallationId, MachineGroupId) 
	VALUES (@machineId, N'MCH-' + convert(varchar(8), @cpt), N'MCH-' +  + convert(varchar(8), @cpt), N'Machine ' +  + convert(varchar(8), @cpt),
			N'2B39B56B-5509-E211-BD17-005056C00008', N'EC5B98B2-34E5-E511-B3EB-005056BE6E2F', N'3A2D58BC-5309-E211-BD17-005056C00008', N'DF592635-6483-E811-A503-005056BE6E2F', N'B3D9A9D4-E5D2-E811-A758-005056BE6E2F');

	INSERT INTO Tags (Id, Expression, Archive, Aggregate, IsFormula, IsEvent, IsWritable, [MaxValue], [MinValue], ValidationExpression, ScaleMultiplier, TagTypeId, HistorizationId, UnitMeasureId, VariableTypeId, ServiceId, MachineId) 
	VALUES(@tagId, N'MCH-1_Expression', 1, 0, 0, 0, 1, NULL, NULL, NULL, 1, N'9B6412D2-5509-E211-BD17-005056C00008', N'F7422DA5-4166-E211-8E58-005056BE6E2F', NULL, N'7A3E15DA-C578-E611-8E9D-005056BE6E2F', N'98249E13-07FC-EB11-A564-204EF66F4E06', @machineId);

	INSERT INTO States (Id, Mask, Code, Description, FirmwareId, TagId) 
	VALUES(@stateId, 1, 8000, N'State', N'3A2D58BC-5309-E211-BD17-005056C00008', @tagId);
	
	INSERT INTO StateHistoryOnline (Id, IsOnline, TimeStampUtcOn, TimeStampUtcOff, StateId, StateValueId) 
	VALUES(@stateHistoryOnlineId, 1, '2022-07-11 13:25:09.210', '2022-07-11 13:25:34.194', @stateId, N'40C558AD-269E-E911-BB81-E4A7A0B6B3DF');

	set @cpt = @cpt +1;
End

Same for the sync code.
I've just edited the code to remove the ugly .Result call on an async method (plz never do that again... anywhere)

class Program
    {
        private const string INSTALLATION_ALIAS = "EGYSO3";
        private const string INSTALLATION_ID = "DF592635-6483-E811-A503-005056BE6E2F";

        private const string SYNCSCOPE_JOB = "StatesSyncJob";

        private const string SERVER_CONNSTRING = "Data Source=(localdb)\\mssqllocaldb; Initial Catalog=MAPRADO.Configuration;Integrated Security=true;MultipleActiveResultSets=True;";
        private const string CLIENT_CONNSTRING = "Data Source=(localdb)\\mssqllocaldb; Initial Catalog=MAPRADO.Client;Integrated Security=true;MultipleActiveResultSets=True;";

        static async Task Main(string[] args)
        {
            await ExecuteDbSync();
        }

        public static async Task ExecuteDbSync()
        {
            var serverProvider = new SqlSyncChangeTrackingProvider(SERVER_CONNSTRING);
            var clientProvider = new SqlSyncChangeTrackingProvider(CLIENT_CONNSTRING);

            // Tables involved in the sync process:
            var tables = new string[]
            {
                SyncServerConstants.Machines,
                SyncServerConstants.StateHistoryOnline
            };

            var syncSetup = GetSyncSetup(tables);

            var syncOptions = GetSyncOptions(ConflictResolutionPolicy.ClientWins);

            // Filters
            var shoFilter = new SetupFilter(SyncServerConstants.StateHistoryOnline);

            shoFilter.AddJoin(Join.Inner, SyncServerConstants.States)
                .On(SyncServerConstants.States,
                SyncServerConstants.Id,
                SyncServerConstants.StateHistoryOnline,
                SyncServerConstants.StateId);

            shoFilter.AddJoin(Join.Inner, SyncServerConstants.Tags)
                .On(SyncServerConstants.Tags,
                SyncServerConstants.Id,
                SyncServerConstants.States,
                SyncServerConstants.TagId);

            shoFilter.AddJoin(Join.Inner, SyncServerConstants.Machines)
                .On(SyncServerConstants.Machines,
                SyncServerConstants.Id,
                SyncServerConstants.Tags,
                SyncServerConstants.MachineId);

            shoFilter.AddParameter(SyncServerConstants.InstallationId, System.Data.DbType.Guid);

            shoFilter.AddWhere(
                SyncServerConstants.InstallationId,
                SyncServerConstants.Machines,
                SyncServerConstants.InstallationId);

            syncSetup.Filters.Add(shoFilter);

            syncSetup.Tables[SyncServerConstants.Machines].SyncDirection = SyncDirection.None;
            syncSetup.Tables[SyncServerConstants.StateHistoryOnline].SyncDirection = SyncDirection.Bidirectional;

            // Sync agent
            var agent = new SyncAgent(
                clientProvider,
                serverProvider,
                syncOptions);

            // Set value to parameters
            var syncParameters = new SyncParameters()
            {
                new SyncParameter()
                {
                    Name = SyncServerConstants.InstallationId,
                    Value = INSTALLATION_ID
                }
            };

            var progress = new SynchronousProgress<ProgressArgs>(args =>
                Console.WriteLine($"{SYNCSCOPE_JOB} - " +
                    $"{args.ProgressPercentage:p}:\t" +
                    $"{args.Source}:\t" +
                    $"{args.Message}"));

            var scopeName = $"{INSTALLATION_ALIAS}-{SYNCSCOPE_JOB}";

            try
            {
                var syncResult = await agent.SynchronizeAsync(scopeName, syncSetup, SyncType.Normal, syncParameters, default, progress);

                Console.WriteLine($"{SYNCSCOPE_JOB} - {syncResult}");
            }
            catch (TaskCanceledException)
            {
                Console.WriteLine($"{SYNCSCOPE_JOB} - Synchronization task was cancelled.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"{SYNCSCOPE_JOB} - Error: {ex}");
            }
        }

        private static SyncSetup GetSyncSetup(string[] tables)
        {
            return new SyncSetup(tables)
            {
                StoredProceduresSuffix = SyncServerConstants.sync,
                TrackingTablesSuffix = SyncServerConstants.sync,
                TriggersSuffix = SyncServerConstants.sync
            };
        }

        private static SyncOptions GetSyncOptions(ConflictResolutionPolicy conflictResolutionPolicy, bool disableConstraintsOnApplyChanges = false)
        {
            string assemblyDirectoryName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);

            return new SyncOptions()
            {
                BatchSize = 8192,
                BatchDirectory = Path.Combine(assemblyDirectoryName, SyncServerConstants.TEMP_DIRECTORY),
                SnapshotsDirectory = Path.Combine(assemblyDirectoryName, SyncServerConstants.SNAPSHOTS_DIRECTORY),
                CleanFolder = true,
                CleanMetadatas = true,
                ConflictResolutionPolicy = conflictResolutionPolicy,
                DisableConstraintsOnApplyChanges = disableConstraintsOnApplyChanges,
                ScopeInfoTableName = SyncServerConstants.SyncInfo,
                DbCommandTimeout = 3600
            };
        }
    }

public static class SyncServerConstants
{
    public const string StateHistoryOnline = nameof(StateHistoryOnline);
    public const string InstallationId = nameof(InstallationId);
    public const string Machines = nameof(Machines);
    public const string MachineId = nameof(MachineId);
    public const string Tags = nameof(Tags);
    public const string TagId = nameof(TagId);
    public const string States = nameof(States);
    public const string StateId = nameof(StateId);
    public const string Id = nameof(Id);

    public const string TEMP_DIRECTORY = "Temp";
    public const string SNAPSHOTS_DIRECTORY = "Snapshots";

    public const string sync = nameof(sync);
    public const string SyncInfo = "__SyncInfo";
}

First test I've done with a little bit less than 1M rows works as expected:

With 3M rows:

MapInetum Nov 16, 2022
Author

Thanks for this work @Mimetis and sorry...
I run an example with your script and code and sure enough everything ran and synced up fine.
I'm preparing another case so you can see my problems.

Mimetis Nov 16, 2022
Maintainer

Please, I'm not a kind of consultant :)
Seems the problem is not coming from DMS, I suggest you to make your own research :)

Uh oh!

Problems with databases synchronization #871

Uh oh!

MapInetum Nov 15, 2022

Replies: 1 comment · 12 replies

Uh oh!

Uh oh!

Mimetis Nov 15, 2022 Maintainer

Uh oh!

Mimetis Nov 15, 2022 Maintainer

Uh oh!

Uh oh!

MapInetum Nov 15, 2022 Author

Uh oh!

Uh oh!

Mimetis Nov 16, 2022 Maintainer

Uh oh!

MapInetum Nov 16, 2022 Author

Uh oh!

Mimetis Nov 16, 2022 Maintainer

MapInetum
Nov 15, 2022

Replies: 1 comment 12 replies

Mimetis
Nov 15, 2022
Maintainer

Mimetis Nov 15, 2022
Maintainer

MapInetum Nov 15, 2022
Author

Mimetis Nov 16, 2022
Maintainer

MapInetum Nov 16, 2022
Author

Mimetis Nov 16, 2022
Maintainer