SolvedPrivate

Transfer Data Between Two Different SQL Server Instances in Separate Servers

Posted on 2014-11-06
7
36 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:ScottPletcher
ScottPletcher 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 45

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

743 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

13 Experts available now in Live!

Get 1:1 Help Now