I am just trying to think through a process I need where I have:
我只是在思考我需要的一个过程:
- One SQL 2008 express database on a webserver - takes orders into an orders table
- web服务器上的一个SQL 2008 express数据库将订单接收到订单表中
- Another SQL 2008 express database on another webserver acting as a backend system to process the orders etc
- 另一个web服务器上的SQL 2008 express数据库充当后台系统来处理订单等
The idea being that the webserver database can be left to do what it does, and only when a new order record is inserted (actually updated.. another part of the story) I wanted that row to be inserted to an orders table on another server...
这样做的目的是让webserver数据库做它该做的事情，并且只有当一个新的订单记录被插入时(实际更新)。故事的另一部分)我希望将这行插入到另一台服务器上的orders表中……
The two servers do have local IP addresses, and are essentially on the same subnet - i.e. can communicate easily enough...
这两个服务器确实有本地IP地址，而且本质上是在同一个子网上——也就是说，可以很容易地进行通信……
Just trying to work out if a SQL Trigger is the way to go, or do I have perhaps a scheduled job on the second server which queries the first server say every 5 mins (plenty, not too many orders) and inserts any new orders into it's own database.. the idea is the table in the source/first server has a bit field to indicate if that row has been seen/read by server two...
我只是想弄清楚SQL触发器是否可行，或者我是否在第二个服务器上有一个预定的任务，它每5分钟查询第一个服务器一次(很多，不是很多订单)，并将任何新订单插入到它自己的数据库中。其思想是源/第一服务器中的表有一个位字段，用于指示服务器2是否已看到/读取该行……
Any ideas? unfortunately it's all SQL 2008 R2 express at this stage...
什么好主意吗?不幸的是，这一阶段都是SQL 2008 R2 express…
2 个解决方案
#1
3
You don't want a trigger accessing another server as part of a transaction. One solution, is to use Service Broker; have the trigger insert a message into the the local queue, and given the other server access to this queue.
您不希望触发器作为事务的一部分访问另一个服务器。一个解决方案是使用服务代理;让触发器将消息插入到本地队列中，并让其他服务器访问该队列。
SQL Server Service Broker provides the SQL Server Database Engine native support for messaging and queuing applications. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications.
SQL Server Service Broker为消息传递和队列应用程序提供SQL Server数据库引擎本地支持。这使得开发人员更容易创建复杂的应用程序，这些应用程序使用数据库引擎组件在不同的数据库之间进行通信。开发人员可以使用Service Broker轻松构建分布式和可靠的应用程序。
#2
0
You need to read up on linking servers first. This ought to be a great article for you to start with. Better yet, the scenario it describes is actually fairly similar to yours. Also, replication between the two servers may be a better choice than triggers. Here's a link for an article doing just that.
您需要首先阅读链接服务器。这应该是一篇很好的文章。更好的是，它描述的场景实际上与您的相似。此外，两个服务器之间的复制可能比触发器更好。这是一篇文章的链接。
#1
3
You don't want a trigger accessing another server as part of a transaction. One solution, is to use Service Broker; have the trigger insert a message into the the local queue, and given the other server access to this queue.
您不希望触发器作为事务的一部分访问另一个服务器。一个解决方案是使用服务代理;让触发器将消息插入到本地队列中，并让其他服务器访问该队列。
SQL Server Service Broker provides the SQL Server Database Engine native support for messaging and queuing applications. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications.
SQL Server Service Broker为消息传递和队列应用程序提供SQL Server数据库引擎本地支持。这使得开发人员更容易创建复杂的应用程序，这些应用程序使用数据库引擎组件在不同的数据库之间进行通信。开发人员可以使用Service Broker轻松构建分布式和可靠的应用程序。
#2
0
You need to read up on linking servers first. This ought to be a great article for you to start with. Better yet, the scenario it describes is actually fairly similar to yours. Also, replication between the two servers may be a better choice than triggers. Here's a link for an article doing just that.
您需要首先阅读链接服务器。这应该是一篇很好的文章。更好的是，它描述的场景实际上与您的相似。此外，两个服务器之间的复制可能比触发器更好。这是一篇文章的链接。