SQL Server, SSIS, SSAS, SSRS - Deployment Architecture

Good day,

I have been asked to get involved with the installation and configuration of a SQL Server BI environment.

I am seeking some guidance for the deployment architecture for SQL Server, SSIS, SSAS, and SSRS as well as SSMS and Visual Studio.

To date, my experience with these products have had all products installed on a single server, with a fairly straightforward, default installation (and training modules a la AdventureWorks).

Given that I have been provided with a development server for DB (Windows 2012R2) and separate server for the apps tier (IIS), I am seeking information/clarification as to which products need to be installed on which server and in which order.

Added into this, is the requirement for SSIS to connect to an Oracle database leveraging Microsoft (Attunity) Connectors for Oracle.

QUESTION: Can you please confirm which of the following products should be installed on which server? (and references/links to the Manual(s) which I should be reading would also be appreciated. Any links to architecture diagrams depicting a similar architecture (with or without Attunity) would be a significant bonus.

MS SQL Server 2016
MS SQL Server Data Tools (2016), including
   - MS SQL Server Analytic Services (SSAS)
   - MS SQL Server Integration Services (SSIS)
   - MS SQL Server Reporting Services (SSRS)
MS SQL Server Management Studio
MS Visual Studio 2015
Microsoft (Attunity) Connectors for Oracle and Teradata (v4)
MS SQL Server Integration Services Attunity Oracle CDC Designer/Service Feature Pack
Oracle Client

Thank you in advance.
jtriftsMI and AutomationAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Server engine should be installed in a dedicated server. Do not install nothing else (besides the SSMS if you want to) in this server. This is the most important server that you'll have and you want to keep it the more clean possible with less people touching on it.

SSRS should be also installed in a dedicated server. The Reporting Service database can be stored in any SQL Server instance that you have.

SSIS usually consumes a lot of resources, depending on what the packages do.
SSAS can be installed in the same server where's SSIS if the server has plenty of free resources and SSAS cubes doesn't consume much resources also. Otherwise, think on install it in a dedicated server as well.

Visual Studio and Data Tools are development tools so they should be installed in a development machine. If the developers need to perform ad-hoc operations on the databases then install SSMS in the same server as well.

I don't really know what's the Attunity but if it's something needed by SSIS packages then install it in the SSIS server, otherwise I guess it can be installed in the development machine.

The Oracle Client should be installed in all servers that need to connect to an Oracle database.
jtriftsMI and AutomationAuthor Commented:
Good morning Vitor,

Thanks for your comment. It is greatly appreciated.
Just want to make sure I have interpreted your reply correctly. Are you saying the following:

DB Server:
   SQL Server (including the database which hosts the data for reporting)
   SSMS (if required)
Integration Server:
   SSAS (or alternatively on separate dedicated server)
   Oracle Client
   Attunity Connector (CDC scraping Oracle Logs)
Reporting Server
Developer Laptop/Machine:
   Visual Studio
   Data Tools
   SSMS (if required)

Does the above match with your thinking, or have I misinterpreted in some way?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Does the above match with your thinking, or have I misinterpreted in some way?
Yes, you really understood my comments and you've resumed it perfectly. I don't have nothing more to add.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jtriftsMI and AutomationAuthor Commented:
Your help has been gratefully received! Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.