Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
SolvedPrivate

Transfer Data Between Two Different SQL Server Instances in Separate Servers

Posted on 2014-11-06
7
Medium Priority
?
44 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 400 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 400 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 400 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 53

Accepted Solution

by:
Vitor Montalvão earned 400 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

OfficeMate Freezes on login or does not load after login credentials are input.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
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…

578 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