How to transfer large amount of data efficiently between SQL server automaticaly ?
I have a SQL table around 6GB and need to be transfered to another SQL server houly. I had setup a SSIS package to transfer them but always get error message"communication link failour". Any idea to improve it? Thanks.
November 21st, 2011 2:42am

what source and destination you used? did you used bulk load? did you set any bulk size? did you applied Data Flow Performance Tips on your data flow? take a look at this great article about SSIS Data Flow performance: http://www.simple-talk.com/sql/ssis/sql-server-2005-ssis-tuning-the-dataflow-task/http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 21st, 2011 2:50am

Hi, You can definately use the SSIS Components to do that. Check this article where they are migrating the data from local to cloud. Same way you can change the destination to another local server to move it completely http://beyondrelational.com/blogs/parasdoshi/archive/2011/07/18/how-to-use-sql-server-integration-services-ssis-to-migrate-data-from-sql-server-to-sql-azure-lt-lt-paras-doshi.aspxThanks Karthikeyan Anbarasan http://f5debug.net/
November 21st, 2011 2:51am

As other pointed you can use fast load feature of OLEDB provider. But also take a look at this blog http://www.dfarber.com/computer-consulting-blog.aspx?filterby=Batch%20processing%20records%20in%20MS%20SQLBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Free Windows Admin Tool Kit Click here and download it now
November 21st, 2011 3:03am

Also as suggested you can use the Bulk Insert task to insert it with much performance. Check this article to get the step on how to use the Bulk insert task http://beyondrelational.com/blogs/tejas/archive/2009/10/28/sql-server-ssis-bulk-insert-task.aspxThanks Karthikeyan Anbarasan http://f5debug.net/
November 21st, 2011 3:19am

yet another option to do a bulk insert is you can use the BCP utility which performs a command line update. Check this article to get the step on how to use the BCP Command line utility http://beyondrelational.com/blogs/nakul/archive/2011/05/02/bcp-amp-bulk-inserts-underappreciated-features-of-microsoft-sql-server.aspxThanks Karthikeyan Anbarasan http://f5debug.net/
Free Windows Admin Tool Kit Click here and download it now
November 21st, 2011 3:20am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics