I was wondering which one is the best way to replicate some data of a database to another.
我想知道哪一个是复制数据库数据到另一个数据的最好方法。
I have a database in one computer and this one receives some transactions. I need to send this data to another server (in the same local network) but with a modified value (I need to add 11 years to a Timestamp value).
我在一台计算机上有一个数据库，这个计算机接收一些事务。我需要将这些数据发送到另一个服务器(在相同的本地网络中)，但是要使用一个修改后的值(我需要将时间戳值增加11年)。
So I was looking for some options for my case, I can develop a windows service to do this but I don't know if the sql server replication can do this for me or if there is another option like some kind of magical trigger that can do that.
我在为我的案例寻找一些选项，我可以开发一个windows服务来做这个，但是我不知道sql server复制是否可以为我做这个或者是否有其他的选项，比如某种神奇的触发器可以做这个。
I'm using SQL Server 2005 on Windows Server 2003 R2.
我在Windows Server 2003 R2上使用SQL Server 2005。
1 个解决方案
#1
2
This link should help you:
这个链接应该可以帮助你:
Selecting the Appropriate Type of Replication
Quoted summary from link:
从链接引用总结:
Microsoft SQL Server offers three types of replication. Each type of replication is suited to different application requirements. Depending on the needs of your application, you can use one or more types of replication in a topology:
Microsoft SQL Server提供三种类型的复制。每种类型的复制都适合不同的应用程序需求。根据应用程序的需要，可以在拓扑中使用一个或多个复制类型:
- Snapshot replication
- 快照复制
- Transactional replication
- 事务性复制
- Merge replication
- 合并复制
I personally would replicate the database (transactional) and then use log shipping to update the replicated database (on your second server) with the latest data changes (from the primary server) then use a stored procedure running as a sql agent job to update the fields you need.
我个人将复制数据库(事务性)，然后使用日志传送来更新复制的数据库(在您的第二个服务器上)，然后使用最新的数据更改(来自主服务器)，然后使用一个存储过程作为sql代理作业来更新您需要的字段。
I personally am not a fan of triggers as you can end up having triggers activating other triggers and something that takes milliseconds to run can take seconds and if you have large volumes of data that can be painful (I manage a system that has exactly this issue - soon to be replaced thankfully)
我个人不喜欢触发器可以有触发激活其他触发器和运行时间毫秒的东西可以秒,如果您有大量的数据,可以痛苦(我管理系统这个问题——即将取代谢天谢地)
hope this helps and if you have some follow up questions I'll be happy to help.
希望这能有所帮助，如果你有什么后续问题，我很乐意帮忙。
#1
2
This link should help you:
这个链接应该可以帮助你:
Selecting the Appropriate Type of Replication
Quoted summary from link:
从链接引用总结:
Microsoft SQL Server offers three types of replication. Each type of replication is suited to different application requirements. Depending on the needs of your application, you can use one or more types of replication in a topology:
Microsoft SQL Server提供三种类型的复制。每种类型的复制都适合不同的应用程序需求。根据应用程序的需要，可以在拓扑中使用一个或多个复制类型:
- Snapshot replication
- 快照复制
- Transactional replication
- 事务性复制
- Merge replication
- 合并复制
I personally would replicate the database (transactional) and then use log shipping to update the replicated database (on your second server) with the latest data changes (from the primary server) then use a stored procedure running as a sql agent job to update the fields you need.
我个人将复制数据库(事务性)，然后使用日志传送来更新复制的数据库(在您的第二个服务器上)，然后使用最新的数据更改(来自主服务器)，然后使用一个存储过程作为sql代理作业来更新您需要的字段。
I personally am not a fan of triggers as you can end up having triggers activating other triggers and something that takes milliseconds to run can take seconds and if you have large volumes of data that can be painful (I manage a system that has exactly this issue - soon to be replaced thankfully)
我个人不喜欢触发器可以有触发激活其他触发器和运行时间毫秒的东西可以秒,如果您有大量的数据,可以痛苦(我管理系统这个问题——即将取代谢天谢地)
hope this helps and if you have some follow up questions I'll be happy to help.
希望这能有所帮助，如果你有什么后续问题，我很乐意帮忙。