I have a remote SQL 2012 server on which I am not allowed to create linked servers, run SSIS, or SSRS. We'll call that "DB Server" I have an applications serer on which I have installed and am running Visual Studio 2008 R2 and have created several SSIS packages to load and report data to that SQL server. We'll call that "App Server."
From within the SSIS designer on the App Server, I am able to execute these packages perfectly. The data is uploaded to the Db Server,
I need to create SQL Server jobs on the DB Server that will run these packages on the App Server.
I am not allowed to run any applications on the DB Server. I am, however, allowed to create SQL Server Agent jobs.
The person managing the App Server will have all necessary permissions to the DB Server to run Agent jobs, though my guess is that the jobs will run under a service account of some kind.
I'm uncertain how to execute jobs on a remote server when you don't have SQL installed on the App server, aside from what little bit of SQL is installed along with Visual Studio 2008 R2.
Any guidance would be helpful. The whole goal here is the separate the Application server from the DB Server. It's just that this requirement seems to make executing jobs on the DB server a little problematic, especially when what will be executed are SSIS projects located on the App Server,
So, the SSIS packages will need to reside on the App server but be executed from a scheduled job on the DB server. I want to use the SQL Server Agent to schedule these packages in large part because of the logging and the email notifications. I'll also be creating a job that will email out a set of reports to email addresses stored on the DB once the data is loaded.
In general, this project has two parts. The first is processing data into the database. I need to use scheduled SQL Server Agent jobs that will execute SSIS packages on the App Server to do that. By the way, they won't let me create linked servers. All they'll let me have on the DB Server is the database and SQL Agent jobs. I can have some stored procedures and views, but I cannot execute any command shell commands.
The second part is reporting and emailing out what will probably be Crystal Reports or perhaps SSRS reports. From what I can tell, they won't let me even install SSRS on the DB Server, so I'm not sure what to do about that. Can I create Crystal reports in Visual Studio 2008 R2 and have them executed and emailed out from a job on the DB Server? The app server cannot be licensed for SQL Server, but it can be licensed for Visual Studio 2008 R2.
The point is that this project will have no SQL or Application support from this company's IT department. They will have no one available to manage jobs, change or reschedule them, for example, so pretty much all of that needs to be integrated into the App Server. They will be in charge of the DB Server in terms of security and backups, but that's it. The person managing the App Server is NOT a DB Admin but knows some SQL, so I'm having to make things as easy as possible.
From the App Server, how can I execute SQL Server Agent jobs that reside on a remote DB Server, which execute Packages that reside on the App Server? That's my basic question. But the larger question is how you totally separate SQL Database and SSIS on different servers when you have SQL 2012 on one server and only Visual Studio 2008 R2 on the other?