patrick_wolf (8/5/2015)
Hi allWe are currently migrating from SQL Server 2008 R2 to SQL Server 2014. Our environment consists of three independent SQL instances (Development, Integration, Production) and we are dealing with 600+ SSIS packages. Our BI team consists of 10+ developers who are working largely in parallel. We are using source control (SVN) for managing our development artefacts across all three stages and we are using a self-developed tool for transporting packages from development to int to prd.
One question that we are currently trying to answer is wether it is a good idea to switch to the Project Deployment Model in SQL Server 2014? We have identified the following pros and cons and I am very interested to read about your experiences regarding the practical use of the Project Deployment Model. Does it perform well in a multi-developer, multi-stage environment? What project size would you recommend to use? Have you refrained from using the new model? Why? Thank you very much for your thoughts on this topics.
+ Catalog supports better organization of packages (folders, projects) than package model/msdb
+ More fine-grained management of catalog permissions
+ Automated logging and standard reporting (however, we do have a self-developed logging solution in place)
+ Different environments and environment variables supported (for DEV/INT/PRD)
+ Project Parameters introduced.
- There is no way to deploy a single package to the server (this will be supported in SQL Server 2016). A project is the deployment unit. This might be challenging if a project consists of a large number of packages. And it is challenging in a multi-developer environment. However, this can be mitigated using automated build mechanism based on a source control system (create project from checked-in packages). In this scenario, developers will still deal with packages. A tool in the background will checkout all packages belonging to a project and will build the ispac file for deployment to the server automatically.
- Performance Problems (?) (=> according to my research, there may occur timeouts when developing large projects to the SSISDB-catalog; what is your experience?)
- Required efforts for adopting the new approach (e.g. changes of processes and self-developed tool)
I am looking forward to your replies.
Kind regards,
Patrick
Good post. You've clearly got to grips with the pros and cons.
I've been using the project deployment model since 2012 and, IMO, the benefits far outweigh the negative aspects.
We do deployments directly from source control using TeamCity. No manual intervention is required. Build/Deployment time increases in line with the number of packages in a project, and their complexity, so don't go down the single-project route. Instead, divide your packages into logical groups and create projects for these.
You'll have config to do in SSISDB for each project, but it's not that onerous.
For me, knowing that the version of a package which has been deployed is definitely the same as that in source control – it has to be – adds a level of comfort. When you are used to deploying packages separately, this will make you nervous at first. But you'll get over it quickly.
I suggest that you change the SSISDB defaults for 'Retention Period' and 'Maximum Number of Versions per Project' – we use 10 and 3 respectively. Using the larger values will lead to a huge SSISDB and potential performance problems. And if one day you decide to run the 'SSIS Server Maintenance Job' after reducing the retention period, don't be surprised if the job takes many hours.
Perhaps you should 'try it out' with a single project and see how you get on.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.