Implementing DC to DR Site incremental Database syncing using HangFire and Dot Net Core

In many scenarios, we need incremental Database sync between DC the DR server. Recently I needed this type of need, so I found a way for my project. I use Hang fire Backgrounds Job to do this.

1st Step: Include DR Database Connection in appsettings.json

  "ConnectionStrings": {
    "DatabaseConnection": "Data Source="";Initial Catalog=SalesPulse;Persist Security Info=True;User ID="";Password=;MultipleActiveResultSets=True",
    "DWDatabaseConnection": "Data Source="";Initial Catalog=SalesPulse;Persist Security Info=True;User ID="";Password=;MultipleActiveResultSets=True"
  },

2nd Step: Make another Database Context for the DR Database

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using Microsoft.EntityFrameworkCore;
using SalesPulse.Domain;
using SalesPulse.Domain.Models;

namespace SalesPulse.DAL
{
    public class DWDatabaseContext : DbContext
    {
        public DWDatabaseContext(DbContextOptions<DWDatabaseContext> options) : base(options)
        {
        }

        // Define API entity sets (DbSets) here



        public DbSet<SmsGatewayVendor> SmsGatewayVendor { get; set; }
        public DbSet<Department> Department { get; set; }
        public DbSet<FieldForce> FieldForce { get; set; }
        public DbSet<FieldForceType> FieldForceType { get; set; }
        public DbSet<Team> Team { get; set; }
        public DbSet<MobileAppUser> MobileAppUser { get; set; }
        public DbSet<FieldForcePriviledge> FieldForcePriviledge { get; set; }
        public DbSet<Area> Area { get; set; }
        public DbSet<District> District { get; set; }
        public DbSet<Division> Division { get; set; }
        public DbSet<FieldForceGroup> FieldForceGroup { get; set; }
        public DbSet<MobileAppUserSession> MobileAppUserSession { get; set; }
        public DbSet<Region> Region { get; set; }
        public DbSet<Zone> Zone { get; set; }
        public DbSet<Thana> Thana { get; set; }
        public DbSet<MobileDeviceHistory> MobileDeviceHistory { get; set; }
        public DbSet<SapOrder> SapOrder { get; set; }
        public DbSet<ProjectConfigs> ProjectConfigs { get; set; }
        public DbSet<Brand> Brand { get; set; }
        public DbSet<Generic> Generic { get; set; }
        public DbSet<Product> Product { get; set; }
        public DbSet<Market> Market { get; set; }
        public DbSet<Depot> Depot { get; set; }
        public DbSet<FieldForceDepotMap> FieldForceDepotMap { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<FieldForceDepotMap>()
                .HasKey(ffm => new { ffm.FieldForceId, ffm.DepotId });

            modelBuilder.Entity<FieldForceDepotMap>()
                .HasOne(ffm => ffm.FieldForce)
                .WithMany(ff => ff.FieldForceDepotMaps)
                .HasForeignKey(ffm => ffm.FieldForceId);

            modelBuilder.Entity<FieldForceDepotMap>()
                .HasOne(ffm => ffm.Depot)
                .WithMany(d => d.FieldForceDepotMaps)
                .HasForeignKey(ffm => ffm.DepotId);
        }




        public virtual void Commit()
        {
            try
            {
                base.SaveChanges();
            }
            catch (DbUpdateException ex)
            {
                var validationErrors = ex.Entries
                    .Select(entry =>
                        entry.State == EntityState.Added || entry.State == EntityState.Modified ? entry.Entity : null)
                    .OfType<IValidatableObject>()
                    .SelectMany(entity => entity.Validate(null))
                    .Where(result => result != ValidationResult.Success) // Check for validation failures
                    .SelectMany(result => result.MemberNames.Select(member => $"{result.ErrorMessage} - {member}"))
                    .ToList();

                if (validationErrors.Any())
                {
                    var fullErrorMessage = string.Join("; ", validationErrors);
                    var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
                    throw new DbUpdateException(exceptionMessage, ex);
                }
                else
                {
                    // Rethrow the original DbUpdateException if there are no validation errors
                    throw;
                }
            }
        }
    }
}

3rd Step: Register Database Context in startup class

 services.AddDbContext<DWDatabaseContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("DWDatabaseConnection")));

4th Step : In Backgroundjob service class make a function for Incremental backup.

 public void PerformIncrementalBackup()
        {
            try
            {
                var lastBackupTime = _prodDb.IncrementalBackUpInfo
                    .OrderByDescending(i => i.IncrementalBackUpTime)
                    .Select(i => i.IncrementalBackUpTime)
                    .FirstOrDefault();

                var updatedTables = new List<string>();

                var mobileAppSessionBackUp = GetAppUserSessionsLastBackUp(lastBackupTime).ToList();
                if (mobileAppSessionBackUp.Any())
                {
                    _dwDb.MobileAppUserSession.AddRange(mobileAppSessionBackUp);
                    _dwDb.SaveChanges();
                    updatedTables.Add("MobileAppUserSession");
                }

                if (updatedTables.Any())
                {
                    var emailBody = GetEmailNotificationBody(updatedTables);
                    var subject = "Incremental Backup Notification";
                    var receiverEmails = new List<string> { "hasan.mahedi@hpl.com.bd" }; 

                    // Send email notification
                    _smsService.SendEmailNotification(receiverEmails, subject, emailBody);
                }
                SaveDbBackUpInfo();
            }
            catch (Exception ex)
            {
                throw;
            }
        }

// email body builder
private string GetEmailNotificationBody(List<string> updatedTables)
        {
            var updatedTablesString = string.Join(", ", updatedTables);
            return $"Dear Sir, Incremental backup in DR Server(192.168.8.140) has successfully updated. Updated Table(s): {updatedTablesString}.";
        }


// get last changeable data

private IQueryable<MobileAppUserSession> GetAppUserSessionsLastBackUp(DateTime lastBackUpTime)
        {
            var changes = _mobileAppSessionRepository.GetAllQueryable()
                .Where(record => record.ServerDate > lastBackUpTime);
            return changes;
        }

// save backup time in dc server.
 private void SaveDbBackUpInfo()
        {
            var dbBackUpInfo = new IncrementalBackUpInfo();
            dbBackUpInfo.IncrementalBackUpTime = DateTime.Now;
            _prodDb.IncrementalBackUpInfo.Add(dbBackUpInfo);
            _prodDb.SaveChanges();
        }

Lastly, Register PerformIncrementalBackUp in startup class

RecurringJob.AddOrUpdate<BackGroundJobService>("DW Backup", service => service.PerformIncrementalBackup(), Cron.Minutely);

That’s how I come up witha solution. In this article, I just show my one db table. In production, i can do it with my all database tables.