SolvedPrivate

Transfer Data Between Two Different SQL Server Instances in Separate Servers

Posted on 2014-11-06
7
39 Views
Last Modified: 2016-02-13
Hello,

Is there another secured way to transfer data between 2 different SQL server instances residing in 2 separate boxes besides linking them?  What if they are different versions and the OS in the boxes is different as well?  For example:



Boxes      OS                                                SQL Server version

Box1      Windows Server 2008 SP1            SQL Server 2008
Box2      Windows Server 2012            SQL Server 2012

We need to pass data between one SQL server instance to another via triggers and stored procedures. Can this be done without linking them?

Thanks.
0
Comment
Question by:TheUndecider
7 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 40427111
you need to link them, if you have to use triggers / stored procedures, If you just need to sync the data from one to another, you can setup  log shipping, in that case the second server can act in a read only / stand by mode.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 40427296
SQL Server Information Services (SSIS) is also an option, where you can create a package that connects to both source and destination SQL boxes, and create Data Flow Tasks that move data between them.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 40427326
You should also look at Replication, rather than trying to write and maintain your own code to do this (:shudder:).
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 100 total points
ID: 40427895
Hi,
You can create linked server on one server to access data from another server.
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40427898
0
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 100 total points
ID: 40428401
You can create linked server on one server to access data from another server.
@HuaMinChen, this is what he wants to avoid to do.
Can this be done without linking them?
If you want a synchronous solution, the Replication it's my advice. For an asynchronous solution you can also use Replication or follow Jim's suggestion to create a SSIS package.
0
 

Author Closing Comment

by:TheUndecider
ID: 40428948
Thanks all, even though we're not happy with it, it looks like having linked servers is the best option for us right now.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now