Well, 2.5 billion is a large set :) When I was dealing with data of that size what I had to do to avoid timeouts was disable parallel processing in certain parts and enable it in others. It's going to depend on your data and structure though on how best to do it. Is it a single table? I was dealing with multiple large tables so I kept each table a single stream but ran them concurrently. I also had to break up the export into chunks of 100k since I was dealing with a geographical conversion (moving it about 3k miles so bandwidth was a consideration.)
Nathan Palmer On Wed, Jun 5, 2013 at 7:14 PM, TJ Roche <[email protected]> wrote: > So I have around 2.5 billion records that i am trying to take from sql > server on one system to sql server on another system. > > Obviously this is slightly beyond the bounds of the standard input command > -> batch/bulk command scenario. > > Is there an accepted methodology to use here? > > The way that keeps tickling my brain is to split the query using some > combination of querying the system table partitions to gather the count, > the paged sql here > http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/ > and > some kind of parallel PartialProcessOperation, I can get some decent > throughput, 10million in around 40 sec, but when I point it to the full > result set I receive a SqlTimeoutException. > > Which reveals a fun little bit of microsoft shenanigans, apparently the > SqlTimeoutException will also throw if you have exceeded the number of > available connections in the pool or if they are all busy when a request > comes in. > > So I may be OVER paralleling the query. *sigh* > > Any help would be greatly appreciated. > > > > > > > > -- > You received this message because you are subscribed to the Google Groups > "Rhino Tools Dev" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
