none
Single SSISDB vs Multiple SSISDB for different Environments

    Question

  • Hi,
    Most companies have development, testing and production environment. I would like to know where should we put SSISDB if we are using project deployment.
    Example:
    Development environment has 2 database servers: DevAppA, DevAppB
    Testing environment has 2 database servers: TestAppA, TestAppB
    Production environment has > 2 database servers: ProdAppA, ProdAppB, ProdAppC, ProdAppD
    Questions:
    1. Should I have a single standalone SSISDB share for all environments or I need to install the SSISDB on each database servers?
    2. If I only have a single SSISDB installed, the SQL Server Agent is running on each database servers or run from the SSISDB server?
    3. How the project deployment works? E.g. If there is a single package I need to fix, am I going to deploy the fixed package or all packages since this is a project deployment?
    Thanks,
    Kenny
    Monday, September 30, 2013 4:16 AM

Answers

  • Hi Kenny,

    1. You can choose either scenario, but considering you have dedicated dev, test  and production servers I would personally use one of each environment for SSIS storage and execution. So one i dev env for SSIS_Dev, one in test env for SSIS_test and one in prod env for SSIS_prod. SSISDB deployment provided you with environment that can be used for each project. So you could have one SSIS installation for dev, test and prod by configuring different project environemnts for each purpose.
    2. If you choose sigle SSISDB scenario than you would normally excecute SQL agent jobs from that SQL server. But, nothing is stopping you to run SQL agent jobs from different SQL server, but why would you do that.
    3. In project deployment single point of deployment is project, so if you make changes in one package file in the project the whole project is deployed meaning that all packages are redeployed to SSISDB. But since you have same version of packages in project and is SSISDB (VersionMajor, VersionMinor, VersionBuild) nothing actually happens with packages that did not changed, they are redeployed to the same version they were on before the deployment.

    HTH, Regards, Dean Savović

    Monday, September 30, 2013 8:35 AM
  • Hi Kenny,

    Considering your server topology I would install one SSISDB catalog per environment:

    1. development
    2. test
    3. production

    Deployment to this environments is very easy and can be done from SQL Server Data Tools (Visual Studio). You should also have a separate solutions for development, test and production environment. This can be easily handled through branching and merging in TFS Source Control Explorer.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by Kenny Kee Friday, October 11, 2013 3:41 AM
    Thursday, October 10, 2013 11:30 AM

All replies

  • Hi Kenny,

    1. You can choose either scenario, but considering you have dedicated dev, test  and production servers I would personally use one of each environment for SSIS storage and execution. So one i dev env for SSIS_Dev, one in test env for SSIS_test and one in prod env for SSIS_prod. SSISDB deployment provided you with environment that can be used for each project. So you could have one SSIS installation for dev, test and prod by configuring different project environemnts for each purpose.
    2. If you choose sigle SSISDB scenario than you would normally excecute SQL agent jobs from that SQL server. But, nothing is stopping you to run SQL agent jobs from different SQL server, but why would you do that.
    3. In project deployment single point of deployment is project, so if you make changes in one package file in the project the whole project is deployed meaning that all packages are redeployed to SSISDB. But since you have same version of packages in project and is SSISDB (VersionMajor, VersionMinor, VersionBuild) nothing actually happens with packages that did not changed, they are redeployed to the same version they were on before the deployment.

    HTH, Regards, Dean Savović

    Monday, September 30, 2013 8:35 AM
  • Thanks Dean.

    In production environment, I have > 10 database servers. All database servers have the same table schema, job, etc. It design as Multi-Tenant architecture.

    Do you think it is better to have a single SSISDB or each database servers need to install SSISDB? I need to make sure the project deployment is easy, performance of job is not affected by the design and so on.

    Thanks,

    Kenny

    Thursday, October 10, 2013 6:34 AM
  • Hi Kenny,

    Considering your server topology I would install one SSISDB catalog per environment:

    1. development
    2. test
    3. production

    Deployment to this environments is very easy and can be done from SQL Server Data Tools (Visual Studio). You should also have a separate solutions for development, test and production environment. This can be easily handled through branching and merging in TFS Source Control Explorer.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by Kenny Kee Friday, October 11, 2013 3:41 AM
    Thursday, October 10, 2013 11:30 AM