Best Protocol between linked servers

Hi All,
I'm setting up an environment with two SQLServer2005 instances (on two
different servers).
They're connected thru a linked server (server B to Server A).
Whenever I set the linked server up (in server B), it allows me to choose
between:
1) write the name of the linked server but it must be the same as the real
network name of the server
2) Allow me to choose several parameters but it doesn't use the SQLServer
protocol (faster than any other choice).
As the Stored Procedures (in server B) contains a reference to A's Tables
and it's kinda not possible to change all of them, how do I make the
communication between those server the best ?
Moreover, which protocol priority do I choose in the client network
configuration (Named Pipes is better than TCPIP) ?
Thanks
Igor.Hi Igor,
Take a look at this:
What to use Named Pipes or TCP/IP:
http://www.sqlcommunity.com/Default.aspx?grm2id=55&tabid=77
HTH
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
SQLTips, Scripts, Discussions, Blogs, Articles, Radio and a lot of SQL
Server Fun.
"Igor" wrote:
> Hi All,
> I'm setting up an environment with two SQLServer2005 instances (on two
> different servers).
> They're connected thru a linked server (server B to Server A).
> Whenever I set the linked server up (in server B), it allows me to choose
> between:
> 1) write the name of the linked server but it must be the same as the real
> network name of the server
> 2) Allow me to choose several parameters but it doesn't use the SQLServer
> protocol (faster than any other choice).
> As the Stored Procedures (in server B) contains a reference to A's Tables
> and it's kinda not possible to change all of them, how do I make the
> communication between those server the best ?
> Moreover, which protocol priority do I choose in the client network
> configuration (Named Pipes is better than TCPIP) ?
> Thanks
> Igor.

Best method of Restoring a SQL 2005 Desktop Edition

For a SQL 2005 Desktop edition that has a user database and some user
accounts and linked servers. What is the best method of backing this
up and restoring to a different SQL Server?Robin9876 wrote:
> For a SQL 2005 Desktop edition that has a user database and some user
> accounts and linked servers. What is the best method of backing this
> up and restoring to a different SQL Server?
See: http://vyaskn.tripod.com/moving_sql_server.htm
--
Razvan Socol
SQL Server MVP

BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION

Hi have have two linked SQL Servers and I am trying to get things working
smootly/quickly.

Should I be using 'BEGIN TRANSACTION' or 'BEGIN DISTRIBUTED TRANSACTION' ?

Basicly, these SPs update a local table and a remote table in the same
transaction. I cant have one table updated and not the other. Please dont
say replicate the tables either as at this time, this is is not an option.

I have for example a number of stored procedures that are based around the
following:
where ACSMSM is a remote (linked) SQL Server.

procedure [psm].ams_Update_VFE
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strVFEID varchar(16),
@.strDescription varchar(64),
@.strVFEVirtualRoot varchar(255),
@.strVFEPhysicalRoot varchar(255),
@.strAuditPath varchar(255),
@.strDefaultBranding varchar(16),
@.strIPAddress varchar(23)
as
declare @.strStep varchar(32)
declare @.trancount int

Set XACT_ABORT ON
set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'

if (@.trancount = 0)
BEGIN TRANSACTION mytran
else
save tran mytran

/* start insert sp code here */

set @.strStep = 'Write VFE to MSM'

update
ACSMSM.msmprim.msm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID;

set @.strStep = 'Write VFE to PSM'

update
ACSPSM.psmprim.psm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID

/* end insert sp code here */

if (@.@.error <> 0)
begin
rollback tran mytran
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : ' + @.@.Error
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it

if (@.trancount = 0)
commit tran

return 0"Steve Thorpe" <stephenthorpe@.nospam.hotmail.com> wrote in message
news:bkn3j2$2om$1@.sparta.btinternet.com...
> Hi have have two linked SQL Servers and I am trying to get things working
> smootly/quickly.
> Should I be using 'BEGIN TRANSACTION' or 'BEGIN DISTRIBUTED TRANSACTION' ?
> Basicly, these SPs update a local table and a remote table in the same
> transaction. I cant have one table updated and not the other. Please dont
> say replicate the tables either as at this time, this is is not an option.
> I have for example a number of stored procedures that are based around the
> following:
> where ACSMSM is a remote (linked) SQL Server.
> procedure [psm].ams_Update_VFE
> @.strResult varchar(8) = 'Failure' output,
> @.strErrorDesc varchar(512) = 'SP Not Executed' output,
> @.strVFEID varchar(16),
> @.strDescription varchar(64),
> @.strVFEVirtualRoot varchar(255),
> @.strVFEPhysicalRoot varchar(255),
> @.strAuditPath varchar(255),
> @.strDefaultBranding varchar(16),
> @.strIPAddress varchar(23)
> as
> declare @.strStep varchar(32)
> declare @.trancount int
> Set XACT_ABORT ON
> set @.trancount = @.@.trancount
> set @.strStep = 'Start of Stored Proc'
> if (@.trancount = 0)
> BEGIN TRANSACTION mytran
> else
> save tran mytran
> /* start insert sp code here */
> set @.strStep = 'Write VFE to MSM'
> update
> ACSMSM.msmprim.msm.VFECONFIG
> set
> DESCRIPTION = @.strDescription,
> VFEVIRTUALROOT = @.strVFEVirtualRoot,
> VFEPHYSICALROOT = @.strVFEPhysicalRoot,
> AUDITPATH = @.strAuditPath,
> DEFAULTBRANDING = @.strDefaultBranding,
> IPADDRESS = @.strIPAddress
> where
> VFEID = @.strVFEID;
> set @.strStep = 'Write VFE to PSM'
> update
> ACSPSM.psmprim.psm.VFECONFIG
> set
> DESCRIPTION = @.strDescription,
> VFEVIRTUALROOT = @.strVFEVirtualRoot,
> VFEPHYSICALROOT = @.strVFEPhysicalRoot,
> AUDITPATH = @.strAuditPath,
> DEFAULTBRANDING = @.strDefaultBranding,
> IPADDRESS = @.strIPAddress
> where
> VFEID = @.strVFEID
> /* end insert sp code here */
> if (@.@.error <> 0)
> begin
> rollback tran mytran
> set @.strResult = 'Failure'
> set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : ' + @.@.Error
> return -1969
> end
> else
> begin
> set @.strResult = 'Success'
> set @.strErrorDesc = ''
> end
> -- commit tran if we started it
> if (@.trancount = 0)
> commit tran
> return 0

Since you're doing an UPDATE on the remote server, the two are equivalent -
MSSQL will promote the local transaction to a distributed one automatically.
This doesn't necessarily happen for executing stored procedures remotely,
though - in that case you do need to use BEGIN DISTRIBUTED TRAN, or set
'remote proc trans' on for the server, which will make it automatic for
procedure calls also.

Simon