We’re trying to troubleshoot some thorny networking issues, and I need to know more about how ADO.net data transfer works behind the scenes - specifically for old-school business unit SQL Server data sources, if that makes a difference.
I know for sure is that data is read from the data source and written into the destination table in the staging database, but I’m fuzzier on what happens between the two places. To start off with, I have two key questions:
We’re using a tool server to run TimeXtender, and a different server to host SQL Server and the TimeXtender databases. Is data routed through the that tool server when it is read via the ADO.net package? I assume that it is, but we need to know for sure.
The other question I have is what happens after the data is pulled from the data source? Is it pulled into memory somewhere? If so, where? The source server, the tool server, the destination data server, or some combination of the three? Is the data ever written to disk anywhere before it is written into the destination table? Or does it skip memory/disk altogether via some continues flow of data between the source and destination?
Based on the fact that TimeXtender appears to first do a SELECT in the source, then an INSERT on the destination, I’m guessing the data is cached in memory in at least one place, probably the tool server, then written to the destination table. But again, we need to be sure in order to understand the issue we’re having, and to be sure that we’re providing enough memory (and/or disk space) for the application to run our execution packages smoothly.
Thanks in advance for your help!