August 12, 2014 at 4:31 pm
We are running SQL Server 2012 on Windows 2008 Server. When I created the Integration Services Catalog (SSISDB) while deploying a package, I notices this process (creating an IS Catalog) appears to have created a database called SSISDB. Is this correct? It placed the data and log files for this database in F:\System\MSSQL11.MSSQLSERVER\MSSQL\DATA with the other system databases' data and log files.
1) Could I have placed these files in another location? While creating the IS Catalog, I did not see any dialog that would have allowed me to change the location of the database files. Is the location of the database files dictated by the model database?
2) If I backup the SSISDB Database, is this enough to recover all packages and everything required to execute them? Or would I still need to backup the IS Catalog and/or the packages at the file level? Just want to make sure I back up everything that is required to fully recover the packages and jobs (msdb backup).
Thanks, Kevin
August 13, 2014 at 12:45 am
The SSISDB is the database that contains all the objects for the SSIS catalog. So yes, it is perfectly normal that this database is created.
I am not sure how this database is created and indeed there is not much control over the creation.
You can move it though: Backup, Restore, and Move the SSIS Catalog
Backing up the SSISDB should be enough to recover everything. Be aware that this database is encrypted though.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2014 at 7:53 am
Fully agree with Koen. The DB is created in the default data/log locations, so easiest way to control where it is placed is to set these first. But not too hard to move afterwards, we've done it.
October 7, 2014 at 11:20 am
Mike Good (8/13/2014)
Fully agree with Koen. The DB is created in the default data/log locations, so easiest way to control where it is placed is to setthese first. But not too hard to move afterwards, we've done it.
The current values for 'Database default locations' are:
DATA = D:\MSSQL11\SCSM\MSSQLDATALOG = L:\MSSQL11\SCSM\MSSQLLOG
Yet when we run the Create SSIS Catalog process the SSISDB created by this is located on the C drive in the same location as the other system DB'.
Thoughts?
It looks like as if SSIS was created without any input form a DB Architect or admin. This default location and inability to specify an alternate DB owner sounds like the kind of thing a developer with some basic SQL Skills would do; the kind that would use a cursor for everything (regardless of whether its appropriate or not) simply because they don't understand the set based approach of a DB (nor do they normally want to know).
Kindest Regards,
Just say No to Facebook!October 7, 2014 at 12:51 pm
Tested this, and you're right, SSISDB does get created in same location as system DBs (not necessarily C: drive). .
Seems to me MS doesn't know whether SSISDB is a system DB or not. Or maybe they decided it was a system DB after the GUI was designed. It clearly shows up as a user DB in the GUI. And we were able to put it in an AG and synchronously mirror it (long ago)...shouldn't be able to do that with a system DB, I think? But back in 2012 when a CU failed to apply and took out our entire instance for a while, MS then told us "SSISDB is a system DB, cannot be in an AG!" So we removed it and have treated SSISDB as a system DB ever since.
If you look at it as a system DB, similar to msdb, then maybe the behavior makes more sense?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply