同步线上线下数据的合适方法
我有两个带有自己数据库的应用程序。
1.) 桌面应用程序，具有 vb.net winforms 界面，在离线企业网络中运行并将数据存储在中央数据库 [SQL Server]
**所有数据输入和其他办公操作均在中央数据库中进行并存储
2.)第二个应用程序是在php 上构建的。它有 html 页面，并在在线环境中作为网站运行。它将所有数据存储在mysql数据库中。
**此应用程序仅由注册会员访问，并且他们可以获取第一个应用程序处理的数据的不同报告。
现在我必须同步在线和离线数据库服务器之间的数据。我计划进行以下工作：
1.) 编写一个小程序，将SQL Server【离线服务器】的所有数据导出到CVS格式的文件中。
2.) 登录实时服务器的管理部分。
3.) 将导出的cvs文件上传到服务器。
4.) 将cvs文件中的数据导入到mysql数据库中。
我计划的方法好还是可以调整以使其表现良好。除了更改应用程序之外，我还希望获得其他好的数据同步方法。网络应用程序到其他一些使用mysql数据库
I have two applications with own database.
1.) Desktop application which has vb.net winforms interface, runs in offline enterprise network and stores data in central database [SQL Server]
**All the data entry and other office operations are carried out and stored in central database
2.) Second application has been build on php. it has html pages and runs as website in online environment. It stores all data in mysql database.
**This application is accessed by registered members only and they are facilitied with different reports of the data processed by 1st application.
Now I have to synchronize data between online and offline database servers. I am planning for following:
1.) Write a small program to export all the data of SQL Server [offline server] to a file in CVS format.
2.) Login to admin Section of live server.
3.) Upload the exported cvs file to the server.
4.) Import the data from cvs file to mysql database.
Is the method i am planning good or it can be tunned to perform good. I would also appreciate for other nice ways for data synchronisation other than changing applications.. ie. network application to some other using mysql database
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您所要求的实际上听起来并不像双向同步（或从 SQL Server 到 MySQL 以及从 MySQL 到 SQL Server 的双向数据移动），这是一件好事，因为它确实为您简化了事情。虽然我怀疑你使用 CSV 的方法（我假设你会使用 BCP 之类的东西来做到这一点）会起作用，但问题之一是你每次运行该过程时都会移动所有数据，并且基本上是覆盖每次都是整个 MySQL 数据库。这显然效率有些低。更不用说在此期间 MySQL 数据库将不处于可用状态。
一种替代方案（假设您有 SQL Server 2008 或更高版本）是研究将此技术与集成更改跟踪或集成更改捕获一起使用。这是 SQL Server 中的一项功能，允许您确定自某个时间点以来已更改的数据。您可以做的是创建一个进程，仅提取自上次检查 CSV 文件以来的更改，然后将这些更改应用到 MySQL。如果您这样做，请不要忘记也应用删除。
What you are asking for does not actually sound like bidirectional sync (or movement of data both ways from SQL Server to MySQL and from MySQL to SQL Server) which is a good thing as it really simplifies things for you. Although I suspect your method of using CSV's (which I would assume you would use something like BCP to do this) would work, one of the issues is that you are moving ALL of the data every time you run the process and you are basically overwriting the whole MySQL db everytime. This is obviously somewhat inefficient. Not to mention during that time the MySQL db would not be in a usable state.
One alternative (assuming you have SQL Server 2008 or higher) would be to look into using this technique along with Integrated Change Tracking or Integrated Change Capture. This is a capability within SQL Server that allows you to determine data that has changed since a certain point of time. What you could do is create a process that just extracts the changes since the last time you checked to a CSV file and then apply those to MySQL. If you do this, don't forget to also apply the deletes as well.
我认为没有现成的解决方案可以满足您的需要，无需自定义即可使用 - 但 MS Sync 框架 (http://msdn.microsoft.com/en-us/sync/default) 听起来很接近。
您可能需要为 MySQL 编写一个提供程序才能使其运行 - 这可能比从头开始编写整个数据同步逻辑的工作量要少。 Voclare 对于编写自己的同步机制可能面临的挑战的看法是正确的......
I don't think there's an off the shelf solution for what you want that you can use without customization - but the MS Sync framework (http://msdn.microsoft.com/en-us/sync/default) sounds close.
You will probably need to write a provider for MySQL to make it go - which may well be less work than writing the whole data synchronization logic from scratch. Voclare is right about the challenges you could face with writing your own synchronization mechanism...
请考虑将 SQL Server 集成服务作为一个很好的替代方案。
Do look into SQL Server Integration Service as a good alternate.