I'm trying to test a proposition that one of our vendors presented to us for accessing their product database and it regards to queries and transactions that span multiple servers. I've never done this directly on the database before and to be frank, I'm clueless, so I'm trying to mock up a proof that this works at least conceptually.
我正在尝试测试我们的某个供应商向我们提供访问其产品数据库的提议,并且它涉及跨多个服务器的查询和事务。我以前从来没有直接在数据库上做过这件事,坦白说,我很无能为力,所以我试图模仿一个证据,证明这至少在概念上是有效的。
I've got two SQL Server 2005 servers. Let's for argument's sake call them Server1 and Server2 [hold your applause] each containing a dummy database. The dummy database on Server1 is called Source and that on Server2 is called Destination, just to keep things simple. The databases each hold a single table called Input and Output respectively, so the structure is quasi explained like so:
我有两个SQL Server 2005服务器。让我们为了论证的缘故,将它们称为Server1和Server2 [掌握你的掌声],每个都包含一个虚拟数据库。 Server1上的虚拟数据库称为Source,而Server2上的虚拟数据库称为Destination,只是为了简单起见。每个数据库分别拥有一个名为Input和Output的表,因此结构的准解释如下:
- Server1.Source.dbo.Input
- Server2.Destination.dbo.Output
I have a stored procedure on Server2 called WriteDataToOutput that receives a single Varchar argument and writes it's content to the output table.
我在Server2上有一个名为WriteDataToOutput的存储过程,它接收一个Varchar参数并将其内容写入输出表。
Now the trickiness starts:
现在诡计开始了:
- I want to create a stored procedure on Server1.Source that calls the WriteDataToOutput stored procedure defined on Server2, which seems like the simple step.
- I want this call to be part of a transaction so that if the procedure that invokes it fails, the entire transaction is is rolled back.
我想在Server1.Source上创建一个存储过程,它调用Server2上定义的WriteDataToOutput存储过程,这看起来就像一个简单的步骤。
我希望此调用成为事务的一部分,以便如果调用它的过程失败,则回滚整个事务。
And here endeth my knowledge of what to do. Can anyone point me in the right direction? I tried this on two different databases on the same server, and it worked just fine, leading me to assume that it will work on different servers, the question is, how do I go about doing such a thing? Where do I start?
在这里,我努力学习如何做。谁能指出我正确的方向?我在同一台服务器上的两个不同的数据库上尝试了这个,它运行得很好,让我认为它可以在不同的服务器上工作,问题是,我该如何做这样的事情?我从哪里开始?
6 个解决方案
#1
As others have noted, I agree that a linked server is the best way to go.
正如其他人所说,我同意链接服务器是最好的方法。
Here are a couple of pointers that snagged me the first time I dealt with linked servers:
以下是我第一次处理链接服务器时遇到的一些指示:
-
If the linked server is an instance, make sure you bracket the name. For example [SERVERNAME\INSTANCENAME].
如果链接服务器是实例,请确保将名称括起来。例如[SERVERNAME \ INSTANCENAME]。
-
Use an alias for the table or view from the linked server or you will get a "multi-part identifier cannot be bound" error. There is a limit of a 4 part naming convention. For example SERVER.DATABASE.dbo.TABLE.FIELD has five parts and will give an error. However,
SELECT linked.FieldName FROM SERVER.DATABASE.dbo.TABLE AS linkedwill work fine对链接服务器中的表或视图使用别名,否则将出现“无法绑定多部分标识符”错误。 4部分命名约定有限制。例如,SERVER.DATABASE.dbo.TABLE.FIELD有五个部分,将给出错误。但是,SELECT linked.FieldName FROM SERVER.DATABASE.dbo.TABLE AS链接将正常工作
#2
#3
for step 2 you need to have Distributed Transaction Coordinator running, you also need to use SET XACT_ABORT ON to make sure it will all rollback you also need to enable RPC which is turned off by default in 2005 and up
对于第2步,你需要运行分布式事务处理协调器,你还需要使用SET XACT_ABORT ON以确保它将全部回滚你还需要启用RPC,它在2005年及以后默认关闭
There is a whole bunch of stuff that can bite you in the neck
有很多东西可以咬你的脖子
#4
MSDN says you can have transactions across linked servers if you use the command BEGIN DISTRIBUTED TRANSACTION.
MSDN表示,如果使用命令BEGIN DISTRIBUTED TRANSACTION,则可以跨链接服务器进行事务处理。
I remember though that I had problems called a stored procedure on a linked server, but I worked around it, rather than solving it.
我记得虽然我在链接服务器上遇到了称为存储过程的问题,但我解决了它,而不是解决它。
#5
Using linked servers, you can run stored procedures on either server within a single transaction using DTC (Distributed Transactino Coordinator). You will definitely want to do some performance analysis. I have found some SPs using links can drastically slow down down database performance, especially if you try to join result sets from each of the two servers.
使用链接服务器,您可以使用DTC(Distributed Transactino Coordinator)在单个事务中的任一服务器上运行存储过程。你肯定想做一些性能分析。我发现一些使用链接的SP可能会大大降低数据库性能,特别是如果您尝试从两个服务器中的每一个加入结果集。
#6
Set up a linked server, then you should be able to execute selects/inserts/updates across the servers. Something like:
设置链接服务器,然后您应该能够跨服务器执行选择/插入/更新。就像是:
INSERT INTO Server2.Destination.dbo.Output
SELECT * FROM Input
WHERE <Criteria>
This assumes you are running the query from Server1.Source, so you wouldn't need to fully qualify.
这假设您正在从Server1.Source运行查询,因此您不需要完全限定。
#1
As others have noted, I agree that a linked server is the best way to go.
正如其他人所说,我同意链接服务器是最好的方法。
Here are a couple of pointers that snagged me the first time I dealt with linked servers:
以下是我第一次处理链接服务器时遇到的一些指示:
-
If the linked server is an instance, make sure you bracket the name. For example [SERVERNAME\INSTANCENAME].
如果链接服务器是实例,请确保将名称括起来。例如[SERVERNAME \ INSTANCENAME]。
-
Use an alias for the table or view from the linked server or you will get a "multi-part identifier cannot be bound" error. There is a limit of a 4 part naming convention. For example SERVER.DATABASE.dbo.TABLE.FIELD has five parts and will give an error. However,
SELECT linked.FieldName FROM SERVER.DATABASE.dbo.TABLE AS linkedwill work fine对链接服务器中的表或视图使用别名,否则将出现“无法绑定多部分标识符”错误。 4部分命名约定有限制。例如,SERVER.DATABASE.dbo.TABLE.FIELD有五个部分,将给出错误。但是,SELECT linked.FieldName FROM SERVER.DATABASE.dbo.TABLE AS链接将正常工作
#2
#3
for step 2 you need to have Distributed Transaction Coordinator running, you also need to use SET XACT_ABORT ON to make sure it will all rollback you also need to enable RPC which is turned off by default in 2005 and up
对于第2步,你需要运行分布式事务处理协调器,你还需要使用SET XACT_ABORT ON以确保它将全部回滚你还需要启用RPC,它在2005年及以后默认关闭
There is a whole bunch of stuff that can bite you in the neck
有很多东西可以咬你的脖子
#4
MSDN says you can have transactions across linked servers if you use the command BEGIN DISTRIBUTED TRANSACTION.
MSDN表示,如果使用命令BEGIN DISTRIBUTED TRANSACTION,则可以跨链接服务器进行事务处理。
I remember though that I had problems called a stored procedure on a linked server, but I worked around it, rather than solving it.
我记得虽然我在链接服务器上遇到了称为存储过程的问题,但我解决了它,而不是解决它。
#5
Using linked servers, you can run stored procedures on either server within a single transaction using DTC (Distributed Transactino Coordinator). You will definitely want to do some performance analysis. I have found some SPs using links can drastically slow down down database performance, especially if you try to join result sets from each of the two servers.
使用链接服务器,您可以使用DTC(Distributed Transactino Coordinator)在单个事务中的任一服务器上运行存储过程。你肯定想做一些性能分析。我发现一些使用链接的SP可能会大大降低数据库性能,特别是如果您尝试从两个服务器中的每一个加入结果集。
#6
Set up a linked server, then you should be able to execute selects/inserts/updates across the servers. Something like:
设置链接服务器,然后您应该能够跨服务器执行选择/插入/更新。就像是:
INSERT INTO Server2.Destination.dbo.Output
SELECT * FROM Input
WHERE <Criteria>
This assumes you are running the query from Server1.Source, so you wouldn't need to fully qualify.
这假设您正在从Server1.Source运行查询,因此您不需要完全限定。