SQL Server 2014: SSISDB vs MSDB for package deployment
I'm currently in the process of upgrading from SQL Server 2008R2 to 2014 (both Enterprise). There are a plethora of SSIS jobs that are in production, and which will need to be migrated. I'm trying to get a handle on how I should manage SSIS jobs going forward.
In 2008R2, I would always using BIDS to deploy packages to MSDB. All permissions were then controlled through SQL Server.
In 2014, I see that you can still save to the file system or MSDB, but that there is now the SSISDB that you create as an Integration Services Catalog. This method clearly allows a lot more flexibility with the addition of easy variable access and even environment variables.
Is deploying SSIS packages to SSISDB in 2014 now the best practice way of deploying and managing SSIS projects, rather than to deploy to MSDB? Am I still able to manage permissions? When I backup SSISDB, are all of my deployed projects backed up (like before with MSDB)? Lastly, when I schedule these packages via the SQL Agent, do they still behave the same, where the permissions of the SQL Agent service account and job owner determine the SSIS package permissions when it is run?
Many thanks to anyone who can help. I've been on Microsoft's site all day, and while the documentation is very helpful, it doesn't actually answer these questions specific questions.
Answer
I recently took the SSIS Exam (70-463) so i can tell you some things about the new deploy model.
Short Answer:
Yes SSISDB is Best Practice. Packages can be deployed to SSISDB. Packages keep a deployment history (like a very basic version control) so you can even rollback some revisions of your package.
Main Advantage of the new model is the configuration. You don't need XML or dedicated SQL tables to save your configurations. You can use input parameters and map them with enviroments defined on sql server.
You can manage security through SQL Server because now everything can be handled via SQL Server Security.
Another cool feature is the Integration Services Dashboard, a report automatically built with report services template. Just click Integration Service Catalog and right click your packages to view "All Executions".
You can see very detailled Information about your packages including execution time.
Long Answer: In my opinion the main advantage are the project parameters. Imagine this like Parameters you can pass to the SSIS Package. You can parametrize your Connection Manager or just parts of it.
Example: You can parametrize the server name and in your ssisdb you can create two enviroments (or more) called "development" and "production". Then you can add variables to both of them and map them to the input parameter of your package. The main advantage is that you can deploy a package to SSISDB and link to an environment and you don't have to handle the connection strings by yourself.