Connecting to a different server : LUSENET : SQL Server Database Administration : One Thread


Could you help me on how to update a table in different servers at the same time ?

+-------+ | S1 | <------ TBLA | | | +-------+ | | +-------+ | | S2 | | | | TBLB <-+ +-------+

Updating the TBLA in the server S1 will also update the TBLB server S2.

I appreciate your help.

-- Anonymous, February 02, 1998


Re: Connecting to a different server


It can be done rather easily if the update is small. 8 million rows on the other hand will nuke your log. If need be you could do it in small chunks.

What you will need to do is execute a stored procedure on the remote server in addition to executing it on the local server. For example, you have two servers S1 and S2. At server S1 you can execute a procedure on server S2 with the following syntax:

EXEC S2.dbname.owner.procname

You can also INSERT based on result from remote execution, like: INSERT tblname EXEC S2.dbname.owner.procname

You might also be able to use a sp_sqlexec, which EXECutes the string sent into it (max 255 characters). INSERT tblname EXEC S2.dbname.dbo.sp_sqlexec "SELECT * FROM authors"

You need to configure the involved SQL Servers for "remote access", check out chapter 10 in Administrators Companion. I have also explained the configuration for "remote access" below.

If you need multiserver transaction integrity, check out the MSDTC service and the BEGIN DISTRIBUTED TRANSACTION command.


Configuration for Remote Access

1. Make sure you have remote access on both servers.

sp_configure "remote access",1 go reconfigure go

(you will need to stop and start SQL Server for this to take)

2. On each server add the other one.

(on S1) sp_addserver S2 go (on S2) sp_addserver S1 go

3. Map all of the logins that you need (proably just sa).

(on S1) sp_addremotelogin S2,sa,sa go (on S2) sp_addremotelogin S1,sa,sa go

-- Anonymous, February 02, 1998

Moderation questions? read the FAQ