将数据从一个 MS SQL Server 复制/克隆到另一个 MS SQL Server
我正在尝试将一个 MSSQL 数据库的内容获取到第二个 MSSQL 数据库。不需要冲突管理，也不需要模式更新。它只是简单的复制和替换数据。目标数据库的数据将被覆盖，以防有人更改了其中的某些内容。
显然，有很多方法可以实现
- SQL Server 复制： 完善，但使用旧协议。除此之外，许多开发人员不断告诉我，细节决定成败，复制可能并不总是按预期工作，这对于管理员来说是最好的选择，但对于开发人员来说却不是。
- MS Sync Framework： MSF 据说是很酷的新技术。是的，这就是你喜欢得到的新东西，因为它听起来很有创意。有一种通用的同步方法，这听起来像：学习一种技术以及如何集成数据源，您将永远不必再次学习如何开发同步。但另一方面，您可以看到主要的使用场景似乎是将 MSSQL Compact 数据库与 MSSQL 同步。
- SQL Server 集成服务：这听起来像是一个可紧急计划的解决方案。如果防火墙不起作用，我们有一个可以一次又一次执行的包......直到防火墙关闭或身份验证修复。
- 暴力复制和替换数据库文件：可能不是最好的选择。
当然，在浏览微软网站时，我读到每项技术（当然除了暴力破解）都被认为是可以应用于许多场景的可靠解决方案。但这当然不是我想听到的。
那么您对此有何看法呢？您会建议哪种技术。
谢谢你！
斯特凡
I am trying to get the content of one MSSQL database to a second MSSQL database. There is no conflict management required, no schema updating. It is just a plain copy and replace data. The data of the destination database would be overwritten, in case somebody would have had changed something there.
Obviously, there are many ways to do that
- SQL Server Replication: Well established, but using old protocols. Besides that, a lot of developers keep telling me that the devil is in the details and the replication might not always work as expected and that is this best choice for an administrator, but not for a developer.
- MS Sync Framework: MSF is said to be the cool, new technology. Yes, it is this new stuff, you love to get, because it sounds so innovative. There is the generic approach for synchronisation, this sounds like: Learn one technology and how to integrate data source, you will never have to learn how to develop syncing again. But on the other hand, you can read that the main usage scenario seems to be to synchronize MSSQL Compact databases with MSSQL.
- SQL Server Integration Services: This sounds like an emergency plannable solution. In case the firewall is not working, we have a package that can be executed again and again... until the firewall drops down or the authentication is fixed.
- Brute Force copy and replace of database files: Probably not the best choice.
Of course, when looking on the Microsoft websites, I read that every technology (apart from brute force of course) is said to be a solid solution that can be applied in many scenarios. But that is, of course, not the stuff I wanted to hear.
So what is your opinion about this? Which technology would you suggest.
Thank you!
Stefan
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
最简单的机制是日志传送。主服务器可以将日志备份放在任何 UNC 路径上，然后您可以使用任何文件同步工具来管理将日志从一台服务器获取到另一台服务器。订阅者只是自动恢复在其本地文件夹中找到的任何事务日志备份。这不仅可以自动处理数据，还可以自动处理模式更改。
订阅者将是只读的，但这正是您想要的 - 否则，如果有人可以更新订阅者的记录，您将陷入一个受伤的世界。
The easiest mechanism is log shipping. The primary server can put the log backups on any UNC path, and then you can use any file sync tools to manage getting the logs from one server to another. The subscriber just automatically restores any transaction log backups it finds in its local folder. This automatically handles not just data, but schema changes too.
The subscriber will be read-only, but that's exactly what you want - otherwise, if someone can update records on the subscriber, you're going to be in a world of hurt.
我会在你的列表中添加两种技术。
如果它是一个大数据库并且您不会经常这样做，那么我会选择备份和恢复选项。它为您完成大部分工作，并保证复制所有对象。
我没有听说过有人使用 Sync Framework，所以我很想知道是否有人成功使用过它。
I'd add two techniques to your list.
If it's a big database and you're not going to be doing this too often, then I'd go for the backup and restore option. It does most of the work for you and is guaranteed to copy all the objects.
I've not heard of anyone using Sync Framework, so I'd be interested to hear if anyone has used it successfully.