I am running SQL Server Integration Service(SSIS) and SQL Server Database Service (SQLDB) on different boxes. I can't have SSIS installed on SQLDB box because in my prod. environment, the DB box has *just* DB - no services or no other fancy stuff. So far so good.
Now, if I want to schedule the SSIS packages (deployed on MSDB) to run periodically, I need to create an SQL Agent job (or everyone else here is using a Windows scheduler instead?). SQL Agent has dependency on SQLDB and so it goes on the SQLDB box, correct? Can I run job steps to execute SSIS packages via remote SSIS service (on another box)? If not, I cannot separate SSIS and SQLDB on two boxes and have SQL Agent run the packages periodically, right?
What am I missing?
thanks,
Nitesh
Is there a reason you cannot install DB on the IS box? That would be best for your case as I understand it. You could also keep your packages on your IS box then as well.
To answer your question, there is no current IS feature for running packages remotely. Please see this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=18177&SiteID=1
KirkHaselden wrote:
Is there a reason you cannot install DB on the IS box? That would be best for your case as I understand it. You could also keep your packages on your IS box then as well.
There are two reasons:
Hard Reason --> Our production environment does not allow to install *ANYTHING* on the box that runs the database (i.e. SQL DB). This was the main reason we were not using DTS.
Soft Reason --> For deployment, we would like to have separate tiers for DB, app server components (i.e. SS*S), and presentation components (SPS etc). One may argue that SSIS is a component at DB tier and its scaling, clustering etc will be dependent on the DB tier.
To answer your question, there is no current IS feature for running packages remotely. Please see this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=18177&SiteID=1
I guess I did not put the question correctly.
What I want to know is if I can configure SQL Agent running on machineA to execute SSIS packages using the SSIS Service running on machineB. Note that machineA does not have SSIS Service running (only machineB). The packages themselves can be stored locally on machineB, if needed (but I am hopeful that it should not be an issue and msdb of machineA should work just fine).
Thanks,
Nitesh
If a process on machineA calls a package, then it will run a A, so you need IS installed on A. You can load the package from B or wherever, but the package runs on the same machine as the process which called it and hosts it. You would need a layer of abstraction, which is not currently available in IS itself, that is really remote execution.
Simplest method is to have SQLAgent on B, and call the job, since this is just saying start a job, which means the process runs on B.
|||<quote>Simplest method is to have SQLAgent on B, and call the job, since this is just saying start a job, which means the process runs on B.
</quote>
The problem with having SQLAgent on B is that I will need to have SQLDB also on B (because SQLAgent has a dependency on SQLDB). This defeats the original plan of having SQLDB on machine A and SSIS on machine B separately.|||In my opinion it is the simplest, but not the only way. You can do any form of remote call, such that the server/listener on the remote machine hosts the SSIS process.
You can use any scheduler you like, it is just that SQL Agent can easily be controlled remotely, through T-SQL which most of us are quite happy with already.
I don't see what the issue is with having another SQL instance on the SSIS machine, after all would it not be nice to have a local working store, in fact don't some tasks want a SQL store, and therefore having it locally makes much more sense. I think your network costs could get excessive, just for the sake of keeping things separate. By all means keep server A clean, and have your main data there, but what is the harm in having SQL on server B as well. You like to have separate tiers, but is it so strict you cannot mix applications, should it not be functions really, in which case where is the issue? Having a no SQL and IS on the same machine at all, regardless of the use of SQL seems rather artificial and just making life hard for the sake of it. Offloading IS onto a separate machine makes sens as well for large volumes, to reduce impact on your main production SQL server, but I woudl argue that the IS box should be the controller of the IS based processes, and not the other way around.
Saying all that remote execution would be cool to have too.|||
In my opinion it is the simplest, but not the only way. You can do any form of remote call, such that the server/listener on the remote machine hosts the SSIS process.
Unfortunately, my prod environment doesn't allow for the simplest solution, that I would love to have.
You can use any scheduler you like, it is just that SQL Agent can easily be controlled remotely, through T-SQL which most of us are quite happy with already.
I would love to use SQL Agent. Since it has a dependency on SQLDB, I cannot have it on SSIS box. If SQL Agent could invoke SSIS remotely, I would have take n that.
I don't see what the issue is with having another SQL instance on the SSIS machine, after all would it not be nice to have a local working store, in fact don't some tasks want a SQL store, and therefore having it locally makes much more sense.
Issue is simple - it won't be supported by our database group. Our DB group controlls all the DB instances, and they have nothing at all on those boxes which they support. So, SQLDB on SSIS box won't be supported. I don't want to get calls in the middle of the night for that and neither do I want to start doing backups, maintenance etc.
I think your network costs could get excessive, just for the sake of keeping things separate.
I know lot many enterprises that enforce such policy of having clean DB boxes.
I thought that network costs for such configurations will not adversely impact performance too much. Am I wrong?
By all means keep server A clean, and have your main data there, but what is the harm in having SQL on server B as well. You like to have separate tiers, but is it so strict you cannot mix applications, should it not be functions really, in which case where is the issue? Having a no SQL and IS on the same machine at all, regardless of the use of SQL seems rather artificial and just making life hard for the sake of it. Offloading IS onto a separate machine makes sens as well for large volumes, to reduce impact on your main production SQL server, but I woudl argue that the IS box should be the controller of the IS based processes, and not the other way around.Saying all that remote execution would be cool to have too.
Bottomline is -> It is not important what I could do to get around the issue, it is more important that the product should be deployable in a layered/tiered fashion on separate boxes with support for scheduling packages on whatever is the preferred scheduling mechanism (i.e. SQLAgent). Please understand that there exist network deployment scenarios in enterprises that might not work with having a side-kick SQLDB instance on SSIS box.
Thanks for your suggestions though.
Nitesh
|||
Issue is simple - it won't be supported by our database group. Our DB group controlls all the DB instances, and they have nothing at all on those boxes which they support. So, SQLDB on SSIS box won't be supported.
Somewhat of a retorical question or point, but if your DB group does not understand SSIS then they should not allow you to use it. Your use of it could adversely impact the avilability of the pure SQL systems, I would never allow any system to interact with my SQL systems that could have such an impact unless I had sufficient understanding of it. If they did understand it, then what is the problem :) This is my personal point, so no response required, but it seems an artificial rule.
I thought that network costs for such configurations will not adversely impact performance too much. Am I wrong?
I was trying to highlight the time and bandwidth costs. The further away from your data your SSIS is, the slower it will be, although you could solve some of that by spending money on faster links. So for large amounts of data processing it could have an impact since you will be running across the two machines.
...for scheduling packages on whatever is the preferred scheduling mechanism (i.e. SQLAgent).
SQL Agent is my preferred scheduler, and maybe yours too, but many organisations have their own standards. Many houses use IBM/Tivoli for example, and SQLAgent is not required for SSIS. If you would prefer to use it, then yes you are stuck. I think buyng SQL Server for SQLAgent is an overkill, but if you look at that SQL Instance in the same way as you look at MSDE, then it is no big deal. There are plenty of proucts our there that install DB engines. I saw a report that said Informix (I think that was the one) was one of the most widely deployed DBs, and that is because CA shoved it under the covers of ArcServe backup, probably for the scheduling :)
