5

I want to insert data in two tables, both belonging to different servers. I'm already aware with linked server which does the same job. But I'm looking for some other way to solve this problem.

I have two tables say TABLE_1 and TABLE_2

TABLE_1 belongs to server 1 and TABLE_2 belongs to server 2.

I want to insert data in both the tables at once without the usage of linked server. Is there any other way out?

Thanks in advance

Sid M
  • 4,354
  • 4
  • 30
  • 50
Jaihind
  • 2,770
  • 1
  • 12
  • 19
  • possible duplicate of [Insert Data From One Server To Another?](http://stackoverflow.com/questions/3285391/insert-data-from-one-server-to-another) – Sadikhasan Jul 21 '14 at 11:37
  • 2
    @Sadikhasan you are taking my question wrong, I need to insert in both the tables at ones. without using linked server. What you posted inserts the data from one server to another. – Jaihind Jul 21 '14 at 11:40
  • 1
    If you have a client side applications that opens 2 connections it could be inserting to both servers at the same time. Or you could do the equivalent in a CLR proc. Or perhaps mirroring or log shipping This seems kind of obvious, so I suspect this is not what you need somehow. Maybe you could explain what you are trying to accomplish since no-one seems to be answering your question. Correction, an answer just popped up using mirroring. – Gary Walker Jul 21 '14 at 11:59
  • why you are looking for another solution? what are the issues you are trying to avoid/solve that prevents you to use the linked server solution? do you have any requirement that is not satisfied by a linked server? – Paolo Jul 21 '14 at 12:00
  • I agree with @GaryWalker, you can create two connections and if you need "once" wrap it at transaction. – progpow Jul 21 '14 at 12:11
  • progpow -- How do you get a transaction to span multiple connections? – Gary Walker Jul 21 '14 at 12:38
  • Are your tags just random, or do they have any meaning? – podiluska Jul 21 '14 at 16:26

2 Answers2

0

Another way (without using linked servers), you can setup Database Mirroring between the servers.

Any transactions in the primary will be 'shipped' to the secondary almost instantly. I've used database mirroring for years (but that is another story).

OpcodePete
  • 887
  • 1
  • 14
  • 28
  • If I am right Database Mirroring works on same database on differenr server. How could I use Database Mirroring on two different Databases placed in differnt server ? If it does, please share some links or solutions. – Jaihind Jul 21 '14 at 13:10
  • When setting up Database mirroring, it requires cloning the target database. This is done manually (or under the hood if using the Wizard) by a backup and restore of the target database to another destination, then a transaction log backup and restore (in non-recovery mode). The original database is known as the principal database, and the cloned (newly created) database is known as the mirror. So yes, the 'same' database. If you were referring to different databases in your question, I didn't pick up on this, and assumed you were taking about having the same database in different locations. – OpcodePete Jul 21 '14 at 14:13
0

Create two connection with diff parameters and use them for inserting the data, but you need both the servers running and connected at the time of insertion.

Sid M
  • 4,354
  • 4
  • 30
  • 50
j1g4r
  • 1
  • 2