What is the best solution for POS application?

Question

I'm current on POS project. User require this application can work both online and offline which mean they need local database. I decide to use SQL Server replication between each shop and head office. Each shop need to install SQL Server Express and head office already has SQL Server Enterprise Edition. Replication will run every 30 minutes as schedule and I choose Merge Replication because data can change at both shop and head office.

When I'm doing POC, I found this solution not work properly, sometime job is error and I need to re-initialize its. This solution also take a very long time, which obviously unacceptable to user.

I want to know, are there any solutions better than one that I'm doing now?

Update 1:

Constraints of the system are

1. Almost of transactions can occur at both shop and head office.
2. Some transaction need to work in real-time mode, that being said, after user save data to their local shop that data should go to update at head office too. (If they're currently online)
3. User can working even their shop has disconnected from head office database.
4. Our estimation about amount of data is at-most 2,000 rows in each day.
5. Windows 2003 is OS of Server at head office and Windows XP is OS of all clients.

Update 2:

1. Currently they're about 15 clients, but this number will growing in fairly slow rate.
2. Data's size is about 100 to 200 rows per replication, I think it may not more than 5 MB.
3. Client connect to server by lease-line connection; 128 kbps.

I'm in situation that replication take a very long time (about 55 minutes while we've only 5 minutes or so) and almost of times I need to re-initialize job to start replicate again, if I don't re-initialize job, it can't replicate at all. In my POC, I find that it always take very long time to replicate after re-initialize, amount of time doesn't depend on amount of data. By the way, re-initialize is only solution I find it work for my problem.

As above, I conclude that, replication may not suitable for my problem and I think it may has another better solution that can serve what I need in Update 1:

Answer 1

My suggestion is to use MS access locally and keep updating data to the server after a certain interval. Add a updated column to every table. When a record is added or updated, set the updated coloumn. For deletion you need to have a seprate table where you can put primary key value and table name. When synchronizing fetch all local records whose updated field not set and update (modify or insert) it to central server. Delete all records using local deleted table and you are done!

I assume that your central server is only for collecting data.

Answer 2

Sounds like you may need to roll your own bi-directional replication engine.

Part of the reason things take so long is that over such a narrow link (128kbps), the two databases have to be consistent (so they need to check all rows) before replication can start. As you can imagine, this can (and does) take a long time. Even 5Mb would take around a minute to transfer over this link.

When writing your own engine, decide what needs to be replicated (using timestamps for when items changed), figure out conflict resolution (what happens if the same record changed in both places between replication periods) and more. This is not easy.

Answer 3

I currently do exactly what you describe using SQL Server Merge Replication configured for Web Synchronization. I have my agents run on a 1-minute schedule and have had success.

What kind of error messages are you seeing?