单个存储 - 单个本地数据库服务器到多个存储多个位置 [英] Single Store - Single Local database server to Multi Store Multi location

问题描述

Hi
我的公司使用devexpress控件在vb.net中开发了桌面应用程序。后端数据库是MySQL。

公司在零售业，在同一个城市有2家零售店。两家商店总是保持忙碌，顾客总是在柜台等候。基本上，它是基于桌面的CRM应用程序，除了发票/收据模块外，其中有许多模块，它还有其他模块，如交付模块，安装模块，服务/修复模块，应收帐款模块以及各种后台使用的许多其他模块公司的部门。其他资源/硬件（如条形码打印机，收据打印机和条形码扫描仪）连接到桌面PC上的CRM。

目前，大约有55个客户端始终连接到服务器并使用应用程序。



问题：

几周前，公司使用这个桌面应用程序和单个MySQL服务器都没有问题客户通过局域网或无线局域网连接。

现在情况发生了变化，新的要求已经提出：公司计划在很远的距离开设新店。这些商店不能通过LAN或WL​​AN连接到当前的中央数据库。每个新的分支机构将有大约20-30个客户，比如说分支机构客户

此外，还有现场执行官将使用他们的笔记本电脑工作。说远程客户端。他们的笔记本电脑上只有3G网络连接。



思想1：在所有分支机构上安装桌面应用程序，并将它们连接到中央通过互联网的MySQL数据库服务器。

不可能：通过互联网连接获取如此庞大的数据非常缓慢。数据真的很大，例如如果客户打开客户主，那么将有超过600,000行，这需要大量的带宽和时间在互联网上打开。还有更多此类模块可以加载大量数据。

此外，如果网络连接丢失，客户端将无法运行该应用程序。如果他们需要等待很长时间，排队等候的客户会发疯。



思想2：在分行安装新的MySQL服务器然后，所有桌面PC都将连接到该本地分支服务器。然后，本地分支服务器将通过MySQL复制选项连接到中央服务器。

不可能：由于MySQL复制仅限于单向复制，因此我们无法实现此结构。应用程序需要将数据从中央服务器移动到分支服务器，并实时从分支移动到中心。此外，MySQL复制工程仅限于仅使用一台服务器进行复制。在这种情况下，我们无法复制多个分支商店。有一个群集服务器选项，但公司无法承担许可费用。



思想3：有人建议我应该转移整个桌面应用到Web应用程序并获取数据库的云服务器。

不可能：我认为目前的需求（快速访问），环境（零售商店-pos）和硬件（打印机，扫描仪）连接到客户端 - 不建议使用Web应用程序和云数据库服务器。此外，如果没有互联网，整个商店都会关闭。



思想4：有人建议我应该从MySQL服务器迁移到MSSQL并保持桌面应用程序不变。 MSSQL能够通过互联网实时同步多台服务器。它没有像MySQL的单向复制和只有一个复制连接那样的限制。



我想，为了实现更快和更稳定的数据库连接，非常需要安装本地分支服务器。但我不知道这些不同的分支服务器如何连接到中央服务器。

我的问题：

•在给定条件下解决上述问题并成功实现上述问题的最佳方法是什么公司的要求？更快，更连续地连接数据库服务器。还有所有分支机构和中央服务器之间的实时更新。如果互联网连接中断，则实时更新延迟是可以接受的，但客户不应受到影响。



•从MySQL迁移到MSSQL会解决问题？因为数据迁移不是问题，因为有许多工具可用于将数据库从一个平台转换为另一个平台。但问题是 - 应用程序非常庞大，有数百个为MySQL编写的查询。我想我也必须更改所有查询，因为MySQL和MSSQL的查询不一样。我是否必须更改所有查询或仅更改几个百分比查询？或者，如果有任何可用的工具将查询从MySQL转换为MSSQL查询。



•一般来说，这样的中小型零售店公司如何设置基础架构和应用程序？让我知道一些想法。

Hi 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.

推荐答案

这里有另一种选择，你可能没有考虑过......



在网络上安装虚拟机，将远程桌面远程桌面放入其中。这样，数据就是本地的，查询速度非常快，系统的所有维护都在一个中心位置完成。分支机构只是瘦客户机（基本上是具有网络连接的计算机）。或者，您可以设置单个服务器并为每个分支设置一个帐户，而不是使用虚拟机。当他们远程登录时，他们可以启动POS / ERP系统并做他们需要的任何事情。



这解决了很多问题，不仅仅是你写的，而是例如，升级软件，维护系统，更换设备就像获得新的瘦客户机等一样简单。

There's another option here you may not have considered...

Install virtual machines on a network, have the remote locations remote-desktop into them. This way the data is local, queries will be very fast, and all maintenance of the system is done in one central location. The branches will just be thin-clients (basically a computer with a network connection). Or, instead of using virtual machines, you could set up a single server and give each branch an account. When they log in remotely they can launch the POS/ERP system and do whatever they need to.

This solves a lot of problems, not just what you wrote, but for example, upgrading the software, maintenance of the system, replacing equipment is as simple as getting a new thin-client, etc.

转移到SQL Server，它会更好。大多数查询应该有效。但是，没有工具可以帮助你。



我会在本地存储数据，并在一夜之间将其发送到中央服务器。这将允许你做到思考2，这是明智的选择IMO。

Move to SQL Server, it's better. Most queries should work. There are no tools to help you, though.

I would store data locally, and send it to a central server in an overnight process. It would allow you do to thought 2, which is the sensible option IMO.

这篇关于单个存储 - 单个本地数据库服务器到多个存储多个位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！