August 25, 2009 at 3:50 pm
Hi,
Could anyone tell me whether it is better for a company to have one central server for SSIS ans SSAS or it is better to install SSIS and SSAS with each SQL Server installation (20-25 SQL Instances exist in our company)? Could you also tell me the Pros and Cons?
Thank You,
Best Regards,
SQL Buddy
August 25, 2009 at 6:19 pm
This is a decision to be made by the db and server groups within your company.
I prefer to separate SSIS off onto it's own server - we call them process servers. From the process server we have several apps that may run concurently from different platforms accessing one server or another. We see an improvement in resource utilization on the DB servers by offloading application processes to a different server. We see it as being like having your webservers separate from your DB servers.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 25, 2009 at 8:34 pm
CirquedeSQLeil (8/25/2009)
This is a decision to be made by the db and server groups within your company.I prefer to separate SSIS off onto it's own server - we call them process servers. From the process server we have several apps that may run concurently from different platforms accessing one server or another. We see an improvement in resource utilization on the DB servers by offloading application processes to a different server. We see it as being like having your webservers separate from your DB servers.
Just make sure with this setup that you are properly licensed to use SSIS on a separate machine. As far as I know, SSIS is licensed with SQL Server and if installed separately needs to be licensed separately.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 26, 2009 at 9:42 am
Hi Jason,
Thank You very much for your reply. I really appreciate your help.
That was what I was looking for.
Thank You,
Best Regards,
SQLBuddy.
August 26, 2009 at 9:45 am
Hi Jeffrey,
Thank you very much for your help. That's really a very good point.
I will make a note of that.
Best Regards,
SQLBuddy
August 28, 2009 at 11:13 am
Could anyone tell me , if we install SSIS on a seperate server dedicated for SSIS, should we also install SQL Server DB Engine on that machine? Also, should we install workstation components on that server ?
Thank You,
Best Regards,
SQLBuddy
August 31, 2009 at 8:48 am
Could anyone tell me , if we install SSIS on a seperate server dedicated for SSIS, should we also install SQL Server DB Engine on that machine? Also, should we install workstation components on that server ?
ANY HELP?
Thank You,
Best Regards,
SQLBuddy
August 31, 2009 at 9:50 am
sqlbuddy123 (8/31/2009)
Could anyone tell me , if we install SSIS on a seperate server dedicated for SSIS, should we also install SQL Server DB Engine on that machine? Also, should we install workstation components on that server ?ANY HELP?
Thank You,
Best Regards,
SQLBuddy
Here is the best technical answer I can give: It depends... 🙂
Do you need the DB Engine? No
Do you need the Workstation components? No
However, you have to purchase licenses for SQL Server on that machine - and having an extra instance of SQL Server that you can use wouldn't hurt.
As for the workstation components - it won't hurt to have them installed on the server. Some will say they never install these on the server, others will tell you that they do. It really depends upon how you are going to manage things - and whether or not you will ever need to modify an SSIS package when connected remotely to the server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 31, 2009 at 10:28 am
Hi Jeffrey,
Thank you very much for your reply. I really appreciate your help. It has clarified most of my doubts
Could you please correct me if I am wrong,
1. SSIS packages are stored on the SSIS Server.
2. And they are executed on the SQL Server Instances to which the packages point to.
3. Also, they are scheduled using the SQL Server Agent Jobs on those SQL instances
For instance, if we have a "SSIS package A" deployed onto the SSIS server and that package A refers to the SQL Server Instance A. When the package has to run at its scheduled time, SQL Server A connects to the Package A on the SSIS and gets the required work done on the SQL Server A using that package. In this way we store the packages of all SQL Server instances on this central SSIS server and schedule those packages to be executed using the SQL Server Agents.
Also, could you please tell me whether it’s better to store the Package configuration files on the SSIS server or on each individual SQL Servers.
Yours,
SQLBuddy
August 31, 2009 at 12:01 pm
Item 1: Not necessarily...packages can either be stored in the file system (on the SSIS Server), or in a SQL Server store, or in MSDB for a particular instance. If you use SQL Server to schedule the packages - that package is going to run on the instance where it is scheduled.
Item 2: No, SSIS packages are run on the system where they are scheduled and executed. If you setup an agent job on ServerA - that package will run on ServerA even if the package exists on ServerB.
Where the SSIS package runs has nothing to do with the connections used in the package.
Item 3: SSIS packages can be executed using any scheduling software you choose. They can be scheduled to run using SQL Server Agent jobs, Task Scheduled, or some other software. Where they are actually run depends upon how that job is setup and whether or not it is capable of submitting and starting jobs on remote hosts (not likely).
As for you last question - again, the answer is: It depends. It depends on how you want to schedule them, how you want to manage them, what access you want to grant to developers, what change control you put in place, etc...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 31, 2009 at 1:14 pm
Hi Jeffrey,
What a fantastic answer! Tremendous indeed! It has dispelled all the myths and misunderstandings that were revolving around my head. You have made it crystal clear. I had never seen such a lucid explanation.
Thank you so much for such an excellent and detailed answer.
Yours
SQLBuddy.
August 31, 2009 at 1:21 pm
I understand this may sound overly cautious, but if you store SSIS packages on the server then there is always the chance that someone could hack in and get the package, if they contain remote connection information, (and i'm guessing they would if your off loading the SSIS jobs), then it would probably be safer to store them within the MSDB.
I know nothing is completely "hack" proof. And I'd love to hear on this subject from some of the folks that posted earlier, but from reading the 70-445 book, and deploying packages myself, I try not to leave anything on the file server that doesn't have to be there.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
August 31, 2009 at 1:26 pm
Brad (8/31/2009)
I understand this may sound overly cautious, but if you store SSIS packages on the server then there is always the chance that someone could hack in and get the package, if they contain remote connection information, (and i'm guessing they would if your off loading the SSIS jobs), then it would probably be safer to store them within the MSDB.I know nothing is completely "hack" proof. And I'd love to hear on this subject from some of the folks that posted earlier, but from reading the 70-445 book, and deploying packages myself, I try not to leave anything on the file server that doesn't have to be there.
I wouldn't say this was overly cautious - but indeed, some of the considerations on where you deploy the packages. I would recommend using package configurations to store this kind of information - either in a database or an encrypted file.
Either way - I would be using Windows Authentication for all connections where that is possible. That way, the service running the job must be running with the correct service account - or, be able to impersonate the service account, in which case - you start to get into using Kerberos authentication and all that entails.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply