0

I am working on a student project and I need some guidance on what solution I can use.

  • The goal of the project is to create a dummy web page that would send dummy data to 2 databases(or read from one of them).
  • If at any point one of them is unavailable it should keep sending data to the one that still works.
  • When the unavailable database returns to function it should check with the one that was still working, synchronize all changes made to data and continue on. It should work both ways.
  • Both DB1 and DB2 can shutdown but when they return to function they should synchronize with the one that was working.

Professor recommended MS SQL server 2019 Developer, so far I managed to make database replication so any change made to DB1 is replicated on DB2, but I can only send data to DB1 because it doesn't work the other way. I followed this How to Configure MS SQL Server Replication: Complete Walkthrough tutorial. Basically it is transactional replication between 2 SQL Server dbs that run on 2 VMs.

I can't figure out the way to do this so I would appreciate if anyone has any suggestions or tutorials that could help me. One thing that I found and I think would work is MS SQL peer to peer replication, but it is only available for Enterprise edition.

Improve this question
2
  • 1
    If you're using SQL Server then give Merge Replication as try as it is bi-directional and is available in all editions, the publisher needs to be Standard edition or higher. Also, note that Peer to Peer Replication is also available in Developer Edition. Commented Aug 25, 2020 at 17:53
  • 1
    Developer edition includes all of the functionality of Enterprise edition, but is licensed for use as a development and test system. Commented Aug 25, 2020 at 18:00

1 Answer 1

Reset to default
0

Check out Basic Availability Groups. These are available in Standard Edition (and Developer) and offer exactly the functionality you're after.

You have two replicas, a Primary and Secondary. The client application connects to a shared listener name, and read and Write workload between the client application and database occurs on the primary replica through this listener. Data changes are synchronised to the secondary replica behind the scenes.

When the primary replica fails, if you have configured automatic failover then the availability group moves to the secondary replica, and the read/write workload begins working off the secondary replica. As this still occurs through the listener name, no application change is required.

After failover, the secondary replica becomes the new primary and once the old primary is online again, it becomes the new secondary. Data synchronisation resumes, but in the other direction to ensure that you can failover back to the original primary at some point if needed.

Check out this Docs article for an overview of Availability Groups. Note that Basic AGs function just like normal AGs but for Standard Edition only, so they have some limitations.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.