Single Store CRM application to Multi Store CRM

[Sanket_2]

My company has Desktop application developed in vb.net using devexpress controls. Back End database is MySQL.

Company is in retailing and have 2 retail stores in in same city. Both stores always stay busy and customers are always in waiting at the counter. Basically, it is desktop based CRM application which has lot of modules inside it apart from invoice/Receipt module, it has other modules like Delivery module, installation module, Service/Repair module, Account Receivable module and many other modules used by various back office departments of the company. Other resources/hardware such as Barcode Printer, Receipt Printer, and Barcode scanner are connected to the CRM on Desktop PC.

Currently, there are around 55 clients always connected to server and using application.

Problem:

Till couple of weeks back, company had no issue using this desktop application and single MySQL server as all clients were connected via LAN or WLAN.

Now situation has changed, and new requirement has raised: Company has planned to open new stores at very far distance. Such stores cannot be connected to current central database via LAN or WLAN. Each new branch would have around 20-30 clients, say “Branch Clients”

Also, there would be field executive who will be working from their laptop. Say “Remote Clients”. They will just have 3G internet connection on their laptop.

Thought 1: Install desktop application at all branch PCs, and connect them to central MySQL database server over the internet.

Not possible: Connection over the internet would be very slow for fetching such huge data. Data is really huge For, e.g. if client opens “Customer Master”, then there would be more than 600,000 rows which takes lot of bandwidth and time to open over the internet. And there are many more such modules which loads lot of data.

Also, in case of losing internet connection, clients would not able to operate the application. Customer waiting in line to make receipt would go crazy if they have to wait for long.

Thought 2: Install new MySQL server at branch store, all the desktop PCs then would be connected to that local branch server. And then that local branch server would be connected to central server via MySQL replication option.

Not possible: Since MySQL replication has limitation of only one way replication, we cannot implement this structure. Application requires to move data from central server to branch server and from Branch to Central in real-time. Also, MySQL replication engineering has limitation to replicate only with one server only. In that case, we cannot replicate with multiple branch stores. There is an option of cluster server, but company cannot afford licensing cost.

Thought 3: Somebody suggested me that I should transfer entire desktop application into Web Application and get cloud server for database.

Not possible: I think looking at current requirement (fast access), environment (retail store-pos) and hardware (printers, scanners) connected to client - it is not advisable to have web application and cloud database server. Also in the event of no internet, entire store would go down.

Thought 4: Somebody suggested me that I should move from MySQL server to MSSQL and keep desktop application as it is. MSSQL has capability to sync with multiple servers in real-time over the internet. It has no limitation like MySQL’s one way replication and only one replication connection.

I guess, to make faster and constant database connection, installing local branch server is highly required. But I don’t know how those different branch servers could be connected to central server.

My Questions:

• What is the best way to resolve above issues in given condition and successfully fulfill the company’s requirement? Faster and constant connection to database server. And also real-time updates between all branches and central server. If internet connection is down, then delay in real-time update is acceptable but clients should not be affected from work.

• Would migration from MySQL to MSSQL resolve the issue? Because data migration is not issue as there are many tools available which converts the database from one platform to other. But issue is - application is very huge having hundreds of query written for MySQL. I guess I have to change those all queries also, because queries are not same for MySQL and MSSQL. Do I have to change all the queries or just the few percentage queries? Or if there is any tool available which convert queries from MySQL to MSSQL query.

• In general, how such small-medium retail store company have their infrastructure and application setup? Let me know some ideas.

[Ewald Horn]

Migrating from one database to another is never a smooth operation, you might be setting yourself up for a lot of effort. Plus the licenses required for MS-SQL might be quite heavy, which is going to have an impact on the sustainability of such a project.

Honestly, what you are trying to do is to retrofit something that was never designed to be multi-tenant to suddenly be multi-tenant. That, in the history of software, has always been a VERY BAD IDEA. Others might not agree, but I foresee tremendous amounts of pain trying to hammer this thing into what will become your own distributed cloud-based solution.

I agree that a web application is not the way to go, not if you are interfacing with hardware. Even with modern browsers and the fantastic tools available, it's still quite difficult to successfully and reliably speak to hardware.

What skills are available in the company? Are we looking at mostly VB and MySQL skills here? How much knowledge is in the company around network programming, queue management etc.?

[Sanket_2]

Currently we don’t have much vast skill available. We are having VB and MySQL skills available and MSSQL at intermediate level.
But if there is a solution we might hire particular skill. Currently having intermediate knowledge of networking and many more fields, they are versatile to different technology also.

[Sanket_2]

awaiting for reply

[pritaeas]

There is an option of cluster server, but company cannot afford licensing cost.

How do these costs weigh against the licensing costs of a SQL Server?

[Sanket_2]

company already has MSSQL which was being used for other small project. so not required to buy it.

[pritaeas]

Oh well. If they can setup the replication, then depending on what kind of queries you are using, you'd need to rewrite some. Stored procedures etc would have to ben rewritten too.

[Ewald Horn]

Hi.

Sorry I was away for a few days - enjoying Africa :)

So, we've had something similar, and ended up using a distributed queue system, where every location has it's own local server that receives updates from the cloud, and pushes changes back to the cloud. The local server runs a mirror of the cloud solution, so look-ups first hit the local database, and if nothing is found, the cloud link is used to get the information.

This solution though, cost a lot of development time, and we had to modify our desktop application server (not the desktop app itself, luckily) quite a bit to make it work.

The solution seems to work well for the client, given that they do more reads than writes on the database. All changes (sales, inventory etc.) are pushed to the cloud queue and is pulled down by each individual location. Our queue is time-stamped and has a sequence number, so each location pulls down only what it needs. For example, if location B loses connectivity for two hours or so, it's not a big deal, it will be able to catch up to the rest, and will continue to work in isolation on the local database until a connection is restored.

We did make a change where each record in the database is also linked to a location, that way, we can view all sales in Location A, even from Location D, or just the sales for Location D, even in Location H.

Hope that makes sense.