Post reply

Copy data from a linked server, performance issue

  • June 25, 2010 at 1:10 pm

    #220536

    Hello folks,

    I have a linked server from which I set up a schedule for a maintenance job to replicate data to my server, currently I set the schedule as once an hour, I wonder if there would be any performance issue if I set the schedule to every minute or 5 minutes? or there is other approach I should take? I would like to hear some advice.

    Thanks.

  • June 25, 2010 at 1:15 pm

    #1185228

    I think that depends on how much data you are copying and what is going on in yoru servers. for my money though I would strongly encourage you to write this as an ssis package. it is very easy to copy data from one server to the other and in my oppinoin you will not find any better performance in copying data.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • June 25, 2010 at 1:48 pm

    #1185240

    Dan.Humphries (6/25/2010)

    I think that depends on how much data you are copying and what is going on in yoru servers. for my money though I would strongly encourage you to write this as an ssis package. it is very easy to copy data from one server to the other and in my oppinoin you will not find any better performance in copying data.

    Thank you for your SSIS suggestion, for this particular teak, it is a very easy job, only one insert query, so I simply put it in a stored procedure and then use a exec sql query in my maintenance job.

    BTW, SSIS is really not so convenient as I thought, compared to DTS

  • June 25, 2010 at 1:51 pm

    #1185242

    I am not sure what you mean by convienant but the tools are far more powerful than dts.

    I also understand about it being a simple job I am just saying if performance is a concern then SSIS may be a way to curb that issue. I am almost willing to guarentee a SSIS copy will function faster then a stored procedure going across a linked server. just my oppinoin though.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply