Copy data from one server to another server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Copy data from one server to another server

Author

Topic

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-03-02 : 11:38:24

Hi,
i have production server. now i need copy data from production server table to xyz server table.
Note : data should be always update after sertain time say eg at 12 am. we have to update data in xyz server table if the data has been updated in producion server.same for insert / delete operation.

now tell me the best solution (performance wise) for this .
How i can do it by SSIS? if any otehrs options are available please let me inform.

Regards,
Avijit

Regards,
avijit

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 11:40:35

Have you thought about replication?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 11:41:33

I also think if it has to happen periodically its better to implement replication

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-03-02 : 11:51:58

even i was thinking same. any idea how i will procced for replication because i never did it before

Regards,
avijit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:37:52

how frequently you want copy to happen?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-03-02 : 22:47:40

minmum one day.
if its possible to update data 2/4 times in a day with out much affecting production server then its welcome.

Regards,
avijit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:59:19

then you can go for transactional replication

http://www.databasejournal.com/features/mssql/article.php/1438201/Setting-Up-Transactional-Replication-A-Step-by-step-Guide.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-03-03 : 12:15:24

thanks

Regards,
avijit

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2010-03-05 : 00:23:17

If any of your table has no primary key, replication wont be applicable for them.

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2010-03-05 : 00:54:58

Try this logic

create table #A (col1 int, col2 int)
create table #B (col1 int, col2 int)

insert into #A values (1,1)
insert into #A values (2,1)
insert into #A values (3,1)
insert into #A values (4,1)

insert into #B values (1,1)
insert into #B values (3,1)
insert into #B values (5,1)

select * from #A
select * from #B

-- move row to #A
insert into #A
select B.*
from #A A
right outer join #B B
on A.col1 = B.col1
where A.col1 is null

select * from #A
select * from #B

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 10:27:16

quote:
Originally posted by saurabhsrivastava

Try this logic

create table #A (col1 int, col2 int)
create table #B (col1 int, col2 int)

insert into #A values (1,1)
insert into #A values (2,1)
insert into #A values (3,1)
insert into #A values (4,1)

insert into #B values (1,1)
insert into #B values (3,1)
insert into #B values (5,1)

select * from #A
select * from #B

-- move row to #A
insert into #A
select B.*
from #A A
right outer join #B B
on A.col1 = B.col1
where A.col1 is null

select * from #A
select * from #B

what has above to do with original question? question was to move data b/w servers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2010-03-08 : 01:57:00

This is simple example of incremental update on destination table. An additional option to consider just like Replication.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 10:45:19

quote:
Originally posted by saurabhsrivastava

This is simple example of incremental update on destination table. An additional option to consider just like Replication.

where are you doing incremental update in above case? have you thought of cases of updates/inserts?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources