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
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/
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/
November 21st, 2011 3:20am