April 1, 2012 at 5:10 am
Hi I am having an issue copying table data from one server to another since the data grow much more faster in volume and it is taking long than I expect ; currently I am using a SSIS package to copy data from one server to another and this is taking longer time please can you tell me how can make this process faster ? ;Or another process that I can follow to improve the data copying.
April 1, 2012 at 6:47 am
If you're just moving data from a single server to a second server then you need to tune the procedures and processes in place. If you're trying to copy the data in real time or near real time, I wouldn't suggest using SSIS. Instead I'd look at setting up mirroring or transactional replication depending on what exactly you're trying to copy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2012 at 9:29 am
Are you looking at copying the changes across or the complete table? If the table is growing then the time taken to copy will increase.
Have you looked at :
1. SSIS with CDC (enterprise only)...with this you would be able to capture the changes on one server to be forwarded onto the second server.
2. SSIS with change tracking
3. Log shipping
4. Replication
Fitz
April 2, 2012 at 8:42 pm
Thank you very much for the replys.
I have already done a SSIS pkg and still its slow copying data across the servers ?
Also on SSIS i do use the link server to communicate with other servers does this make it slow ? not sure .
April 3, 2012 at 4:40 am
I wouldn't use a linked server within SSIS. It should be able to connect directly to both servers in question. Is that the cause of the problem? Not sure since I can't see what you're doing. But it's possible.
Again, sounds like tuning your queries and your structures is the way to go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2012 at 4:53 am
Grant Fritchey (4/3/2012)
I wouldn't use a linked server within SSIS. It should be able to connect directly to both servers in question. Is that the cause of the problem? Not sure since I can't see what you're doing. But it's possible.Again, sounds like tuning your queries and your structures is the way to go.
+1.
If you use SSIS, you need NOT to go for Linked server. Check the wait stats in server for any preemptive_* Or Async_Network_IO types. You would get a rough idea of it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply