SQL Server – Using Replication for Point-of-Sale (POS)

replicationsql serversql-server-express

We are looking for a solution that will synchronize the databases of our POS.

The scenario says we have 3 POS with SQL Server Express installed on each machine.

We want these databases to be in-sync at all times. Let's say POS A goes down, then it will resync as soon as it is up. Also, while POS A is down POS B and C should still work and continue to sync their data.

Is SQL Server replication the right solution? If yes, what type of replication?

Updated

  • No central database
  • 3/4 nodes/POS are down, remaining POS will still work
  • Databases will be restored if POS is online
  • Pretty much we are think like MongoDb replica set
  • Database should be updated as much as possible

enter image description here

Best Answer

You have 3 types of replication:

  • Snapshot
  • Transactional
  • Merge

Reference Link - Selecting the Appropriate Type of Replication

Per Microsoft, these are easily broken down into two broad categories:

  • Server to Server
  • Server to Client

In the scenario of Point of Sale (POS). You are recommended to use Merge Replication due to it's Server to Client category and scenario.

Reference Link - Merge Replication

Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

enter image description here

Reference Link - Consumer Point of Sale (POS) Applications

POS applications typically have the following characteristics, which an appropriate replication solution must address:

  • Most data is entered and updated at the remote sites.
  • Remote users must be able to make updates independently, without requiring a connection to the central site.
  • Data updated at a remote site is not updated at any other sites; therefore conflicts typically do not occur.
  • Some data should only be updated at the central site; for example, data in product description tables.
  • Users synchronize data at scheduled times (such as the end of the business day).
  • The application must control how long a remote site can remain unsynchronized.
  • Some tables require filtering so that each store receives different data for one or more tables. For example, each store receives information only for products it stocks.
  • The application might require custom business logic to be executed when data is synchronized.
  • The application might require that data be synchronized over the Internet rather than through a dedicated connection.

enter image description here

Hope that helps!

Related Question