March 21, 2014 at 2:35 am
Hi everyone, does anyone know if it's possible to update 2 tables that reside on 2 different servers from within one SP? I have access to 2 servers that are in 2 different locations, Server 1 is the main one we work to, Server 2 is in a different building and is used mainly as a backup for server 1, it does a backup most evenings. I've been asked if it's possible to use Server 2 in the unlikelyhood of Server 1 going out of action for a small amount of time. I've heard of mirroring and I don't think that would work for us as a small group use Server 2 for their own work. I just wondered if a SP could do inserts and updates on 2 tables across 2 different servers?
March 21, 2014 at 2:45 am
mick burden (3/21/2014)
Hi everyone, does anyone know if it's possible to update 2 tables that reside on 2 different servers from within one SP? I have access to 2 servers that are in 2 different locations, Server 1 is the main one we work to, Server 2 is in a different building and is used mainly as a backup for server 1, it does a backup most evenings. I've been asked if it's possible to use Server 2 in the unlikelyhood of Server 1 going out of action for a small amount of time. I've heard of mirroring and I don't think that would work for us as a small group use Server 2 for their own work. I just wondered if a SP could do inserts and updates on 2 tables across 2 different servers?
Its possible but there would possibly be a performance impact depending on how you do it. You could use linked servers as one solution. Merge or Transactional replication is another option. Merge is generally better for R/W ability on Server 2.
Does server 2 need to be R/W or R/O? Does it need to be "real-time"? If you can get away with day old data and R/O mirroring could work with snapshots (Ent. Edition).
March 21, 2014 at 2:52 am
MysteryJimbo (3/21/2014)
mick burden (3/21/2014)
Hi everyone, does anyone know if it's possible to update 2 tables that reside on 2 different servers from within one SP? I have access to 2 servers that are in 2 different locations, Server 1 is the main one we work to, Server 2 is in a different building and is used mainly as a backup for server 1, it does a backup most evenings. I've been asked if it's possible to use Server 2 in the unlikelyhood of Server 1 going out of action for a small amount of time. I've heard of mirroring and I don't think that would work for us as a small group use Server 2 for their own work. I just wondered if a SP could do inserts and updates on 2 tables across 2 different servers?Its possible but there would possibly be a performance impact depending on how you do it. You could use linked servers as one solution. Merge or Transactional replication is another option. Merge is generally better for R/W ability on Server 2.
Does server 2 need to be R/W or R/O? Does it need to be "real-time"? If you can get away with day old data and R/O mirroring could work with snapshots (Ent. Edition).
Thank you for replying to my question. I'm relatively new to SQL servers as in I don't spend a lot of time with them. As I'm not the owner of the servers I didn't want to "mess around" with the main set up, as I'm allowed to write my own SP's on my own Databases I thought if it was at all possible I could try out SP's that would allow me to write to both servers at the same time. Do you have any simple examples I could try out please?
March 21, 2014 at 3:05 am
No examples of this
The principle is you have identical stored procedures on Server 1 and Server 2.
A linked server on Server 1 pointing at Server 2.
You then use 4 part naming to call the same procedure over the network
CREATE PROCEDURE update2servers (@param1 int)
AS
BEGIN
IF @@SERVERNAME = 'Server1'
BEGIN
exec server2.database.dbo.update2servers @param1
END
SELECT @param1
END
GO
Something like this. Its not what I would do given the chance but its a basic way of calling the same code. You may want to explicitly start a distributed transaction if its critical to run.
March 21, 2014 at 3:08 am
That's great, thank you. It gives me a starting point at least.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply