I'm new to MS SQL Server replication and I have to design a replication solution to the following problem:
We have implemented an ERP system for the retail industry with POS and back–office (invoices etc.) functionality.
The system has to be installed in several different stores and I have to setup replication between them.
I am considering to do the following:
1. Install a main server in Store1 and create a publication.
2. Have a backup server in Store1 which subscribes to the publication and synchronizes every 15 minutes.
3. Have a second server in Store2 which subscribes to Store1 and synchronizes every 1 hour.
4. Republish everything on Store2 and have a backup server in Store2 which subscribes to Store2 and synchronizes every 15 minutes.
There are two stores currently.
5. Later on we need to make all POS terminals (8) of them in each Store1 and Store2 subscribe to the publications in Store1 and Store2 respectively so that they can work in offline mode.
I am wondering if my solution seems good and if anybody can suggest anything better.
Currently I have the Store2 server’s subscriptions as push i.e. Store1 pushes the changes and the distributor
is running on the same machine. Also the server is used for everything, including reports.
I am researching an option for making a separate server for the distributor and wandering where to setup
pull and where to set up push subscriptions.
Any suggestion on possible solutions will be appreciated.