Transfer Data Between Two Different SQL Server Instances in Separate Servers

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.
TheUndeciderAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
Scott PletcherSenior DBACommented:
You should also look at Replication, rather than trying to write and maintain your own code to do this (:shudder:).
0
 
HuaMin ChenSystem AnalystCommented:
Hi,
You can create linked server on one server to access data from another server.
0
 
TheUndeciderAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.