September 12, 2014 at 10:24 am
I work in a mixed environment with everything from SQL Server 2005 to 2012 (and soon 2014). I'd like to utilize the benefits of SSIS 2012 while sourcing and targeting data from other versions.
I have to copy nine multi-million row tables from Server A to Server B prior to processing on Server B. Server A is a 2005 instance, server B is a 2008 R2 instance.
What if I put my SSIS package on Server C (a 2012 instance) but the package still did the same thing, i.e. copy from A to B.
Am I doubling my network load by introducing Server C, i.e. does the data have to flow from Server A through server C to get to B or is Server C just facilitating the connections between A and B?
The larger question is, can I create a separate SSIS instance whose sole job is to wave the baton over SSIS packages in the same way that many people deploy SSRS to separate servers to distribute the web processing component.
I hope I've made myself clear.
"Beliefs" get in the way of learning.
September 12, 2014 at 2:58 pm
I am hardly an expert on SSIS but for the first part of your question: will traffic go from a to c to b. yes it will. The work that your SSIS package does will occur on the server that SSIS is installed on.
For the second part: can SSIS be on a standalone server? yes it can. All the work SSIS does is in memory so be having it run on its own server you would have it competing for memory resources with your database instance.
September 12, 2014 at 3:38 pm
keep in mind that if you install SSIS on a server by itself, you will require at leas one more license.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 15, 2014 at 2:43 pm
In an ideal world, every SQL Server Service should be on it's own server, Reporting Services, Integration Services, Analysis Services and Data Quality Services. This definitely pushes up your licensing cost, but you don't need enterprise for these, standard will do fine (except for DQS which only comes with Business Intelligence version).
Putting SSIS on it's own box has many benefits, mostly by distributing load, in fact it was designed to be used this way. Hopefully though you use dataflows and not Stored procs on the server to move data, which I have encountered before!
September 16, 2014 at 8:01 am
Putting SSIS on it's own box has many benefits, mostly by distributing load, in fact it was designed to be used this way. Hopefully though you use dataflows and not Stored procs on the server to move data, which I have encountered before!
That's what I thought but one of the dba's here was worried that it would double the network load in that it would first have to be copied to the SSIS server before it was then placed on the target server. That's why I posted the question in the first place.
I have subsequently tested this informally, i.e. executed the packages on a 2012 server to move data from a 2005 server to a 2008 server, and did actually see a slight performance improvement in terms of execution time. I'm not going to say this is why performance improved but I am satisfied that performance did not degrade dramatically which was my concern. I'm going to, when I get a chance, create a cleaner test scenario in a dedicated environment.
"Beliefs" get in the way of learning.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply