Back in my day, we stored SQL Server Integration Services (SSIS) packages in msdb, or the file system. Well, I’m neither dead nor retired, so that’s not an accurate statement–today is still “my time.” But when I was learning about SQL Server, those were the only two options. If you or your environment predate SQL Server 2012, then you might only be familiar with those two options.
In SQL Server 2012, Microsoft introduced the SSISDB. Since SQL Server Integration Services (SSIS) is its own service, it deserves its own database. In a service-oriented world, it is discouraged to put data for one service (the database engine) in the same database as another service (integration services).
The docs do a pretty good job at an introductory explanation of it:
The SSISDB catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the Integration Services server. For example, you set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations.
…
You access the SSISDB catalog in SQL Server Management Studio by connecting to the SQL Server Database Engine and then expanding the Integration Services Catalogs node in Object Explorer. You access the SSISDB database in SQL Server Management Studio by expanding the Databases node in Object Explorer.
SSIS is basically just the database that supports the SSIS service. SSIS packages and service metadata are stored in SSISDB.
Where do SSISDBs come from?
I found this to be really interesting–it’s really what spurred me to write this post.
You create the SSISDB database in SSIS by simply right-clicking on the “Integration Services Catalog” node in Object Explorer & selecting “Create Catalog…” then stepping through the wizard.
But I got curious about what actually happens when you get to the end of that wizard. How does Microsoft actually ship that database definition?
You need to install SSIS first
It all starts with having the SSIS components installed on the server itself. If you want to create an SSISDB on a server, you must first install the SSIS components on that server. When that component is installed, the installer drops a backup file(‼) on the server. The file is located at INSTALLSHAREDDIR\\DTS\Binn\SSISDBBackup.bak. The INSTALLSHAREDDIR location is configurable during install, but if you use the defaults during install, the file will be located on your database server at C:\Program Files\Microsoft SQL Server\150\DTS\Binn\SSISDBBackup.bak (where 150 is the version number for SQL Server 2019).
Then, the SSIS Catalog Creation Wizard in SSMS begins by restoring that backup file to your instance. In fact, if the SSIS components aren’t installed, the Catalog Creation Wizard will fail with a message that it cannot find that backup file! This error message gives no indication that the solution is to install the SSIS components. 🤦♂️
Poking at the backup
I was curious about the backup, so I took a look at the header metadata in it:
RESTORE HEADERONLY FROM DISK = 'C:\MSSQL\Backup\SSISDBBackup.bak';
Here’s a subset of the columns returned–just the ones I found interesting (There’s no great insight gained by examining these–it’s just interesting):
First we can see that there’s some server named DM3AAP24EBD72BD running a named instance of SQL Server (MKMASTR). It’s running version 15.0.2000, which is the RTM build of SQL Server 2019. The backup was created from a database named SSISDB, using the default collation (SQL_Latin1_General_CP1_CI_AS). That database was created at 2019-09-24 14:21:11.000 and the backup created just a few seconds later at 2019-09-24 14:21:11.000. Those timestamps are between the final Release Candidate was released (2019-08-29), but before the GA release (2019-08-29).
What I found really interesting was that Microsoft ships this database as a backup, rather than say, a BACPAC, or other collection of scripts. The creation & backup dates indicate that an automated process creates the database from scratch, then immediately backs it up to ship out to folks like us. I presume that this is all done as part of their build pipeline (the instance name seems to support this as well).
Personally, I think this is smart. Shipping a database backup is by far the easiest way to move a database around. Backups have the fewest moving parts, and fewest vectors for failure. Version compatibility is the only major portability issue–and that isn’t an issue for Microsoft since they are shipping the backup as part of the SQL Server installation media. (Sidenote: This is why I find the need to use BACPACs to move between an on prem install & Azure SQL DB very frustrating.)
For fun, I looked at the file location of the original database, too:
RESTORE FILELISTONLY FROM DISK = 'C:\MSSQL\Backup\SSISDBBackup.bak';
Those are some wild file paths–but they just confirm that the the database was created as part of a build pipeline.
s19sprobably refers to SQL Server 20190924_133725looks like a date/time. 09/24 matches up with the database creation date. 13:37:25 is probably the time the build started, about an hour before the SSISDB database was created.x64retaildescribes the flavor of SQL Server I’m using.mkmastr\databases\mkmastr.projappears to be the project file that makes the version of system databases that ship with the project.
What does it all mean?
Nothing really.
None of this stuff really matters for how you run your SQL Server or your SSISDB. A smaller takeaway is that if you do happen to get the obscure error above, it is an indication that SSIS is not installed on your database server–to fix it, you’ll need to install (or reinstall) that service.
If there’s anything “big” to learn, it’s going to be more about the complexity of build pipelines and shipping a product that needs to have a rock-solid install path without creating “install friction” where people need help before the product is even installed.