SSDT server setup and using 3rd party tools across servers

  • dmusson17

    Ten Centuries

    Points: 1235


    We are a small shop and want to run, for data warehousing/reporting, 2016 standard across two boxes, a "db" server and an "etl" server.  The db server would just have db engine installed and the etl server would have SSDT/SSIS/SSAS/SSRS.

    Is this possible without installing an instance on both servers?

    If we could get away with only installing an instance on the db server, where we also would publish ssis packages to the SSISDB and schedule them in SSA, what considerations do we need to take into account regarding the third party tools we need in SSDT (zappysys, zip, winscp, etc) and have installed on the etl server?  If these tools are installed on the etl sever but ssis packages are deployed to the db server, how do we ensure they can be accessed by the published packages?

    Any advice/direction/correction are much appreciated.



  • frederico_fonseca


    Points: 14705

    you can't.

    SSIS Catalog packages execute on the server where the catalog is installed so your setup needs to be

    Server A

    - SQL Server Instance

    - - User databases

    - - ReportServer db's - (optional but not advisable)

    Server B

    - SQL Server instance

    - - SSIS Catalog

    - - ReportServer db's - (optional if you choose to install its db on the application server - not advisable)

    - SSIS Integration Services (required to create SSIS Catalog and to allow execution of DTS Packages from the command line

    - SSAS instance

    - SSRS Instance

    - - ponting to either ReportServer db's on Server B or Server A (Not advisable)

  • Phil Parkin

    SSC Guru

    Points: 244656

    Frederico is spot on.

    In addition, note that SSDT is a client tool and should, therefore, not be installed on either of your servers.

    • This reply was modified 6 months, 2 weeks ago by  Phil Parkin. Reason: Fix typo

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See for details of how to post T-SQL code-related questions.

  • dmusson17

    Ten Centuries

    Points: 1235

    Thanks guys.  All makes sense.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply