3

Right now both SSRS, SSAS and SQL Server Engine are on the same server. We are using 2008R2 Enterprise. We are thinking to upgrade to SQL Server 2012 or 2014. and We want put on separate serve the SQL Engine and SSRS,SSAS on SQL Server 2012 or 2014.

What we want is:

Server 1 for SQL Server Engine

Server 2 for SSRS and SSAS

My questions are:

  1. How they talk each other(the two Servers)?
  2. Where the cube for SSAS stored?
  3. Where the report builder build report?
  4. Can you give my high level explanation how server 1 and server 2 interact or direct me some document I can read?
Improve this question

1 Answer 1

Reset to default
2

1.How they talk each other(the two Servers)?

From the database engine perspective, both SSRS and SSAS are clients like any other application. These query data from SQL Server using SQL queries. Microsoft just chose to include these components with SQL Server licensing and media.

2.Where the cube for SSAS stored?

On the server with SSAS installed (server 2).

3.Where the report builder build report?

On the server with SSRS installed (server 2).

4.Can you give my high level explanation how server 1 and server 2 interact or direct me some document I can read?

In addition to what I mentioned in #1, client applications can query the SSAS server without involving the database engine at all after cubes are built. However, SSRS has a dependency on the reporting server databases as well as data sources that may also be on SQL Server.

Below are a couple of architecture diagrams you might find useful:

SSAS: https://msdn.microsoft.com/en-us/library/bb510502.aspx

SSRS: https://msdn.microsoft.com/en-us/library/ms157231.aspx

Improve this answer
8
  • Now all the data ,cubes and everything are on server 1 d: drive. when we separate the server. Is going to be the data on server 1 d: drive where as the cubes and report builder stay on server 2 lets say d:drive?
    – SQL_DBA_Bini
    Commented Jul 8, 2015 at 14:08
  • when we open one of the cube is the data comes from where server 2 drive at or there is a pointer to direct to where the data stored (server 1 d: drive) ? I am wondering whether the cube will be as a big as the original data size or not?
    – SQL_DBA_Bini
    Commented Jul 8, 2015 at 19:45
  • @SQL_DBA_Bini, the general rule of thumb is SSAS disk space needs are about 20% of the database size. Your space needs may be higher if you have large fact tables or large dimensions.
    – Dan Guzman
    Commented Jul 9, 2015 at 1:33
  • 1
    @RBT, those features can be installed independently as you said. Note that SSIS is a shared component and not tied to a specific instance. This is a consideration when multiple instances (default and/or named instances) are installed on the same box.
    – Dan Guzman
    Commented Jun 17, 2017 at 12:15
  • 1
    @RBT, SQL Server setup will create a new instance if you don't already have a SQL instance installed. If you already have one or more instances, you can select which one to add the SSAS feature to. Although you could create a new instance for SSAS along side of other instances, that would typically be done in specialized scenarios like different SQL versions on the same box.
    – Dan Guzman
    Commented Jun 17, 2017 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.