1

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

3
  • Do you have a central database, and how long can it be allowed to be unreachable? – ErikEJ May 16 '15 at 17:11
  • @ErikEJ, just updated my question. Thank you – Lee May 17 '15 at 1:26
  • SQL Server Express only supports Merge/Transactional/Snapshot replication as a subscriber, i.e. a central (Standard Edition, licensed) SQL Server would be required, see: msdn.microsoft.com/library/cc645993.aspx#Replication – Matt May 18 '15 at 15:11
1

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!

3
  • thank you. I updated my question. Everything is correct except that we don't have a central server. If we will assign a central server, what if that server goes down, what will happen to the client, say POS A(ordering) and POS B(Cashier)? I assume we can user Merge and Server - Server? Please enlighten me. Thank you – Lee May 17 '15 at 1:41
  • That might go beyond the scope of this comment as a whole series of new questions will need to be answered. But, I would surely do research on disaster recovery and high availability with SQL Server and the different editions you plan to utilize to recover at the hardware level and at the database level in case of fire. There are plenty of options, some can be pretty extensive depending on your business needs to keep that centralized server always available. – Glen Swan May 17 '15 at 21:00
  • @Lee: Having a central server may be far easier than not. Without that you have to make sure all the clients can see each other (and potentially they need to discover each other too) to push updates around and have to manage all the network security around that. Also having a main central store of truth makes it easier for a client to be sure that it is getting uptodate data. A fully distributed system can be far more difficult to arrange reliably and efficiently. – David Spillett Feb 20 '18 at 16:17
0

If you want to keep your Main Server (Central Server) up, protect your Data from a disaster and avoid downtime due to any disaster, you should make a replica of this Server through "SQL Server 2012 Failover Clustering" technique.

It will continuously make a copy of your required Database(s) on the secondary Server. If Main Server gets down, the Secondary Server will take the charge immediately and become the Primary Server without any downtime. All COMMITTED Transactions on the Main Server will be replicated on the Secondary Server instantly. You may also configure additional Secondary Servers (Replicas) at different locations, if required.

Following points need to be considered carefully.

  • SQL SERVER 2012 ENTERPRISE Edition should be installed on both PRIMARY (CENTRAL) & SECONDARY Servers

  • SQL SERVER AlwaysON feature should be configured on both Servers. It will configure required Database(s) and Secondary Server (Replica) for data synchronization with the Primary Server

  • A Windows Server "Failover Cluster" should be available (needs to be configured, if not available) before configuring AlwaysON feature (if it is already configured, then while configuring AlwaysON, it will be Automatically picked)

  • Once, AlwaysON feature is configure, you are free from worries. Just need to monitor the AlwaysON Dashboard for the status of all the Nodes and Database(s)

  • When both Servers are up and running, the Primary Server will be used for Transaction Processing, while Secondary Server for reporting purpose only, it will NOT be used for Transaction Processing. However, reports may also be generated from the Primary Server, but it is not recommended to avoid load on this Server, in order to increase the performance

  • If Main Server gets down, its Database(s) will not get down, and All COMMITTED Transactions on the Main / Primary Server will be available on the Secondary Server. Users just need to connect with the Secondary Server to start working

  • Both Primary and Secondary Servers should be a part of Windows DOMAIN Environment and be authorized by the Domain Controller Server

Kindly follow the below links for a better understanding.

SQL SERVER 2012 AlwaysON Setup

Features and Benefits of AlwaysON availability groups

1
  • OP is already limited by the fact that they only have SQL Server Express at the various Point-Of-Sale (POS) locations. Suggesting they use Availability Groups which requires an Enterprise Edition is a bit of an overkill. – John K. N. Feb 20 '18 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.