Solved

server to server option in SQL

Posted on 2013-01-30
15
293 Views
Last Modified: 2013-02-18
If you have to write from one server to another (or read for that matter), is

1)Linked Server
2)Import/Export
3)BCP

the only options? anything else?
0
Comment
Question by:25112
  • 6
  • 4
  • 3
  • +2
15 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 56 total points
ID: 38835851
you can add replication to the list.

the choice depends on the needs, the volume etc..
for example, is this for a batch?
must the change be part of a "trigger/transaction"?
how many records to be the written/read ?
etc ...
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 167 total points
ID: 38835883
+ Bulk insert
0
 
LVL 77

Assisted Solution

by:arnold
arnold earned 222 total points
ID: 38835921
You can have an external mechanism as well.

What is the goal?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Author Comment

by:25112
ID: 38835955
the end goal is to do a DELETE or INSERT in 2nd server db from first server, from inside a SP. What would you recommend?

we want to avoid linked server, because there is a linked server already between these 2 servers, and for security reason, management wants only login to be on a linked server between 2 servers.. so we need something other than LS.
0
 
LVL 5

Author Comment

by:25112
ID: 38835962
>>You can have an external mechanism as well.
do you mean ETL like SSIS?
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 167 total points
ID: 38836071
if you want to do it within an sp, linked server is the best bet (you can setup SPN for the linked server  or a proxy for best security) ;
other option will be to call 2 sps, first one connect to the first server and executes and then application make another connection to the second server and call the second sp .
0
 
LVL 77

Assisted Solution

by:arnold
arnold earned 222 total points
ID: 38836078
Depending on your programming skills you can create a windows service (vb, c#) that can be run on either or a separate server that will establish a connection into one server and perform transactions while having a second connection to the other database server and perform the transaction it needs.

SSIS is one point.
The issue is more whether you need the two synchronized in real time.  Depending on your need and sql version, database mirroring might be a better option when you can bring up a read only snapshot from the mirrored database.
0
 
LVL 5

Author Comment

by:25112
ID: 38836342
>>other option will be to call 2 sps, first one connect to the first server and executes and then application make another connection to the second server and call the second sp .

you still need a LS with this, right?

is the above in essence the same concept as
"
establish a connection into one server and perform transactions while having a second connection to the other database server and perform the transaction it needs."
0
 
LVL 5

Author Comment

by:25112
ID: 38836344
the 2 dbs are totally different, so mirroring may not be an option.. the proecss we are dealing with is to write or remove a record on the other database, based on some conditions.
0
 
LVL 5

Author Comment

by:25112
ID: 38836347
you can't use bcp for this, can you? (from one sp, write directly into table in another server?)
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 167 total points
ID: 38836390
>you still need a LS with this, right?
Nope, you are basically making two seperate connections from the application for calling the two different sps, the issue associated with this will  be it wont be run as one transaction
0
 
LVL 77

Assisted Solution

by:arnold
arnold earned 222 total points
ID: 38836657
An external process either as a service or as a scheduled task is more like what you would need. I.e. it connects into the first to see if there are entries matching the requisite condition, then it does what it needs. either copies the entries from one to the other and deletes, or deletes one from database2.
0
 
LVL 5

Author Comment

by:25112
ID: 38836783
aneeshattingal, thanks

arnold, other than ssis, what are other sql technologies that will come under external processes as you mentioned?
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 55 total points
ID: 38837122
Be aware that you can have different callers use different security contexts on the linked server, even though all users are using the same linked server name.

And of course the linked server name doesn't have to bear any relation to the actual server name it links to.  That does make the link more obvious, but in cases where you don't want that, simply pick a different, and non-revealing, name for the linked server:


EXEC sp_addlinkedserver  
   @server='F89C2503DAA5',
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc='server_name\instance_name'
0
 
LVL 77

Accepted Solution

by:
arnold earned 222 total points
ID: 38837705
The process is outside of sql services/components.
You can use powershell cmndlets, vbscripts, etc. this is a completely separate application that connects to dbserver1 database1 and to dbserver2 database2.
This application performs the queries you deine on each databse without the need to link them.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Email Header Detail 12 64
SubQuery link 4 36
domain login has permission in database, but gets error 3 18
SQL Query assistance 16 36
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question