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?
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.
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?
jtriftsMI and AutomationAuthor Commented:
Your help has been gratefully received! Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.