SolvedPrivate

Transfer Data Between Two Different SQL Server Instances in Separate Servers

Posted on 2014-11-06
7
42 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 66

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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 11

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 11

Expert Comment

by:HuaMinChen
ID: 40427898
0
 
LVL 50

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

A safe way to clean winsxs folder from your windows server 2008 R2 editions
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
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…

695 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